セクションから得られる Explain 情報を使った照会パフォーマンスの調査
Explain 機能を使用して、ステートメント自体のセクションからアクセス・プランを生成することにより、特定のステートメントが実際に実行されるときの (または実際に実行されたときの) そのアクセス・プランを調べることができます。 それに対し、EXPLAIN ステートメントを使用する場合は、ステートメントを再コンパイルすることによってアクセス・プランが作成されます。 これら 2 つの各アクセス・プランの作成方法によって生成されるアクセス・プランは、それぞれ異なる場合があります。 例えば、セクションのステートメントが 2 時間前にコンパイルされた場合、そのステートメントが使用するアクセス・プランは、ステートメントに対して EXPLAIN ステートメントを実行することによって生成されるアクセス・プランと異なる可能性があります。
アクティビティー・イベント・モニターの情報が得られる場合、EXPLAIN_FROM_ACTIVITY プロシージャーを使って実行した後、セクションのアクセス・プランを生成できます。 (セクション actuals を収集している場合、アクセス・プランの Explain 機能によって生成される見積もりとこの情報を一緒に表示することもできます。 詳しくは、 「セクション actuals のキャプチャーおよびアクセス」 を参照してください。)
ステートメントのアクティビティー・イベント・モニター情報が得られない場合、EXPLAIN_FROM_SECTION プロシージャーを使用して、パッケージ・キャッシュに保管されているセクションに基づいてステートメントが実行されるときのそのアクセス・プランを生成できます。 このトピックでは、EXPLAIN_FROM_SECTION を使用してパッケージ・キャッシュのセクション情報に基づいてステートメントのアクセス・プラン情報を表示する方法を示します。
始める前に
このタスクでは、Explain 機能で必要な Explain 表が既に作成済みであることを前提としています。
このタスクについて
プロシージャー
このプロシージャーの最初の部分は、CPU が最も集中しているステートメントを特定する方法を示しています。 その後、EXPLAIN_FROM_SECTION プロシージャーを使用して、実際に実行されるそのステートメントのアクセス・プラン情報を表示する方法を示しています。
- 費やされているプロセッサー時間が最も長いステートメントを特定します。
上記 SQL は、全メンバーの平均プロセッサー時間を計算するときに 0 で除算されないようにするために作成されます。 また、Explain 機能は DDL ステートメントに対しては実行されないため、DML ステートメントの調査のみを行います。SELECT SECTION_TYPE, CASE WHEN SUM(NUM_COORD_EXEC_WITH_METRICS) > 0 THEN SUM(TOTAL_CPU_TIME)/SUM(NUM_COORD_EXEC_WITH_METRICS) ELSE 0 END as AVG_CPU_TIME, EXECUTABLE_ID, VARCHAR(STMT_TEXT, 200) AS TEXT FROM TABLE(MON_GET_PKG_CACHE_STMT ( 'D', NULL, NULL, -2)) as T WHERE T.NUM_EXEC_WITH_METRICS <> 0 AND STMT_TYPE_ID LIKE 'DML%' GROUP BY SECTION_TYPE, EXECUTABLE_ID, VARCHAR(STMT_TEXT, 200) ORDER BY AVG_CPU_TIME DESCこの照会の結果は、次のようになります。SECTION_TYPE AVG_CPU_TIME EXECUTABLE_ID TEXT ------------ -------------------- ------------------------------------------------------------------- -------------------------------------------------- D 250000 x'01000000000000005F0000000000000000000000020020101108135629359000' select cust_last_name, cust_cc_number, cust_intere SQL0445W Value "SELECT POLICY FROM SYSTOOLS.POLICY WHERE MED='DB2TableMainte" has been truncated. SQLSTATE=01004 D 15625 x'01000000000000001B0000000000000000000000020020101108135017625000' UPDATE SYSTOOLS.HMON_ATM_INFO SET STATS_LOCK = 'N' D 15625 x'0100000000000000200000000000000000000000020020101108135018296001' UPDATE SYSTOOLS.HMON_ATM_INFO AS ATM SET ATM.STATS D 15625 x'0100000000000000210000000000000000000000020020101108135018312001' UPDATE SYSTOOLS.HMON_ATM_INFO AS ATM SET STATS_FLA D 7812 x'0100000000000000150000000000000000000000020020101108135016984000' SELECT COLNAME, TYPENAME FROM SYSCAT.COLUMNS WHER D 0 x'0100000000000000160000000000000000000000020020101108135017156000' SELECT TRIGNAME FROM SYSCAT.TRIGGERS WHERE TABNAM D 0 x'0100000000000000190000000000000000000000020020101108135017484000' SELECT TABNAME FROM SYSCAT.TABLES WHERE TABNAME=' D 0 x'01000000000000001A0000000000000000000000020020101108135017500000' SELECT TABNAME FROM SYSCAT.TABLES WHERE TABNAME=' D 0 x'01000000000000001C0000000000000000000000020020101108135017750002' SELECT CREATOR, NAME, CTIME FROM SYSIBM.SYSTABLES D 0 x'01000000000000001D0000000000000000000000020020101108135017828001' SELECT CREATE_TIME FROM SYSTOOLS.HMON_ATM_INFO WHE D 0 x'01000000000000001E0000000000000000000000020020101108135018000001' DELETE FROM SYSTOOLS.HMON_ATM_INFO AS ATM WHERE NO D 0 x'01000000000000001F0000000000000000000000020020101108135018093000' SELECT POLICY FROM SYSTOOLS.POLICY WHERE MED='DB2T D 0 x'0100000000000000220000000000000000000000020020101108135018328001' SELECT IBM.TID, IBM.FID FROM SYSIBM.SYSTABLES AS I D 0 x'0100000000000000230000000000000000000000020020101108135018343001' SELECT POLICY FROM SYSTOOLS.POLICY WHERE MED='DB2C 14 record(s) selected with 1 warning messages printed. - 上記の照会の出力に基づいて、EXPLAIN_FROM_SECTION プロシージャーを使用して、CPU が最も集中しているステートメントのセクションから Explain 情報を生成します。
CALL EXPLAIN_FROM_SECTION (x'01000000000000005F0000000000000000000000020020101108135629359000' ,'M', NULL, 0, NULL, ?, ?, ?, ?, ? )EXPLAIN_FROM_SECTION プロシージャーの出力は、次のようになります。Value of output parameters -------------------------- Parameter Name : EXPLAIN_SCHEMA Parameter Value : DB2DOCS Parameter Name : EXPLAIN_REQUESTER Parameter Value : DB2DOCS Parameter Name : EXPLAIN_TIME Parameter Value : 2010-11-08-13.57.52.984001 Parameter Name : SOURCE_NAME Parameter Value : SQLC2H21 Parameter Name : SOURCE_SCHEMA Parameter Value : NULLID Parameter Name : SOURCE_VERSION Parameter Value : - Explain 情報を調べるには、SQL を使用して Explain 表を調べるか、 db2exfmt コマンドを使用して情報を読みやすいようにフォーマット設定します。 例えば、前のステップで収集した Explain 情報に対して db2exfmt -d gsdb -e db2docs -w 2010-11-08-13.57.52.984001 -n SQLC2H21 -s NULLID -t -#0 を実行すると、以下の出力が生成されます。
(上記の出力では、表示上の都合でいくつかの行が削除されています。)Connecting to the Database. Db2 Universal Database Version 9.7, 5622-044 (c) Copyright IBM Corp. 1991, 2008 Licensed Material - Program Property of IBM IBM DATABASE 2 Explain Table Format Tool ******************** EXPLAIN INSTANCE ******************** DB2_VERSION: 09.07.2 SOURCE_NAME: SQLC2H21 SOURCE_SCHEMA: NULLID SOURCE_VERSION: EXPLAIN_TIME: 2010-11-08-13.57.52.984001 EXPLAIN_REQUESTER: DB2DOCS Database Context: ---------------- Parallelism: None CPU Speed: 8.029852e-007 Comm Speed: 100 Buffer Pool size: 21418 Sort Heap size: 6590 Database Heap size: 1196 Lock List size: 21386 Maximum Lock List: 97 Average Applications: 1 Locks Available: 663821 Package Context: --------------- SQL Type: Dynamic Optimization Level: 5 Blocking: Block All Cursors Isolation Level: Cursor Stability ---------------- STATEMENT 1 SECTION 201 ---------------- QUERYNO: 0 QUERYTAG: CLP Statement Type: Select Updatable: No Deletable: No Query Degree: 1 Original Statement: ------------------ select cust_last_name, cust_cc_number, cust_interest_code from gosalesct.cust_crdt_card C, gosalesct.cust_customer D, gosalesct.cust_interest E where C.cust_code=d.cust_code AND c.cust_code=e.cust_code group by d.cust_last_name, c.cust_cc_number, e.cust_interest_code order by d.cust_last_name ASC, c.cust_cc_number DESC, e.cust_interest_code ASC Optimized Statement: ------------------- SELECT Q5.CUST_LAST_NAME AS "CUST_LAST_NAME", Q5.CUST_CC_NUMBER AS "CUST_CC_NUMBER", Q5.CUST_INTEREST_CODE AS "CUST_INTEREST_CODE" FROM (SELECT Q4.CUST_LAST_NAME, Q4.CUST_CC_NUMBER, Q4.CUST_INTEREST_CODE FROM (SELECT Q2.CUST_LAST_NAME, Q3.CUST_CC_NUMBER, Q1.CUST_INTEREST_CODE FROM GOSALESCT.CUST_INTEREST AS Q1, GOSALESCT.CUST_CUSTOMER AS Q2, GOSALESCT.CUST_CRDT_CARD AS Q3 WHERE (Q3.CUST_CODE = Q1.CUST_CODE) AND (Q1.CUST_CODE = Q2.CUST_CODE)) AS Q4 GROUP BY Q4.CUST_INTEREST_CODE, Q4.CUST_CC_NUMBER, Q4.CUST_LAST_NAME) AS Q5 ORDER BY Q5.CUST_LAST_NAME, Q5.CUST_CC_NUMBER DESC, Q5.CUST_INTEREST_CODE Explain level: Explain from section Access Plan: ----------- Total Cost: 1255.29 Query Degree: 1 Rows RETURN ( 1) Cost I/O | 31255 GRPBY ( 2) 1255.29 NA | 31255 TBSCAN ( 3) 1249.02 NA | 31255 SORT ( 4) 1242.74 NA | 31255 ^HSJOIN ( 5) 1134.96 NA /---------+---------\ 31255 31255 HSJOIN TBSCAN ( 6) ( 9) 406.871 716.136 NA NA /------+-------\ | 31255 31255 31255 TBSCAN IXSCAN TABLE: GOSALESCT ( 7) ( 8) CUST_CUSTOMER 235.505 159.488 Q2 NA NA | | 31255 -1 TABLE: GOSALESCT INDEX: SYSIBM CUST_CRDT_CARD SQL101108113609000 Q3 Q1 ⋮ Objects Used in Access Plan: --------------------------- Schema: SYSIBM Name: SQL101108113609000 Type: Index Last statistics update: 2010-11-08-13.29.58.531000 Number of rows: -1 Number of buffer pool pages: -1 Distinct row values: Yes Tablespace name: GOSALES_TS Tablespace overhead: 7.500000 Tablespace transfer rate: 0.060000 Prefetch page count: 32 Container extent page count: 32 Index clustering statistic: 1.000000 Index leaf pages: 37 Index tree levels: 2 Index full key cardinality: 31255 Base Table Schema: GOSALESCT Base Table Name: CUST_INTEREST Columns in index: CUST_CODE(A) CUST_INTEREST_CODE(A) Schema: GOSALESCT Name: CUST_CRDT_CARD Type: Table Last statistics update: 2010-11-08-11.59.58.531000 Number of rows: 31255 Number of buffer pool pages: 192 Distinct row values: No Tablespace name: GOSALES_TS Tablespace overhead: 7.500000 Tablespace transfer rate: 0.060000 Prefetch page count: 32 Container extent page count: 32 Table overflow record count: 0 Table Active Blocks: -1 Average Row Compression Ratio: 0 Percentage Rows Compressed: 0 Average Compressed Row Size: 0 Schema: GOSALESCT Name: CUST_CUSTOMER Type: Table Last statistics update: 2010-11-08-11.59.59.437000 Number of rows: 31255 Number of buffer pool pages: 672 Distinct row values: No Tablespace name: GOSALES_TS Tablespace overhead: 7.500000 Tablespace transfer rate: 0.060000 Prefetch page count: 32 Container extent page count: 32 Table overflow record count: 0 Table Active Blocks: -1 Average Row Compression Ratio: 0 Percentage Rows Compressed: 0 Average Compressed Row Size: 0 Base Table For Index Not Already Shown: --------------------------------------- Schema: GOSALESCT Name: CUST_INTEREST Time of creation: 2010-11-08-11.30.28.203002 Last statistics update: 2010-11-08-13.29.58.531000 Number of rows: 31255 Number of pages: 128 Number of pages with rows: 124 Table overflow record count: 0 Indexspace name: GOSALES_TS Tablespace name: GOSALES_TS Tablespace overhead: 7.500000 Tablespace transfer rate: 0.060000 Prefetch page count: -1 Container extent page count: 32 Long tablespace name: GOSALES_TS