While reviewing a clients’ DB2 performance issues, we researched the SQL that was being executed against a table on a daily basis by analyzing the DB2 access methods with the plan table. The DB2 access methods are captured at compile and bind time through the explain process. Its output is available in the plan table. By joining the IBM DB2 catalog table with the explain plan table information, we saw that the access to this table had many tablespace scans and index scans.
Excess Tablespace Scans Revealed in Plan Table
Looking at the plan table showed that there were over 1,000 different SQL statements that have been compiled and bound against the table. Of these statements there were 78 DB2 tablespace scans which are very resource intensive, because the access has to scan the entire table to retrieve the rows requested.
Also there were 23 DB2 index scans indicated in the plan table which can also be very expensive depending on the query criteria. The index scans needed further research. If the query referenced a large number of rows it may be too big for the system and cause the index scan to be dynamically changed to a tablespace scan. In this case, I found 613 index scans that were changed to tablespace scans. Application development needed to understand which programs caused these performance problems or when these situations were happening.
Researching the access paths chosen by all the programs system wide showed that the access against this table was fairly typical. There were a large number of programs that have good access methods and a smaller number with DB2 tablespace or index scans.
Query for DB2 Tablespace or Index Scans
The following query can be used in your own shop to determine queries that are using tablespace or index space scans from the explain plan table. It will also show you which queries are using list prefetch.
| — ACCESS TYPE FOR THE PROGRAMS REFERENCING A PARTICULAR TABLE — SELECT CONDITION, COUNT(*) FROM ( SELECT DISTINCT PROGNAME, QUERYNO, CASE WHEN ACCESSTYPE = ‘R’ THEN ‘TABLESPACE SCAN’ WHEN ACCESSTYPE = ‘I’ AND PREFETCH = ‘L’ THEN ‘LIST PREFETCH ‘ WHEN ACCESSTYPE = ‘I’ AND MATCHCOLS = 0 THEN ‘INDEX SCAN ‘ WHEN METHOD = 4 AND SORTN_JOIN= ‘Y’ THEN ‘HYBIRD JOIN ‘ WHEN MERGE_JOIN_COLS IS NOT NULL THEN ‘MERGE SCAN JOIN’ ELSE ‘GOOD ACCESS’ END AS CONDITION FROM PROD.PLAN_TABLE WHERE PROGNAME IN (SELECT DISTINCT DNAME FROM SYSIBM.SYSPACKDEP WHERE BTYPE =’T’ AND BNAME =’TABLE_NAME’ AND BQUALIFIER = ‘PROD’) ) AS STUFF GROUP BY CONDITION ORDER BY 2; |
Summary
Use the plan table queries listed to give you an idea of what queries are taking excess resources in your own system.
__________________________________________________________
Dave Beulke is an internationally recognized DB2 consultant, DB2 trainer and education instructor. Dave helps his clients improve their strategic direction, dramatically improve DB2 performance and reduce their CPU demand saving millions in their systems, databases and application areas within their mainframe, UNIX and Windows environments.
Leave a Reply