DB2 II: Performance Monitoring, Tuning and Capacity Planning Guide

450 DB2 II: Performance Monitoring, Tuning and Capacity Planning Guide

----------------

Parallelism: None

CPU Speed: 4.723442e-07

Comm Speed: 100

Buffer Pool size: 75000

Sort Heap size: 20000

Database Heap size: 1200

Lock List size: 100

Maximum Lock List: 10

Average Applications: 1

Locks Available: 1020

Package Context:

---------------

SQL Type: Dynamic

Optimization Level: 5

Blocking: Block All Cursors

Isolation Level: Cursor Stability

The EXPLAIN INSTANCE section lists overall DB2 instance and database

parameter information.

Besides identification details such as the DB2 code base level, requestor name,

and time of request, this section also provides environmental settings in which

the SQL compiler optimized the query. Information provided includes total buffer

pool size (sum of all buffer pools including temporary table spaces), sort heap

size, type of query parallelism, dynamic or static SQL, optimization level, and

isolation levels. The CPU and Communications speeds are used by the optimizer

to compare the capabilities of the remote data source servers during cost

optimization to determine predicate pushdown. This environmental settings are

critical to understanding the optimizer’s decision in arriving at a particular access

plan.

SQL STATEMENT section

Example B-3 lists the section that provides additional information about the SQL

statement, and shows how DB2 Query Rewrite has rewritten the SQL

before it is

processed by pushdown analysis. Using global semantics, query rewrite

transforms SQL statements into forms that can be optimized more easily, and as

a result can improve the possible access paths. Queries might be rewritten in

multiple ways, including operation merging, operation movement, and predicate

translation.

Example: B-3 STATEMENT section

---------------- STATEMENT 1 SECTION 1 ----------------

QUERYNO: 1

Appendix B. DB2 EXPLAIN facility with DB2 Information Integrator 451

QUERYTAG:

Statement Type: Select

Updatable: No

Deletable: No

Query Degree: 1

Original Statement:

------------------

SELECT L_RETURNFLAG, L_LINESTATUS, SUM(L_QUANTITY) AS SUM_QTY,

SUM(L_EXTENDEDPRICE) AS SUM_BASE_PRICE, SUM(L_EXTENDEDPRICE *

(1-L_DISCOUNT)) AS SUM_DISC_PRICE, SUM(L_EXTENDEDPRICE *

(1-L_DISCOUNT) * (1+L_TAX)) AS SUM_CHARGE, AVG(L_QUANTITY) AS AVG_QTY,

AVG(L_EXTENDEDPRICE) AS AVG_PRICE, AVG(L_DISCOUNT) AS AVG_DISC,

COUNT(*) AS COUNT_ORDER

FROM ora.LINEITEM

WHERE L_SHIPDATE <= DATE ('1998-12-01') - 90 DAYS

GROUP BY L_RETURNFLAG, L_LINESTATUS

ORDER BY L_RETURNFLAG, L_LINESTATUS

Optimized Statement:

-------------------

SELECT Q3.$C0 AS "L_RETURNFLAG", Q3.$C1 AS "L_LINESTATUS", Q3.$C2 AS

"SUM_QTY", Q3.$C3 AS "SUM_BASE_PRICE", Q3.$C4 AS "SUM_DISC_PRICE",

Q3.$C5 AS "SUM_CHARGE", (Q3.$C2 / Q3.$C6) AS "AVG_QTY", (Q3.$C3 /

Q3.$C6) AS "AVG_PRICE", (Q3.$C7 / Q3.$C6) AS "AVG_DISC", Q3.$C6 AS

"COUNT_ORDER"

FROM

(SELECT Q2.$C0, Q2.$C1, SUM(Q2.$C2), SUM(Q2.$C3), SUM((Q2.$C3 * (1 -

Q2.$C4))), SUM(((Q2.$C3 * (1 - Q2.$C4)) * (1 + Q2.$C5))), COUNT(*

), SUM(Q2.$C4)

FROM

(SELECT Q1.L_RETURNFLAG, Q1.L_LINESTATUS, Q1.L_QUANTITY,

Q1.L_EXTENDEDPRICE, Q1.L_DISCOUNT, Q1.L_TAX

FROM ORA.LINEITEM AS Q1

WHERE (Q1.L_SHIPDATE <= '09/02/1998')) AS Q2

GROUP BY Q2.$C1, Q2.$C0) AS Q3

ORDER BY Q3.$C0, Q3.$C1

Two versions of the text of the SQL statement are recorded for each statement

explained.

򐂰 The original statement is the code that the SQL compiler receives from the

application. The original SQL statement in db2exfmt shows the SQL

submitted by the user. If the user SQL included any views, their names

appear in the original SQL statement.

򐂰 The Optimized statement is reverse translated from the internal compiler

representation of the query.