CODEWITHSUNDEEP
sqlhana
Nishanth Reddy
Nishanth Reddy

Reputation: 609

Extracting query-history in SAP HANA

I have been using SAP HANA db instance, and have been running several queries on this. I need to extract the query-history, preferably from a system-table or elsewhere. Please let me know if this is possible and any pointers to achieve it, if possible.

Upvotes: 4

Views: 17689

Answers (2)

pawelek69420
pawelek69420

Reputation: 367

  1. you can trace DDL statements by querying M_EXECUTED_STATEMENTS view, which is located in SYS schema, note that your used needs to have select permission on this view to be able to query it
  2. you can enable dumping executed SQL statements into a flatfile in hana studio and then either use grep on those flatfiles in bash or query the M_TRACEFILE_CONTENTS view (once again from SYS schema)
  3. Note that trace files are very messy and you need proper grep skills to be able to extract the executed SQL statements from it - I didn't figure out yet how to configure HANA database to generate pretty trace files

handy grep commands forc finding trace files:

# find / -name *.trc # finding trace files
$ grep -n -B 5 -A 1 '^.*select.*$' flatfile # displays matches in a flatfile with context and line numbers ( surrounding five lines above and 1 line below ) 
$ grep -n -B 5 -A 1 '^.*select\|84443781510009.*$' flatfile # <- or statement for keywords with \| characters

  1. hana studio allows you to apply configuration to tracing behavior (tracing only for a given user, object etc) it is best to change this behavior from hana studio / hdbsql level

  2. As mentioned earlier M_SQL_PLAN_CACHE and M_SQL_PLAN_CACHE_RESET system views allow for handy querying and retrieving executed sql statements as well as their statistics.

Upvotes: 0

Timo D
Timo D

Reputation: 1803

If you want a detailed history of executed queries, you need to activate the HANA SQL trace. You can find more information in the HANA documentation. Of course, this will not work retrospectively. So you will have to activate the trace first and then run the queries that you want to look at.

Additionally, the SQL Plan Cache provides aggregated information about past queries. It is aggregated by the prepared statements and provides runtime information like average execution time and result size. The monitoring view for this is SYS.M_SQL_PLAN_CACHE.

Upvotes: 7

Related Questions