DB2 Performance Queries: Stolen from Scott hayes and changed to use mon_get_functions

scott hayes DB2 performance article on IDUG 


first script to check which tables are read the most:

Figure 1 – Table Weights

select substr(a.tabschema,1,20) as TABSCHEMA,

substr(a.tabname,1,25) as TABNAME,

a.rows_read as RowsRead,

CAST((((A.ROWS_READ) * 100.0)

/ (Select (SUM(Z.ROWS_READ) + 1.0)

FROM table(mon_get_table(null,null,-2)) Z

WHERE A.MEMBER = Z.MEMBER

)) AS DECIMAL(5,2)) AS PCT_DB_TB_ROWSREAD

from table(mon_get_table(null,null,-2)) a

order by a.rows_read desc fetch first 20 rows only;

Figure 4 – SQL Heavy CPU Consumers

SELECT

CAST(

A.TOTAL_CPU_TIME


/ A.NUM_EXECUTIONS

AS DECIMAL (15,0)) AS AVG_CPU_TIME,

CAST (A.NUM_EXECUTIONS AS INTEGER) AS NUM_EXECS,

CAST(((

A.TOTAL_CPU_TIME
* 100.0)

/ Coalesce((Select SUM(TOTAL_CPU_TIME)

FROM table(mon_get_pkg_cache_stmt('D',null,null,-2)) B

WHERE A.MEMBER = B.MEMBER and Member=0

),1)) AS DECIMAL(5,2)) AS PCT_CPU_TIME,

SUBSTR(A.STMT_TEXT,1,110) AS CPU_SUCKING_SQL

FROM table(mon_get_pkg_cache_stmt('D',null,null,-2)) A

WHERE A.NUM_EXECUTIONS > 0

ORDER BY A.MEMBER ASC, 3 DESC, 1 DESC FETCH FIRST 25 ROWS ONLY

Figure 5 – SQL Heavy I/O Consumers

SELECT CAST (A.NUM_EXECUTIONS AS INTEGER) AS NUM_EXECS,

CAST( (A.ROWS_READ + 0.001) / (A.NUM_EXECUTIONS + 0.001)

AS DECIMAL (13,4)) AS AVG_ROWS_READ,

CAST((((A.ROWS_READ) * 100.0)

/ (Select (SUM(B.ROWS_READ) + 1.0)

FROM table(mon_get_pkg_cache_stmt('D',null,null,-2)) B

WHERE A.MEMBER = B.MEMBER

)) AS DECIMAL(5,2)) AS PCT_ROWS_READ,

SUBSTR(A.STMT_TEXT,1,110) AS HEAVY_READER_SQL

FROM table(mon_get_pkg_cache_stmt('D',null,null,-2)) A

WHERE A.ROWS_READ > 0 AND A.NUM_EXECUTIONS > 0

ORDER BY A.MEMBER ASC, 3 DESC, 2 DESC FETCH FIRST 25 ROWS ONLY;

I'll work on the other scripts too...