Need help with query performance.

[Hemant Shah]

Folks,

I am having problem with an application that uses static SQL,
the application basically browses through the table given start and
end key most of the time it is processed from begining to end.

The db2expln tells me that there is no Data or Index prefetch.

I am running DB2 UDB 8 on AIX 5.3, and I am using DMS tablespace on
raw logical volumes on a SAN.

# db2level
DB21085I Instance "db2dev1" uses "32" bits and DB2 code release "SQL08023"
with level identifier "03040106".
Informational tokens are "DB2 v8.1.1.96", "s050811", "U803920", and FixPak
"10".
Product is installed at "/usr/opt/db2_08_01".
This is the ddl for the table:

CREATE TABLE SCHEMA.EADMFX00
(KEY0 CHAR (29) NOT NULL,
KEY1 CHAR (26 ) NOT NULL,
KEY2 CHAR (30 ) NOT NULL,
KEY3 CHAR (50 ) NOT NULL,
REC VARCHAR(1000) NOT NULL)
IN TBSP1
INDEX IN TBSP1
LONG IN TBSP1;
CREATE UNIQUE INDEX EADMFX00_ASC ON SCHEMA.EADMFX00 (KEY0 ASC) ALLOW REVERSE SCANS;
CREATE INDEX EADMFX01_ASC ON SCHEMA.EADMFX00 (KEY1 ASC);
CREATE INDEX EADMFX02_ASC ON SCHEMA.EADMFX00 (KEY2 ASC);
CREATE INDEX EADMFX03_ASC ON SCHEMA.EADMFX00 (KEY3 ASC);
Here is the output of db2expln:

SQL Statement:
DECLARE KEY0_ASCBR CURSOR
FOR
SELECT KEY0, REC
FROM cfgdev1.EADMFX00
WHERE KEY0 >=:H00006 AND KEY0 <=:H00001
ORDER BY KEY0 ASC
FOR
FETCH ONLY
OPTIMIZE
FOR 15 ROWS

Intra-Partition Parallelism Degree = 4

Section Code Page = 819

Estimated Cost = 166.983475
Estimated Cardinality = 828.506165

Process Using 4 Subagents
| Table Constructor
| | 1-Row(s)
| Residual Predicate(s)
| | #Predicates = 1
| Nested Loop Join
| | Access Table Name = SCHEMA.EADMFX00 ID = 4,23
| | | Index Scan: Name = SCHEMA.EADMFX00_ASC ID = 1
| | | | Regular Index (Not Clustered)
| | | | Index Columns:
| | | | | 1: KEY0 (Ascending)
| | | #Columns = 2
| | | Parallel Scan
| | | #Key Columns = 1
| | | | Start Key: Inclusive Value
| | | | | | 1: ?
| | | | Stop Key: Inclusive Value
| | | | | | 1: ?
| | | Data Prefetch: None
| | | Index Prefetch: None
| | | Lock Intents
| | | | Table: Intent Share
| | | | Row : Next Key Share
| Insert Into Asynchronous Local Table Queue ID = q1
Access Local Table Queue ID = q1 #Columns = 2
| Output Sorted
| | #Key Columns = 1
| | | Key 1: (Ascending)
Return Data to Application
| #Columns = 2
Here is how I bind the package:

db2 PREP ${SqcCode} BINDFILE USING ${BindFile} ISOLATION CS \
BLOCKING NO DATETIME ISO EXPLAIN ALL EXPLSNAP ALL LANGLEVEL SAA1 \
OPTLEVEL 1 \
OUTPUT ${CCode} SQLERROR NOPACKAGE TARGET C

db2 BIND ${BindFile} BLOCKING NO DATETIME ISO EXPLAIN ALL EXPLSNAP ALL \
GRANT PUBLIC ISOLATION CS SQLERROR NOPACKAGE


Here is the output of reorgchk:

SCHEMA NAME CARD OV NP FP ACTBLK TSIZE F1 F2 F3 REORG
----------------------------------------------------------------------------------------
Table: SCHEMA.EADMFX00
SCHEMA EADMFX00 2413580 0 31529 31529 - 1.03e+09 0 99 100 ---
----------------------------------------------------------------------------------------

