EXPLAIN facility tools
The EXPLAIN facility provides
the capability to capture and view detailed information about the access plan
chosen for a particular SQL statement. This information includes performance
data that can be used to identify poorly written or executing queries.
The EXPLAIN facility uses a
special set of tables to store the information about the queries. These tables
can be created by running the EXPLAIN.DDL script found in the sqllib/misc
directory or by executing the SYSINSTALLOBJECTS procedure:
CALL SYSPROC.SYSINSTALLOBJECTS('EXPLAIN',
'C', CAST (NULL AS VARCHAR (128)), 'DBA');
Attribute
Visual Plan db2expln db2exfmt
Type of output
GUI
Text Text
Static SQL statement
No
Yes
Yes
Dynamic SQL Statement
Yes
Yes
Yes
Requires Explain tables
Yes
No
Yes
Detailed optimizer information Yes
No
Yes
If you want to use the db2expln
tool to generate an access plan, use the following command:
db2expln -d SAMPLE -t -g
-statement "SELECT * FROM employee"
This will generate access plan information
that looks something like the following:
Statement:
SELECT * FROM employee
Intra-Partition Parallelism
Degree = 4
Section Code Page = 1208
Estimated Cost = 21.522827
Estimated Cardinality =
1000.000000
Process Using 4 Subagents
| Access Table Name =
DB2INST1.EMPLOYEE ID = 2,7
| | #Columns = 14
| | Parallel Scan
| | Skip Inserted Rows
| | Avoid Locking Committed Data
| | Currently Committed for
Cursor Stability
| | May participate in Scan
Sharing structures
| | Scan may start anywhere and
wrap, for completion
| | Fast scan, for purposes of
scan sharing management
| | Scan can be throttled in
scan sharing management
| | Relation Scan
| | | Prefetch: Eligible
| | Lock Intents
| | Table: Intent Share
| | | Row: Next Key Share
| | Sargable Predicate(s)
| | | Insert Into Asynchronous
Local Table Queue ID () = q1
| Insert Into Asynchronous Local
Table Queue Completion () ID = q1
Access Local Table Queue ID () =
q1 #Columns = 14
Return Data to Application
| #Columns = 14
End of section
Optimizer Plan:
Rows
Operator
(ID)
Cost
1000
RETURN
( 1)
21.5228
|
1000
LTQ
( 2)
21.5228
|
1000
TBSCAN
( 3)
21.3901
|
1000
Table:
DB2INST1
EMPLOYEE
If you want to generate a
detailed report containing all of the explain data, you can execute the
db2exfmt command in a sequence shown as follows:
Enable the Explain facility by
setting the current explain mode to EXPLAIN. This will configure the CURRENT EXPLAIN
MODE special register so that the EXPLAIN facility can capture the explain
information without executing the SQL statement:
SET CURRENT EXPLAIN MODE
EXPLAIN;
Run the actual SQL statement in
the same session:
db2 -tvf employee.ddl >
employee.log
Disable the Explain facility by
setting the current explain mode to NO:
SET CURRENT EXPLAIN MODE NO
Run the db2exfmt tool:
db2exfmt -d SAMPLE -g TIC -w -1
-s % -n % -# 0 -o employee.db2exfmt.out
The detailed explain report will
look something like the following:
Original Statement:
Select * from employee
Optimized Statement:
-------------------
SELECT
Q1.EMPNO AS "EMPNO",
Q1.FIRSTNME AS
"FIRSTNME",
Q1.MIDINIT AS
"MIDINIT",
Q1.LASTNAME AS
"LASTNAME",
Q1.WORKDEPT AS
"WORKDEPT",
Q1.PHONENO AS
"PHONENO",
Q1.HIREDATE AS
"HIREDATE",
Q1.JOB AS "JOB",
Q1.EDLEVEL AS
"EDLEVEL",
Q1.SEX AS "SEX",
Q1.BIRTHDATE AS
"BIRTHDATE",
Q1.SALARY AS "SALARY",
Q1.BONUS AS "BONUS",
Q1.COMM AS "COMM"
FROM DB2INST1.EMPLOYEE AS Q1
Access Plan:
-----------
Total Cost: 21.5228
Query Degree: 4
Rows
RETURN
( 1)
Cost
I/O
|
1000
LTQ
( 2)
21.5228
3
|
1000
TBSCAN
( 3)
21.3901
3
|
1000
TABLE: DB2INST1
EMPLOYEE
Q1
Operator Symbols :
------------------
Symbol Description
ATQ : Asynchrony
BTQ : Broadcast
CTQ : Column-organized data
DTQ : Directed
LTQ : Intra-partition
parallelism
MTQ : Merging (sorted)
STQ : Scatter
RCTQ : Column-organized data
with row as the source
XTQ : XML aggregation
TQ* : Listener
Plan Details:
-------------
1) RETURN: (Return Result)
Cumulative Total Cost: 21.5228
Cumulative CPU Cost: 2.73691e+06
Cumulative I/O Cost: 3
Cumulative Re-Total Cost:
0.244257
Cumulative Re-CPU Cost:
1.72372e+06
Cumulative Re-I/O Cost: 0
Cumulative First Row Cost:
7.07554
Estimated Bufferpool Buffers: 3
Arguments:
---------
BLDLEVEL: (Build level)
DB2 v11.1.3.3 : s1803021700
ENVVAR : (Environment Variable)
DB2_ANTIJOIN=EXTEND
HEAPUSE : (Maximum Statement
Heap Usage)
96 Pages
PLANID : (Access plan
identifier)
41f5e77e71817356
PREPTIME: (Statement prepare
time)
15 milliseconds
SEMEVID : (Semantic environment
identifier)
431f78d03d9bb07e
STMTHEAP: (Statement heap size)
16384
STMTID : (Normalized statement
identifier)
20314d5950426a1f
Input Streams:
-------------
3) From Operator #2
Estimated number of rows: 1000
Number of columns: 14
Subquery predicate ID: Not
Applicable
Column Names:
------------
+Q2.COMM+Q2.BONUS+Q2.SALARY+Q2.BIRTHDATE
+Q2.SEX+Q2.EDLEVEL+Q2.JOB+Q2.HIREDATE
+Q2.PHONENO+Q2.WORKDEPT+Q2.LASTNAME+Q2.MIDINIT
+Q2.FIRSTNME+Q2.EMPNO
....
....
Objects Used in Access Plan:
---------------------------
Schema: DB2INST1
Name: ADEFUSR
Type: Materialized View
(reference only)
Schema: DB2INST1
Name: EMPLOYEE
Type: Table
Time of creation:
2018-05-13-20.43.49.871419
Last statistics update:
Number of columns: 14
Number of rows: 1000
Width of rows: 88
Number of buffer pool pages: 3
Number of data partitions: 1
Distinct row values: No
Tablespace name: USERSPACE1
Tablespace overhead: 6.725000
Tablespace transfer rate:
0.320000
Source for statistics: Single
Node
Prefetch page count: 32
Container extent page count: 32
Table overflow record count: 0
Table Active Blocks: -1
Average Row Compression Ratio:
-1
Percentage Rows Compressed: -1
Average Compressed Row Size: -1
Comments
Post a Comment