UDB 8.2 poerformance problem

[Hemant Shah]

Folks,

We have an SQL statement that was coded in an application many years ago
(starting with DB V2 I think). When I upgraded to UDB 8.2, the optimizer
does not use optimal path to access the data.
It takes about 4 minutes to get the data. In previous versions it was
instantaneous.

What do I need to do to increase the performance?
There are the details:

# oslevel
5.1.0.0

# db2level
DB21085I Instance "dbap3" uses "32" bits and DB2 code release "SQL08021" with
level identifier "03020106".
Informational tokens are "DB2 v8.1.1.80", "s041221", "U800400", and FixPak "8".
Product is installed at "/usr/opt/db2_08_01".
create table apdev3.EADMPF00
(KEY0 CHAR (31) not null,
REC VARCHAR(3000))
IN cfgtbsp
INDEX IN cfgtbsp
LONG IN cfgtbsp;
create unique index EADMPF00_ASC on apdev3.EADMPF00 (KEY0 asc);
# db2 reorgchk current statistics on table apdev3.eadmpf00

Table statistics:

F1: 100 * OVERFLOW / CARD < 5
F2: 100 * (Effective Space Utilization of Data Pages) > 70
F3: 100 * (Required Pages / Total Pages) > 80

SCHEMA NAME CARD OV NP FP ACTBLK TSIZE F1 F2 F3 REORG
----------------------------------------------------------------------------------------
Table: APDEV3.EADMPF00
APDEV3 EADMPF00 1.6e+07 0 84020 84020 - 2.72e+09 0 98 100 ---
----------------------------------------------------------------------------------------

Index statistics:

F4: CLUSTERRATIO or normalized CLUSTERFACTOR > 80
F5: 100 * (KEYS * (ISIZE + 9) + (CARD - KEYS) * 5) / ((NLEAF - NUM EMPTY LEAFS) * INDEXPAGESIZE) > 50
F6: (100 - PCTFREE) * ((INDEXPAGESIZE - 96) / (ISIZE + 12)) ** (NLEVELS - 2) * (INDEXPAGESIZE - 96) / (KEYS * (ISIZE + 9) + (CARD - KEYS) * 5) < 100
F7: 100 * (NUMRIDS DELETED / (NUMRIDS DELETED + CARD)) < 20
F8: 100 * (NUM EMPTY LEAFS / NLEAF) < 20

SCHEMA NAME CARD LEAF ELEAF LVLS ISIZE NDEL KEYS F4 F5 F6 F7 F8 REORG
-------------------------------------------------------------------------------------------------
Table: APDEV3.EADMPF00
APDEV3 EADMPF00_ASC 2e+07 21137 0 3 31 0 2e+07 100 89 3 0 0 -----
-------------------------------------------------------------------------------------------------
Here is the explain output:
Section = 5
SQL Statement:
DECLARE KEY0_ASCBR CURSOR
FOR
SELECT KEY0, REC
FROM apdev3.EADMPF00
WHERE KEY0 >=:H00006 AND KEY0 <=:H00001
ORDER BY KEY0 ASC
FOR
FETCH ONLY
OPTIMIZE
FOR 15 ROWS
Section Code Page = 819

Estimated Cost = 199.729889
Estimated Cardinality = 2101.966309

Table Constructor
| 1-Row(s)
Residual Predicate(s)
| #Predicates = 1
Nested Loop Join
| Access Table Name = APDEV3.EADMPF00 ID = 3,47
| | Index Scan: Name = APDEV3.EADMPF00_ASC ID = 1
| | | Regular Index (Not Clustered)
| | | Index Columns:
| | | | 1: KEY0 (Ascending)
| | #Columns = 2
| | #Key Columns = 1
| | | Start Key: Inclusive Value
| | | | | 1: ?
| | | Stop Key: Inclusive Value
| | | | | 1: ?
| | Data Prefetch: None
| | Index Prefetch: None
| | Lock Intents
| | | Table: Intent None
| | | Row : None
Return Data to Application
| #Columns = 2

End of section

------------------------------------------------------------------
Section = 8
SQL Statement:
DECLARE KEY0_ASCRD CURSOR
FOR
SELECT KEY0, REC
FROM apdev3.EADMPF00
WHERE KEY0 >=:H00006 AND KEY0 <=:H00001
ORDER BY KEY0 ASC
FETCH FIRST 1 ROWS ONLY
Section Code Page = 819

Estimated Cost = 193.504868
Estimated Cardinality = 1.000000

Table Constructor
| 1-Row(s)
Residual Predicate(s)
| #Predicates = 1
Nested Loop Join
| Piped Inner
| Access Table Name = APDEV3.EADMPF00 ID = 3,47
| | Index Scan: Name = APDEV3.EADMPF00_ASC ID = 1
| | | Regular Index (Not Clustered)
| | | Index Columns:
| | | | 1: KEY0 (Ascending)
| | #Columns = 0
| | #Key Columns = 1
| | | Start Key: Inclusive Value
| | | | | 1: ?
| | | Stop Key: Inclusive Value
| | | | | 1: ?
| | Index-Only Access
| | Index Prefetch: None
| | Lock Intents
| | | Table: Intent None
| | | Row : None
| | Sargable Index Predicate(s)
| | | Insert Into Sorted Temp Table ID = t1
| | | | #Columns = 1
| | | | #Sort Key Columns = 1
| | | | | Key 1: (Ascending)
| | | | Sortheap Allocation Parameters:
| | | | | #Rows = 2102
| | | | | Row Width = 12
| | | | Piped
| | | | Duplicate Elimination
| Sorted Temp Table Completion ID = t1
| List Prefetch Preparation
| | Access Table Name = APDEV3.EADMPF00 ID = 3,47
| | | #Columns = 2
| | | Fetch Using Prefetched List
| | | | Prefetch: 11 Pages
| | | Lock Intents
| | | | Table: Intent None
| | | | Row : None
| | | Sargable Predicate(s)
| | | | #Predicates = 2
Insert Into Sorted Temp Table ID = t2
| #Columns = 2
| #Sort Key Columns = 1
| | Key 1: KEY0 (Ascending)
| Sortheap Allocation Parameters:
| | #Rows = 1
| | Row Width = 172
| | 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

As you can see, in section 5 I use OPTIMIZE clause and it is is able to
get data quickly. In section 8 use FETCH FIRST 1 ROWS ONLY and it takes
forever.

How do I optimize the Section 8 query?

Thanks.

--
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.

[Jan M. Nelken]

Hemant Shah wrote:

Folks,

We have an SQL statement that was coded in an application many years ago
(starting with DB V2 I think). When I upgraded to UDB 8.2, the optimizer
does not use optimal path to access the data.
It takes about 4 minutes to get the data. In previous versions it was
instantaneous.

What do I need to do to increase the performance?
There are the details:

# oslevel
5.1.0.0

# db2level
DB21085I Instance "dbap3" uses "32" bits and DB2 code release "SQL08021" with
level identifier "03020106".
Informational tokens are "DB2 v8.1.1.80", "s041221", "U800400", and FixPak "8".
Product is installed at "/usr/opt/db2_08_01".
create table apdev3.EADMPF00
(KEY0 CHAR (31) not null,
REC VARCHAR(3000))
IN cfgtbsp
INDEX IN cfgtbsp
LONG IN cfgtbsp;
create unique index EADMPF00_ASC on apdev3.EADMPF00 (KEY0 asc);
# db2 reorgchk current statistics on table apdev3.eadmpf00

Table statistics:

F1: 100 * OVERFLOW / CARD < 5
F2: 100 * (Effective Space Utilization of Data Pages) > 70
F3: 100 * (Required Pages / Total Pages) > 80

SCHEMA NAME CARD OV NP FP ACTBLK TSIZE F1 F2 F3 REORG
----------------------------------------------------------------------------------------
Table: APDEV3.EADMPF00
APDEV3 EADMPF00 1.6e+07 0 84020 84020 - 2.72e+09 0 98 100 ---
----------------------------------------------------------------------------------------

Index statistics:

