Skip to Content

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]

Parameter Details
global.ini -> [traceprofile_<context>] -> <component> Component to be traced
global.ini -> [traceprofile_<context>] -> application_user Application user to be traced
global.ini -> [traceprofile_<context>] -> sql_user Database user to be traced
global.ini -> [traceprofile_<context>] -> connection_id Connection ID to be traced (SAP HANA >= 2.00.030)
global.ini -> [traceprofile_<context>] -> statement_hash Statement hash of database request to be traced (SAP HANA >= 2.00.030)

* 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%';
STATEMENT_STRING STATEMENT_HASH
SELECT * FROM M_HOST_INFORMATION 58ecbac56fda3e8a8b84dc2d8ebf3988
  1. 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;
  1. 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;
  1. Run the command to disable the trace profile
ALTER SYSTEM ALTER CONFIGURATION ('global.ini','SYSTEM') 
UNSET ('traceprofile_mytrace') with reconfigure;
  1. 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;
  1. 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'; 
  1. Run the command to disable the trace profile
ALTER SYSTEM ALTER CONFIGURATION ('global.ini','SYSTEM') UNSET ('traceprofile_mytrace') with reconfigure;
  1. See trace file having the trace profile context in the file name like <service_name>_<host_name>.<port_number>_mytrace.trc
To report this post you need to login first.

1 Comment

You must be Logged on to comment or reply to a post.

  1. Jan-Markus Gomer

    Hi Se Mi,

    thank you for posting about this interesting topic.

     

    I think one important point is missing: If you combine a filter for trace profiles it is evaluating the filter condition in the following way:
    (sql_user AND application_user) OR statement_hash OR connection_id

    Hence, to trace as few as possible you should only specify the most restrictive filter criteria and not all filter criteria.

     

    Cheers,
    Jan

     

    (0) 

Leave a Reply