Step-by-step guide to analyze HANA SQL traces in the local machine.

[AndreyF]

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:

"export PYTHONPATH=~/sta:$PYTHONPATH"

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:

To fix the issue to be able to run it, you need to open the file SQLTraceAnalyzer.py and change in the line 37 from comma to dot

 

except Exception.e:

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:

unzip sqlTraceAnalyzer_v2.40.zip,

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:

cd sqlTraceAnalyzer_v2.40/

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.

[JonasH]

Dear @AndreyF ,
thank you very much for your detailed instructions.

Currently the Note 2412519 comes with Version 4 of the Tool. Unfortunately the ZIP-file only contains a python script but it's lacking a separate file that contains the necessary (non-public?) modules:

Following Modules are missing and they cannot be installed via PIP - so they must be provided by SAP:

Following error occurs when trying to run the script:

 

You wouldn't still have a copy of the Version V2.4 which you used for your how-to, would you? Unfortunately the SAP Note 2412519 only contains the most recent version V4.

Thank you so much in advance!

Kind regards,
Jonas

[AndreyF]

hi @JonasH 

i shared it in the link

https://drive.google.com/file/d/13pCgSRIkqPnp2df3iODhuRJ7wGzsxjZe/view?usp=sharing

hope that works for you.

Kind regards,

Andrey Farzatinov

[JonasH]

Hi @AndreyF,
thank you so much for your swift help. The missing module files did the trick.

It's running now (on Windows). 

Kind regards,
Jonas