SELECT
/*
[NAME]
- HANA_SQL_StatementHash_BindValues_CommaList_1.00.100+
[DESCRIPTION]
- Show bind values for prepared statement in a comma separated list
- Comma separated output format useful for prepared statement execution in SAP HANA
Studio
[SOURCE]
- SAP Note 1969700
[DETAILS AND RESTRICTIONS]
- ORDER BY clause for STRING_AGG only available as of Rev. 100
- M_SQL_PLAN_CACHE_PARAMETERS only available as of Rev. 80
- Controlled by three parameters:
indexserver.ini -> [sql] -> plan_cache_parameter_enabled
-> true: Enables M_SQL_PLAN_CACHE_PARAMETERS information collection (default)
-> false: Disables M_SQL_PLAN_CACHE_PARAMETERS information collection
indexserver.ini -> [sql] -> plan_cache_parameter_sum_threshold
-> Minimum total execution of SQL statement to capture first set of bind values
(in ms, default: 100000)
indexserver.ini -> [sql] -> plan_cache_parameter_threshold
-> Additional sets of bind values are captured if execution time is larger than
execution time during last capturing and
execution time is higher than the parameter value (in ms, default: 100)
[VALID FOR]
- Revisions: >= 1.00.100
- Statistics server type: all
[SQL COMMAND VERSION]
- 2015/05/12: 1.0 (initial version)
- 2015/10/02: 1.1 (ORDER BY for STRING_AGG included)
- 2017/10/26: 1.2 (TIMEZONE included)
[INVOLVED TABLES]
- M_SQL_PLAN_CACHE_PARAMETERS
- M_SQL_PLAN_CACHE
[INPUT PARAMETERS]
- BEGIN_TIME
Begin time
TO_TIMESTAMP('2014/06/05 14:05:00', 'YYYY/MM/DD HH24:MI:SS') --> Set begin time
to 5th of June 2014, 14:05
ADD_DAYS(CURRENT_TIMESTAMP, -2) --> Set begin time
to two days before current time
- END_TIME
End time
TO_TIMESTAMP('2014/06/08 14:05:00', 'YYYY/MM/DD HH24:MI:SS') --> Set end time to
8th of June 2014, 14:05
CURRENT_TIMESTAMP --> Set end time to
current time
- TIMEZONE
Used timezone (both for input and output parameters)
'SERVER' --> Display times in SAP HANA server time
'UTC' --> Display times in UTC time
- STATEMENT_HASH
Hash of SQL statement to be analyzed
'51f62795010e922370bf897325148783' --> Consider SQL statement with statement hash
51f62795010e922370bf897325148783
'%' --> No restriction in tersm of statement hash
- PLAN_ID
SQL plan identifier
12345678 --> SQL plan identifier 12345678
-1 --> No restriction based on SQL plan identifier
[OUTPUT PARAMETERS]
- EXECUTION_TIMESTAMP: Timestamp of statement execution
- STATEMENT_HASH: Statement hash
- PLAN_ID: Execution plan ID
- NUM_VALUES: Number of bind values
- VALUE_LIST: Comma separated list of bind values
[EXAMPLE OUTPUT]
-----------------------------------------------------------------------------------
--------------------------------------------------------------
|EXECUTION_TIMESTAMP|STATEMENT_HASH |PLAN_ID |NUM_VALUES|
VALUE_LIST |
-----------------------------------------------------------------------------------
--------------------------------------------------------------
|2015/05/12 15:18:36|f01a5732bd7f32100871db0ce58b81f6|5181509000| 1|A
|
|2015/05/11 13:27:13|f0ec9225830ba8adddb1b3a0e4604f09|4854858000| 6|
NL01,Z1,F480003500,8000001498,PMC_HT_CU,201506 |
|2015/05/11 12:38:15|f05f537090da0ba31eea563a2db419f9|4863880000| 2|
100,D27BBC007020DD5C519F6A4AF6905D99FBA135ECF7C611E48D0ED48564EE94CA|
|2015/05/08 01:19:50|f0a4785a6ada81a04e600dc2b06f0e49|3708763000|
5|/CPMB/ZTIYVAS,00000000,0000000000,CUBE,99991230 |
-----------------------------------------------------------------------------------
--------------------------------------------------------------
*/
TO_VARCHAR(CASE BI.TIMEZONE WHEN 'UTC' THEN ADD_SECONDS(B.EXECUTION_TIMESTAMP,
SECONDS_BETWEEN(CURRENT_TIMESTAMP, CURRENT_UTCTIMESTAMP)) ELSE
B.EXECUTION_TIMESTAMP END, 'YYYY/MM/DD HH24:MI:SS') EXECUTION_TIMESTAMP,
S.STATEMENT_HASH,
LPAD(B.PLAN_ID, 10) PLAN_ID,
LPAD(COUNT(*), 10) NUM_VALUES,
STRING_AGG(TO_VARCHAR(B.PARAMETER_VALUE), ',' ORDER BY POSITION) VALUE_LIST
FROM
( SELECT /* Modification section */
TO_TIMESTAMP('1000/05/16 11:00:00', 'YYYY/MM/DD HH24:MI:SS') BEGIN_TIME,
TO_TIMESTAMP('9999/12/31 23:00:00', 'YYYY/MM/DD HH24:MI:SS') END_TIME,
'SERVER' TIMEZONE, /* SERVER, UTC */
'f0a4785a6ada81a04e600dc2b06f0e49' STATEMENT_HASH,
-1 PLAN_ID
FROM
DUMMY
) BI,
M_SQL_PLAN_CACHE S,
M_SQL_PLAN_CACHE_PARAMETERS B
WHERE
S.STATEMENT_HASH LIKE BI.STATEMENT_HASH AND
( BI.PLAN_ID = -1 OR S.PLAN_ID = BI.PLAN_ID ) AND
B.PLAN_ID = S.PLAN_ID AND
CASE BI.TIMEZONE WHEN 'UTC' THEN ADD_SECONDS(B.EXECUTION_TIMESTAMP,
SECONDS_BETWEEN(CURRENT_TIMESTAMP, CURRENT_UTCTIMESTAMP)) ELSE
B.EXECUTION_TIMESTAMP END BETWEEN BI.BEGIN_TIME AND BI.END_TIME
GROUP BY
B.EXECUTION_TIMESTAMP,
S.STATEMENT_HASH,
B.PLAN_ID,
BI.TIMEZONE
ORDER BY
B.EXECUTION_TIMESTAMP DESC,
S.STATEMENT_HASH,
B.PLAN_ID
