Is it possible to retrieve HANA trace files externally?

[patricksteffens]

By "externally", I mean from external sites such as Python of Java code or via SQL.

[lbreddemann · Accepted solution]

To implement client-side performance tracing, you can either use the functionality provided by any of the persistence framework, e.g. hibernate or you can instrument your code yourself.

The concept here is not too complex: take the time before the DB call, perform the call, take the DB again and compute the difference between both times. This information (together with the code-location, bind parameters, SQL statement text, etc.) can be written into a log. For JAVA there are plenty of logger-libraries available (J4Log, perf4log, ...) and I assume that's also the case for python.

Concerning the difference between SQL Plan Cache time and SERVER PROCESSING TIME my guess(!) would be that these cover slightly different parts of the query processing (e.g. incl. data fetching vs. not including data fetching). Also be aware that SQL Plan Cache aggregates runtime information for identical statement executions.

Looking for the "real" processing time of a query on the server is a stochastic analysis over multiple if not many runs. As this is a dynamic shared system, the performance of ad-hoc queries against changing data cannot be deterministic - but predictable within certain boundaries. That's what you try to establish during system sizing: the common response times of your important application interactions during a certain, hopefully realistic, workload.

While I guess that there are plenty more questions on your side, let me stop this discussion at this point. The concept of the "answers" site of the SAP Community is meant to deliver a question & answers platform and makes it hard and unrewarding to have longer discussions. This design had been chosen on purpose by the SAP Community team; needless to say, I liked the old discussion forums better.

Anyhow, at this point I can only recommend to come up with distinct specific questions and post them separately. That, in theory, should increase the chance of getting at least some of them answered, compared to one big problem query, to which nobody has the full answer and therefore does not reply.

Cheers,

Lars

[BJarkowski]

Check out following SAP Note:

1732157 - Collecting diagnosis information for SAP HANA

[patricksteffens]

Thanks for the hint. It however only explains how to download the iagnosis files using HANA means (e.g. HANA Studio). What I meant by "externally" is downloading a certain trace from external sites such as Python scripts and Java programs.

[BJarkowski]

What about the parts of the note where is written you can use a python script to download the data?

python fullSystemInfoDump.py

Or creating the dumps through stored procedures?

[patricksteffens]

Running a python script which is lying on the database is not exactly what I'm looking for. I was hoping that I can keep everything in a single program not running on the HANA server.

Do you have any information on how to call the stored procedures to create the dumps? Documentation is only mentioning that one need privileges to execute them.

[BJarkowski]

What trace files are you looking for? Database logs?

[patricksteffens]

Specifically, I am calling procedures from Python and/or Java (not yet sure what to eventually use) and want to measure the call's execution time on the database. So I was wondering whether I can try to parse the sql trace for example.

[BJarkowski]

Isn't it saved on local drive? Then you could connect through ssh to the host and retrieve the log file..

https://help.sap.com/viewer/bed8c14f9f024763b0777aa72b5436f6/2.0.02/en-US/bedc9668bb5710149d56d29fe2...

Check the file name parameter.

[lbreddemann]

Hi Patrick

for client-side tracing you basically have two options:

• use the JDBC and SQLDBC tracing, which is a feature provided by the SAP HANA client software
• implement call tracing in your application

The latter would be the option I pick if I wanted to provide the application with general performance metrics and instrumentation. Frameworks like hibernate and SAP NetWeaver use this approach, which is useful to decide whether or not DB calls are performance issues.

The driver based tracing is an option that is used most often to analyse issues when there is a concrete suspicion that something is not right with the client-server communication.

To gather more insight into the processing of database calls, the server side monitoring options like "Expensive Statement Trace" and the SQL Cache are usually good starting points to find potentially problematic statements.

However, there is no permanent trace enabled by default that would trace all aspects of all statement executions all the time. The idea with SAP HANA rather is to generally monitor response times (either from each client or from the server point of view) and in case these get worse than your threshold to investigate deeper.

To answer the more general question of trace file access: SAP HANA exposes its trace files via system views M_TRACEFILES and M_TRACEFILE_CONTENTS. Be aware that these are the trace files for the server processes and not directly related to specific database calls.

[patricksteffens]

Thanks for the insights, now I have some more ideas.

How would I be able to implement call tracing manually in an external client? Is there an API that assists me? Or can this be done with JDBC for instance?

I activated the SQLDBC trace which is then generated when I execute my Python script. The file contains an entry "SERVER PROCESSING TIME" which however gives different numbers compared to the execution time stated in the SQL Plan Cache. So I'm wondering: does this entry really tell me the actually processing type of the call on the database server??

[patricksteffens]

That's definitely an idea, however cumbersome to code and I expect it to be quite slow 😕