User-specific Database Traces filtered by connection_id and statement_hash as of HANA Version 2.0 SPS03

HANA has the capability to create user specific trace files. Such a trace profile can be configured that only specific traces are collected for a specified database or application user [SAP HANA Administration Guide].

Starting with SAP HANA 2.0 SPS 03 it is possible to filter a user-specific database trace by connection ID and statement hash.

Even though it is filtered by database or application user it produces bunch of traces. so it is hard to narrow down only for a specific reproducible scenario like a query execution.

The filter connection_id and statement_hash are not provided in Studio or Cockpit yet. Here provides an example by using the commands to set the parameters configuration of trace profile [Table1].

[Table1. INI Parameters for trace profile]

* Note with SAP HANA 2.00.030 to 2.00.031 the statement_hash filter cannot be combined with any other filter condition, otherwise nothing will be traced.

[Syntax of the command]

-- to enable traces
ALTER SYSTEM ALTER CONFIGURATION ('global.ini','SYSTEM') 
SET 
('traceprofile_<context>','application_user') = '<application user>', 
('traceprofile_<context>','sql_user') = '<database user>', 
('traceprofile_<context>','connection_id') = '<connection id>',
('traceprofile_<context>','statement_hash') = '<statement_hash of DB>', 
('traceprofile_<context>', '<component1>') = '<trace level>', 
('traceprofile_<context>', '<component2>') = '<trace level>' 
with reconfigure;

-- to disable traces
ALTER SYSTEM ALTER CONFIGURATION ('global.ini','SYSTEM') 
UNSET ('traceprofile_<context>') with reconfigure;

Example using statement_hash filter

For example, it is to trace a specific query SELECT * FROM M_HOST_INFORMATION filtered by statement_hash as debug level for ‘transaction’ component.

1. Find statement hash for the SQL from m_sql_plan_cache. of course, the SQL has been run before searching from m_sql_plan_cache).

select statement_string, statement_hash from m_sql_plan_cache where statement_string like '%M_HOST_INFORMATION%';

2. Run the command to enable trace profile with ‘mytrace’ context and statement_hash filter.

ALTER SYSTEM ALTER CONFIGURATION ('global.ini','SYSTEM') 
SET 
('traceprofile_mytrace','statement_hash') = '58ecbac56fda3e8a8b84dc2d8ebf3988', 
('traceprofile_mytrace', 'transaction') = 'debug' 
with reconfigure;

3. Reproduce by running the query in studio directly or from application side if it can be reproduced from application side.

SELECT * FROM M_HOST_INFORMATION;

4. Run the command to disable the trace profile

ALTER SYSTEM ALTER CONFIGURATION ('global.ini','SYSTEM') 
UNSET ('traceprofile_mytrace') with reconfigure;

5. See trace file having the trace profile context in the file name like <service_name>_<host_name>.<port_number>_mytrace.trc

Example using trace profile session variable

As of SAP HANA 1.0 SPS 12 it is possible to trace via session variable. It is an option to trace specific scenario in lower revisions than HANA 2.0 SPS 03.

Followings are the steps in case of same example above.

1. Run the command to enable trace profile with ‘mytrace’ context.

ALTER SYSTEM ALTER CONFIGURATION ('global.ini','SYSTEM') SET ('traceprofile_mytrace', 'transaction') = 'debug' with reconfigure;

2. Set session variable for the trace profile and reproduce.
   • If reproduced by running the query in the same session

--set session variable.
SET SESSION 'TRACEPROFILE'='mytrace';  
-- run the query in the session where it is to set the session variable.
SELECT * FROM M_HOST_INFORMATION;  

• If reproduced by running the query in other session and the session id (connection id) is identified. Here is for example session id 300123.

--set session variable for session id 300123.
ALTER SYSTEM ALTER SESSION 300123 SET 'TRACEPROFILE'='mytrace'; 

3. Run the command to disable the trace profile

ALTER SYSTEM ALTER CONFIGURATION ('global.ini','SYSTEM') UNSET ('traceprofile_mytrace') with reconfigure;

4. See trace file having the trace profile context in the file name like <service_name>_<host_name>.<port_number>_mytrace.trc

You can see similar topic from my blog Kernel profiler filtered by connection ID and statement hash as of HANA Version 2.0 SPS02 / SPS03