Scribd
Upload
27 views9 pages

Db2explain Advis

db2explain_advis

Uploaded by

Atul Sharma
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as TXT, PDF, TXT or read online on Scribd
27 views9 pages

Db2explain Advis

db2explain_advis

Uploaded by

Atul Sharma
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as TXT, PDF, TXT or read online on Scribd
You are on page 1/ 9

Steps

Please connect the db2 database :-

1) find explain plan for culprit sql

e.g.
vi culpritsql.sql

explain plan for


SELECT JOB_EXECUTION_ID, START_TIME, END_TIME, STATUS, EXIT_CODE, EXIT_MESSAGE,
CREATE_TIME, LAST_UPDATED, VERSION, JOB_CONFIGURATION_LOCATION
from BATCH_JOB_EXECUTION
where JOB_INSTANCE_ID =1 order by JOB_EXECUTION_ID desc;

2) extract the file


db2 -tvf culpritsql.sql

e.g.

db2 -tvf culpritsql.sql


explain plan for SELECT JOB_EXECUTION_ID, START_TIME, END_TIME, STATUS, EXIT_CODE,
EXIT_MESSAGE, CREATE_TIME, LAST_UPDATED, VERSION, JOB_CONFIGURATION_LOCATION from
BATCH_JOB_EXECUTION where JOB_INSTANCE_ID =1 order by JOB_EXECUTION_ID desc
DB20000I The SQL command completed successfully.

3) check the access plan

db2exfmt -d <DBNAME> -1 -o explain.out

db2exfmt -d ACCTDB -1 -o explain.out

4) check the advise for new index recommendations

db2advis -d ACCTDB -s "SELECT JOB_EXECUTION_ID, START_TIME, END_TIME, STATUS,


EXIT_CODE, EXIT_MESSAGE, CREATE_TIME, LAST_UPDATED, VERSION,
JOB_CONFIGURATION_LOCATION from BATCH_JOB_EXECUTION where JOB_INSTANCE_ID =1 order
by JOB_EXECUTION_ID desc"

5) add index and perform runstats on that table

db2 "CREATE INDEX "ACCT"."IDX1" ON "ACCT"."BATCH_JOB_EXECUTION" ("JOB_INSTANCE_ID"


ASC, "JOB_EXECUTION_ID" DESC) ALLOW REVERSE SCANS COLLECT SAMPLED DETAILED
STATISTICS "

DB20000I The SQL command completed successfully.

db2 "runstats on table "ACCT"."BATCH_JOB_EXECUTION""

DB20000I The RUNSTATS command completed successfully.

6) check access plan again after index for new cost after index

###########################################################################
E.g.
===> Pre index explain

DB2 Universal Database Version 11.1, 5622-044 (c) Copyright IBM Corp. 1991, 2017
Licensed Material - Program Property of IBM
IBM DATABASE 2 Explain Table Format Tool

******************** EXPLAIN INSTANCE ********************

DB2_VERSION: 11.01.3
FORMATTED ON DB: TEST
SOURCE_NAME: SQLC2O27
SOURCE_SCHEMA: NULLID
SOURCE_VERSION:
EXPLAIN_TIME: 2021-02-10-14.38.07.984952
EXPLAIN_REQUESTER: DB2INST1

Database Context:
----------------
Parallelism: None
CPU Speed: 1.417033e-07
Comm Speed: 100
Buffer Pool size: 1173668
Sort Heap size: 2825
Database Heap size: 4478
Lock List size: 511962
Maximum Lock List: 98
Average Applications: 1
Locks Available: 16055128

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
JOB_EXECUTION_ID,
START_TIME,
END_TIME,
STATUS,
EXIT_CODE,
EXIT_MESSAGE,
CREATE_TIME,
LAST_UPDATED,
VERSION,
JOB_CONFIGURATION_LOCATION
from
BATCH_JOB_EXECUTION
where
JOB_INSTANCE_ID =1
order by
JOB_EXECUTION_ID desc

Optimized Statement:
-------------------
SELECT
Q1.JOB_EXECUTION_ID AS "JOB_EXECUTION_ID",
Q1.START_TIME AS "START_TIME",
Q1.END_TIME AS "END_TIME",
Q1.STATUS AS "STATUS",
Q1.EXIT_CODE AS "EXIT_CODE",
Q1.EXIT_MESSAGE AS "EXIT_MESSAGE",
Q1.CREATE_TIME AS "CREATE_TIME",
Q1.LAST_UPDATED AS "LAST_UPDATED",
Q1.VERSION AS "VERSION",
Q1.JOB_CONFIGURATION_LOCATION AS "JOB_CONFIGURATION_LOCATION"
FROM
ACCT.BATCH_JOB_EXECUTION AS Q1
WHERE
(Q1.JOB_INSTANCE_ID = 1)
ORDER BY
Q1.JOB_EXECUTION_ID DESC