F4: CLUSTERRATIO or normalized CLUSTERFACTOR > 80
F5: 100 * (KEYS * (ISIZE + 9) + (CARD - KEYS) * 5) / ((NLEAF - NUM EMPTY LEAFS) * INDEXPAGESIZE) > 50
F6: (100 - PCTFREE) * ((INDEXPAGESIZE - 96) / (ISIZE + 12)) ** (NLEVELS - 2) * (INDEXPAGESIZE - 96) / (KEYS * (ISIZE + 9) + (CARD - KEYS) * 5) < 100
F7: 100 * (NUMRIDS DELETED / (NUMRIDS DELETED + CARD)) < 20
F8: 100 * (NUM EMPTY LEAFS / NLEAF) < 20

SCHEMA NAME CARD LEAF ELEAF LVLS ISIZE NDEL KEYS F4 F5 F6 F7 F8 REORG
-------------------------------------------------------------------------------------------------
Table: APDEV3.EADMPF00
APDEV3 EADMPF00_ASC 2e+07 21137 0 3 31 0 2e+07 100 89 3 0 0 -----
-------------------------------------------------------------------------------------------------
Here is the explain output:
Section = 5
SQL Statement:
DECLARE KEY0_ASCBR CURSOR
FOR
SELECT KEY0, REC
FROM apdev3.EADMPF00
WHERE KEY0 >=:H00006 AND KEY0 <=:H00001
ORDER BY KEY0 ASC
FOR
FETCH ONLY
OPTIMIZE
FOR 15 ROWS
Section Code Page = 819

Estimated Cost = 199.729889
Estimated Cardinality = 2101.966309

Table Constructor
| 1-Row(s)
Residual Predicate(s)
| #Predicates = 1
Nested Loop Join
| Access Table Name = APDEV3.EADMPF00 ID = 3,47
| | Index Scan: Name = APDEV3.EADMPF00_ASC ID = 1
| | | Regular Index (Not Clustered)
| | | Index Columns:
| | | | 1: KEY0 (Ascending)
| | #Columns = 2
| | #Key Columns = 1
| | | Start Key: Inclusive Value
| | | | | 1: ?
| | | Stop Key: Inclusive Value
| | | | | 1: ?
| | Data Prefetch: None
| | Index Prefetch: None
| | Lock Intents
| | | Table: Intent None
| | | Row : None
Return Data to Application
| #Columns = 2

End of section

------------------------------------------------------------------
Section = 8
SQL Statement:
DECLARE KEY0_ASCRD CURSOR
FOR
SELECT KEY0, REC
FROM apdev3.EADMPF00
WHERE KEY0 >=:H00006 AND KEY0 <=:H00001
ORDER BY KEY0 ASC
FETCH FIRST 1 ROWS ONLY
Section Code Page = 819

Estimated Cost = 193.504868
Estimated Cardinality = 1.000000

Table Constructor
| 1-Row(s)
Residual Predicate(s)
| #Predicates = 1
Nested Loop Join
| Piped Inner
| Access Table Name = APDEV3.EADMPF00 ID = 3,47
| | Index Scan: Name = APDEV3.EADMPF00_ASC ID = 1
| | | Regular Index (Not Clustered)
| | | Index Columns:
| | | | 1: KEY0 (Ascending)
| | #Columns = 0
| | #Key Columns = 1
| | | Start Key: Inclusive Value
| | | | | 1: ?
| | | Stop Key: Inclusive Value
| | | | | 1: ?
| | Index-Only Access
| | Index Prefetch: None
| | Lock Intents
| | | Table: Intent None
| | | Row : None
| | Sargable Index Predicate(s)
| | | Insert Into Sorted Temp Table ID = t1
| | | | #Columns = 1
| | | | #Sort Key Columns = 1
| | | | | Key 1: (Ascending)
| | | | Sortheap Allocation Parameters:
| | | | | #Rows = 2102
| | | | | Row Width = 12
| | | | Piped
| | | | Duplicate Elimination
| Sorted Temp Table Completion ID = t1
| List Prefetch Preparation
| | Access Table Name = APDEV3.EADMPF00 ID = 3,47
| | | #Columns = 2
| | | Fetch Using Prefetched List
| | | | Prefetch: 11 Pages
| | | Lock Intents
| | | | Table: Intent None
| | | | Row : None
| | | Sargable Predicate(s)
| | | | #Predicates = 2
Insert Into Sorted Temp Table ID = t2
| #Columns = 2
| #Sort Key Columns = 1
| | Key 1: KEY0 (Ascending)
| Sortheap Allocation Parameters:
| | #Rows = 1
| | Row Width = 172
| | 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

As you can see, in section 5 I use OPTIMIZE clause and it is is able to
get data quickly. In section 8 use FETCH FIRST 1 ROWS ONLY and it takes
forever.

How do I optimize the Section 8 query?

Thanks.

1. Create explain tables by running EXPLAIN.DDL contained in sqllib/misc
subdirectory.

2. issue:

db2 set current explain mode explain

3. issue

db2 "select key0, rec frop appdev3.eadmpf00 where key0 >= :h00006 and key0 <=
:h00001 order by key0 asc fetch first 1 rows only"

4. issue

db2 set current explain mode no

5. issue

db2exfmt -d databasename -g TIC -o explain.out
hit Enter for all prompts

6. post explain.out here
Jan M. Nelken

[Hemant Shah]

While stranded on information super highway Jan M. Nelken wrote:

1. Create explain tables by running EXPLAIN.DDL contained in sqllib/misc
subdirectory.

2. issue:

db2 set current explain mode explain

3. issue

db2 "select key0, rec frop appdev3.eadmpf00 where key0 >= :h00006 and key0 <=
:h00001 order by key0 asc fetch first 1 rows only"

4. issue

db2 set current explain mode no

5. issue

db2exfmt -d databasename -g TIC -o explain.out
hit Enter for all prompts

6. post explain.out here
Jan M. Nelken

I already have the information in explain table. Here is the output of the
two sections in question.

Thanks.

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

DB2_VERSION: 08.02.1
SOURCE_NAME: SQLC2E06
SOURCE_SCHEMA: NULLID
SOURCE_VERSION:
EXPLAIN_TIME: 2005-07-08-11.58.03.247950
EXPLAIN_REQUESTER: APDEV3

Database Context:
----------------
Parallelism: None
CPU Speed: 7.085163e-07
Comm Speed: 100
Buffer Pool size: 2639
Sort Heap size: 512
Database Heap size: 1200
Lock List size: 2048
Maximum Lock List: 10
Average Applications: 8
Locks Available: 20889
---------------- STATEMENT 5 SECTION 5 ----------------
QUERYNO: 5
QUERYTAG:
Statement Type: Select
Updatable: No
Deletable: No
Query Degree: 1

Original Statement:
------------------
DECLARE KEY0_ASCBR CURSOR
FOR
SELECT KEY0, REC
FROM apdev3.EADMPF00
WHERE KEY0 >= :H00006 AND KEY0 <= :H00001
ORDER BY KEY0 ASC
FOR
FETCH ONLY
OPTIMIZE
FOR 15 ROWS
Optimized Statement:
-------------------
SELECT Q3.KEY0, Q3.REC
FROM
(SELECT Q1.$C0
FROM (VALUES 0) AS Q1
WHERE (:H00006 <= :H00001 SELECTIVITY 1.000000)) AS Q2, APDEV3.EADMPF00 AS
Q3
WHERE (Q3.KEY0 <= :H00001) AND (:H00006 <= Q3.KEY0)
ORDER BY Q3.KEY0

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

Rows
RETURN
( 1)
Cost
I/O
|
2101.97
NLJOIN
( 2)
199.73
15.2282
/------+------\
1 2101.97
TBSCAN FETCH
( 3) ( 4)
0.000400312 199.729
0 15.2282
| /---+---\
1 2101.97 1.55354e+07
TABFNC: SYSIBM IXSCAN TABLE: APDEV3
GENROW ( 5) EADMPF00
52.2223
3.85988
|
1.55354e+07
INDEX: APDEV3
EADMPF00_ASC


1) RETURN: (Return Result)
Cumulative Total Cost: 199.73
Cumulative CPU Cost: 5.71287e+06
Cumulative I/O Cost: 15.2282
Cumulative Re-Total Cost: 3.93082
Cumulative Re-CPU Cost: 5.54796e+06
Cumulative Re-I/O Cost: 0
Cumulative First Row Cost: 38.6057
Estimated Bufferpool Buffers: 15.2282

Arguments:
---------
BLDLEVEL: (Build level)
DB2 v8.1.1.80 : s041221
STMTHEAP: (Statement heap size)
16384

