Kernel profiler filtered by connection ID and statement hash as of HANA Version 2.0 SPS02 / SPS03

[semi_yoon]

The kernel profiler is a sampling profiler built into the SAP HANA database. The kernel profile collects, for example, information about frequent and/or expensive execution paths during query processing. [SAP HANA Administration Guide]

 

The filter application user and database user of kernel profile trace have been provided and utilized to narrow down the scope of trace. But it is hard to trace profile only for a specific scenario like an expensive query execution as HANA systems situation even application user or database user filter is set. In case of production systems of customers, kernel profile is recommended to trace while business idle time.

 

Therefore, HANA Development planned to support connection ID and statement hash filter from the requirement. It is possible to filter a kernel profile by connection ID as of SAP HANA2.0 SPS02 and by statement hash as of SAP HANA2.0 SPS03 [Table1].

 

[Table1. Filter options of kernel profile trace]

Option	Details
--ca <application_user_name>	Application user to be traced
--cs <database_user_name>	Database user to be traced
--cc <connection_id>	Connection ID to be traced (SAP HANA >= 2.00.020)
--ch <statement_hash>	Statement hash of database request to be traced (SAP HANA >= 2.00.030)

Note that the options are logically combined with ‘or’ and so recommended to trace as few as possible you should only specify the most restrictive filter criteria and not all filter criteria.

 

[Syntax of the command]

hdbcons -p <service_pid> "profiler start --cs <database_user_name> --ca <application_user_name> --cc <connection_id> --ch <statement_hash>”

The provided syntax is to enable kernel profile with filters specified by database user <database_user_name> and / or application user <application_user_name> and /or connection ID <connection_id> and /or statement hash <statement_hash>.

 

The filter connection ID and statement hash are not provided in Studio or Cockpit yet. Here provides an example by using hdbcons command line tool.

For example, it is to trace kernel profile for a specific query SELECT * FROM M_HOST_INFORMATION filtered by statement hash or connection ID.

 

In case of filtering by statement hash:

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

 

2. Clear profiler.

hdbcons -p <indexserver_pid> "profiler clear"

3. Start profiler filtered by statement hash.

hdbcons -p <indexserver_pid> "profiler start --ch 58ecbac56fda3e8a8b84dc2d8ebf3988"

4. 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;

5. Stop profiler

hdbcons -p <indexserver_pid> "profiler stop"

6. Print as a file

hdbcons -p <indexserver_pid> "profiler print -o <path>/cpu.dot,<path>/wait.dot"

 

In case of filtering by connection ID:

1. You can find connection ID by running the SQL below in HANA studio. here, for example, is connection ID 300123.

SELECT CURRENT_CONNECTION "current connection" FROM DUMMY;

current connection

300123

 

2. Clear profiler.

hdbcons -p <indexserver_pid> "profiler clear"

3. Start profiler filtered by connection ID.

hdbcons -p <indexserver_pid> "profiler start --cc 300123"

4. Reproduce by running the query in the same session of studio as where the step #1 was run to get the connection ID. Otherwise nothing will be sampled since the filter value is mismatched.

SELECT * FROM M_HOST_INFORMATION;

5. Stop profiler

hdbcons -p <indexserver_pid> "profiler stop"

6. Print as a file

hdbcons -p <indexserver_pid> "profiler print -o <path>/cpu.dot,<path>/wait.dot"

 

From the example, only the transaction with the given statement hash / connection ID, if no further transaction is run within the session 300123, will be traced.

I hope to share the usage of the newly supported filters and to be utilized extensively.

You can see similar topic from my blog “User-specific Database Traces filtered by connection_id and statement_hash as of HANA Version 2.0 SP...”.