Step-by-step guide to analyze HANA SQL traces in the local machine.
From my working experience, I can say, that reading the HANA SQL traces could be very time-consuming because generated trace .py files do not have any short summary, so finding the most time-consuming events it could take an eternity, especially if you have multiple HANA-nodes, each of them generating different .py file.
You basically need to check all the files for all the statements ran, and in my case, it’s a BW/4 HANA system which generates many SQL’s even for a single query runtime:
Unfortunately, ABAP-trace catching the SQL’s would be not enough in my scenario, since the typical SQL statement in it would look like this:
CALL SYS.TREXviaDBSLWithParameter ( ‘asxml/olapCellSearch;obj_name=SAPRBW:0BW:BIA:Z137;query_id=8CO8ZM2PILHD4GBZI00ME07BY:M137’ , <BLOB> , <BLOB> , ‘ROUTE_BY_TABLES( “SAPRBW”.”0BW:BIA:Z137″ )’ )
SAP proposing a tool for it, a note 2412519 – FAQ: SAP HANA SQL Trace Analyzer, and if you have access to the HANA OS it should work out of the box, but if you don’t, you still can use it in the local machine with downloaded traces. However, I did not find a comprehensive guide on how to properly use it.
I’m not familiar with Python, so, it took quite some time to make it work, plus help from a Python expert has been required.
In the text of the Note there is some important details missing, which could take a lot of time to figure-out how to use the tool, especially if you are not familiar with Python, like I am.
First of all, despite it is said than Python 3 is supported, actually the attachment sqlTraceAnalyzer_v2.40.zip from the Note works only with Python version 2. To run in the local machine, you need a Python environment.
If you don’t have any Python environment, you need to install it, it could be done with Windows Subsystem for Linux, or directly with installing the python version 2.
In our scenario we used Ubuntu installed on Windows Subsystem for Linux (WSL) as per https://ubuntu.com/tutorials/install-ubuntu-on-wsl2-on-windows-11-with-gui-support#1-overview
This way has been chosen because it simplifies working with Python, rather than directly installing it to Windows via https://www.python.org/download/releases/2.7/
After installing the Ubuntu you need to specify Pythonpath;
In the note, the following command is recommended:
“export PYTHONPATH= ~/sta:$PYTHONPATH”
which is a mistype, it should be like that, there is extra space which is not needed:
Then you need to download the .zip file from the attachment of the note 2412519 – FAQ: SAP HANA SQL Trace Analyzer, and check the content, in the time of creating the post, the following error appeared in the runtime:
save it and update the archive
then, you need to copy the file into the pythonpath which has been created before:
cp sqlTraceAnalyzer_v2.40.zip ~/sta/sqlTraceAnalyzer_v2.40.zip
the next step is to unzip the object:
if there is an error with unzipping, please run the command:
sudo apt-get install unzip
and then it’s ready to run, you need to go to the folder with the unzipped directory using the command:
and then you are ready to run the with the e.g. following command
python2 sqlTraceAnalyzer.py /mnt/c/Users/…/Downloads/sqltrace_FG6 _35003_000.py
keep in mind that you need to change a backslash used in Windows to a Backslash used in Ubuntu.
The generated result of the trace summary you will find in the text files in the same folder where your downloaded trace persists.
Hope SAP will come in the near future with a detailed guide of the tool usage, and maybe create some tool with GUI, which will help in the analysis of the traces.