Input Streams:
-------------
7) From Operator #2

Estimated number of rows: 2101.97
Number of columns: 2
Subquery predicate ID: Not Applicable

Column Names:
------------
+Q4.KEY0(A)+Q4.REC
2) NLJOIN: (Nested Loop Join)
Cumulative Total Cost: 199.73
Cumulative CPU Cost: 5.71287e+06
Cumulative I/O Cost: 15.2282
Cumulative Re-Total Cost: 3.93082
Cumulative Re-CPU Cost: 5.54796e+06
Cumulative Re-I/O Cost: 0
Cumulative First Row Cost: 38.6057
Estimated Bufferpool Buffers: 15.2282

Arguments:
---------
EARLYOUT: (Early Out flag)
NONE
FETCHMAX: (Override for FETCH MAXPAGES)
IGNORE
ISCANMAX: (Override for ISCAN MAXPAGES)
IGNORE

Input Streams:
-------------
2) From Operator #3

Estimated number of rows: 1
Number of columns: 0
Subquery predicate ID: Not Applicable

6) From Operator #4

Estimated number of rows: 2101.97
Number of columns: 2
Subquery predicate ID: Not Applicable

Column Names:
------------
+Q3.KEY0(A)+Q3.REC
Output Streams:
--------------
7) To Operator #1

Estimated number of rows: 2101.97
Number of columns: 2
Subquery predicate ID: Not Applicable

Column Names:
------------
+Q4.KEY0(A)+Q4.REC
3) TBSCAN: (Table Scan)
Cumulative Total Cost: 0.000400312
Cumulative CPU Cost: 565
Cumulative I/O Cost: 0
Cumulative Re-Total Cost: 0.000400312
Cumulative Re-CPU Cost: 565
Cumulative Re-I/O Cost: 0
Cumulative First Row Cost: 0.000400312
Estimated Bufferpool Buffers: 0

Arguments:
---------
JN INPUT: (Join input leg)
OUTER
MAXPAGES: (Maximum pages for prefetch)
ALL
PREFETCH: (Type of Prefetch)
NONE
ROWLOCK : (Row Lock intent)
NONE
SCANDIR : (Scan Direction)
FORWARD
TABLOCK : (Table Lock intent)
INTENT NONE
TBISOLVL: (Table access Isolation Level)
UNCOMMITTED READ

Predicates:
----------
4) Residual Predicate
Relational Operator: Less Than or Equal (<=)
Subquery Input Required: No
Filter Factor: 1

Predicate Text:
--------------
(:H00006 <= :H00001 SELECTIVITY 1.000000)
Input Streams:
-------------
1) From Object SYSIBM.GENROW

Estimated number of rows: 1
Number of columns: 1
Subquery predicate ID: Not Applicable

Column Names:
------------
+Q1.NUMB
Output Streams:
--------------
2) To Operator #2

Estimated number of rows: 1
Number of columns: 0
Subquery predicate ID: Not Applicable
4) FETCH : (Fetch)
Cumulative Total Cost: 199.729
Cumulative CPU Cost: 5.7123e+06
Cumulative I/O Cost: 15.2282
Cumulative Re-Total Cost: 3.93042
Cumulative Re-CPU Cost: 5.5474e+06
Cumulative Re-I/O Cost: 0
Cumulative First Row Cost: 38.6053
Estimated Bufferpool Buffers: 16.2279

Arguments:
---------
JN INPUT: (Join input leg)
INNER
MAXPAGES: (Maximum pages for prefetch)
1
MAXPAGES: (Maximum pages for prefetch)
1
PREFETCH: (Type of Prefetch)
NONE
ROWLOCK : (Row Lock intent)
NONE
TABLOCK : (Table Lock intent)
INTENT NONE
TBISOLVL: (Table access Isolation Level)
UNCOMMITTED READ

Input Streams:
-------------
4) From Operator #5

Estimated number of rows: 2101.97
Number of columns: 1
Subquery predicate ID: Not Applicable

Column Names:
------------
+Q3.KEY0(A)

5) From Object APDEV3.EADMPF00

Estimated number of rows: 1.55354e+07
Number of columns: 1
Subquery predicate ID: Not Applicable

Column Names:
------------
+Q3.REC
Output Streams:
--------------
6) To Operator #2

Estimated number of rows: 2101.97
Number of columns: 2
Subquery predicate ID: Not Applicable

Column Names:
------------
+Q3.KEY0(A)+Q3.REC
5) IXSCAN: (Index Scan)
Cumulative Total Cost: 52.2223
Cumulative CPU Cost: 3.70197e+06
Cumulative I/O Cost: 3.85988
Cumulative Re-Total Cost: 2.57454
Cumulative Re-CPU Cost: 3.6337e+06
Cumulative Re-I/O Cost: 0
Cumulative First Row Cost: 25.7487
Estimated Bufferpool Buffers: 4.85988

Arguments:
---------
MAXPAGES: (Maximum pages for prefetch)
1
PREFETCH: (Type of Prefetch)
NONE
ROWLOCK : (Row Lock intent)
NONE
SCANDIR : (Scan Direction)
FORWARD
TABLOCK : (Table Lock intent)
INTENT NONE

Predicates:
----------
2) Stop Key Predicate
Relational Operator: Less Than or Equal (<=)
Subquery Input Required: No
Filter Factor: 0.000451006

Predicate Text:
--------------
(Q3.KEY0 <= :H00001)

3) Start Key Predicate
Relational Operator: Less Than or Equal (<=)
Subquery Input Required: No
Filter Factor: 0.000451006

Predicate Text:
--------------
(:H00006 <= Q3.KEY0)
Input Streams:
-------------
3) From Object APDEV3.EADMPF00_ASC

Estimated number of rows: 1.55354e+07
Number of columns: 2
Subquery predicate ID: Not Applicable

Column Names:
------------
+Q3.KEY0(A)+Q3.$RID$
Output Streams:
4) To Operator #4

Estimated number of rows: 2101.97
Number of columns: 1
Subquery predicate ID: Not Applicable

Column Names:
------------
+Q3.KEY0(A)
Objects Used in Access Plan:
---------------------------

Schema: APDEV3
Name: EADMPF00_ASC
Type: Index
Time of creation: 2005-07-07-16.20.18.130417
Last statistics update: 2005-07-07-16.53.42.240024
Number of columns: 1
Number of rows: 15535378
Width of rows: -1
Number of buffer pool pages: 84020
Distinct row values: Yes
Tablespace name: CFGTBSP
Tablespace overhead: 12.670000
Tablespace transfer rate: 0.180000
Source for statistics: Single Node
Prefetch page count: 10
Container extent page count: 10
Index clustering statistic: 1.000000
Index leaf pages: 21137
Index tree levels: 3
Index full key cardinality: 15535378
Index first key cardinality: 15535378
Index first 2 keys cardinality: -1
Index first 3 keys cardinality: -1
Index first 4 keys cardinality: -1
Index sequential pages: 21136
Index page density: 99
Index avg sequential pages: 21136
Index avg gap between sequences:0
Index avg random pages: 0
Fetch avg sequential pages: -1
Fetch avg gap between sequences:-1
Fetch avg random pages: -1
Index RID count: 15535378
Index deleted RID count: 0
Index empty leaf pages: 0
Base Table Schema: APDEV3
Base Table Name: EADMPF00
Columns in index:
KEY0

Schema: APDEV3
Name: EADMPF00
Type: Table
Time of creation: 2005-06-29-00.06.57.810801
Last statistics update: 2005-07-07-16.53.42.240024
Number of columns: 2
Number of rows: 15535378
Width of rows: 171
Number of buffer pool pages: 84020
Distinct row values: No
Tablespace name: CFGTBSP
Tablespace overhead: 12.670000
Tablespace transfer rate: 0.180000
Source for statistics: Single Node
Prefetch page count: 10
Container extent page count: 10
Table overflow record count: 0
Table Active Blocks: -1

Schema: SYSIBM
Name: GENROW
Type: Table Function
Time of creation:
Last statistics update:
Number of columns: 1
Number of rows: 1
Width of rows: 11
Number of buffer pool pages: -1
Distinct row values: No
Source for statistics: Single Node

