[go: up one dir, main page]
Scribd
Upload
14 views11 pages

Hana SQL Sqlcache Diffreport

The document outlines the HANA_SQL_SQLCache_DiffReport, which compares top SQL statements across two time frames using data from SAP HANA. It details input parameters, involved tables, and output parameters necessary for generating the report, as well as SQL command versions and restrictions. The report helps in analyzing SQL performance by providing elapsed time and execution statistics for specified intervals.

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
14 views11 pages

Hana SQL Sqlcache Diffreport

The document outlines the HANA_SQL_SQLCache_DiffReport, which compares top SQL statements across two time frames using data from SAP HANA. It details input parameters, involved tables, and output parameters necessary for generating the report, as well as SQL command versions and restrictions. The report helps in analyzing SQL performance by providing elapsed time and execution statistics for specified intervals.

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
You are on page 1/ 11

WITH

/*

[NAME]

- HANA_SQL_SQLCache_DiffReport

[DESCRIPTION]

- Comparison of top SQL statements in two time frames (or in history vs. current /
reset information)

[SOURCE]

- SAP Note 1969700

[DETAILS AND RESTRICTIONS]

- RESET can be performed via:

ALTER SYSTEM RESET MONITORING VIEW M_SQL_PLAN_CACHE_RESET

[VALID FOR]

- Revisions: all

[SQL COMMAND VERSION]

- 2016/09/25: 1.0 (initial version)


- 2017/10/26: 1.1 (TIMEZONE included)
- 2018/02/24: 1.2 (SQL_TYPE included)

[INVOLVED TABLES]

- M_SQL_PLAN_CACHE
- M_SQL_PLAN_CACHE_RESET
- HOST_SQL_PLAN_CACHE

[INPUT PARAMETERS]

- BEGIN_TIME_1

Begin time of first interval, only considered with DATA_SOURCE_1 = 'HISTORY'

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_1

End time of first interval, only considered with DATA_SOURCE_1 = 'HISTORY'

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
- BEGIN_TIME_2

Begin time of second interval, only considered with DATA_SOURCE_2 = 'HISTORY'

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_2

End time of second interval, only considered with DATA_SOURCE_2 = 'HISTORY'

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

- 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

- SCHEMA_NAME

Schema name or pattern

'SAPSR3' --> Specific schema SAPSR3


'SAP%' --> All schemata starting with 'SAP'
'%' --> All schemata

- TABLE_LOCATION

Location(s) of table(s)

'%saphana01%' --> Tables with location containing 'saphana01'


'%' --> No limitation related to table locations

- 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

- IS_DISTRIBUTED_EXECUTION

Possibility to restrict result to distributed executions

'TRUE' --> Only show distributed executions (accessing multiple scale-


out nodes)
'FALSE' --> Only show local executions (accessing only a single SAP HANA
node)
'%' --> No restriction related to distributed executions

- SHARING_TYPE

Plan sharing type filter

'SESSION LOCAL' --> Display SQL statements with 'SESSION LOCAL' plan sharing type
'%' --> No restriction related to plan sharing type

- STORE

Table store

'ROW' --> Only display accesses where all tables are located in row
store
'%COLUMN%' --> Only display accesses where at least one table is located in
column store
'%' --> No restriction related to table store

- EXCLUDE_INTERNAL

Possibility to exclude internal SQL statements

'X' --> Do not consider internal SQL statements


' ' --> No restriction related to internal SQL statements

- DATA_SOURCE_1

Source of analysis data of first interval

'CURRENT' --> Data from memory information (M_* tables)


'HISTORY' --> Data from persisted history information (HOST_* tables),
related to BEGIN_TIME_1 and END_TIME_1
'RESET' --> Data from reset memory information (M_*_RESET tables)

- DATA_SOURCE_2
Source of analysis data of second interval

'CURRENT' --> Data from memory information (M_* tables)


'HISTORY' --> Data from persisted history information (HOST_* tables),
related to BEGIN_TIME_2 and END_TIME_2
'RESET' --> Data from reset memory information (M_*_RESET tables)

- ORDER_BY

Sort order (available values are provided in comment)

'ELAPSED_1' --> Sorting by elapsed time of first interval


'ELA_PER_EXEC_2' --> Sorting by elapsed time per execution of second interval

-[OUTPUT PARAMETERS]

- STATEMENT_HASH: Hash value of SQL statement


- TP: SQL type (SE -> SELECT, IN -> INSERT, DE -> DELETE, UP ->
UPDATE, ...)
- ELAPSED_S_1: First interval: Total elapsed time (s)
- PER_EXEC_MS_1: First interval: Elapsed time per execution (ms)
- REC_PER_EXEC_1: First interval: Records per execution
- ELAPSED_S_1: Second interval: Total elapsed time (s)
- PER_EXEC_MS_1: Second interval: Elapsed time per execution (ms)
- REC_PER_EXEC_1: Second interval: Records per execution
- STATEMENT_STRING: SQL statement text
- DIFF_ELA_PCT: Difference in total elapsed time between first and second
interval (%)
- DIFF_ELA_PER_EXEC_PCT: Different in elapsed time per execution between first and
second interval (%)
- STATEMENT_STRING: SQL text

[EXAMPLE OUTPUT]

-----------------------------------------------------------------------------------
--------------------------------------------------------------------
|STATEMENT_HASH |ELAPSED_S_1|PER_EXEC_MS_1|REC_PER_EXEC_1|
ELAPSED_S_2|PER_EXEC_MS_2|REC_PER_EXEC_2|DIFF_ELA_PCT|DIFF_ELA_PER_EXEC_PCT|
-----------------------------------------------------------------------------------
--------------------------------------------------------------------
|START_1: 2016/09/13 00:00:00 | | | |
| | | | |
|END___1: 2016/09/16 00:00:00 | | | |
| | | | |
|START_2: 2016/09/20 00:00:00 | | | |
| | | | |
|END___2: 2016/09/23 00:00:00 | | | |
| | | | |
| | | | |
| | | | |
|2afa9311f17e325d6d1418b3dd3eb388| 14930.25| 1028.75| 1.00|
76902.78| 1050.32| 1.00| 415.08| 2.09|
|0ad1a4c1c1a5844d01595f5b3cdc2977| 10976.40| 756.41| 1.00|
58963.81| 805.20| 1.00| 437.18| 6.45|
|d6fd6678833f9a2e25e7b53239c50e9a| 37282.72| 199.30| 2.99|
38866.69| 217.78| 2.99| 4.24| 9.26|
|6b1d10732401fe82992d93fa91f4ae86| 287937.01| 2433.01| 0.90|
34993.62| 247.72| 0.94| -87.84| -89.81|
|eff2bb4b779078d3c87d194f81b0b948| | | |
26502.36| 974.45| 1.00| 9999.99| 9999.99|
|92ddd849f90a501dbb2e8acea6245948| | | |
22722.29| 976.33| 1.00| 9999.99| 9999.99|
|6e7160c8a3b3a1052bdce2c3c9eb0b13| | | |
21850.60| 960.71| 1.00| 9999.99| 9999.99|
|5ef81b3843efbe961b19fdd79c2bd86b| 10013.60| 60.93| 0.99|
11683.93| 66.61| 1.00| 16.68| 9.31|
|8343a81d8e7025d91e72be8d530bc095| 11249.93| 62.39| 0.99|
11069.25| 56.46| 0.99| -1.60| -9.50|
|b5ec08e6423d7821b62ba0043f789520| 6260.40| 0.08| 0.07|
9613.51| 0.34| 0.22| 53.56| 290.38|
|dc571bf5eb7cad9c313c20de904ab709| 8688.88| 849.18| 2.00|
9373.40| 886.96| 2.00| 7.87| 4.44|
|905dbaa93a672b087c6f226bc283431d| 8028.73| 15499.49| 0.00|
8425.08| 15718.44| 0.00| 4.93| 1.41|
|4bdb681fc92553a14e25fda29410b448| | | |
5459.21| 0.29| 1.00| 9999.99| 9999.99|
|a00bb2b75434d0c51a4d9e3dd60f0ac6| 5903.52| 0.29| 0.00|
5038.02| 0.25| 0.00| -14.66| -14.49|
|76f246be96028db1e4ac3997b6a07373| 4464.86| 188.30| 1.18|
5031.20| 205.13| 0.68| 12.68| 8.93|
|e3db20ea4f6f4d8991dcdfc5b7746cdd| 3873.18| 0.05| 0.00|
4804.69| 0.17| 0.00| 24.05| 239.93|
|2ecb45d580ce0768f2979354eb7d84ee| 122.53| 12253.72| 1.00|
4299.07| 16041.32| 1.00| 3408.38| 30.90|
-----------------------------------------------------------------------------------
--------------------------------------------------------------------

*/

