Showing historical SQL statements run

[patrickbachmann]

Hi folks,

In the System Information tab of default administration screen there is a 'connections' table I find very useful where I can see currently running SQL statements against the HANA db and the user that is executing the statement.  My question; is there a place where I can see historical SQL statements?  ie: a statement that ran to completion an hour ago?

Thanks,

-Patrick

[lbreddemann · Accepted solution]

Hey Patrick,

well, "historic" SQL statements would be really nice.

We're not just there yet.

Meanwhile, we do have a one-hour window, that allows us to see the statements of the past 60 minutes.

Check the

[Adminstration Perspective] -> [Performance] -> [SQL Plan Cache]

table to access these information.

Alternatively you could simply query the m_sql_plan_cache system view to read the same data via SQL.

Another option is to turn on the "expensive statement trace" - this doesn't age out and also collects some useful information about long running statements.

Cheers,

Lars

[patrickbachmann · Accepted solution]

Lars, in the SQL Plan Cache tab there are no entries, it's blank.  Even if I go and run some things.  The m_sql_plan_cache is also cool but no datestamp.  But why would SQL Plan Cache be blank?

Thanks

[patrickbachmann · Accepted solution]

Lars I just realized I can see this information in our dev box but not in our production.  As far as I can tell I have the same roles in each system (ie: MONITORING).  Do you know what role is required to see SQL Plan Cache?  Thanks

[patrickbachmann · Accepted solution]

Lars I just figured out that the SQL Plan Cache tab in the performance monitoring is the same exact table as the m_sql_plan_cache.  And I did find a 'last executed' timestamp which is exactly what i need.  Strangely however, I can run SQL statement to view m_sql_plan_cache however it does not display from my performance monitoring/SQL Plan Cache tab.  That method is blank everytime in production.  But alas the SQL statement is all I need but it is strange behavior.

[lbreddemann · Accepted solution]

Patrick,

this is not about an authorization, but whether the collection of SQL has been activated or not.

In the very same screen/window/information area (whatever you want to call this part of the screen where you usually find the shared cursor information) there is a little, tiny, small and well hidden little icon on the right side (something with a gear wheel).

Click on this and a dialogue window occurs - just to present you with one (= 1 !) checkbox (don't guess how I like this UI design...) to activate the collection of the shared cursor statistics.

Click this on and you should see the data as soon as you run some SQL

And sure enough, all your applications and SQL queries will suffer the worst performance ever, simply because you ticket this on *hahahahahaha*.

(just kiddin'!)

cheers,

Lars

[patrickbachmann · Accepted solution]

Lars thanks for the tip.  I didn't know you were such a funny guy!  Perhaps I shall call you fungi.  haha.  By the way I decided to just write a simple SQL to order the SQL Plan Cache by Last Execution time and it's great, I can see most recent SQL now (although the first few lines always seem to be system user which I disregard - guess I could filter that out of my query if I wanted);

select * from M_SQL_PLAN_CACHE ORDER BY LAST_EXECUTION_TIMESTAMP DESC

Thanks for leading me to the information I was looking for. 

[lbreddemann · Accepted solution]

Fungi???

BTW: I was wrong about the little icon as I figured out today when I opened the HANA Studio again.

There's a little "pencil" icon for this setting instead:

The other icon is useful nevertheless - you can configure the layout of the grid with it.

cheers, Lars

[patrickbachmann · Accepted solution]

lol.  Ok fun guy.    Thanks for clarifying that.  The pencil is dimmed out for me.

[lbreddemann · Accepted solution]

The pencil is dimmed out, because the user account you're using the Administration perspective with doesn't have the ADMIN INIFILE system privilege.

The setting for collecting the SQL plan cache statistics is handled by the indexserver.ini parameter file:

 Name                              | Default
 indexserver.ini                   |
   sql                             |
     plan_cache_statistics_enabled | true
     plan_cache_size               | 268435456
     plan_cache_enabled            | True

Cheers,

Lars

[patrickbachmann · Accepted solution]

In my dev box under SQL Plan Cache tab I see visible rows 2208/2208 and the pencil is dimmed out.

In my prod box under SQL Plan Cache tab I see zero rows and the pencil is dimmed out.

I do not have ADMIN INIFILE system privilege in either system.  Yet in dev I can see entries and prod I do not.  Yet interestingly I can simply write SQL to see the cache in production so it's not a hindrance.

-Patrick

[lbreddemann · Accepted solution]

What revisions you're working on?

Are you using the SUPPORT role in any of those systems?

Finally: the pencil icon is there just to allow the switch on/off of the data collection.

As long as you've got CATALOG READ privileges, you should always be able to review the data, once it's collected.

[patrickbachmann · Accepted solution]

Ahhh a light bulb just went off in my head!!  Currently our dev box is SP5 and production is SP4 so that is probably it.  Hope to upgrade prod soon.

Thanks Lars.