---------------------------------------------------------------------------
---------------- STATEMENT 8 SECTION 8 ----------------
QUERYNO: 8
QUERYTAG:
Statement Type: Select
Updatable: No
Deletable: No
Query Degree: 1

Original Statement:
------------------
DECLARE KEY0_ASCRD CURSOR
FOR
SELECT KEY0, REC
FROM apdev3.EADMPF00
WHERE KEY0 >= :H00006 AND KEY0 <= :H00001
ORDER BY KEY0 ASC
FETCH FIRST 1 ROWS ONLY
Optimized Statement:
-------------------
SELECT Q3.KEY0, Q3.REC
FROM
(SELECT Q1.$C0
FROM (VALUES 0) AS Q1
WHERE (:H00006 <= :H00001 SELECTIVITY 1.000000)) AS Q2, APDEV3.EADMPF00 AS
Q3
WHERE (Q3.KEY0 <= :H00001) AND (:H00006 <= Q3.KEY0)
ORDER BY Q3.KEY0

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

Rows
RETURN
( 1)
Cost
I/O
|
1
TBSCAN
( 2)
193.505
31.4559
|
1
SORT
( 3)
193.504
31.4559
|
2101.97
NLJOIN
( 4)
192.952
31.4559
/------+------\
1 2101.97
TBSCAN FETCH
( 5) ( 6)
0.000400312 97.3694
0 15.2279
| /---+---\
1 2101.97 1.55354e+07
TABFNC: SYSIBM RIDSCN TABLE: APDEV3
GENROW ( 7) EADMPF00
54.0633
3.85988
|
2101.97
SORT
( 8)
54.0624
3.85988
|
2101.97
IXSCAN
( 9)
52.2223
3.85988
|
1.55354e+07
INDEX: APDEV3
EADMPF00_ASC


1) RETURN: (Return Result)
Cumulative Total Cost: 193.505
Cumulative CPU Cost: 1.1723e+07
Cumulative I/O Cost: 31.4559
Cumulative Re-Total Cost: 6.1522
Cumulative Re-CPU Cost: 8.68322e+06
Cumulative Re-I/O Cost: 0
Cumulative First Row Cost: 193.505
Estimated Bufferpool Buffers: 0

Arguments:
---------
BLDLEVEL: (Build level)
DB2 v8.1.1.80 : s041221
STMTHEAP: (Statement heap size)
16384

Input Streams:
-------------
11) From Operator #2

Estimated number of rows: 1
Number of columns: 2
Subquery predicate ID: Not Applicable

Column Names:
------------
+Q4.REC+Q4.KEY0
2) TBSCAN: (Table Scan)
Cumulative Total Cost: 193.505
Cumulative CPU Cost: 1.1723e+07
Cumulative I/O Cost: 31.4559
Cumulative Re-Total Cost: 6.1522
Cumulative Re-CPU Cost: 8.68322e+06
Cumulative Re-I/O Cost: 0
Cumulative First Row Cost: 193.505
Estimated Bufferpool Buffers: 0

Arguments:
---------
MAXPAGES: (Maximum pages for prefetch)
ALL
PREFETCH: (Type of Prefetch)
NONE
SCANDIR : (Scan Direction)
FORWARD

Input Streams:
-------------
10) From Operator #3

Estimated number of rows: 1
Number of columns: 2
Subquery predicate ID: Not Applicable

Column Names:
------------
+Q3.KEY0(A)+Q3.REC
Output Streams:
--------------
11) To Operator #1

Estimated number of rows: 1
Number of columns: 2
Subquery predicate ID: Not Applicable

Column Names:
------------
+Q4.REC+Q4.KEY0
3) SORT : (Sort)
Cumulative Total Cost: 193.504
Cumulative CPU Cost: 1.17212e+07
Cumulative I/O Cost: 31.4559
Cumulative Re-Total Cost: 6.15094
Cumulative Re-CPU Cost: 8.68143e+06
Cumulative Re-I/O Cost: 0
Cumulative First Row Cost: 193.504
Estimated Bufferpool Buffers: 16.2279

Arguments:
---------
DUPLWARN: (Duplicates Warning flag)
FALSE
NUMROWS : (Estimated number of rows)
1
ROWWIDTH: (Estimated width of rows)
172
SORTKEY : (Sort Key column)
1: Q3.KEY0(A)
TEMPSIZE: (Temporary Table Page Size)
4096
TRUNCSRT: (Truncated sort (for Fetch First n Rows Only))
TRUE
UNIQUE : (Uniqueness required flag)
FALSE

Input Streams:
-------------
9) From Operator #4

Estimated number of rows: 2101.97
Number of columns: 2
Subquery predicate ID: Not Applicable

Column Names:
------------
+Q3.REC+Q3.KEY0
Output Streams:
--------------
10) To Operator #2

Estimated number of rows: 1
Number of columns: 2
Subquery predicate ID: Not Applicable

Column Names:
------------
+Q3.KEY0(A)+Q3.REC
4) NLJOIN: (Nested Loop Join)
Cumulative Total Cost: 192.952
Cumulative CPU Cost: 1.09429e+07
Cumulative I/O Cost: 31.4559
Cumulative Re-Total Cost: 6.15094
Cumulative Re-CPU Cost: 8.68143e+06
Cumulative Re-I/O Cost: 0
Cumulative First Row Cost: 66.9203
Estimated Bufferpool Buffers: 16.2279

Arguments:
---------
EARLYOUT: (Early Out flag)
NONE
FETCHMAX: (Override for FETCH MAXPAGES)
IGNORE
ISCANMAX: (Override for ISCAN MAXPAGES)
IGNORE

Input Streams:
-------------
2) From Operator #5

Estimated number of rows: 1
Number of columns: 0
Subquery predicate ID: Not Applicable

8) From Operator #6

Estimated number of rows: 2101.97
Number of columns: 2
Subquery predicate ID: Not Applicable

Column Names:
------------
+Q3.REC+Q3.KEY0
Output Streams:
--------------
9) To Operator #3

Estimated number of rows: 2101.97
Number of columns: 2
Subquery predicate ID: Not Applicable

Column Names:
------------
+Q3.REC+Q3.KEY0
5) TBSCAN: (Table Scan)
Cumulative Total Cost: 0.000400312
Cumulative CPU Cost: 565
Cumulative I/O Cost: 0
Cumulative Re-Total Cost: 0.000400312
Cumulative Re-CPU Cost: 565
Cumulative Re-I/O Cost: 0
Cumulative First Row Cost: 0.000379056
Estimated Bufferpool Buffers: 0

Arguments:
---------
JN INPUT: (Join input leg)
OUTER
MAXPAGES: (Maximum pages for prefetch)
ALL
PREFETCH: (Type of Prefetch)
NONE
ROWLOCK : (Row Lock intent)
NONE
SCANDIR : (Scan Direction)
FORWARD
TABLOCK : (Table Lock intent)
INTENT NONE
TBISOLVL: (Table access Isolation Level)
UNCOMMITTED READ

Predicates:
----------
4) Residual Predicate
Relational Operator: Less Than or Equal (<=)
Subquery Input Required: No
Filter Factor: 1

Predicate Text:
--------------
(:H00006 <= :H00001 SELECTIVITY 1.000000)
Input Streams:
-------------
1) From Object SYSIBM.GENROW

Estimated number of rows: 1
Number of columns: 1
Subquery predicate ID: Not Applicable

Column Names:
------------
+Q1.NUMB
Output Streams:
--------------
2) To Operator #4

Estimated number of rows: 1
Number of columns: 0
Subquery predicate ID: Not Applicable
6) FETCH : (Fetch)
Cumulative Total Cost: 97.3694
Cumulative CPU Cost: 1.08872e+07
Cumulative I/O Cost: 15.2279
Cumulative Re-Total Cost: 6.15054
Cumulative Re-CPU Cost: 8.68087e+06
Cumulative Re-I/O Cost: 0
Cumulative First Row Cost: 66.9199
Estimated Bufferpool Buffers: 16.2279

Arguments:
---------
JN INPUT: (Join input leg)
INNER
MAX RIDS: (Maximum RIDs per list prefetch request)
1024
MAXPAGES: (Maximum pages for prefetch)
11
PREFETCH: (Type of Prefetch)
LIST
ROWLOCK : (Row Lock intent)
NONE
TABLOCK : (Table Lock intent)
INTENT NONE
TBISOLVL: (Table access Isolation Level)
UNCOMMITTED READ