BASIS_INFO AS
( SELECT /* Modification section */
TO_TIMESTAMP('2017/04/27 00:00:00', 'YYYY/MM/DD HH24:MI:SS') BEGIN_TIME_1,
TO_TIMESTAMP('2017/04/28 00:00:00', 'YYYY/MM/DD HH24:MI:SS') END_TIME_1,
TO_TIMESTAMP('2017/05/04 00:00:00', 'YYYY/MM/DD HH24:MI:SS') BEGIN_TIME_2,
TO_TIMESTAMP('2017/05/05 00:00:00', 'YYYY/MM/DD HH24:MI:SS') END_TIME_2,
'SERVER' TIMEZONE, /* SERVER, UTC */
'%' HOST,
'%' PORT,
'%' SCHEMA_NAME,
'%' TABLE_LOCATION,
'%' STATEMENT_HASH,
'%EDID4%' SQL_PATTERN,
'%' IS_DISTRIBUTED_EXECUTION, /* TRUE, FALSE, % */
'%' SHARING_TYPE,
'%' STORE,
' ' EXCLUDE_INTERNAL,
'HISTORY' DATA_SOURCE_1, /* CURRENT, HISTORY, RESET */
'HISTORY' DATA_SOURCE_2, /* CURRENT, HISTORY, RESET */
'ELAPSED_2' ORDER_BY /* ELAPSED_1, ELAPSED_2, ELA_PER_EXEC_1,
ELA_PER_EXEC_2, REC_PER_EXEC_1, REC_PER_EXEC_2 */
FROM
DUMMY
),
SQL_HISTORY_1 AS
( SELECT
STATEMENT_HASH,
STATEMENT_STRING,
REPLACE(UPPER(LTRIM(MAP(SUBSTR(TO_VARCHAR(STATEMENT_STRING), 1, 2), '/*',
SUBSTR(TO_VARCHAR(STATEMENT_STRING), LOCATE(TO_VARCHAR(STATEMENT_STRING), '*/') +
2), TO_VARCHAR(STATEMENT_STRING)), ' ({')), CHAR(10), '') STATEMENT_STRING_CLEANED,
SUM(EXECUTION_COUNT) EXECUTION_COUNT,
SUM(TOTAL_ELAPSED_TIME) TOTAL_ELAPSED_TIME,
SUM(TOTAL_RESULT_RECORD_COUNT) TOTAL_RESULT_RECORD_COUNT
FROM
( SELECT
C.STATEMENT_HASH,
SUBSTR(C.STATEMENT_STRING, 1, 4000) STATEMENT_STRING,
MAX(C.EXECUTION_COUNT) EXECUTION_COUNT,
MAX(C.TOTAL_EXECUTION_TIME + C.TOTAL_PREPARATION_TIME) TOTAL_ELAPSED_TIME,
MAX(C.TOTAL_RESULT_RECORD_COUNT) TOTAL_RESULT_RECORD_COUNT
FROM
BASIS_INFO BI,
_SYS_STATISTICS.HOST_SQL_PLAN_CACHE C
WHERE
CASE BI.TIMEZONE WHEN 'UTC' THEN ADD_SECONDS(C.SERVER_TIMESTAMP,
SECONDS_BETWEEN(CURRENT_TIMESTAMP, CURRENT_UTCTIMESTAMP)) ELSE C.SERVER_TIMESTAMP
END BETWEEN BI.BEGIN_TIME_1 AND BI.END_TIME_1 AND
C.HOST LIKE BI.HOST AND
TO_VARCHAR(C.PORT) LIKE BI.PORT AND
C.SCHEMA_NAME LIKE BI.SCHEMA_NAME AND
C.STATEMENT_HASH LIKE BI.STATEMENT_HASH AND
C.PLAN_SHARING_TYPE LIKE BI.SHARING_TYPE AND
UPPER(C.STATEMENT_STRING) LIKE UPPER(BI.SQL_PATTERN) AND
C.IS_DISTRIBUTED_EXECUTION LIKE BI.IS_DISTRIBUTED_EXECUTION AND
( BI.EXCLUDE_INTERNAL = ' ' OR C.IS_INTERNAL = 'FALSE' ) AND
C.TABLE_LOCATIONS LIKE BI.TABLE_LOCATION AND
C.TABLE_TYPES LIKE BI.STORE
GROUP BY
C.STATEMENT_HASH,
SUBSTR(C.STATEMENT_STRING, 1, 4000),
C.SNAPSHOT_ID
)
GROUP BY
STATEMENT_HASH,
STATEMENT_STRING
),
SQL_HISTORY_2 AS
( SELECT
STATEMENT_HASH,
STATEMENT_STRING,
REPLACE(UPPER(LTRIM(MAP(SUBSTR(TO_VARCHAR(STATEMENT_STRING), 1, 2), '/*',
SUBSTR(TO_VARCHAR(STATEMENT_STRING), LOCATE(TO_VARCHAR(STATEMENT_STRING), '*/') +
2), TO_VARCHAR(STATEMENT_STRING)), ' ({')), CHAR(10), '') STATEMENT_STRING_CLEANED,
SUM(EXECUTION_COUNT) EXECUTION_COUNT,
SUM(TOTAL_ELAPSED_TIME) TOTAL_ELAPSED_TIME,
SUM(TOTAL_RESULT_RECORD_COUNT) TOTAL_RESULT_RECORD_COUNT
FROM
( SELECT
C.STATEMENT_HASH,
SUBSTR(C.STATEMENT_STRING, 1, 4000) STATEMENT_STRING,
MAX(C.EXECUTION_COUNT) EXECUTION_COUNT,
MAX(C.TOTAL_EXECUTION_TIME + C.TOTAL_PREPARATION_TIME) TOTAL_ELAPSED_TIME,
MAX(C.TOTAL_RESULT_RECORD_COUNT) TOTAL_RESULT_RECORD_COUNT
FROM
BASIS_INFO BI,
_SYS_STATISTICS.HOST_SQL_PLAN_CACHE C
WHERE
CASE BI.TIMEZONE WHEN 'UTC' THEN ADD_SECONDS(C.SERVER_TIMESTAMP,
SECONDS_BETWEEN(CURRENT_TIMESTAMP, CURRENT_UTCTIMESTAMP)) ELSE C.SERVER_TIMESTAMP
END BETWEEN BI.BEGIN_TIME_2 AND BI.END_TIME_2 AND
C.HOST LIKE BI.HOST AND
TO_VARCHAR(C.PORT) LIKE BI.PORT AND
C.SCHEMA_NAME LIKE BI.SCHEMA_NAME AND
C.STATEMENT_HASH LIKE BI.STATEMENT_HASH AND
C.PLAN_SHARING_TYPE LIKE BI.SHARING_TYPE AND
UPPER(C.STATEMENT_STRING) LIKE UPPER(BI.SQL_PATTERN) AND
C.IS_DISTRIBUTED_EXECUTION LIKE BI.IS_DISTRIBUTED_EXECUTION AND
( BI.EXCLUDE_INTERNAL = ' ' OR C.IS_INTERNAL = 'FALSE' ) AND
C.TABLE_LOCATIONS LIKE BI.TABLE_LOCATION
GROUP BY
C.STATEMENT_HASH,
SUBSTR(C.STATEMENT_STRING, 1, 4000),
C.SNAPSHOT_ID
)
GROUP BY
STATEMENT_HASH,
STATEMENT_STRING
),
SQL_CURRENT AS
( SELECT
C.STATEMENT_HASH,
TO_VARCHAR(C.STATEMENT_STRING) STATEMENT_STRING,
REPLACE(UPPER(LTRIM(MAP(SUBSTR(TO_VARCHAR(STATEMENT_STRING), 1, 2), '/*',
SUBSTR(TO_VARCHAR(STATEMENT_STRING), LOCATE(TO_VARCHAR(STATEMENT_STRING), '*/') +
2), TO_VARCHAR(STATEMENT_STRING)), ' ({')), CHAR(10), '') STATEMENT_STRING_CLEANED,
SUM(C.EXECUTION_COUNT) EXECUTION_COUNT,
SUM(C.TOTAL_EXECUTION_TIME + C.TOTAL_PREPARATION_TIME) TOTAL_ELAPSED_TIME,
SUM(C.TOTAL_RESULT_RECORD_COUNT) TOTAL_RESULT_RECORD_COUNT
FROM
BASIS_INFO BI,
M_SQL_PLAN_CACHE C
WHERE
C.HOST LIKE BI.HOST AND
TO_VARCHAR(C.PORT) LIKE BI.PORT AND
C.SCHEMA_NAME LIKE BI.SCHEMA_NAME AND
C.STATEMENT_HASH LIKE BI.STATEMENT_HASH AND
C.PLAN_SHARING_TYPE LIKE BI.SHARING_TYPE AND
UPPER(C.STATEMENT_STRING) LIKE UPPER(BI.SQL_PATTERN) AND
C.IS_DISTRIBUTED_EXECUTION LIKE BI.IS_DISTRIBUTED_EXECUTION AND
( BI.EXCLUDE_INTERNAL = ' ' OR C.IS_INTERNAL = 'FALSE' ) AND
C.TABLE_LOCATIONS LIKE BI.TABLE_LOCATION
GROUP BY
C.STATEMENT_HASH,
TO_VARCHAR(C.STATEMENT_STRING)
),
SQL_RESET AS
( SELECT
C.STATEMENT_HASH,
TO_VARCHAR(C.STATEMENT_STRING) STATEMENT_STRING,
REPLACE(UPPER(LTRIM(MAP(SUBSTR(TO_VARCHAR(STATEMENT_STRING), 1, 2), '/*',
SUBSTR(TO_VARCHAR(STATEMENT_STRING), LOCATE(TO_VARCHAR(STATEMENT_STRING), '*/') +
2), TO_VARCHAR(STATEMENT_STRING)), ' ({')), CHAR(10), '') STATEMENT_STRING_CLEANED,
SUM(C.EXECUTION_COUNT) EXECUTION_COUNT,
SUM(C.TOTAL_EXECUTION_TIME + C.TOTAL_PREPARATION_TIME) TOTAL_ELAPSED_TIME,
SUM(C.TOTAL_RESULT_RECORD_COUNT) TOTAL_RESULT_RECORD_COUNT
FROM
BASIS_INFO BI,
M_SQL_PLAN_CACHE_RESET C
WHERE
C.HOST LIKE BI.HOST AND
TO_VARCHAR(C.PORT) LIKE BI.PORT AND
C.SCHEMA_NAME LIKE BI.SCHEMA_NAME AND
C.STATEMENT_HASH LIKE BI.STATEMENT_HASH AND
C.PLAN_SHARING_TYPE LIKE BI.SHARING_TYPE AND
UPPER(C.STATEMENT_STRING) LIKE UPPER(BI.SQL_PATTERN) AND
C.IS_DISTRIBUTED_EXECUTION LIKE BI.IS_DISTRIBUTED_EXECUTION AND
( BI.EXCLUDE_INTERNAL = ' ' OR C.IS_INTERNAL = 'FALSE' ) AND
C.TABLE_LOCATIONS LIKE BI.TABLE_LOCATION
GROUP BY
C.STATEMENT_HASH,
TO_VARCHAR(C.STATEMENT_STRING)
),
SQLSTATS AS
( SELECT
IFNULL(H1.STATEMENT_HASH, IFNULL(H2.STATEMENT_HASH, IFNULL(C.STATEMENT_HASH,
R.STATEMENT_HASH))) STATEMENT_HASH,
IFNULL(H1.STATEMENT_STRING, IFNULL(H2.STATEMENT_STRING,
IFNULL(C.STATEMENT_STRING, R.STATEMENT_STRING))) STATEMENT_STRING,
IFNULL(H1.STATEMENT_STRING_CLEANED, IFNULL(H2.STATEMENT_STRING_CLEANED,
IFNULL(C.STATEMENT_STRING_CLEANED, R.STATEMENT_STRING_CLEANED)))
STATEMENT_STRING_CLEANED,
CASE BI.DATA_SOURCE_1
WHEN 'HISTORY' THEN H1.EXECUTION_COUNT
WHEN 'CURRENT' THEN C.EXECUTION_COUNT
WHEN 'RESET' THEN R.EXECUTION_COUNT
END EXECUTIONS_1,
CASE BI.DATA_SOURCE_1
WHEN 'HISTORY' THEN H1.TOTAL_ELAPSED_TIME
WHEN 'CURRENT' THEN C.TOTAL_ELAPSED_TIME
WHEN 'RESET' THEN R.TOTAL_ELAPSED_TIME
END / 1000 ELAPSED_MS_1,
CASE BI.DATA_SOURCE_1
WHEN 'HISTORY' THEN H1.TOTAL_RESULT_RECORD_COUNT
WHEN 'CURRENT' THEN C.TOTAL_RESULT_RECORD_COUNT
WHEN 'RESET' THEN R.TOTAL_RESULT_RECORD_COUNT
END RECORDS_1,
CASE BI.DATA_SOURCE_2
WHEN 'HISTORY' THEN H2.EXECUTION_COUNT
WHEN 'CURRENT' THEN C.EXECUTION_COUNT
WHEN 'RESET' THEN R.EXECUTION_COUNT
END EXECUTIONS_2,
CASE BI.DATA_SOURCE_2
WHEN 'HISTORY' THEN H2.TOTAL_ELAPSED_TIME
WHEN 'CURRENT' THEN C.TOTAL_ELAPSED_TIME
WHEN 'RESET' THEN R.TOTAL_ELAPSED_TIME
END / 1000 ELAPSED_MS_2,
CASE BI.DATA_SOURCE_2
WHEN 'HISTORY' THEN H2.TOTAL_RESULT_RECORD_COUNT
WHEN 'CURRENT' THEN C.TOTAL_RESULT_RECORD_COUNT
WHEN 'RESET' THEN R.TOTAL_RESULT_RECORD_COUNT
END RECORDS_2
FROM
BASIS_INFO BI,
SQL_HISTORY_1 H1 FULL OUTER JOIN
SQL_HISTORY_2 H2 ON
H2.STATEMENT_HASH = H1.STATEMENT_HASH FULL OUTER JOIN
SQL_CURRENT C ON
C.STATEMENT_HASH = H2.STATEMENT_HASH FULL OUTER JOIN
SQL_RESET R ON
R.STATEMENT_HASH = C.STATEMENT_HASH
)
SELECT
STATEMENT_HASH,
TP,
ELAPSED_S_1,
PER_EXEC_MS_1 ,
REC_PER_EXEC_1,
ELAPSED_S_2,
PER_EXEC_MS_2,
REC_PER_EXEC_2,
DIFF_ELA_PCT,
DIFF_ELA_PER_EXEC_PCT,
STATEMENT_STRING
FROM
( SELECT
10 LINE_NO,
RPAD('START_1:', 10) || MAP(DATA_SOURCE_1, 'HISTORY',TO_VARCHAR(BEGIN_TIME_1,
'YYYY/MM/DD HH24:MI:SS'), DATA_SOURCE_1) STATEMENT_HASH,
'' TP,
'' ELAPSED_S_1,
'' PER_EXEC_MS_1 ,
'' REC_PER_EXEC_1,
'' ELAPSED_S_2,
'' PER_EXEC_MS_2,
'' REC_PER_EXEC_2,
'' DIFF_ELA_PCT,
'' DIFF_ELA_PER_EXEC_PCT,
'' STATEMENT_STRING
FROM
BASIS_INFO
UNION ALL
SELECT 15, RPAD('END___1:', 10) || MAP(DATA_SOURCE_1, 'HISTORY',
TO_VARCHAR(END_TIME_1, 'YYYY/MM/DD HH24:MI:SS'), CURRENT_TIMESTAMP),
'', '', '', '', '', '', '', '', '', '' FROM BASIS_INFO
UNION ALL
SELECT 20, RPAD('START_2:', 10) || MAP(DATA_SOURCE_2, 'HISTORY',
TO_VARCHAR(BEGIN_TIME_2, 'YYYY/MM/DD HH24:MI:SS'), DATA_SOURCE_2),
'', '', '', '', '', '', '', '', '', '' FROM BASIS_INFO
UNION ALL
SELECT 25, RPAD('END___2:', 10) || MAP(DATA_SOURCE_2, 'HISTORY',
TO_VARCHAR(END_TIME_2, 'YYYY/MM/DD HH24:MI:SS'), CURRENT_TIMESTAMP),
'', '', '', '', '', '', '', '', '', '' FROM BASIS_INFO
UNION ALL
SELECT 30, RPAD('HOST:', 10) || HOST, '', '', '', '', '', '', '', '', '', '' FROM
BASIS_INFO WHERE HOST != '%'
UNION ALL
SELECT 35, RPAD('PORT:', 10) || PORT, '', '', '', '', '', '', '', '', '', '' FROM
BASIS_INFO WHERE PORT != '%'
UNION ALL
SELECT 40, RPAD('SCHEMA:', 10) || SCHEMA_NAME, '', '', '', '', '', '', '', '',
'', '' FROM BASIS_INFO WHERE SCHEMA_NAME != '%'
UNION ALL
SELECT 45, RPAD('LOCATION:', 10) || TABLE_LOCATION, '', '', '', '', '', '', '',
'', '', '' FROM BASIS_INFO WHERE TABLE_LOCATION != '%'
UNION ALL
SELECT 50, RPAD('HASH:', 10) || STATEMENT_HASH, '', '', '', '', '', '', '', '',
'', '' FROM BASIS_INFO WHERE STATEMENT_HASH != '%'
UNION ALL
SELECT 55, RPAD('SQL_TEXT:', 10) || SQL_PATTERN, '', '', '', '', '', '', '', '',
'', '' FROM BASIS_INFO WHERE SQL_PATTERN != '%'
UNION ALL
SELECT 60, RPAD('DISTRIB.:', 10) || IS_DISTRIBUTED_EXECUTION, '', '', '', '', '',
'', '', '', '', '' FROM BASIS_INFO WHERE IS_DISTRIBUTED_EXECUTION != '%'
UNION ALL
SELECT 65, RPAD('SHARING:', 10) || SHARING_TYPE, '', '', '', '', '', '', '', '',
'', '' FROM BASIS_INFO WHERE SHARING_TYPE != '%'
UNION ALL
SELECT 70, RPAD('STORE:', 10) || STORE, '', '', '', '', '', '', '', '', '', ''
FROM BASIS_INFO WHERE STORE != '%'
UNION ALL
SELECT 99, '', '', '', '', '', '', '', '', '', '', '' FROM DUMMY
UNION ALL
SELECT
LINE_NO + 100 LINE_NO,
STATEMENT_HASH,
SQL_TYPE TP,
IFNULL(LPAD(TO_DECIMAL(ELAPSED_S_1, 10, 2), 11), '') ELAPSED_S_1,
IFNULL(LPAD(TO_DECIMAL(PER_EXEC_MS_1, 10, 2), 13), '') PER_EXEC_MS_1,
IFNULL(LPAD(TO_DECIMAL(REC_PER_EXEC_1, 10, 2), 14), '') REC_PER_EXEC_1,
IFNULL(LPAD(TO_DECIMAL(ELAPSED_S_2, 10, 2), 11), '') ELAPSED_S_2,
IFNULL(LPAD(TO_DECIMAL(PER_EXEC_MS_2, 10, 2), 13), '') PER_EXEC_MS_2,
IFNULL(LPAD(TO_DECIMAL(REC_PER_EXEC_2, 10, 2), 14), '') REC_PER_EXEC_2,
IFNULL(LPAD(TO_DECIMAL(LEAST(9999.99,
MAP(IFNULL(ELAPSED_S_1, 0), 0, 9999.99, ( ELAPSED_S_2 / ELAPSED_S_1 * 100 ) -
100 ) ), 10, 2), 12), '') DIFF_ELA_PCT,
IFNULL(LPAD(TO_DECIMAL(LEAST(9999.99,
MAP(IFNULL(PER_EXEC_MS_1, 0), 0, 9999.99, ( PER_EXEC_MS_2 / PER_EXEC_MS_1 *
100 ) - 100 ) ), 10, 2), 21), '') DIFF_ELA_PER_EXEC_PCT,
STATEMENT_STRING
FROM
( SELECT
ROW_NUMBER () OVER (ORDER BY
MAP(BI.ORDER_BY,
'ELAPSED_1', S.ELAPSED_MS_1,
'ELAPSED_2', S.ELAPSED_MS_2,
'ELA_PER_EXEC_1', MAP(S.EXECUTIONS_1, 0, 0, S.ELAPSED_MS_1 /
S.EXECUTIONS_1),
'ELA_PER_EXEC_2', MAP(S.EXECUTIONS_2, 0, 0, S.ELAPSED_MS_2 /
S.EXECUTIONS_2),
'REC_PER_EXEC_1', MAP(S.EXECUTIONS_1, 0, 0, S.RECORDS_1 / S.EXECUTIONS_1),
'REC_PER_EXEC_2', MAP(S.EXECUTIONS_2, 0, 0, S.RECORDS_2 / S.EXECUTIONS_2)
) DESC ) LINE_NO,
S.STATEMENT_HASH,
S.ELAPSED_MS_1 / 1000 ELAPSED_S_1,
MAP(S.EXECUTIONS_1, 0, 0, S.ELAPSED_MS_1 / S.EXECUTIONS_1) PER_EXEC_MS_1,
MAP(S.EXECUTIONS_1, 0, 0, S.RECORDS_1 / S.EXECUTIONS_1) REC_PER_EXEC_1,
S.ELAPSED_MS_2 / 1000 ELAPSED_S_2,
MAP(S.EXECUTIONS_2, 0, 0, S.ELAPSED_MS_2 / S.EXECUTIONS_2) PER_EXEC_MS_2,
MAP(S.EXECUTIONS_2, 0, 0, S.RECORDS_2 / S.EXECUTIONS_2) REC_PER_EXEC_2,
S.STATEMENT_STRING,
CASE
WHEN STATEMENT_STRING_CLEANED LIKE 'ALTER INDEX%' THEN 'AI'
WHEN STATEMENT_STRING_CLEANED LIKE 'ALTER SYSTEM%' THEN 'AS'
WHEN STATEMENT_STRING_CLEANED LIKE 'ALTER TABLE%' THEN 'AT'
WHEN STATEMENT_STRING_CLEANED LIKE 'ALTER%' THEN 'AL'
WHEN STATEMENT_STRING_CLEANED LIKE 'CALL%' THEN 'CA'
WHEN STATEMENT_STRING_CLEANED LIKE 'COMMIT%' THEN 'CO'
WHEN STATEMENT_STRING_CLEANED LIKE 'CREATE INDEX%' THEN 'CI'
WHEN STATEMENT_STRING_CLEANED LIKE 'CREATE TABLE%' THEN 'CT'
WHEN STATEMENT_STRING_CLEANED LIKE 'CREATE%' THEN 'CR'
WHEN STATEMENT_STRING_CLEANED LIKE 'DELETE%' THEN 'DE'
WHEN STATEMENT_STRING_CLEANED LIKE 'DROP INDEX%' THEN 'DI'
WHEN STATEMENT_STRING_CLEANED LIKE 'DROP TABLE%' THEN 'DT'
WHEN STATEMENT_STRING_CLEANED LIKE 'DROP%' THEN 'DR'
WHEN STATEMENT_STRING_CLEANED LIKE 'EXECUTE%' THEN 'EX'
WHEN STATEMENT_STRING_CLEANED LIKE 'INSERT%' THEN 'IN'
WHEN STATEMENT_STRING_CLEANED LIKE 'REPLACE%' THEN 'RE'
WHEN STATEMENT_STRING_CLEANED LIKE 'ROLLBACK%' THEN 'RO'
WHEN STATEMENT_STRING_CLEANED LIKE 'SELECT%FOR UPDATE%' THEN 'SU'
WHEN STATEMENT_STRING_CLEANED LIKE 'SELECT%' THEN 'SE'
WHEN STATEMENT_STRING_CLEANED LIKE 'TRUNCATE%' THEN 'TR'
WHEN STATEMENT_STRING_CLEANED LIKE 'UPDATE%' THEN 'UP'
WHEN STATEMENT_STRING_CLEANED LIKE 'UPSERT%' THEN 'US'
WHEN STATEMENT_STRING_CLEANED LIKE 'WITH%' THEN 'WI'
ELSE 'unknown'
END SQL_TYPE
FROM
BASIS_INFO BI,
SQLSTATS S
WHERE
( ELAPSED_MS_1 IS NOT NULL OR ELAPSED_MS_2 IS NOT NULL )
)
)
ORDER BY
LINE_NO

You might also like