Access Plan:
-----------
Total Cost: 466.116
Query Degree: 1

Rows
RETURN
( 1)
Cost
I/O
|
1.00542
TBSCAN
( 2)
466.116
99
|
1.00542
SORT
( 3)
466.116
99
|
1.00542
TBSCAN
( 4)
466.116
99
|
2414
TABLE: ACCT
BATCH_JOB_EXECUTION
Q1

Extended Diagnostic Information:


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

No extended Diagnostic Information for this statement.

===> pre index advis

db2advis -d TEST -s "SELECT JOB_EXECUTION_ID, START_TIME, END_TIME, STATUS,


EXIT_CODE, EXIT_MESSAGE, CREATE_TIME, LAST_UPDATED, VERSION,
JOB_CONFIGURATION_LOCATION from ACCT.BATCH_JOB_EXECUTION where JOB_INSTANCE_ID =1
order by JOB_EXECUTION_ID desc"

Using user id as default schema name. Use -n option to specify schema


execution started at timestamp 2021-02-10-14.40.23.489969
Recommending indexes...
total disk space needed for initial set [ 0.036] MB
total disk space constrained to [2479.934] MB
Trying variations of the solution set.
1 indexes in current solution
[466.0000] timerons (without recommendations)
[ 14.0000] timerons (with current solution)
[97.00%] improvement

--
--
-- LIST OF RECOMMENDED INDEXES
-- ===========================
-- index[1], 0.036MB
CREATE INDEX "DB2INST1"."IDX2102101240290" ON "ACCT "."BATCH_JOB_EXECUTION"
("JOB_INSTANCE_ID" ASC, "JOB_EXECUTION_ID" DESC) ALLOW
REVERSE SCANS COLLECT SAMPLED DETAILED STATISTICS;
COMMIT WORK ;

--
--
-- RECOMMENDED EXISTING INDEXES
-- ============================

--
--
-- UNUSED EXISTING INDEXES
-- ============================
-- ===========================
--

-- ====ADVISOR DETAILED XML OUTPUT=============


-- ==(Benefits do not include clustering recommendations)==
--

--<?xml version="1.0"?>
--<design-advisor>
--<index>
--<identifier>
--<name>IDX2102101240290</name>
--<schema>DB2INST1</schema>
--</identifier>
--<table><identifier>
--<name>BATCH_JOB_EXECUTION</name>
--<schema>ACCT </schema>
--</identifier></table>
--<statementlist>1</statementlist>
--<benefit>452.000000</benefit>
--<overhead>0.000000</overhead>
--<diskspace>0.036156</diskspace>
--</index>
--<statement>
--<statementnum>1</statementnum>
--<statementtext>
-- SELECT JOB_EXECUTION_ID, START_TIME, END_TIME, STATUS,
-- EXIT_CODE, EXIT_MESSAGE, CREATE_TIME, LAST_UPDATED,
-- VERSION, JOB_CONFIGURATION_LOCATION from ACCT.BATCH_JOB_EXECUTION
-- where JOB_INSTANCE_ID =1 order by JOB_EXECUTION_ID
-- desc
--</statementtext>
--<objects>
--<identifier>
--<name>BATCH_JOB_EXECUTION</name>
--<schema>ACCT </schema>
--</identifier>
--<identifier>
--<name>IDX2102101240290</name>
--<schema>DB2INST1</schema>
--</identifier>
--</objects>
--<benefit>452.000000</benefit>
--<frequency>1</frequency>
--</statement>
--</design-advisor>

-- ====ADVISOR DETAILED XML OUTPUT=============


--

14 solutions were evaluated by the advisor


DB2 Workload Performance Advisor tool is finished.

===>Post index explain


DB2 Universal Database Version 11.1, 5622-044 (c) Copyright IBM Corp. 1991, 2017
Licensed Material - Program Property of IBM
IBM DATABASE 2 Explain Table Format Tool

******************** EXPLAIN INSTANCE ********************


DB2_VERSION: 11.01.3
FORMATTED ON DB: TEST
SOURCE_NAME: SQLC2O27
SOURCE_SCHEMA: NULLID
SOURCE_VERSION:
EXPLAIN_TIME: 2021-02-10-14.42.14.823357
EXPLAIN_REQUESTER: DB2INST1