Predicates:
----------
2) Sargable Predicate
Relational Operator: Less Than or Equal (<=)
Subquery Input Required: No
Filter Factor: 0.000451006

Predicate Text:
--------------
(Q3.KEY0 <= :H00001)

3) Sargable Predicate
Relational Operator: Less Than or Equal (<=)
Subquery Input Required: No
Filter Factor: 0.000451006

Predicate Text:
--------------
(:H00006 <= Q3.KEY0)
Input Streams:
-------------
6) From Operator #7

Estimated number of rows: 2101.97
Number of columns: 1
Subquery predicate ID: Not Applicable

Column Names:
------------
+Q3.$RID$(A)

7) From Object APDEV3.EADMPF00

Estimated number of rows: 1.55354e+07
Number of columns: 2
Subquery predicate ID: Not Applicable

Column Names:
------------
+Q3.REC+Q3.KEY0
Output Streams:
--------------
8) To Operator #4

Estimated number of rows: 2101.97
Number of columns: 2
Subquery predicate ID: Not Applicable

Column Names:
------------
+Q3.REC+Q3.KEY0
7) RIDSCN: (Row Identifier Scan)
Cumulative Total Cost: 54.0633
Cumulative CPU Cost: 6.30032e+06
Cumulative I/O Cost: 3.85988
Cumulative Re-Total Cost: 2.94794
Cumulative Re-CPU Cost: 4.16073e+06
Cumulative Re-I/O Cost: 0
Cumulative First Row Cost: 54.0624
Estimated Bufferpool Buffers: 4.85988

Arguments:
---------
NUMROWS : (Estimated number of rows)
2102

Input Streams:
-------------
5) From Operator #8

Estimated number of rows: 2101.97
Number of columns: 1
Subquery predicate ID: Not Applicable

Column Names:
------------
+Q3.$RID$(A)
Output Streams:
--------------
6) To Operator #6

Estimated number of rows: 2101.97
Number of columns: 1
Subquery predicate ID: Not Applicable

Column Names:
------------
+Q3.$RID$(A)
8) SORT : (Sort)
Cumulative Total Cost: 54.0624
Cumulative CPU Cost: 6.29904e+06
Cumulative I/O Cost: 3.85988
Cumulative Re-Total Cost: 2.57454
Cumulative Re-CPU Cost: 3.6337e+06
Cumulative Re-I/O Cost: 0
Cumulative First Row Cost: 54.0624
Estimated Bufferpool Buffers: 4.85988

Arguments:
---------
DUPLWARN: (Duplicates Warning flag)
TRUE
NUMROWS : (Estimated number of rows)
2102
ROWWIDTH: (Estimated width of rows)
12
SORTKEY : (Sort Key column)
1: Q3.$RID$(A)
TEMPSIZE: (Temporary Table Page Size)
4096
UNIQUE : (Uniqueness required flag)
TRUE

Input Streams:
-------------
4) From Operator #9

Estimated number of rows: 2101.97
Number of columns: 1
Subquery predicate ID: Not Applicable

Column Names:
------------
+Q3.KEY0(A)
Output Streams:
--------------
5) To Operator #7

Estimated number of rows: 2101.97
Number of columns: 1
Subquery predicate ID: Not Applicable

Column Names:
------------
+Q3.$RID$(A)
9) IXSCAN: (Index Scan)
Cumulative Total Cost: 52.2223
Cumulative CPU Cost: 3.70197e+06
Cumulative I/O Cost: 3.85988
Cumulative Re-Total Cost: 2.57454
Cumulative Re-CPU Cost: 3.6337e+06
Cumulative Re-I/O Cost: 0
Cumulative First Row Cost: 25.7487
Estimated Bufferpool Buffers: 4.85988

Arguments:
---------
MAXPAGES: (Maximum pages for prefetch)
2
PREFETCH: (Type of Prefetch)
NONE
ROWLOCK : (Row Lock intent)
NONE
SCANDIR : (Scan Direction)
FORWARD
TABLOCK : (Table Lock intent)
INTENT NONE

Predicates:
----------
2) Stop Key Predicate
Relational Operator: Less Than or Equal (<=)
Subquery Input Required: No
Filter Factor: 0.000451006

Predicate Text:
--------------
(Q3.KEY0 <= :H00001)

3) Start Key Predicate
Relational Operator: Less Than or Equal (<=)
Subquery Input Required: No
Filter Factor: 0.000451006

Predicate Text:
--------------
(:H00006 <= Q3.KEY0)
Input Streams:
-------------
3) From Object APDEV3.EADMPF00_ASC

Estimated number of rows: 1.55354e+07
Number of columns: 2
Subquery predicate ID: Not Applicable

Column Names:
------------
+Q3.KEY0(A)+Q3.$RID$
Output Streams:
--------------
4) To Operator #8

Estimated number of rows: 2101.97
Number of columns: 1
Subquery predicate ID: Not Applicable

Column Names:
------------
+Q3.KEY0(A)
Objects Used in Access Plan:
---------------------------

Schema: APDEV3
Name: EADMPF00_ASC
Type: Index
Time of creation: 2005-07-07-16.20.18.130417
Last statistics update: 2005-07-07-16.53.42.240024
Number of columns: 1
Number of rows: 15535378
Width of rows: -1
Number of buffer pool pages: 84020
Distinct row values: Yes
Tablespace name: CFGTBSP
Tablespace overhead: 12.670000
Tablespace transfer rate: 0.180000
Source for statistics: Single Node
Prefetch page count: 10
Container extent page count: 10
Index clustering statistic: 1.000000
Index leaf pages: 21137
Index tree levels: 3
Index full key cardinality: 15535378
Index first key cardinality: 15535378
Index first 2 keys cardinality: -1
Index first 3 keys cardinality: -1
Index first 4 keys cardinality: -1
Index sequential pages: 21136
Index page density: 99
Index avg sequential pages: 21136
Index avg gap between sequences:0
Index avg random pages: 0
Fetch avg sequential pages: -1
Fetch avg gap between sequences:-1
Fetch avg random pages: -1
Index RID count: 15535378
Index deleted RID count: 0
Index empty leaf pages: 0
Base Table Schema: APDEV3
Base Table Name: EADMPF00
Columns in index:
KEY0

Schema: APDEV3
Name: EADMPF00
Type: Table
Time of creation: 2005-06-29-00.06.57.810801
Last statistics update: 2005-07-07-16.53.42.240024
Number of columns: 2
Number of rows: 15535378
Width of rows: 171
Number of buffer pool pages: 84020
Distinct row values: No
Tablespace name: CFGTBSP
Tablespace overhead: 12.670000
Tablespace transfer rate: 0.180000
Source for statistics: Single Node
Prefetch page count: 10
Container extent page count: 10
Table overflow record count: 0
Table Active Blocks: -1

Schema: SYSIBM
Name: GENROW
Type: Table Function
Time of creation:
Last statistics update:
Number of columns: 1
Number of rows: 1
Width of rows: 11
Number of buffer pool pages: -1
Distinct row values: No
Source for statistics: Single Node

--
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.

[Mark A]

"Hemant Shah" <sh**@typhoon.xnet.com> wrote in message
news:da**********@new7.xnet.com...

Folks,

We have an SQL statement that was coded in an application many years ago
(starting with DB V2 I think). When I upgraded to UDB 8.2, the optimizer
does not use optimal path to access the data.
It takes about 4 minutes to get the data. In previous versions it was
instantaneous.

What do I need to do to increase the performance?

Make sure you do runstats on the tables AND the indexes. Get distribution
stats on key columns (at least).

RUNSTATS ON TABLE db2user.employee ON KEY COLUMNS AND INDEXES ALL

Issue FLUSH PACKAGE CACHE command, or if you have static SQL programs then
do a rebind.

[Jan M. Nelken]

Hemant Shah wrote:

I already have the information in explain table. Here is the output of the
two sections in question.
...

I would try to modify your section 8 statement like this:
DECLARE
KEY0_ASCRD CURSOR
FOR
SELECT
KEY0,
REC
FROM
EADMPF00
WHERE
KEY0 >= :H00006
AND
KEY0 <= :H00001
ORDER BY
KEY0 ASC
FETCH FIRST 1 ROWS ONLY
OPTIMIZE FOR 1 ROWS
;

