Statements / Executed Sql
Purpose
Executed statements view shows a list of dynamic SQL statements that are recently run. This is useful for identifying statemnts that are costly or long running.
Drill down is provided to see the full SQL text or run explain on a statement.
Screenshot
Metric shown
Stmt ID
An opaque binary token generated on the data server that uniquely identifies the section of the database package cache.
Source: MON_GET_PKG_CACHE_STMT(‘D’, NULL, NULL, #MEMBER#).EXECUTABLE_ID
Sql stmt (first 30 chars.)
The first 30 characters of the SQL statement.
Source: MON_GET_PKG_CACHE_STMT(‘D’, NULL, NULL, #MEMBER#).STMT_TEXT
Num execution
The total number of times that an SQL statement has been executed.
Source: MON_GET_PKG_CACHE_STMT(‘D’, NULL, NULL, #MEMBER#).SUM(NUM_EXECUTIONS)
Exec time
The total time spent executing the statements.
Source: MON_GET_PKG_CACHE_STMT(‘D’, NULL, NULL, #MEMBER#).SUM(STMT_EXEC_TIME)
Avg exec time
The average time spent executing a statement.
= STMT_EXEC_TIME / NUM_EXEC_WITH_METRICS
Source:
STMT_EXEC_TIME = MON_GET_PKG_CACHE_STMT(‘D’, NULL, NULL, #MEMBER#).SUM(STMT_EXEC_TIME)
NUM_EXEC_WITH_METRICS= MON_GET_PKG_CACHE_STMT(‘D’, NULL, NULL, #MEMBER#).SUM(NUM_EXEC_WITH_METRICS)
Cpu time
The total amount of CPU time used while within the database system.
Source: MON_GET_PKG_CACHE_STMT(‘D’, NULL, NULL, #MEMBER#).SUM(TOTAL_CPU_TIME)
Avg cpu time
The average amount of CPU time used executing a statement.
= TOTAL_CPU_TIME / NUM_EXEC_WITH_METRICS
Source:
TOTAL_CPU_TIME = MON_GET_PKG_CACHE_STMT(‘D’, NULL, NULL, #MEMBER#).SUM(TOTAL_CPU_TIME)
NUM_EXEC_WITH_METRICS= MON_GET_PKG_CACHE_STMT(‘D’, NULL, NULL, #MEMBER#).SUM(NUM_EXEC_WITH_METRICS)
Rows read
The number of rows read from the table.
Source: MON_GET_PKG_CACHE_STMT(‘D’, NULL, NULL, #MEMBER#).SUM(ROWS_READ)
Rows written
The number of rows inserted, updated, or deleted.
Source: MON_GET_PKG_CACHE_STMT(‘D’, NULL, NULL, #MEMBER#).SUM(ROWS_MODIFIED)
Data l_reads
The number of data pages which have been synchronously read by agents from the buffer pool (logical) for regular and large table spaces.
Source: MON_GET_PKG_CACHE_STMT(‘D’, NULL, NULL, #MEMBER#).SUM(POOL_DATA_L_READS)
Data hits
Data page hit ratio. Formulas for calculating buffer pool hit ratios.
= 1 - POOL_DATA_P_READS / POOL_DATA_L_READS
Source:
POOL_DATA_P_READS = MON_GET_PKG_CACHE_STMT(‘D’, NULL, NULL, #MEMBER#).SUM(POOL_DATA_P_READS)
POOL_DATA_L_READS = MON_GET_PKG_CACHE_STMT(‘D’, NULL, NULL, #MEMBER#).SUM(POOL_DATA_L_READS)
Index l_reads
The number of index pages which have been synchronously read by agents from the buffer pool (logical) for regular and large table spaces.
Source: MON_GET_PKG_CACHE_STMT(‘D’, NULL, NULL, #MEMBER#).SUM(POOL_INDEX_L_READS)
Index hits
Index page hit ratio. Formulas for calculating buffer pool hit ratios.
= 1 - POOL_INDEX_P_READS / POOL_INDEX_L_READS
Source:
POOL_INDEX_P_READS = MON_GET_PKG_CACHE_STMT(‘D’, NULL, NULL, #MEMBER#).SUM(POOL_INDEX_P_READS)
POOL_INDEX_L_READS = MON_GET_PKG_CACHE_STMT(‘D’, NULL, NULL, #MEMBER#).SUM(POOL_INDEX_L_READS)
Temp l_reads
The number of data pages read in for temporary table spaces.
= POOL_TEMP_DATA_L_READS + POOL_TEMP_INDEX_L_READS
Source:
POOL_TEMP_DATA_L_READS = MON_GET_PKG_CACHE_STMT(‘D’, NULL, NULL, #MEMBER#).SUM(POOL_TEMP_DATA_L_READS)
POOL_TEMP_INDEX_L_READS = MON_GET_PKG_CACHE_STMT(‘D’, NULL, NULL, #MEMBER#).SUMPOOL_TEMP_INDEX_L_READS)
Temp hits
Page hit ratio in temporary table spaces.
= 1 - (POOL_TEMP_DATA_P_READS + POOL_TEMP_INDEX_P_READS) / (POOL_TEMP_DATA_L_READS + POOL_TEMP_INDEX_L_READS)
Source:
POOL_TEMP_DATA_P_READS = MON_GET_PKG_CACHE_STMT(‘D’, NULL, NULL, #MEMBER#).SUM(POOL_TEMP_DATA_P_READS)
POOL_TEMP_INDEX_P_READS = MON_GET_PKG_CACHE_STMT(‘D’, NULL, NULL, #MEMBER#).SUM(POOL_TEMP_INDEX_P_READS)
POOL_TEMP_DATA_L_READS = MON_GET_PKG_CACHE_STMT(‘D’, NULL, NULL, #MEMBER#).SUM(POOL_TEMP_DATA_L_READS)
POOL_TEMP_INDEX_L_READS = MON_GET_PKG_CACHE_STMT(‘D’, NULL, NULL, #MEMBER#).SUMPOOL_TEMP_INDEX_L_READS)
Avg sort per exec
The average number of sorts performed for each statement execution.
= TOTAL_SECTION_SORTS / NUM_EXEC_WITH_METRICS
Source:
TOTAL_SECTION_SORTS = MON_GET_PKG_CACHE_STMT(‘D’, NULL, NULL, #MEMBER#).SUM(TOTAL_SECTION_SORTS )
NUM_EXEC_WITH_METRICS= MON_GET_PKG_CACHE_STMT(‘D’, NULL, NULL, #MEMBER#).SUM(NUM_EXEC_WITH_METRICS)
Sort time
The total amount of time spent performing sorts while executing a section.
Source: MON_GET_PKG_CACHE_STMT(‘D’, NULL, NULL, #MEMBER#).SUM(TOTAL_SECTION_SORT_TIME)