Database Context:
----------------
Parallelism: None
CPU Speed: 1.417033e-07
Comm Speed: 100
Buffer Pool size: 1171844
Sort Heap size: 2263
Database Heap size: 4478
Lock List size: 230259
Maximum Lock List: 98
Average Applications: 1
Locks Available: 7220922

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
JOB_EXECUTION_ID,
START_TIME,
END_TIME,
STATUS,
EXIT_CODE,
EXIT_MESSAGE,
CREATE_TIME,
LAST_UPDATED,
VERSION,
JOB_CONFIGURATION_LOCATION
from
BATCH_JOB_EXECUTION
where
JOB_INSTANCE_ID =1
order by
JOB_EXECUTION_ID desc
Optimized Statement:
-------------------
SELECT
Q1.JOB_EXECUTION_ID AS "JOB_EXECUTION_ID",
Q1.START_TIME AS "START_TIME",
Q1.END_TIME AS "END_TIME",
Q1.STATUS AS "STATUS",
Q1.EXIT_CODE AS "EXIT_CODE",
Q1.EXIT_MESSAGE AS "EXIT_MESSAGE",
Q1.CREATE_TIME AS "CREATE_TIME",
Q1.LAST_UPDATED AS "LAST_UPDATED",
Q1.VERSION AS "VERSION",
Q1.JOB_CONFIGURATION_LOCATION AS "JOB_CONFIGURATION_LOCATION"
FROM
ACCT.BATCH_JOB_EXECUTION AS Q1
WHERE
(Q1.JOB_INSTANCE_ID = 1)
ORDER BY
Q1.JOB_EXECUTION_ID DESC

Access Plan:
-----------
Total Cost: 13.7992
Query Degree: 1

Rows
RETURN
( 1)
Cost
I/O
|
1.00583
FETCH
( 2)
13.7992
2.02625
/----+-----\
1.00583 2414
IXSCAN TABLE: ACCT
( 3) BATCH_JOB_EXECUTION
6.81342 Q1
1
|
2414
INDEX: ACCT
IDX1
Q1

Extended Diagnostic Information:


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

No extended Diagnostic Information for this statement.


===> post index advis

db2advis -d TEST -s "SELECT JOB_EXECUTION_ID, START_TIME, END_TIME, STATUS,


EXIT_CODE, EXIT_MESSAGE, CREATE_TIME, LAST_UPDATED, VERSION,
JOB_CONFIGURATION_LOCATION from ACCT.BATCH_JOB_EXECUTION where JOB_INSTANCE_ID =1
order by JOB_EXECUTION_ID desc"

Using user id as default schema name. Use -n option to specify schema


execution started at timestamp 2021-02-10-14.42.57.167358
Recommending indexes...
total disk space needed for initial set [ 0.000] MB
total disk space constrained to [2479.934] MB
Trying variations of the solution set.
0 indexes in current solution
[ 14.0000] timerons (without recommendations)
[ 14.0000] timerons (with current solution)
[0.00%] improvement

--
--
-- LIST OF RECOMMENDED INDEXES
-- ===========================
-- no indexes are recommended for this workload.

--
--
-- RECOMMENDED EXISTING INDEXES
-- ============================
-- RUNSTATS ON TABLE "ACCT "."BATCH_JOB_EXECUTION" FOR SAMPLED DETAILED INDEX
"ACCT "."IDX1" ;
-- COMMIT WORK ;

--
--
-- UNUSED EXISTING INDEXES
-- ============================
-- ===========================
--

-- ====ADVISOR DETAILED XML OUTPUT=============


-- ==(Benefits do not include clustering recommendations)==
--

--<?xml version="1.0"?>
--<design-advisor>
--<statement>
--<statementnum>1</statementnum>
--<statementtext>
-- SELECT JOB_EXECUTION_ID, START_TIME, END_TIME, STATUS,
-- EXIT_CODE, EXIT_MESSAGE, CREATE_TIME, LAST_UPDATED,
-- VERSION, JOB_CONFIGURATION_LOCATION from ACCT.BATCH_JOB_EXECUTION
-- where JOB_INSTANCE_ID =1 order by JOB_EXECUTION_ID
-- desc
--</statementtext>
--<objects>
--<identifier>
--<name>BATCH_JOB_EXECUTION</name>
--<schema>ACCT </schema>
--</identifier>
--<identifier>
--<name>IDX1</name>
--<schema>ACCT </schema>
--</identifier>
--</objects>
--<benefit>0.000000</benefit>
--<frequency>1</frequency>
--</statement>
--</design-advisor>

-- ====ADVISOR DETAILED XML OUTPUT=============


--

3 solutions were evaluated by the advisor


DB2 Workload Performance Advisor tool is finished.

You might also like