DB2 Universal Database Version 9.5, 5622-044 (c) Copyright IBM Corp. 1991, 2007 Licensed Material - Program Property of IBM IBM DATABASE 2 Explain Table Format Tool ******************** EXPLAIN INSTANCE ******************** DB2_VERSION: 09.05.2 SOURCE_NAME: SQLC2G13 SOURCE_SCHEMA: NULLID SOURCE_VERSION: EXPLAIN_TIME: 2009-03-12-12.47.10.071556 EXPLAIN_REQUESTER: DB2INST1 Database Context: ---------------- Parallelism: Inter-Partition Parallelism CPU Speed: 1.889377e-07 Comm Speed: 100 Buffer Pool size: 162631 Sort Heap size: 1036 Database Heap size: 2550 Lock List size: 3200 Maximum Lock List: 60 Average Applications: 1 Locks Available: 122880 Package Context: --------------- SQL Type: Dynamic Optimization Level: 5 Blocking: Block All Cursors Isolation Level: Cursor Stability ---------------- STATEMENT 1 SECTION 203 ---------------- QUERYNO: 1 QUERYTAG: Statement Type: Select Updatable: No Deletable: No Query Degree: 1 Original Statement: ------------------ select p.url, sum(p.ref_cnt) from html_pages h, table(parse_urls(h.page)) p group by p.url Optimized Statement: ------------------- SELECT Q4.$C0 AS "URL", Q4.$C1 FROM (SELECT Q3.$C0, SUM(Q3.$C1) FROM (SELECT Q1.$C0, Q1.$C1 FROM (TABLE("DB2INST1"."PARSE_URLS"(Q2.PAGE))) AS Q1, DB2INST1.HTML_PAGES AS Q2) AS Q3 GROUP BY Q3.$C0) AS Q4 Access Plan: ----------- Total Cost: 191.061 Query Degree: 1 Rows RETURN ( 1) Cost I/O | 2000 DTQ ( 2) 191.061 1 | 1000 GRPBY ( 3) 190.715 1 | 2000 MDTQ ( 4) 190.621 1 | 2000 GRPBY ( 5) 190.22 1 | 2000 TBSCAN ( 6) 190.126 1 | 2000 SORT ( 7) 190.031 1 | 488000 NLJOIN ( 8) 10.4944 1 /----+---\ 488 1000 BTQ TBSCAN ( 9) ( 11) 7.72005 0.00568514 1 0 | | 244 1000 TBSCAN TABFNC: DB2INST1 ( 10) PARSE_URLS 7.64927 Q1 1 | 244 TABLE: DB2INST1 HTML_PAGES Q2 Extended Diagnostic Information: -------------------------------- No extended Diagnostic Information for this statement. Plan Details: ------------- 1) RETURN: (Return Result) Cumulative Total Cost: 191.061 Cumulative CPU Cost: 9.71227e+08 Cumulative I/O Cost: 1 Cumulative Re-Total Cost: 3.22201 Cumulative Re-CPU Cost: 1.70533e+07 Cumulative Re-I/O Cost: 0 Cumulative First Row Cost: 190.07 Cumulative Comm Cost: 395.493 Cumulative First Comm Cost: 18.0826 Estimated Bufferpool Buffers: 0 Arguments: --------- BLDLEVEL: (Build level) DB2 v9.5.0.2 : s080811 HEAPUSE : (Maximum Statement Heap Usage) 96 Pages STMTHEAP: (Statement heap size) 6402 Input Streams: ------------- 12) From Operator #2 Estimated number of rows: 2000 Partition Map ID: -100 Partitioning: (COOR ) Coordinator Partition Number of columns: 2 Subquery predicate ID: Not Applicable Column Names: ------------ +Q5.$C1+Q5.URL Partition Column Names: ---------------------- +NONE 2) TQ : (Table Queue) Cumulative Total Cost: 191.061 Cumulative CPU Cost: 9.71227e+08 Cumulative I/O Cost: 1 Cumulative Re-Total Cost: 3.22201 Cumulative Re-CPU Cost: 1.70533e+07 Cumulative Re-I/O Cost: 0 Cumulative First Row Cost: 190.07 Cumulative Comm Cost: 395.493 Cumulative First Comm Cost: 18.0826 Estimated Bufferpool Buffers: 0 Arguments: --------- LISTENER: (Listener Table Queue type) FALSE TQMERGE : (Merging Table Queue flag) FALSE TQREAD : (Table Queue Read type) READ AHEAD TQSEND : (Table Queue Write type) DIRECTED UNIQUE : (Uniqueness required flag) FALSE Input Streams: ------------- 11) From Operator #3 Estimated number of rows: 1000 Partition Map ID: 2 Partitioning: (MULT ) Multiple Partitions Number of columns: 2 Subquery predicate ID: Not Applicable Column Names: ------------ +Q5.$C1+Q5.URL Partition Column Names: ---------------------- +1: Q5.URL Output Streams: -------------- 12) To Operator #1 Estimated number of rows: 2000 Partition Map ID: -100 Partitioning: (COOR ) Coordinator Partition Number of columns: 2 Subquery predicate ID: Not Applicable Column Names: ------------ +Q5.$C1+Q5.URL Partition Column Names: ---------------------- +NONE 3) GRPBY : (Group By) Cumulative Total Cost: 190.715 Cumulative CPU Cost: 9.69394e+08 Cumulative I/O Cost: 1 Cumulative Re-Total Cost: 3.13785 Cumulative Re-CPU Cost: 1.66078e+07 Cumulative Re-I/O Cost: 0 Cumulative First Row Cost: 190.052 Cumulative Comm Cost: 272.281 Cumulative First Comm Cost: 18.0826 Estimated Bufferpool Buffers: 0 Arguments: --------- AGGMODE : (Aggregration Mode) FINAL GROUPBYC: (Group By columns) TRUE GROUPBYN: (Number of Group By columns) 1 GROUPBYR: (Group By requirement) 1: Q3.URL ONEFETCH: (One Fetch flag) FALSE Input Streams: ------------- 10) From Operator #4 Estimated number of rows: 2000 Partition Map ID: 2 Partitioning: (MULT ) Multiple Partitions Number of columns: 2 Subquery predicate ID: Not Applicable Column Names: ------------ +Q3.URL(A)+Q3.REF_CNT Partition Column Names: ---------------------- +1: Q3.URL Output Streams: -------------- 11) To Operator #2 Estimated number of rows: 1000 Partition Map ID: 2 Partitioning: (MULT ) Multiple Partitions Number of columns: 2 Subquery predicate ID: Not Applicable Column Names: ------------ +Q5.$C1+Q5.URL Partition Column Names: ---------------------- +1: Q5.URL 4) TQ : (Table Queue) Cumulative Total Cost: 190.621 Cumulative CPU Cost: 9.68894e+08 Cumulative I/O Cost: 1 Cumulative Re-Total Cost: 3.04333 Cumulative Re-CPU Cost: 1.61076e+07 Cumulative Re-I/O Cost: 0 Cumulative First Row Cost: 190.051 Cumulative Comm Cost: 272.281 Cumulative First Comm Cost: 18.0826 Estimated Bufferpool Buffers: 0 Arguments: --------- LISTENER: (Listener Table Queue type) FALSE PARTCOLS: (Table partitioning columns) 1: Q3.URL SORTKEY : (Sort Key column) 1: Q3.URL(A) TQMERGE : (Merging Table Queue flag) TRUE TQREAD : (Table Queue Read type) READ AHEAD TQSEND : (Table Queue Write type) DIRECTED UNIQUE : (Uniqueness required flag) FALSE Input Streams: ------------- 9) From Operator #5 Estimated number of rows: 2000 Partition Map ID: 2 Partitioning: (MULT ) Multiple Partitions Number of columns: 2 Subquery predicate ID: Not Applicable Column Names: ------------ +Q3.URL(A)+Q3.REF_CNT Partition Column Names: ---------------------- +NONE Output Streams: -------------- 10) To Operator #3 Estimated number of rows: 2000 Partition Map ID: 2 Partitioning: (MULT ) Multiple Partitions Number of columns: 2 Subquery predicate ID: Not Applicable Column Names: ------------ +Q3.URL(A)+Q3.REF_CNT Partition Column Names: ---------------------- +1: Q3.URL 5) GRPBY : (Group By) Cumulative Total Cost: 190.22 Cumulative CPU Cost: 9.66774e+08 Cumulative I/O Cost: 1 Cumulative Re-Total Cost: 3.04333 Cumulative Re-CPU Cost: 1.61076e+07 Cumulative Re-I/O Cost: 0 Cumulative First Row Cost: 190.031 Cumulative Comm Cost: 14.0826 Cumulative First Comm Cost: 14.0826 Estimated Bufferpool Buffers: 0 Arguments: --------- AGGMODE : (Aggregration Mode) INTERMEDIATE GROUPBYC: (Group By columns) TRUE GROUPBYN: (Number of Group By columns) 1 GROUPBYR: (Group By requirement) 1: Q3.URL ONEFETCH: (One Fetch flag) FALSE Input Streams: ------------- 8) From Operator #6 Estimated number of rows: 2000 Partition Map ID: 2 Partitioning: (MULT ) Multiple Partitions Number of columns: 2 Subquery predicate ID: Not Applicable Column Names: ------------ +Q3.URL(A)+Q3.REF_CNT Partition Column Names: ---------------------- +NONE Output Streams: -------------- 9) To Operator #4 Estimated number of rows: 2000 Partition Map ID: 2 Partitioning: (MULT ) Multiple Partitions Number of columns: 2 Subquery predicate ID: Not Applicable Column Names: ------------ +Q3.URL(A)+Q3.REF_CNT Partition Column Names: ---------------------- +NONE 6) TBSCAN: (Table Scan) Cumulative Total Cost: 190.126 Cumulative CPU Cost: 9.66274e+08 Cumulative I/O Cost: 1 Cumulative Re-Total Cost: 2.94882 Cumulative Re-CPU Cost: 1.56073e+07 Cumulative Re-I/O Cost: 0 Cumulative First Row Cost: 190.031 Cumulative Comm Cost: 14.0826 Cumulative First Comm Cost: 14.0826 Estimated Bufferpool Buffers: 0 Arguments: --------- MAXPAGES: (Maximum pages for prefetch) ALL PREFETCH: (Type of Prefetch) NONE SCANDIR : (Scan Direction) FORWARD Input Streams: ------------- 7) From Operator #7 Estimated number of rows: 2000 Partition Map ID: 2 Partitioning: (MULT ) Multiple Partitions Number of columns: 2 Subquery predicate ID: Not Applicable Column Names: ------------ +Q3.URL(A)+Q3.REF_CNT Partition Column Names: ---------------------- +NONE Output Streams: -------------- 8) To Operator #5 Estimated number of rows: 2000 Partition Map ID: 2 Partitioning: (MULT ) Multiple Partitions Number of columns: 2 Subquery predicate ID: Not Applicable Column Names: ------------ +Q3.URL(A)+Q3.REF_CNT Partition Column Names: ---------------------- +NONE 7) SORT : (Sort) Cumulative Total Cost: 190.031 Cumulative CPU Cost: 9.65772e+08 Cumulative I/O Cost: 1 Cumulative Re-Total Cost: 2.85406 Cumulative Re-CPU Cost: 1.51058e+07 Cumulative Re-I/O Cost: 0 Cumulative First Row Cost: 190.031 Cumulative Comm Cost: 14.0826 Cumulative First Comm Cost: 14.0826 Estimated Bufferpool Buffers: 1 Arguments: --------- AGGMODE : (Aggregration Mode) PARTIAL DUPLWARN: (Duplicates Warning flag) FALSE NUMROWS : (Estimated number of rows) 2000 ROWWIDTH: (Estimated width of rows) 89 SORTKEY : (Sort Key column) 1: Q3.URL(A) TEMPSIZE: (Temporary Table Page Size) 4096 UNIQUE : (Uniqueness required flag) FALSE Input Streams: ------------- 6) From Operator #8 Estimated number of rows: 488000 Partition Map ID: 2 Partitioning: (MULT ) Multiple Partitions Number of columns: 2 Subquery predicate ID: Not Applicable Column Names: ------------ +Q3.REF_CNT+Q3.URL Partition Column Names: ---------------------- +NONE Output Streams: -------------- 7) To Operator #6 Estimated number of rows: 2000 Partition Map ID: 2 Partitioning: (MULT ) Multiple Partitions Number of columns: 2 Subquery predicate ID: Not Applicable Column Names: ------------ +Q3.URL(A)+Q3.REF_CNT Partition Column Names: ---------------------- +NONE 8) NLJOIN: (Nested Loop Join) Cumulative Total Cost: 10.4944 Cumulative CPU Cost: 1.5531e+07 Cumulative I/O Cost: 1 Cumulative Re-Total Cost: 2.85406 Cumulative Re-CPU Cost: 1.51058e+07 Cumulative Re-I/O Cost: 0 Cumulative First Row Cost: 7.58945 Cumulative Comm Cost: 14.0826 Cumulative First Comm Cost: 0 Estimated Bufferpool Buffers: 1 Arguments: --------- EARLYOUT: (Early Out flag) NONE FETCHMAX: (Override for FETCH MAXPAGES) IGNORE ISCANMAX: (Override for ISCAN MAXPAGES) IGNORE Input Streams: ------------- 3) From Operator #9 Estimated number of rows: 488 Partition Map ID: 2 Partitioning: (MULT ) Multiple Partitions Number of columns: 1 Subquery predicate ID: Not Applicable Column Names: ------------ +Q2.PAGE Partition Column Names: ---------------------- +NONE 5) From Operator #11 Estimated number of rows: 1000 Partition Map ID: 2 Partitioning: (MULT ) Multiple Partitions Number of columns: 2 Subquery predicate ID: Not Applicable Column Names: ------------ +Q1.REF_CNT+Q1.URL Partition Column Names: ---------------------- +NONE Output Streams: -------------- 6) To Operator #7 Estimated number of rows: 488000 Partition Map ID: 2 Partitioning: (MULT ) Multiple Partitions Number of columns: 2 Subquery predicate ID: Not Applicable Column Names: ------------ +Q3.REF_CNT+Q3.URL Partition Column Names: ---------------------- +NONE 9) TQ : (Table Queue) Cumulative Total Cost: 7.72005 Cumulative CPU Cost: 847099 Cumulative I/O Cost: 1 Cumulative Re-Total Cost: 0.0797109 Cumulative Re-CPU Cost: 421890 Cumulative Re-I/O Cost: 0 Cumulative First Row Cost: 7.58943 Cumulative Comm Cost: 14.0826 Cumulative First Comm Cost: 0 Estimated Bufferpool Buffers: 1 Arguments: --------- JN INPUT: (Join input leg) OUTER LISTENER: (Listener Table Queue type) FALSE TQMERGE : (Merging Table Queue flag) FALSE TQREAD : (Table Queue Read type) READ AHEAD TQSEND : (Table Queue Write type) BROADCAST UNIQUE : (Uniqueness required flag) FALSE Input Streams: ------------- 2) From Operator #10 Estimated number of rows: 244 Partition Map ID: 1 Partitioning: (MULT ) Multiple Partitions Number of columns: 2 Subquery predicate ID: Not Applicable Column Names: ------------ +Q2.$RID$+Q2.PAGE Partition Column Names: ---------------------- +NONE Output Streams: -------------- 3) To Operator #8 Estimated number of rows: 488 Partition Map ID: 2 Partitioning: (MULT ) Multiple Partitions Number of columns: 1 Subquery predicate ID: Not Applicable Column Names: ------------ +Q2.PAGE Partition Column Names: ---------------------- +NONE 10) TBSCAN: (Table Scan) Cumulative Total Cost: 7.64927 Cumulative CPU Cost: 472483 Cumulative I/O Cost: 1 Cumulative Re-Total Cost: 0.0797109 Cumulative Re-CPU Cost: 421890 Cumulative Re-I/O Cost: 0 Cumulative First Row Cost: 7.56988 Cumulative Comm Cost: 0 Cumulative First Comm Cost: 0 Estimated Bufferpool Buffers: 1 Arguments: --------- MAXPAGES: (Maximum pages for prefetch) ALL PREFETCH: (Type of Prefetch) NONE ROWLOCK : (Row Lock intent) NEXT KEY SHARE SCANDIR : (Scan Direction) FORWARD TABLOCK : (Table Lock intent) INTENT SHARE TBISOLVL: (Table access Isolation Level) CURSOR STABILITY Input Streams: ------------- 1) From Object DB2INST1.HTML_PAGES Estimated number of rows: 244 Partition Map ID: 1 Partitioning: (MULT ) Multiple Partitions Number of columns: 2 Subquery predicate ID: Not Applicable Column Names: ------------ +Q2.$RID$+Q2.PAGE Partition Column Names: ---------------------- +NONE Output Streams: -------------- 2) To Operator #9 Estimated number of rows: 244 Partition Map ID: 1 Partitioning: (MULT ) Multiple Partitions Number of columns: 2 Subquery predicate ID: Not Applicable Column Names: ------------ +Q2.$RID$+Q2.PAGE Partition Column Names: ---------------------- +NONE 11) TBSCAN: (Table Scan) Cumulative Total Cost: 0.00568514 Cumulative CPU Cost: 30090 Cumulative I/O Cost: 0 Cumulative Re-Total Cost: 0.00568514 Cumulative Re-CPU Cost: 30090 Cumulative Re-I/O Cost: 0 Cumulative First Row Cost: 1.70044e-05 Cumulative Comm Cost: 0 Cumulative First Comm Cost: 0 Estimated Bufferpool Buffers: 0 Arguments: --------- JN INPUT: (Join input leg) INNER MAXPAGES: (Maximum pages for prefetch) ALL PREFETCH: (Type of Prefetch) NONE ROWLOCK : (Row Lock intent) NEXT KEY SHARE SCANDIR : (Scan Direction) FORWARD TABLOCK : (Table Lock intent) INTENT SHARE TBISOLVL: (Table access Isolation Level) CURSOR STABILITY Input Streams: ------------- 4) From Object DB2INST1.PARSE_URLS Estimated number of rows: 1000 Partition Map ID: 2 Partitioning: (MULT ) Multiple Partitions Number of columns: 2 Subquery predicate ID: Not Applicable Column Names: ------------ +Q1.REF_CNT+Q1.URL Partition Column Names: ---------------------- +NONE Output Streams: -------------- 5) To Operator #8 Estimated number of rows: 1000 Partition Map ID: 2 Partitioning: (MULT ) Multiple Partitions Number of columns: 2 Subquery predicate ID: Not Applicable Column Names: ------------ +Q1.REF_CNT+Q1.URL Partition Column Names: ---------------------- +NONE Objects Used in Access Plan: --------------------------- Schema: DB2INST1 Name: HTML_PAGES Type: Table Time of creation: 2009-03-12-12.30.30.524750 Last statistics update: 2009-03-12-12.46.26.741046 Number of columns: 2 Number of rows: 244 Width of rows: 19 Number of buffer pool pages: 1 Number of data partitions: 1 Distinct row values: No Tablespace name: CACMTS Tablespace overhead: 7.500000 Tablespace transfer rate: 0.060000 Source for statistics: Single Node Prefetch page count: 64 Container extent page count: 64 Table overflow record count: 0 Table Active Blocks: -1 Average Row Compression Ratio: 0 Percentage Rows Compressed: 0 Average Compressed Row Size: 0 Schema: DB2INST1 Name: PARSE_URLS Type: Table Function Time of creation: Last statistics update: Number of columns: 2 Number of rows: 1000 Width of rows: 84 Number of buffer pool pages: -1 Distinct row values: No Tablespace name: Tablespace overhead: -1.000000 Tablespace transfer rate: -1.000000 Source for statistics: Single Node