Scribd
Upload
6 views5 pages

HANA Threads Overview and Statistics

The document provides an SQL command for retrieving thread information from SAP HANA, detailing input parameters such as HOST, PORT, and SERVICE_NAME, as well as options for aggregation and sorting. It specifies output parameters including thread state, duration, and CPU time, along with an example output format. The command is valid for all revisions and statistics server types, and it references SAP Note 1969700.

Uploaded by

87matteocasanova
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as TXT, PDF, TXT or read online on Scribd
6 views5 pages

HANA Threads Overview and Statistics

The document provides an SQL command for retrieving thread information from SAP HANA, detailing input parameters such as HOST, PORT, and SERVICE_NAME, as well as options for aggregation and sorting. It specifies output parameters including thread state, duration, and CPU time, along with an example output format. The command is valid for all revisions and statistics server types, and it references SAP Note 1969700.

Uploaded by

87matteocasanova
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as TXT, PDF, TXT or read online on Scribd

SELECT

/*

[NAME]

- HANA_Threads_CurrentThreads

[DESCRIPTION]

- Thread information

[SOURCE]

- SAP Note 1969700

[DETAILS AND RESTRICTIONS]

[VALID FOR]

- Revisions: all
- Statistics server type: all

[SQL COMMAND VERSION]

- 2014/03/06: 1.0 (initial version)

[INVOLVED TABLES]

- M_SERVICE_THREADS

[INPUT PARAMETERS]

- HOST

Host name

'saphana01' --> Specic 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

- ONLY_ACTIVE_THREADS
Possibility to restrict output to active threads

'X' --> Only show threads that are currently active


' ' --> Show all threads

- CONN_ID

Connection ID

330655 --> Connection ID 330655


-1 --> No connection ID restriction

- AGGREGATION_TYPE

Type of aggregation (e.g. average, sum, maximum)

'AVG' --> Average value


'SUM' --> Total value
'MAX' --> Maximum value

- AGGREGATE_BY

Aggregation criteria (possible values can be found in comment)

'THREAD_ID' --> Aggregation by thread ID


'HOST, PORT' --> Aggregation by host and port
'NONE' --> No aggregation

- ORDER_BY

Sort criteria (available values are provided in comment)

'THREADS' --> Sorting by number of threads (useful when aggregating)


'CONNECTION' --> Sorting by connection ID

[OUTPUT PARAMETERS]

- HOST: Host name


- PORT: Port
- SERVICE: Service name
- NUM: Number of threads (1 if no aggregation is done)
- CONN_ID: Connection ID
- THREAD_ID: Thread ID
- THREAD_TYPE: Thread type
- THREAD_STATE: Thread state
- ACTIVE: 'TRUE' if thread is active, otherwise false
- APP_USER: Application user name
- DURATION_S: Thread duration (s)
- CPU_TIME_S: Thread CPU consumption (s)

[EXAMPLE OUTPUT]

-----------------------------------------------------------------------------------
----------------------------------------------
|HOST |SERVICE_NAME |NUM |CONN_ID |THREAD_ID|THREAD_TYPE |
THREAD_STATE|ACTIVE|APP_USER |DURATION_S|CPU_TIME_S|
-----------------------------------------------------------------------------------
----------------------------------------------
|saphana20|preprocessor | 1| 338803| 24424|Request |any
|TRUE |ZSAP_10 | 0.00| 0.00|
|saphana20|indexserver | 1| 338803| 24449|Request |any
|TRUE |ZSAP_10 | 0.00| 0.00|
|saphana20|nameserver | 1| 338803| 28364|Request |any
|TRUE |ZSAP_10 | 0.00| 0.00|
|saphana21|indexserver | 1| 338803| 52999|SqlExecutor |any
|TRUE |ZSAP_10 | 0.03| 0.00|
|saphana21|indexserver | 1| 300257| 53005|SqlExecutor |any
|TRUE |RT_VENTES | 0.00| 0.00|
|saphana21|indexserver | 1| 301432| 53011|SqlExecutor |any
|TRUE |RT_LOG | 0.00| 0.00|
|saphana21|indexserver | 1| 301551| 53024|SqlExecutor |any
|TRUE |RT_VENTES | 0.00| 0.00|
|saphana21|indexserver | 1| 300290| 53508|SqlExecutor |any
|TRUE |RT_PUSHW045| 0.00| 0.00|
|saphana21|indexserver | 1| 300776| 65701|SqlExecutor |any
|TRUE |RT_PUSHW045| 0.00| 0.00|
|saphana20|compileserver | 1| 338803| 93615|Request |any
|TRUE |ZSAP_10 | 0.00| 0.00|
|saphana21|nameserver | 1| 338803| 102512|Request |any
|TRUE |ZSAP_10 | 0.00| 0.00|
-----------------------------------------------------------------------------------
----------------------------------------------

*/

