interpertting a db2expln

[Database Administrator]

Hi,

I'm trying to interpet a db2 explain. I'm trying to determine if the SQL is
being executed as I expect/hope.

The sql is generally:

select key from table where key1=value and key2 > value2 and key2 < maxkey
order by key2 fetch first 1 row only

2 notes: key1 = value is 99% of the table and the max key is more or less
useless b/c its always the last possible key, making the statement for
practical purposes:

select key from table key2 > value2 order by key2 fetch first 1 row only

By big question is: If their were 26 rows w/ keys A-Z , and the value of
value2 is B, does the explain show that the sql statement still inserts C-Z
into the temp table and sort it?

Below is the explain of the statement in question.

Thanks!
Frank

--

-------------------- SECTION ---------------------------------------
Section = 17


SQL Statement:

SELECT LFKEY, CDATA INTO :H00038 :H00039 , :H00018 :H00019
FROM RCFGTD
WHERE LFPFX=:H00004 :H00005 AND LFKEY >= :H00038 :H00039 AND LFKEY
<= :H00020 :H00021
ORDER BY lfkey
FETCH FIRST 1 ROW ONLY


Section Code Page = 819

Estimated Cost = 78.628693
Estimated Cardinality = 1.000000

Table Constructor
| 1-Row(s)
Residual Predicate(s)
| #Predicates = 1
Nested Loop Join
| Piped Inner
| Access Table Name = PRODPCIA.RCFGTD ID = 22,115
| | #Columns = 0
| | Index Scan: Name = PRODPCIA.LNG01_RCFGTD_PK ID = 1
| | | Regular Index (Clustered)
| | | Index Columns:
| | | | 1: LFPFX (Ascending)
| | | | 2: LFKEY (Ascending)
| | | #Key Columns = 2
| | | | Start Key: Inclusive Value
| | | | | 1: ?
| | | | | 2: ?
| | | | Stop Key: Inclusive Value
| | | | | 1: ?
| | | | | 2: ?
| | | Index-Only Access
| | | Index Prefetch: None
| | | Insert Into Sorted Temp Table ID = t1
| | | | #Columns = 1
| | | | #Sort Key Columns = 1
| | | | | Key 1: (Ascending)
| | | | Sortheap Allocation Parameters:
| | | | | #Rows = 43
| | | | | Row Width = 12
| | | | Piped
| | | | Duplicate Elimination
| | Isolation Level: Uncommitted Read
| | Lock Intents
| | | Table: Intent None
| | | Row : None
| Sorted Temp Table Completion ID = t1
| List Prefetch Preparation
| | Access Table Name = PRODPCIA.RCFGTD ID = 22,115
| | | #Columns = 2
| | | Relation Scan
| | | | Prefetch: 5 Pages
| | | Lock Intents
| | | | Table: Intent Share
| | | | Row : Next Key Share
| | | Sargable Predicate(s)
| | | | #Predicates = 3
Insert Into Sorted Temp Table ID = t2
| #Columns = 2
| #Sort Key Columns = 1
| | Key 1: LFKEY (Ascending)
| Sortheap Allocation Parameters:
| | #Rows = 1
| | Row Width = 3504
| | Sort Limited To Estimated Row Count
| Piped
Access Temp Table ID = t2
| #Columns = 2
| Single Record
| Relation Scan
| | Prefetch: Eligible
Return Data to Application
| #Columns = 2

End of section


Optimizer Plan:

RETURN
( 1)
|
TBSCAN
( 2)
|
SORT
( 3)
|
NLJOIN
( 4)
/ \
TBSCAN RIDSCN
( 5) ( 7)
| |
TFunc: SORT
SYSIBM ( 8)
GENROW |
IXSCAN
( 9)
/ \
Index: Table:
PRODPCIA PRODPCIA
LNG01_RCFGTD_PK RCFGTD

[Database Administrator]

Feed the query into
db2 EXPLAIN PLAN FOR
then do:
db2exfmt

The result will be a lot more verbose.
db2expln reverse engineers (disassembles) the plan from the runtime.

E.g. db2exfmt will tell you the estimated (or proven) cardinality of
each plan operator.
In your case I would expect a "truncated sort".
Cheers
Serge
--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab

[Database Administrator]

Feed the query into
db2 EXPLAIN PLAN FOR
then do:
db2exfmt

The result will be a lot more verbose.
db2expln reverse engineers (disassembles) the plan from the runtime.

E.g. db2exfmt will tell you the estimated (or proven) cardinality of
each plan operator.
In your case I would expect a "truncated sort".
Cheers
Serge
--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab

[Database Administrator]

Feed the query into
db2 EXPLAIN PLAN FOR
then do:
db2exfmt

The result will be a lot more verbose.
db2expln reverse engineers (disassembles) the plan from the runtime.

E.g. db2exfmt will tell you the estimated (or proven) cardinality of
each plan operator.
In your case I would expect a "truncated sort".
Cheers
Serge
--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab

[Database Administrator]

Feed the query into
db2 EXPLAIN PLAN FOR
then do:
db2exfmt

The result will be a lot more verbose.
db2expln reverse engineers (disassembles) the plan from the runtime.

E.g. db2exfmt will tell you the estimated (or proven) cardinality of
each plan operator.
In your case I would expect a "truncated sort".
Cheers
Serge
--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab

[Database Administrator]

Feed the query into
db2 EXPLAIN PLAN FOR
then do:
db2exfmt

The result will be a lot more verbose.
db2expln reverse engineers (disassembles) the plan from the runtime.

E.g. db2exfmt will tell you the estimated (or proven) cardinality of
each plan operator.
In your case I would expect a "truncated sort".
Cheers
Serge
--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab

[Database Administrator]

Hey Serge,

Is there actually somewhere a good document describing the explain plan and
what each section means etc..?
For example, MAXPAGES. From what I understand so far is the optimizer
setting the maximum number of pages to prefetch correct?
But I've seen a setting of like 1000, so that would mean to maximum fetch
1000 pages correct?
Thus if I have a prefetchsize of 32, I would get 32 prefetch requests
correct?

So if it would for example say 16, would this mean that although my
prefetchsize if set to 32, the optimizer will override this and only get 16
pages?

I can not find any good documentation explaining every item within the
explain output, and what it means etc..

Thanks.

"Serge Rielau" wrote in message
news:c1lhar$hmk$1@hanover.torolab.ibm.com...
> Feed the query into
> db2 EXPLAIN PLAN FOR
> then do:
> db2exfmt
>
> The result will be a lot more verbose.
> db2expln reverse engineers (disassembles) the plan from the runtime.
>
> E.g. db2exfmt will tell you the estimated (or proven) cardinality of
> each plan operator.
> In your case I would expect a "truncated sort".
> Cheers
> Serge
> --
> Serge Rielau
> DB2 SQL Compiler Development
> IBM Toronto Lab

[Database Administrator]

Hey Serge,

Is there actually somewhere a good document describing the explain plan and
what each section means etc..?
For example, MAXPAGES. From what I understand so far is the optimizer
setting the maximum number of pages to prefetch correct?
But I've seen a setting of like 1000, so that would mean to maximum fetch
1000 pages correct?
Thus if I have a prefetchsize of 32, I would get 32 prefetch requests
correct?

So if it would for example say 16, would this mean that although my
prefetchsize if set to 32, the optimizer will override this and only get 16
pages?

I can not find any good documentation explaining every item within the
explain output, and what it means etc..

Thanks.

"Serge Rielau" wrote in message
news:c1lhar$hmk$1@hanover.torolab.ibm.com...
> Feed the query into
> db2 EXPLAIN PLAN FOR
> then do:
> db2exfmt
>
> The result will be a lot more verbose.
> db2expln reverse engineers (disassembles) the plan from the runtime.
>
> E.g. db2exfmt will tell you the estimated (or proven) cardinality of
> each plan operator.
> In your case I would expect a "truncated sort".
> Cheers
> Serge
> --
> Serge Rielau
> DB2 SQL Compiler Development
> IBM Toronto Lab

[Database Administrator]

Hey Serge,

Is there actually somewhere a good document describing the explain plan and
what each section means etc..?
For example, MAXPAGES. From what I understand so far is the optimizer
setting the maximum number of pages to prefetch correct?
But I've seen a setting of like 1000, so that would mean to maximum fetch
1000 pages correct?
Thus if I have a prefetchsize of 32, I would get 32 prefetch requests
correct?

So if it would for example say 16, would this mean that although my
prefetchsize if set to 32, the optimizer will override this and only get 16
pages?

I can not find any good documentation explaining every item within the
explain output, and what it means etc..

Thanks.

"Serge Rielau" wrote in message
news:c1lhar$hmk$1@hanover.torolab.ibm.com...
> Feed the query into
> db2 EXPLAIN PLAN FOR
> then do:
> db2exfmt
>
> The result will be a lot more verbose.
> db2expln reverse engineers (disassembles) the plan from the runtime.
>
> E.g. db2exfmt will tell you the estimated (or proven) cardinality of
> each plan operator.
> In your case I would expect a "truncated sort".
> Cheers
> Serge
> --
> Serge Rielau
> DB2 SQL Compiler Development
> IBM Toronto Lab

[Database Administrator]

Hey Serge,

Is there actually somewhere a good document describing the explain plan and
what each section means etc..?
For example, MAXPAGES. From what I understand so far is the optimizer
setting the maximum number of pages to prefetch correct?
But I've seen a setting of like 1000, so that would mean to maximum fetch
1000 pages correct?
Thus if I have a prefetchsize of 32, I would get 32 prefetch requests
correct?

So if it would for example say 16, would this mean that although my
prefetchsize if set to 32, the optimizer will override this and only get 16
pages?

I can not find any good documentation explaining every item within the
explain output, and what it means etc..

Thanks.

"Serge Rielau" wrote in message
news:c1lhar$hmk$1@hanover.torolab.ibm.com...
> Feed the query into
> db2 EXPLAIN PLAN FOR
> then do:
> db2exfmt
>
> The result will be a lot more verbose.
> db2expln reverse engineers (disassembles) the plan from the runtime.
>
> E.g. db2exfmt will tell you the estimated (or proven) cardinality of
> each plan operator.
> In your case I would expect a "truncated sort".
> Cheers
> Serge
> --
> Serge Rielau
> DB2 SQL Compiler Development
> IBM Toronto Lab