SELECT
/*
[NAME]
- HANA_SQL_ActiveStatements_Internal
[DESCRIPTION]
- Runtime information for active SQL statements
[SOURCE]
- SAP Note 1969700
[DETAILS AND RESTRICTIONS]
- Can only be executed with SAP_INTERNAL_HANA_SUPPORT role or as SYSTEM
- Not available for public use
[VALID FOR]
- Revisions: all
- Statistics server type: all
[SQL COMMAND VERSION]
- 2015/01/02: 1.0 (initial version)
- 2017/05/08: 1.6 (dedicated internal variante including ALLOCATED_NUMA_NODE)
[INVOLVED TABLES]
- M_SQL_PLAN_CACHE
- M_CONNECTIONS
[INPUT PARAMETERS]
- HOST
Host name
'saphana01' --> Specific host saphana01
'saphana%' --> All hosts starting with saphana
'%' --> All hosts
- PORT
Port number
'30007' --> Port 30007
'%03' --> All ports ending with '03'
'%' --> No restriction to ports
- SERVICE_NAME
Service name
'indexserver' --> Specific service indexserver
'%server' --> All services ending with 'server'
'%' --> All services
- NUMA_NODE
NUMA node
4 --> Display information for NUMA node 4
-1 --> No restriction related to NUMA node
- CONN_ID
Connection ID
330655 --> Connection ID 330655
-1 --> No connection ID restriction
- STATEMENT_HASH
Hash of SQL statement to be analyzed
'2e960d7535bf4134e2bd26b9d80bd4fa' --> SQL statement with hash
'2e960d7535bf4134e2bd26b9d80bd4fa'
'%' --> No statement hash restriction (only
possible if hash is not mandatory)
- SQL_PATTERN
Pattern for SQL text (case insensitive)
'INSERT%' --> SQL statements starting with INSERT
'%DBTABLOG%' --> SQL statements containing DBTABLOG
'%' --> All SQL statements
- MIN_MEM_USED_MB
Lower limit of memory usage of SQL statements
100 --> Display only SQL statements with a memory utilization of at
least 100 MB
-1 --> No restriction related to the memory utilization of SQL
statements
- MIN_EXEC_TIME_S
Lower limit for SQL statement execution time
20 --> Only display SQL statements with an execution time of at
least 20 seconds
-1 --> No restriction related to the execution time of SQL
statements
- SQL_TEXT_LENGTH
Maximum displayed size of SQL statement text
50 --> SQL text is truncated to 50 characters
-1 --> No SQL text size limitation
[OUTPUT PARAMETERS]
- HOST: Host name
- PORT: Port
- SERVICE: Service name
- NUMA_NODE: NUMA node
- CONN_ID: Connection ID
- STATEMENT_HASH: Statement hash
- STATEMENT_STATUS: Statement status
- EXECUTION_START: Start time of execution
- EXEC_TIME_MS: Execution time (ms)
- APPLICATION_SOURCE: Application source name
- USED_MEM_MB: Memory used by SQL statement (in MB), may only be populated
if memory_tracking is activated
- SQL_TEXT: SQL text
[EXAMPLE OUTPUT]
-----------------------------------------------------------------------------------
-----------------------------------------------------------------------------------
--------------------------------------------------------
|HOST |PORT |CONN_ID |STATEMENT_HASH |EXECUTION_START |
EXEC_TIME_MS|APPLICATION_SOURCE |USED_MEM_MB|SQL_TEXT
|
-----------------------------------------------------------------------------------
-----------------------------------------------------------------------------------
--------------------------------------------------------
|saphana06|30003| 928139|d19de40f66a00a30566b6e1b39e40a2d|2015/01/02 19:10:39,039|
631.45|GP4T95GIYPE9NBMMP4MP95RGV8A:3225| 47.22|SELECT * FROM "/BIC/PF_SKB1"
|
|saphana01|30003| 400368|d6fd6678833f9a2e25e7b53239c50e9a|2015/01/02 19:10:39,267|
403.89| | 0.00|call
_SYS_STATISTICS.STATISTICS_SCHEDULABLEWRAPPER('Timer', X, X, X, X) |
|saphana01|30003| 400368|db2a5d8b668a837677bb6946de2a8d76|2015/01/02 19:10:39,270|
400.42| | 0.08|{ call
"_SYS_STATISTICS"."COLLECTOR_HOST_BLOCKED_TRANSACTIONS" (X, X, X) } |
|saphana01|30003| 400368|83ac4bf74da990133f1c525d05f43714|2015/01/02 19:10:39,272|
398.47| | 0.20|INSERT ... FROM
"M_BLOCKED_TRANSACTIONS" AS "B" INNER JOIN "M_TRANSACTIONS" AS |
|saphana01|30003| 412023|d6fd6678833f9a2e25e7b53239c50e9a|2015/01/02 19:10:39,576|
94.42| | 0.00|call
_SYS_STATISTICS.STATISTICS_SCHEDULABLEWRAPPER('Timer', X, X, X, X) |
|saphana01|30003| 412023|7006fb197e11dee78456d5f36a522bc4|2015/01/02 19:10:39,580|
90.20| | 0.08|{ call
"_SYS_STATISTICS"."COLLECTOR_HOST_DELTA_MERGE_STATISTICS" (X, X, X) } |
|saphana01|30003| 412023|132f1674beaf346715112322487f430e|2015/01/02 19:10:39,583|
87.66| | 0.07|INSERT ... FROM
"SYS"."M_DELTA_MERGE_STATISTICS" WHERE "START_TIME" > IFNULL(( |
|saphana01|30003| 454956|0e30f2cd9cda54594a8c72afbb69d8fd|2015/01/02 19:10:39,627|
43.39|SAPLRSM1:65963 | 6.65|SELECT * FROM "RSICCONT" WHERE
"ICUBE" = X |
|saphana01|30003| 404257|262fba9d35f58599edae924d6c2bd8c8|2015/01/02 19:10:39,677|
0.00| | 0.06|INSERT ... FROM
"SYS"."M_WORKLOAD" |
-----------------------------------------------------------------------------------
-----------------------------------------------------------------------------------
--------------------------------------------------------
*/
'Only available SAP internally, see internal SAP Note 2082221.' INFORMATION
FROM
DUMMY