SCHEMA NAME CARD LEAF ELEAF LVLS ISIZE NDEL KEYS F4 F5 F6 F7 F8 REORG
-------------------------------------------------------------------------------------------------
Table: SCHEMA.EADMFX00
SCHEMA EADMFX00_ASC 2e+06 3119 0 3 29 14 2e+06 100 90 22 0 0 -----
SCHEMA EADMFX01_ASC 2e+06 2060 0 3 26 0 2e+06 63 89 37 0 0 *----
SCHEMA EADMFX02_ASC 2e+06 3202 0 3 30 0 2e+06 100 90 21 0 0 -----
SCHEMA EADMFX03_ASC 2e+06 4658 0 3 50 0 2e+06 97 89 10 0 0 -----
-------------------------------------------------------------------------------------------------
It takes 3 minutes to browse through the whole table. I have other tables that
are bigger then this one and I can browse through them in few seconds.
--
Hemant Shah /"\ ASCII ribbon campaign
E-mail: No************@xnet.com \ / ---------------------
X against HTML mail
TO REPLY, REMOVE NoJunkMail / \ and postings
FROM MY E-MAIL ADDRESS.
-----------------[DO NOT SEND UNSOLICITED BULK E-MAIL]------------------
I haven't lost my mind, Above opinions are mine only.
it's backed up on tape somewhere. Others can have their own.

[ChrisC]

Hi.
Hemant Shah wrote:

...
FOR
FETCH ONLY
OPTIMIZE
FOR 15 ROWS
...
It takes 3 minutes to browse through the whole table. I have other tables that
are bigger then this one and I can browse through them in few seconds.

Why do you have the OPTIMIZE FOR 15 ROWS in the query? I would expect
that DB2 will actually pay attention to that, and only allocate space
for for 15 rows being returned - which won't be optimal if you are
returning significantly more rows than that.

Also, since you are using parameter holders in the query, db2 can't
make quite a good a guess at what the query should do - so it makes a
best guess, and uses it in all cases. Where you are returning the
whole table (because the keys encompass the whole table), it isn't
going to know that - it will still assume that the index lookup is the
best choice.

Some ways around this might be to use a second query that just returns
the whole table where that makes sense - it will know how to do that.
Alternatively, if most queries will return a significant portion of the
table, you might try rebinding the query with REOPT ONCE (that is, it
will wait until the first call to the query to determine the plan, when
it has some actual values to use). If the results vary wildly from
call to call (some do return just 15 rows, others return millions) that
you could rebind with REOPT ALWAYS (that is, always rebuild the plan
when running the query). That last should give you the best possible
plan - at a cost of always rebuilding the plan.

-Chris

[Hemant Shah]

While stranded on information super highway ChrisC wrote:

Hi.
Hemant Shah wrote:

>...
FOR
FETCH ONLY
OPTIMIZE
FOR 15 ROWS
...
It takes 3 minutes to browse through the whole table. I have other tables that
are bigger then this one and I can browse through them in few seconds.

Why do you have the OPTIMIZE FOR 15 ROWS in the query? I would expect
that DB2 will actually pay attention to that, and only allocate space
for for 15 rows being returned - which won't be optimal if you are
returning significantly more rows than that.

Also, since you are using parameter holders in the query, db2 can't
make quite a good a guess at what the query should do - so it makes a
best guess, and uses it in all cases. Where you are returning the
whole table (because the keys encompass the whole table), it isn't
going to know that - it will still assume that the index lookup is the
best choice.

Some ways around this might be to use a second query that just returns
the whole table where that makes sense - it will know how to do that.
Alternatively, if most queries will return a significant portion of the
table, you might try rebinding the query with REOPT ONCE (that is, it
will wait until the first call to the query to determine the plan, when
it has some actual values to use). If the results vary wildly from
call to call (some do return just 15 rows, others return millions) that
you could rebind with REOPT ALWAYS (that is, always rebuild the plan
when running the query). That last should give you the best possible
plan - at a cost of always rebuilding the plan.

I tried with and without OPTIMIZE clause with same results.
Unfortunately REOPT option is not available on LUW, it is only available
on OS/390.

>
-Chris

Any idea why does it not prefetch data?
Based upon the query optimizer must know that it is going to fetch data
sequentially.

--
Hemant Shah /"\ ASCII ribbon campaign
E-mail: No************@xnet.com \ / ---------------------
X against HTML mail
TO REPLY, REMOVE NoJunkMail / \ and postings
FROM MY E-MAIL ADDRESS.
-----------------[DO NOT SEND UNSOLICITED BULK E-MAIL]------------------
I haven't lost my mind, Above opinions are mine only.
it's backed up on tape somewhere. Others can have their own.

[Phil Sherman]

Statistics from your original post indicate ~75 rows/page. The plan
shows that ~825 rows are expected. 825/75 = 11 pages of data to
retrieve, way to low to start the query with prefetch.

If you have the SEQDETECT configuration parameter set to NO, then you
will not see the I/O change to sequential prefetch when physical
sequential page retrieval is occurring. In this case, even if sequential
prefetch occurs, you may not have the optimum retrieval mechanism.