Jan M. Nelken

[Mark Yudkin]

We had a similar problem recently, in which the latest DB2 "decided to be
smart", ignored the obvious index, and thereby messed up a particular query
completely (few seconds changed to 15 minutes).

We used EXPLAIN to figure out what's happening, then fiddled with the index
definitions until DB2 was happy to do things properly. We tried a PMR, but
it got nowhere. Nevertheless, you should definitely try that route too.

"Hemant Shah" <sh**@typhoon.xnet.com> wrote in message
news:da**********@new7.xnet.com...

Folks,

We have an SQL statement that was coded in an application many years ago
(starting with DB V2 I think). When I upgraded to UDB 8.2, the optimizer
does not use optimal path to access the data.
It takes about 4 minutes to get the data. In previous versions it was
instantaneous.

What do I need to do to increase the performance?
There are the details:

# oslevel
5.1.0.0

# db2level
DB21085I Instance "dbap3" uses "32" bits and DB2 code release "SQL08021"
with
level identifier "03020106".
Informational tokens are "DB2 v8.1.1.80", "s041221", "U800400", and FixPak
"8".
Product is installed at "/usr/opt/db2_08_01".
create table apdev3.EADMPF00
(KEY0 CHAR (31) not null,
REC VARCHAR(3000))
IN cfgtbsp
INDEX IN cfgtbsp
LONG IN cfgtbsp;
create unique index EADMPF00_ASC on apdev3.EADMPF00 (KEY0 asc);
# db2 reorgchk current statistics on table apdev3.eadmpf00

Table statistics:

F1: 100 * OVERFLOW / CARD < 5
F2: 100 * (Effective Space Utilization of Data Pages) > 70
F3: 100 * (Required Pages / Total Pages) > 80

SCHEMA NAME CARD OV NP FP ACTBLK TSIZE F1
F2 F3 REORG
----------------------------------------------------------------------------------------
Table: APDEV3.EADMPF00
APDEV3 EADMPF00 1.6e+07 0 84020 84020 - 2.72e+09 0
98 100 ---
----------------------------------------------------------------------------------------

Index statistics:

F4: CLUSTERRATIO or normalized CLUSTERFACTOR > 80
F5: 100 * (KEYS * (ISIZE + 9) + (CARD - KEYS) * 5) / ((NLEAF - NUM EMPTY
LEAFS) * INDEXPAGESIZE) > 50
F6: (100 - PCTFREE) * ((INDEXPAGESIZE - 96) / (ISIZE + 12)) ** (NLEVELS -
2) * (INDEXPAGESIZE - 96) / (KEYS * (ISIZE + 9) + (CARD - KEYS) * 5) < 100
F7: 100 * (NUMRIDS DELETED / (NUMRIDS DELETED + CARD)) < 20
F8: 100 * (NUM EMPTY LEAFS / NLEAF) < 20

SCHEMA NAME CARD LEAF ELEAF LVLS ISIZE NDEL KEYS
F4 F5 F6 F7 F8 REORG
-------------------------------------------------------------------------------------------------
Table: APDEV3.EADMPF00
APDEV3 EADMPF00_ASC 2e+07 21137 0 3 31 0 2e+07
100 89 3 0 0 -----
-------------------------------------------------------------------------------------------------
Here is the explain output:
Section = 5
SQL Statement:
DECLARE KEY0_ASCBR CURSOR
FOR
SELECT KEY0, REC
FROM apdev3.EADMPF00
WHERE KEY0 >=:H00006 AND KEY0 <=:H00001
ORDER BY KEY0 ASC
FOR
FETCH ONLY
OPTIMIZE
FOR 15 ROWS
Section Code Page = 819

Estimated Cost = 199.729889
Estimated Cardinality = 2101.966309

Table Constructor
| 1-Row(s)
Residual Predicate(s)
| #Predicates = 1
Nested Loop Join
| Access Table Name = APDEV3.EADMPF00 ID = 3,47
| | Index Scan: Name = APDEV3.EADMPF00_ASC ID = 1
| | | Regular Index (Not Clustered)
| | | Index Columns:
| | | | 1: KEY0 (Ascending)
| | #Columns = 2
| | #Key Columns = 1
| | | Start Key: Inclusive Value
| | | | | 1: ?
| | | Stop Key: Inclusive Value
| | | | | 1: ?
| | Data Prefetch: None
| | Index Prefetch: None
| | Lock Intents
| | | Table: Intent None
| | | Row : None
Return Data to Application
| #Columns = 2

End of section

------------------------------------------------------------------
Section = 8
SQL Statement:
DECLARE KEY0_ASCRD CURSOR
FOR
SELECT KEY0, REC
FROM apdev3.EADMPF00
WHERE KEY0 >=:H00006 AND KEY0 <=:H00001
ORDER BY KEY0 ASC
FETCH FIRST 1 ROWS ONLY
Section Code Page = 819

Estimated Cost = 193.504868
Estimated Cardinality = 1.000000

Table Constructor
| 1-Row(s)
Residual Predicate(s)
| #Predicates = 1
Nested Loop Join
| Piped Inner
| Access Table Name = APDEV3.EADMPF00 ID = 3,47
| | Index Scan: Name = APDEV3.EADMPF00_ASC ID = 1
| | | Regular Index (Not Clustered)
| | | Index Columns:
| | | | 1: KEY0 (Ascending)
| | #Columns = 0
| | #Key Columns = 1
| | | Start Key: Inclusive Value
| | | | | 1: ?
| | | Stop Key: Inclusive Value
| | | | | 1: ?
| | Index-Only Access
| | Index Prefetch: None
| | Lock Intents
| | | Table: Intent None
| | | Row : None
| | Sargable Index Predicate(s)
| | | Insert Into Sorted Temp Table ID = t1
| | | | #Columns = 1
| | | | #Sort Key Columns = 1
| | | | | Key 1: (Ascending)
| | | | Sortheap Allocation Parameters:
| | | | | #Rows = 2102
| | | | | Row Width = 12
| | | | Piped
| | | | Duplicate Elimination
| Sorted Temp Table Completion ID = t1
| List Prefetch Preparation
| | Access Table Name = APDEV3.EADMPF00 ID = 3,47
| | | #Columns = 2
| | | Fetch Using Prefetched List
| | | | Prefetch: 11 Pages
| | | Lock Intents
| | | | Table: Intent None
| | | | Row : None
| | | Sargable Predicate(s)
| | | | #Predicates = 2
Insert Into Sorted Temp Table ID = t2
| #Columns = 2
| #Sort Key Columns = 1
| | Key 1: KEY0 (Ascending)
| Sortheap Allocation Parameters:
| | #Rows = 1
| | Row Width = 172
| | 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

As you can see, in section 5 I use OPTIMIZE clause and it is is able to
get data quickly. In section 8 use FETCH FIRST 1 ROWS ONLY and it takes
forever.

How do I optimize the Section 8 query?

Thanks.

--
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.

[Hemant Shah]

There is something wrong with the optimizer. I changed

"FETCH FIRST 1 ROWS ONLY"

to

"OPTIMIZE FOR 1 ROWS"

and it was using correct access path again.

I would think that it would use same method in the above statements.

While stranded on information super highway Mark Yudkin wrote:

We had a similar problem recently, in which the latest DB2 "decided to be
smart", ignored the obvious index, and thereby messed up a particular query
completely (few seconds changed to 15 minutes).

We used EXPLAIN to figure out what's happening, then fiddled with the index
definitions until DB2 was happy to do things properly. We tried a PMR, but
it got nowhere. Nevertheless, you should definitely try that route too.

"Hemant Shah" <sh**@typhoon.xnet.com> wrote in message
news:da**********@new7.xnet.com...

Folks,

We have an SQL statement that was coded in an application many years ago
(starting with DB V2 I think). When I upgraded to UDB 8.2, the optimizer
does not use optimal path to access the data.
It takes about 4 minutes to get the data. In previous versions it was
instantaneous.

What do I need to do to increase the performance?
There are the details:

# oslevel
5.1.0.0

