Scribd
Upload
15 views17 pages

Understanding DB2 EXPLAIN Fundamentals

This document is an agenda for a session titled "DB2 EXPLAIN Explained" presented by Bonnie Baker on May 15, 2009. The agenda covers four levels of EXPLAIN details, from basic concepts and identifying issues to more advanced topics like new features in recent releases. Level 1 focuses on important EXPLAIN plan table columns, common access types, and how to fix problems. Level 2 discusses combining columns and new conclusions. Level 3 covers index-only updates/deletes and other advanced transformations. Level 4 presents new features like stage 2 predicates and filter factor calculations.

Uploaded by

Nitin Motiramani
Copyright
© Attribution Non-Commercial (BY-NC)
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
15 views17 pages

Understanding DB2 EXPLAIN Fundamentals

This document is an agenda for a session titled "DB2 EXPLAIN Explained" presented by Bonnie Baker on May 15, 2009. The agenda covers four levels of EXPLAIN details, from basic concepts and identifying issues to more advanced topics like new features in recent releases. Level 1 focuses on important EXPLAIN plan table columns, common access types, and how to fix problems. Level 2 discusses combining columns and new conclusions. Level 3 covers index-only updates/deletes and other advanced transformations. Level 4 presents new features like stage 2 predicates and filter factor calculations.

Uploaded by

Nitin Motiramani
Copyright
© Attribution Non-Commercial (BY-NC)
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd

Session: C14

DB2 EXPLAIN Explained


Bonnie Baker Bonnie Baker Corporation
May 15, 2009 10:30am 11:30am Platform: DB2 for z/OS

Agenda
EXPLAIN Potpourri LEVEL 1
Basics: If you dont know this you dont know EXPLAIN

LEVEL 2
Graduate School

LEVEL 3
Gee Whiz Stuff

LEVEL 4
New Release Stuff
2

LEVEL 1
Important Plan_Table columns Know what to expect How to spot RED FLAGS Know how to fix the problems

LEVEL 2
Combining Columns
Come to new conclusions What you see is not always what you get

LEVEL 3
Index_Only Updates/Deletes An R may not be a tablespace scan Join transformation Subselect transformation Transitive Closure impact

LEVEL 4
Stage 2 Predicates Identified Screencols Documented Filter-Factor Calculations OSC

Most Important Plan_table Columns


Accesstype Accessname + Matchcols Index-Only Prefetch Page_Range

Most Important Plan_table Cols cont.


Method When_Optimize Opthint Hint_Used Primary_Accesstype

Accesstype Essentials
R Usually* a tablespace scan I Using an index but which one? And HOW is the index used? I1 This one is special we long for it

Accesstype Essentials
N Two ways to look at this one But why not just say I Blank Sort lines

10

Accessname & Matchcols


You Must Know Index Names Columns + Order of columns And understand
Matching = Matchcols Screening = Screencols Stage 1 vs. Stage 2 Impact Transitive Closure Impact
11

Index_Only
True Index_Only Multiple Index Access Index Only Updates/Deletes

12

Prefetch
Blank Sequential Detection S V9 Changes L D V9 Changes
13

Page_Range
N Y Within Accesstype R * Within Accesstype I

14

Method, Opthint, Hint_Used


Method
0 1, 2, 4 Join Transformation Subquery Transformation 3 = Eight Sort_. Columns

Opthint = Named Input + BIND parm Hint_Used = Output


15

Primary Accesstype
D ROWID

blank

16

Session: C14 DB2 EXPLAIN Explained

Bonnie Baker
Bonnie Baker Corporation bkbaker@[Link]

17

You might also like