HOST,
LPAD(PORT, 5) PORT,
SERVICE_NAME SERVICE,
LPAD(NUM, 5) NUM,
CONN_ID,
LPAD(THREAD_ID, 9) THREAD_ID,
THREAD_TYPE,
THREAD_STATE,
ACTIVE,
APP_USER,
DURATION_S,
CPU_TIME_S
FROM
( SELECT
CASE WHEN BI.AGGREGATE_BY = 'NONE' OR INSTR(BI.AGGREGATE_BY, 'HOST') !=
0 THEN [Link] ELSE MAP([Link], '%', 'any', [Link])
END HOST,
CASE WHEN BI.AGGREGATE_BY = 'NONE' OR INSTR(BI.AGGREGATE_BY, 'PORT') !=
0 THEN TO_VARCHAR([Link]) ELSE MAP([Link], '%', 'any', [Link])
END PORT,
CASE WHEN BI.AGGREGATE_BY = 'NONE' OR INSTR(BI.AGGREGATE_BY, 'SERVICE') !=
0 THEN S.SERVICE_NAME ELSE MAP(BI.SERVICE_NAME, '%', 'any', BI.SERVICE_NAME)
END SERVICE_NAME,
CASE WHEN BI.AGGREGATE_BY = 'NONE' OR INSTR(BI.AGGREGATE_BY, 'APP_USER') !=
0 THEN T.APP_USER ELSE 'any'
END APP_USER,
CASE WHEN BI.AGGREGATE_BY = 'NONE' OR INSTR(BI.AGGREGATE_BY, 'THREAD_TYPE') !=
0 THEN T.THREAD_TYPE ELSE 'any'
END THREAD_TYPE,
CASE WHEN BI.AGGREGATE_BY = 'NONE' OR INSTR(BI.AGGREGATE_BY, 'THREAD_STATE') !=
0 THEN T.THREAD_STATE ELSE 'any'
END THREAD_STATE,
CASE WHEN BI.AGGREGATE_BY = 'NONE' OR INSTR(BI.AGGREGATE_BY, 'THREAD_ID') !=
0 THEN TO_VARCHAR(T.THREAD_ID) ELSE 'any'
END THREAD_ID,
COUNT(*) NUM,
MAP(MIN(T.CONN_ID), MAX(T.CONN_ID), LPAD(MAX(T.CONN_ID), 10), 'various')
CONN_ID,
MAP(MIN([Link]), MAX([Link]), MAX([Link]), 'various') ACTIVE,
LPAD(TO_DECIMAL(MAP(BI.AGGREGATION_TYPE, 'AVG', AVG(T.DURATION_MS), 'MAX',
MAX(T.DURATION_MS), 'SUM', SUM(T.DURATION_MS)) / 1000, 10, 2), 10) DURATION_S,
LPAD(TO_DECIMAL(MAP(BI.AGGREGATION_TYPE, 'AVG', AVG(T.CPU_TIME_US), 'MAX',
MAX(T.CPU_TIME_US), 'SUM', SUM(T.CPU_TIME_US)) / 1000 / 1000, 10, 2), 10)
CPU_TIME_S,
BI.ORDER_BY
FROM
( SELECT /* Modification section */
'%' HOST,
'%' PORT,
'%' SERVICE_NAME,
'X' ONLY_ACTIVE_THREADS,
-1 CONN_ID,
'SUM' AGGREGATION_TYPE, /* MAX, AVG, SUM */
'NONE' AGGREGATE_BY, /* HOST, PORT, SERVICE, APP_USER, THREAD_TYPE,
THREAD_STATE, THREAD_ID and comma separated combinations, NONE for no aggregation
*/
'THREADS' ORDER_BY /* THREAD_ID, CONNECTION, THREADS */
FROM
DUMMY
) BI,
M_SERVICES S,
( SELECT
HOST,
PORT,
CONNECTION_ID CONN_ID,
THREAD_ID,
THREAD_TYPE,
THREAD_STATE,
IS_ACTIVE ACTIVE,
APPLICATION_USER_NAME APP_USER,
DURATION DURATION_MS,
CPU_TIME_SELF CPU_TIME_US
FROM
M_SERVICE_THREADS
) T
WHERE
[Link] LIKE [Link] AND
TO_VARCHAR([Link]) LIKE [Link] AND
S.SERVICE_NAME LIKE BI.SERVICE_NAME AND
[Link] = [Link] AND
[Link] = [Link] AND
( BI.ONLY_ACTIVE_THREADS = ' ' OR [Link] = 'TRUE' ) AND
( BI.CONN_ID = -1 OR T.CONN_ID = BI.CONN_ID )
GROUP BY
CASE WHEN BI.AGGREGATE_BY = 'NONE' OR INSTR(BI.AGGREGATE_BY, 'HOST') !=
0 THEN [Link] ELSE MAP([Link], '%', 'any', [Link])
END,
CASE WHEN BI.AGGREGATE_BY = 'NONE' OR INSTR(BI.AGGREGATE_BY, 'PORT') !=
0 THEN TO_VARCHAR([Link]) ELSE MAP([Link], '%', 'any', [Link])
END,
CASE WHEN BI.AGGREGATE_BY = 'NONE' OR INSTR(BI.AGGREGATE_BY, 'SERVICE') !=
0 THEN S.SERVICE_NAME ELSE MAP(BI.SERVICE_NAME, '%', 'any', BI.SERVICE_NAME)
END,
CASE WHEN BI.AGGREGATE_BY = 'NONE' OR INSTR(BI.AGGREGATE_BY, 'APP_USER') !=
0 THEN T.APP_USER ELSE 'any'
END,
CASE WHEN BI.AGGREGATE_BY = 'NONE' OR INSTR(BI.AGGREGATE_BY, 'THREAD_TYPE') !=
0 THEN T.THREAD_TYPE ELSE 'any'
END,
CASE WHEN BI.AGGREGATE_BY = 'NONE' OR INSTR(BI.AGGREGATE_BY, 'THREAD_STATE') !=
0 THEN T.THREAD_STATE ELSE 'any'
END,
CASE WHEN BI.AGGREGATE_BY = 'NONE' OR INSTR(BI.AGGREGATE_BY, 'THREAD_ID') !=
0 THEN TO_VARCHAR(T.THREAD_ID) ELSE 'any'
END,
BI.ORDER_BY,
BI.AGGREGATION_TYPE
)
ORDER BY
MAP(ORDER_BY, 'THREAD_ID', THREAD_ID, 'any', 'CONNECTION', CONN_ID, 'any'),
MAP(ORDER_BY, 'THREADS', NUM) DESC

You might also like