# db2level
DB21085I Instance "dbap3" uses "32" bits and DB2 code release "SQL08021"
with
level identifier "03020106".
Informational tokens are "DB2 v8.1.1.80", "s041221", "U800400", and FixPak
"8".
Product is installed at "/usr/opt/db2_08_01".
create table apdev3.EADMPF00
(KEY0 CHAR (31) not null,
REC VARCHAR(3000))
IN cfgtbsp
INDEX IN cfgtbsp
LONG IN cfgtbsp;
create unique index EADMPF00_ASC on apdev3.EADMPF00 (KEY0 asc);
# db2 reorgchk current statistics on table apdev3.eadmpf00

Table statistics:

F1: 100 * OVERFLOW / CARD < 5
F2: 100 * (Effective Space Utilization of Data Pages) > 70
F3: 100 * (Required Pages / Total Pages) > 80

SCHEMA NAME CARD OV NP FP ACTBLK TSIZE F1
F2 F3 REORG
----------------------------------------------------------------------------------------
Table: APDEV3.EADMPF00
APDEV3 EADMPF00 1.6e+07 0 84020 84020 - 2.72e+09 0
98 100 ---
----------------------------------------------------------------------------------------

Index statistics:

F4: CLUSTERRATIO or normalized CLUSTERFACTOR > 80
F5: 100 * (KEYS * (ISIZE + 9) + (CARD - KEYS) * 5) / ((NLEAF - NUM EMPTY
LEAFS) * INDEXPAGESIZE) > 50
F6: (100 - PCTFREE) * ((INDEXPAGESIZE - 96) / (ISIZE + 12)) ** (NLEVELS -
2) * (INDEXPAGESIZE - 96) / (KEYS * (ISIZE + 9) + (CARD - KEYS) * 5) < 100
F7: 100 * (NUMRIDS DELETED / (NUMRIDS DELETED + CARD)) < 20
F8: 100 * (NUM EMPTY LEAFS / NLEAF) < 20

SCHEMA NAME CARD LEAF ELEAF LVLS ISIZE NDEL KEYS
F4 F5 F6 F7 F8 REORG
-------------------------------------------------------------------------------------------------
Table: APDEV3.EADMPF00
APDEV3 EADMPF00_ASC 2e+07 21137 0 3 31 0 2e+07
100 89 3 0 0 -----
-------------------------------------------------------------------------------------------------
Here is the explain output:
Section = 5
SQL Statement:
DECLARE KEY0_ASCBR CURSOR
FOR
SELECT KEY0, REC
FROM apdev3.EADMPF00
WHERE KEY0 >=:H00006 AND KEY0 <=:H00001
ORDER BY KEY0 ASC
FOR
FETCH ONLY
OPTIMIZE
FOR 15 ROWS
Section Code Page = 819

Estimated Cost = 199.729889
Estimated Cardinality = 2101.966309

Table Constructor
| 1-Row(s)
Residual Predicate(s)
| #Predicates = 1
Nested Loop Join
| Access Table Name = APDEV3.EADMPF00 ID = 3,47
| | Index Scan: Name = APDEV3.EADMPF00_ASC ID = 1
| | | Regular Index (Not Clustered)
| | | Index Columns:
| | | | 1: KEY0 (Ascending)
| | #Columns = 2
| | #Key Columns = 1
| | | Start Key: Inclusive Value
| | | | | 1: ?
| | | Stop Key: Inclusive Value
| | | | | 1: ?
| | Data Prefetch: None
| | Index Prefetch: None
| | Lock Intents
| | | Table: Intent None
| | | Row : None
Return Data to Application
| #Columns = 2

End of section

------------------------------------------------------------------
Section = 8
SQL Statement:
DECLARE KEY0_ASCRD CURSOR
FOR
SELECT KEY0, REC
FROM apdev3.EADMPF00
WHERE KEY0 >=:H00006 AND KEY0 <=:H00001
ORDER BY KEY0 ASC
FETCH FIRST 1 ROWS ONLY
Section Code Page = 819

Estimated Cost = 193.504868
Estimated Cardinality = 1.000000

Table Constructor
| 1-Row(s)
Residual Predicate(s)
| #Predicates = 1
Nested Loop Join
| Piped Inner
| Access Table Name = APDEV3.EADMPF00 ID = 3,47
| | Index Scan: Name = APDEV3.EADMPF00_ASC ID = 1
| | | Regular Index (Not Clustered)
| | | Index Columns:
| | | | 1: KEY0 (Ascending)
| | #Columns = 0
| | #Key Columns = 1
| | | Start Key: Inclusive Value
| | | | | 1: ?
| | | Stop Key: Inclusive Value
| | | | | 1: ?
| | Index-Only Access
| | Index Prefetch: None
| | Lock Intents
| | | Table: Intent None
| | | Row : None
| | Sargable Index Predicate(s)
| | | Insert Into Sorted Temp Table ID = t1
| | | | #Columns = 1
| | | | #Sort Key Columns = 1
| | | | | Key 1: (Ascending)
| | | | Sortheap Allocation Parameters:
| | | | | #Rows = 2102
| | | | | Row Width = 12
| | | | Piped
| | | | Duplicate Elimination
| Sorted Temp Table Completion ID = t1
| List Prefetch Preparation
| | Access Table Name = APDEV3.EADMPF00 ID = 3,47
| | | #Columns = 2
| | | Fetch Using Prefetched List
| | | | Prefetch: 11 Pages
| | | Lock Intents
| | | | Table: Intent None
| | | | Row : None
| | | Sargable Predicate(s)
| | | | #Predicates = 2
Insert Into Sorted Temp Table ID = t2
| #Columns = 2
| #Sort Key Columns = 1
| | Key 1: KEY0 (Ascending)
| Sortheap Allocation Parameters:
| | #Rows = 1
| | Row Width = 172
| | 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

As you can see, in section 5 I use OPTIMIZE clause and it is is able to
get data quickly. In section 8 use FETCH FIRST 1 ROWS ONLY and it takes
forever.

How do I optimize the Section 8 query?

Thanks.

--
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.

--
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.

[Hemant Shah]

While stranded on information super highway Jan M. Nelken wrote:

Hemant Shah wrote:

I already have the information in explain table. Here is the output of the
two sections in question.
...

I would try to modify your section 8 statement like this:
DECLARE
KEY0_ASCRD CURSOR
FOR
SELECT
KEY0,
REC
FROM
EADMPF00
WHERE
KEY0 >= :H00006
AND
KEY0 <= :H00001
ORDER BY
KEY0 ASC
FETCH FIRST 1 ROWS ONLY
OPTIMIZE FOR 1 ROWS
;

Jan M. Nelken

Changing "FETCH FIRST 1 ROWS ONLY" to "OPTIMIZE FOR 1 ROWS" fixed the problem
but why would DB2 not use optimal method without OPTIMIZE clause?

Something changed in the optimizer with UDB 8.2.
--
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.

[Jan M. Nelken]

Hemant Shah wrote:

While stranded on information super highway Jan M. Nelken wrote:

Hemant Shah wrote:

I already have the information in explain table. Here is the output of the
two sections in question.
...

I would try to modify your section 8 statement like this:
DECLARE
KEY0_ASCRD CURSOR
FOR
SELECT
KEY0,
REC
FROM
EADMPF00
WHERE
KEY0 >= :H00006
AND
KEY0 <= :H00001
ORDER BY
KEY0 ASC
FETCH FIRST 1 ROWS ONLY
OPTIMIZE FOR 1 ROWS
;

Jan M. Nelken

Changing "FETCH FIRST 1 ROWS ONLY" to "OPTIMIZE FOR 1 ROWS" fixed the problem
but why would DB2 not use optimal method without OPTIMIZE clause?

Something changed in the optimizer with UDB 8.2.

"Optimize for n rows" is a suggestion to optimizer to take into consideration
cost of delivering n rows instead of full result set; "Fetch m rows only" is
request to stop delivering result set after m rows - but *not* a suggestion to
change costing criteria - so optimizer will construct access plan to deliver
full result set and stop delivery after m rows - those two clauses are *Different*!

Your first query had "Optimize for 15 rows" clause - so your second query should
have "optimize for 1 row" in addition to "fetch 1 row only" - to have comparable
access plans.

Optimizer is working as designed and intended to in this case.

Jan M. Nelken

[Jan M. Nelken]

Hemant Shah wrote:

There is something wrong with the optimizer. I changed

"FETCH FIRST 1 ROWS ONLY"

to

"OPTIMIZE FOR 1 ROWS"

and it was using correct access path again.

I would think that it would use same method in the above statements.

