Technical Articles
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]
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.
- 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 |
- 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;
- 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;
- Run the command to disable the trace profile
ALTER SYSTEM ALTER CONFIGURATION ('global.ini','SYSTEM')
UNSET ('traceprofile_mytrace') with reconfigure;
- 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.
- Run the command to enable trace profile with ‘mytrace’ context.
ALTER SYSTEM ALTER CONFIGURATION ('global.ini','SYSTEM') SET ('traceprofile_mytrace', 'transaction') = 'debug' with reconfigure;
- 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';
- Run the command to disable the trace profile
ALTER SYSTEM ALTER CONFIGURATION ('global.ini','SYSTEM') UNSET ('traceprofile_mytrace') with reconfigure;
- 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
hi SeMi,
Thanks for meaningful information.
I believe the same parameters can be set for indexserver.ini. If I'm wrong please explain the difference.
Best regards,
Nha
Hi Nha,
if you want to collect user-specific trace only for indexserver it is possible by using the command for example.
ALTER SYSTEM ALTER CONFIGURATION ('global.ini','SYSTEM') SET
('traceprofile_mytrace','statement_hash') = '58ecbac56fda3e8a8b84dc2d8ebf3988'
with reconfigure;
ALTER SYSTEM ALTER CONFIGURATION ('indexserver.ini','SYSTEM') SET
('traceprofile_mytrace', 'transaction') = 'debug'
with reconfigure;
Regards,
Semi
Hi SeMi,
Yes, I was thinking of having the trace on for indexserver. When I want to collect the trace for all services I'll use global.ini parameters. Thanks.
FYI, I've tried below alter statements and got same trace info:
+ set statement_hash filter for global.ini
ALTER SYSTEM ALTER CONFIGURATION ('global.ini','SYSTEM') SET
('traceprofile_mytrace','statement_hash') = '58ecbac56fda3e8a8b84dc2d8ebf3988'
with reconfigure;
ALTER SYSTEM ALTER CONFIGURATION ('indexserver.ini','SYSTEM') SET
('traceprofile_mytrace', 'transaction') = 'debug'
with reconfigure;
+ set statement_hash filter for indexserver.ini
ALTER SYSTEM ALTER CONFIGURATION ('indexserver.ini','SYSTEM') SET
('traceprofile_mytrace','statement_hash') = '58ecbac56fda3e8a8b84dc2d8ebf3988'
with reconfigure;
ALTER SYSTEM ALTER CONFIGURATION ('indexserver.ini','SYSTEM') SET
('traceprofile_mytrace', 'transaction') = 'debug'
with reconfigure;
Many thanks,
Nha