The explain shows that the retrieval is using the index for sequencing,
avoiding a sort. This is very good for a small number of rows but MAY be
less efficient than a table acan and sort. It's a balancing act between
the additional costs of the sort vs the overhead of the nested loop join
process to use the index to access the data.

Phil Sherman

Hemant Shah wrote:

While stranded on information super highway ChrisC wrote:

>Hi.
Hemant Shah wrote:

>>...
FOR
FETCH ONLY
OPTIMIZE
FOR 15 ROWS
...
It takes 3 minutes to browse through the whole table. I have other tables that
are bigger then this one and I can browse through them in few seconds.

Why do you have the OPTIMIZE FOR 15 ROWS in the query? I would expect
that DB2 will actually pay attention to that, and only allocate space
for for 15 rows being returned - which won't be optimal if you are
returning significantly more rows than that.

Also, since you are using parameter holders in the query, db2 can't
make quite a good a guess at what the query should do - so it makes a
best guess, and uses it in all cases. Where you are returning the
whole table (because the keys encompass the whole table), it isn't
going to know that - it will still assume that the index lookup is the
best choice.

Some ways around this might be to use a second query that just returns
the whole table where that makes sense - it will know how to do that.
Alternatively, if most queries will return a significant portion of the
table, you might try rebinding the query with REOPT ONCE (that is, it
will wait until the first call to the query to determine the plan, when
it has some actual values to use). If the results vary wildly from
call to call (some do return just 15 rows, others return millions) that
you could rebind with REOPT ALWAYS (that is, always rebuild the plan
when running the query). That last should give you the best possible
plan - at a cost of always rebuilding the plan.

I tried with and without OPTIMIZE clause with same results.
Unfortunately REOPT option is not available on LUW, it is only available
on OS/390.

>-Chris

Any idea why does it not prefetch data?
Based upon the query optimizer must know that it is going to fetch data
sequentially.

[Hemant Shah]

While stranded on information super highway Phil Sherman wrote:

Statistics from your original post indicate ~75 rows/page. The plan
shows that ~825 rows are expected. 825/75 = 11 pages of data to
retrieve, way to low to start the query with prefetch.

If you have the SEQDETECT configuration parameter set to NO, then you
will not see the I/O change to sequential prefetch when physical
sequential page retrieval is occurring. In this case, even if sequential
prefetch occurs, you may not have the optimum retrieval mechanism.

SEQDETECT is set to yes.

Sequential detect flag (SEQDETECT) = YES

>
The explain shows that the retrieval is using the index for sequencing,
avoiding a sort. This is very good for a small number of rows but MAY be
less efficient than a table acan and sort. It's a balancing act between
the additional costs of the sort vs the overhead of the nested loop join
process to use the index to access the data.

Thanks for the explaination.

Phil Sherman

Hemant Shah wrote:

>While stranded on information super highway ChrisC wrote:

>>Hi.
Hemant Shah wrote:
...
FOR
FETCH ONLY
OPTIMIZE
FOR 15 ROWS
...
It takes 3 minutes to browse through the whole table. I have other tables that
are bigger then this one and I can browse through them in few seconds.
Why do you have the OPTIMIZE FOR 15 ROWS in the query? I would expect
that DB2 will actually pay attention to that, and only allocate space
for for 15 rows being returned - which won't be optimal if you are
returning significantly more rows than that.

Also, since you are using parameter holders in the query, db2 can't
make quite a good a guess at what the query should do - so it makes a
best guess, and uses it in all cases. Where you are returning the
whole table (because the keys encompass the whole table), it isn't
going to know that - it will still assume that the index lookup is the
best choice.

Some ways around this might be to use a second query that just returns
the whole table where that makes sense - it will know how to do that.
Alternatively, if most queries will return a significant portion of the
table, you might try rebinding the query with REOPT ONCE (that is, it
will wait until the first call to the query to determine the plan, when
it has some actual values to use). If the results vary wildly from
call to call (some do return just 15 rows, others return millions) that
you could rebind with REOPT ALWAYS (that is, always rebuild the plan
when running the query). That last should give you the best possible
plan - at a cost of always rebuilding the plan.

I tried with and without OPTIMIZE clause with same results.
Unfortunately REOPT option is not available on LUW, it is only available
on OS/390.

>>-Chris

Any idea why does it not prefetch data?
Based upon the query optimizer must know that it is going to fetch data
sequentially.

--
Hemant Shah /"\ ASCII ribbon campaign
E-mail: No************@xnet.com \ / ---------------------
X against HTML mail
TO REPLY, REMOVE NoJunkMail / \ and postings
FROM MY E-MAIL ADDRESS.
-----------------[DO NOT SEND UNSOLICITED BULK E-MAIL]------------------
I haven't lost my mind, Above opinions are mine only.
it's backed up on tape somewhere. Others can have their own.