See my reply a few posts above.

Jan M. Nelken

[Mark A]

"Hemant Shah" <sh**@typhoon.xnet.com> wrote in message
news:db**********@new7.xnet.com...

While stranded on information super highway Jan M. Nelken wrote:

Hemant Shah wrote:

I already have the information in explain table. Here is the output of
the
two sections in question.
...

I would try to modify your section 8 statement like this:
DECLARE
KEY0_ASCRD CURSOR
FOR
SELECT
KEY0,
REC
FROM
EADMPF00
WHERE
KEY0 >= :H00006
AND
KEY0 <= :H00001
ORDER BY
KEY0 ASC
FETCH FIRST 1 ROWS ONLY
OPTIMIZE FOR 1 ROWS
;

Jan M. Nelken

Changing "FETCH FIRST 1 ROWS ONLY" to "OPTIMIZE FOR 1 ROWS" fixed the
problem
but why would DB2 not use optimal method without OPTIMIZE clause?

Something changed in the optimizer with UDB 8.2.

DB2 does not know how many rows need to be fetched for KEY0 >= :H00006 AND
KEY0 <= :H00001. It previously assumed an access path that probably would
have been optimal if more that 1/2 the table rows qualified, but if a much
smaller number of rows would be returned then you need to give it some
hints.

Another way to give it some hints might have been to run statistics
(runstats) with all the options (table and all indexes) including
distribution on all columns, so that DB2 would have a better idea of what
the data actually looks like. But even then, it may be hard for DB2 to know
for sure.

[Gert van der Kooij]

>

"Optimize for n rows" is a suggestion to optimizer to take into consideration
cost of delivering n rows instead of full result set; "Fetch m rows only" is
request to stop delivering result set after m rows - but *not* a suggestion to
change costing criteria - so optimizer will construct access plan to deliver
full result set and stop delivery after m rows - those two clauses are *Different*!

Your first query had "Optimize for 15 rows" clause - so your second query should
have "optimize for 1 row" in addition to "fetch 1 row only" - to have comparable
access plans.

Optimizer is working as designed and intended to in this case.

Jan M. Nelken

Does the optimizer react different if the fetch option is used in a
straight select compared to the same select used in a cursor?
If used in a straight select the optimizer knows how many rows will
be returned and should take the fetch option in consideration.

Kind regards, Gert

[Jan M. Nelken]

Gert van der Kooij wrote:

"Optimize for n rows" is a suggestion to optimizer to take into consideration
cost of delivering n rows instead of full result set; "Fetch m rows only" is
request to stop delivering result set after m rows - but *not* a suggestion to
change costing criteria - so optimizer will construct access plan to deliver
full result set and stop delivery after m rows - those two clauses are *Different*!

Your first query had "Optimize for 15 rows" clause - so your second query should
have "optimize for 1 row" in addition to "fetch 1 row only" - to have comparable
access plans.

Optimizer is working as designed and intended to in this case.

Jan M. Nelken

Does the optimizer react different if the fetch option is used in a
straight select compared to the same select used in a cursor?
If used in a straight select the optimizer knows how many rows will
be returned and should take the fetch option in consideration.

Kind regards, Gert

Searching online documentation (available at:
http://publib.boulder.ibm.com/infoce...lp/index.jsp):

"...The OPTIMIZE FOR clause requests special processing of the select statement.
If the clause is omitted, it is assumed that all rows of the result table will
be retrieved; if it is specified, it is assumed that the number of rows
retrieved will probably not exceed n, where n is the value of integer. The value
of n must be a positive integer. Use of the OPTIMIZE FOR clause influences query
optimization, based on the assumption that n rows will be retrieved. In
addition, for cursors that are blocked, this clause will influence the number of
rows that will be returned in each block (that is, no more than n rows will be
returned in each block). If both the fetch-first-clause and the
optimize-for-clause are specified, the lower of the integer values from these
clauses will be used to influence the communications buffer size. The values are
considered independently for optimization purposes.

This clause does not limit the number of rows that can be fetched, or affect the
result in any other way than performance. Using OPTIMIZE FOR n ROWS can improve
performance if no more than n rows are retrieved, but may degrade performance if
more than n rows are retrieved..."

"...The FETCH FIRST n ROWS ONLY clause sets the maximum number of rows that can
be retrieved. Limiting the result table to the first several rows can improve
performance. Only n rows are retrieved regardless of the number of rows that the
result set might otherwise contain..."

So my answer is that it is irrelevant here whether statement is a SELECT ... or
DECLARE CURSOR ...: optimize for n rows will influence optimizer, fetch n rows
controls delivery.

Jan M. Nelken

O

[Hemant Shah]

While stranded on information super highway Mark A wrote:

"Hemant Shah" <sh**@typhoon.xnet.com> wrote in message
news:db**********@new7.xnet.com...

While stranded on information super highway Jan M. Nelken wrote:

Hemant Shah wrote:

I already have the information in explain table. Here is the output of
the
two sections in question.
...

I would try to modify your section 8 statement like this:
DECLARE
KEY0_ASCRD CURSOR
FOR
SELECT
KEY0,
REC
FROM
EADMPF00
WHERE
KEY0 >= :H00006
AND
KEY0 <= :H00001
ORDER BY
KEY0 ASC
FETCH FIRST 1 ROWS ONLY
OPTIMIZE FOR 1 ROWS
;

Jan M. Nelken
Changing "FETCH FIRST 1 ROWS ONLY" to "OPTIMIZE FOR 1 ROWS" fixed the
problem
but why would DB2 not use optimal method without OPTIMIZE clause?

Something changed in the optimizer with UDB 8.2.

DB2 does not know how many rows need to be fetched for KEY0 >= :H00006 AND
KEY0 <= :H00001. It previously assumed an access path that probably would
have been optimal if more that 1/2 the table rows qualified, but if a much
smaller number of rows would be returned then you need to give it some
hints.

Another way to give it some hints might have been to run statistics
(runstats) with all the options (table and all indexes) including
distribution on all columns, so that DB2 would have a better idea of what
the data actually looks like. But even then, it may be hard for DB2 to know
for sure.

I had run runstats "with distribution and detailed indexes all", and that did
not make any difference.

--
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.

[Hemant Shah]

While stranded on information super highway Jan M. Nelken wrote:

Hemant Shah wrote:

While stranded on information super highway Jan M. Nelken wrote:

Hemant Shah wrote:
I already have the information in explain table. Here is the output of the
two sections in question.
...

I would try to modify your section 8 statement like this:
DECLARE
KEY0_ASCRD CURSOR
FOR
SELECT
KEY0,
REC
FROM
EADMPF00
WHERE
KEY0 >= :H00006
AND
KEY0 <= :H00001
ORDER BY
KEY0 ASC
FETCH FIRST 1 ROWS ONLY
OPTIMIZE FOR 1 ROWS
;

Jan M. Nelken

Changing "FETCH FIRST 1 ROWS ONLY" to "OPTIMIZE FOR 1 ROWS" fixed the problem
but why would DB2 not use optimal method without OPTIMIZE clause?

Something changed in the optimizer with UDB 8.2.

"Optimize for n rows" is a suggestion to optimizer to take into consideration
cost of delivering n rows instead of full result set; "Fetch m rows only" is
request to stop delivering result set after m rows - but *not* a suggestion to
change costing criteria - so optimizer will construct access plan to deliver
full result set and stop delivery after m rows - those two clauses are *Different*!

Your first query had "Optimize for 15 rows" clause - so your second query should
have "optimize for 1 row" in addition to "fetch 1 row only" - to have comparable
access plans.

Optimizer is working as designed and intended to in this case.

Jan M. Nelken

If I specify FETCH 1 ROW ONLY, it means I am only interested in 1 row so it
should OPTIMIZE for 1 row. It seem to do it until UDB 8.2.

--
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.

[Serge Rielau]

Hemant Shah wrote:

If I specify FETCH 1 ROW ONLY, it means I am only interested in 1 row so it
should OPTIMIZE for 1 row. It seem to do it until UDB 8.2.

FETCH 1 ROW ONLY is (and alwasy has been) treated orthogonally to
OPTIMIZE FOR 1 ROW.
There is a reason (which I never understood) preventing FETCH 1 ROW ONLY
from implying OPTIMIZE FOR 1 ROW.
Cheers
Serge

--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab