Hello and welcome to the SAP HANA Academy.
The topic of this video tutorial series is SAP HANA Administration
and in this video we will discuss the SAP HANA SQL Trace Analyzer.
This video has been recorded on release SAP HANA 2.0 SPS 03 (April 2018).
For earlier or later releases, please check the playlist on the SAP HANA Academy.
Hi, I am Denys van Kempen.
In the previous video about the SQL trace topic, we looked at the trace output and observed
that it is very verbose, there is a lot of data, and that it is not all that easy to
distill any information out of it. We also mentioned that SQL Trace is mostly
used in the context of support cases (advanced debugging).
Now, there is a tool, initially only internally available, written in the Python script language,
which makes it a bit easier to analyze the output: SQL Trace Analyzer.
It returns an overview of the most resource-intensive statements, tables accessed, statistics on
different statement types and statistics on transactions executed.
Here I am connect with a PuTTY terminal to an SAP HANA system. I am connected with the
operating system administration account, +adm, here ha0adm,
which will have setup the environment properly for us.
You can enter the command 'python -- version', for example, and it should return the version.
The way to run the command is: python .
The script files are in the python_support directory below exe.
So, from /usr/sap/SID/HDB instance number which should be the default directory after
logon with the sidadm account, you just go down exe and then python_support
If you enter ‘python sql’ and then Tab, you can see that there are actually a number
of Python scripts in this toolbox.
If we then enter ‘python sqlTraceAnalyzer.py’ the script prints out usage information.
There are quite a number of options, but lets just start with providing a trace file.
Let’s change to the location where trace files are stored, by default.
Recall that when enabling SQL trace, you can specify where you want the file to be stored.
Here, I am using the default location.
So, we need to go back two levels and then navigate down the hostname directory and then trace
If we “ls” the contents of that directory, we get all the trace and log files of the instance.
For the tenant databases, we need to go down one level more. Here, DB, that’s for tenant, HA0.
If we then “ls”, we only get the trace files for this tenant.
There are a couple of files generated by indexserver, the actual database engine.
and here we have apple.py and pumpkin.py, which we generated in the previous video.
Let’s run the trace analyzer on apple.py.
Enter python, then we need to go up three levels and go down exe/python support, sqltraceAnalyzer
and then the name of the trace file.
And here we have the output
Let’s scroll a little bit up
First, we get the version of SQL Trace Analyzer, 2.29
Then an overview.
What I did, in the previous video, is run a bit of script with an ALTER SYSTEM to enable
sql trace, then a SELECT ‘Hello World’ FROM dummy and then an ALTER SYSTEM to disable sql trace.
We can see that in fact, 3 SQL statements were executed, 2 different statement,
so one was executed twice.
The SQL time, that’s the time HANA needed to parse the statement, check the syntax and
semantics, generate the execution plan, and so on, all the things a database engine does.
The total fetch time, that’s time it took to get the actual result set, in 1/1000 of
a second and elapsed time, in seconds.
Statement types are provided and then we get the top 20 statements.
SELECT CURRENT_SCHEMA FROM DUMMY and SELECT ‘Hello World’ .
CURRENT_SCHEMA was executed by the tool, we used the SQL Console of the SAP HANA Database
Explorer, and not by us.
This is typically always the case, this type of SQL being executed in the background by
the tool for its own purposes.
The table shows the time it took, in miliseconds, as percentage of total, the number of times,
2 apparently, the fastest and slowest, in microseconds now, memory footprint, min, max, and average.
Finally, we get the top 10 tables for SELECT, INSERT and UPDATE.
OK
Let’s try another trace file.
I have used HANA studio to enable SQL Trace.
Trace Level All with a filter on the database user, User1.
We discussed the different filters in the previous video.
Then here we have our script.
Creating a table, inserting some data, selecting a bit, update, delete, ending with a drop.
And here have our python command, same as beforewith the output.
If we scroll up a bit, we count 14 statements, 9 of them unique.
7 microseconds SQL time, 1 microsecond fetch time. 1 second elapsed time.
Then we get to the statements, you can see that we have some internal SQL going in
SELECT ‘PING’ FROM SYS.DUMMY, SELECT * FROM SYS.M_FEATURES
Both were executed 3 times and we see them listed here in the tables as well.
This type of internal SQL, as mentioned, is
executed by the tool, here HANA studio, and each tool will run different statements.
OK - when we list of the contents of the directory with a filter on the name of the file, we
can see that actually a number of output files have been generated by sqlTraceAnalyzer.
If we look at the statements, we see the different statement back again but with the raw statistics.
If we look at the tables, we see the tables being used.
You can think of these files as the input, the intermediary stage, for the SQL Trace report
Now, there is a lot more to SQL Trace Analyzer then I can cover here.
As mentioned, it is a tool primarily used by SAP Support and sqlTraceAnalyzer was initially
only internally available for that purpose.
However, if you are curious or would like to analyze some odd behaviour yourself, you
may find SQL Trace and the Analyzer actually quite helpful.
Take a look at SAP Note 2412519 - FAQ SAP HANA SQL Trace Analyzer for a more comprehensive
overview of the tool.
OK - That’s it, so much for SAP HANA SQL Trace Analyzer
Thanks for watching.
You can find more video tutorials on our YouTube channel.
If you would like to be informed about new video tutorials, please subscribe to our channel.
You can connect with us on LinkedIn or follow us on Twitter, as well for updates, and if
you are watching this video on YouTube, do not hesitate to leave your comments to the
video page and, if you like, give us your vote on this video.
Thank you for watching.