473,567 Members | 3,066 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

UDB 8.2 poerformance problem

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.
Nov 12 '05 #1
15 3604
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.eadmpf0 0 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
Nov 12 '05 #2
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.eadmpf0 0 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_REQUEST ER: 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.
Nov 12 '05 #3
"Hemant Shah" <sh**@typhoon.x net.com> wrote in message
news:da******** **@new7.xnet.co m...
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.employe e ON KEY COLUMNS AND INDEXES ALL

Issue FLUSH PACKAGE CACHE command, or if you have static SQL programs then
do a rebind.
Nov 12 '05 #4
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
Nov 12 '05 #5
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.x net.com> wrote in message
news:da******** **@new7.xnet.co m...
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.

Nov 12 '05 #6
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.x net.com> wrote in message
news:da******** **@new7.xnet.co m...
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.
Nov 12 '05 #7
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.
Nov 12 '05 #8
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
Nov 12 '05 #9
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
Nov 12 '05 #10

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

0
3054
by: Bruce Davis | last post by:
I'm having a problem on windows (both 2000 and XP) with a multi-threaded tkinter gui application. The problem appears to be a deadlock condition when a child thread pops up a Pmw dialog window in the context of a main window. The problem does not occur on HPUX or Linux. The following simple example code illustrates the problem and the...
11
3739
by: Kostatus | last post by:
I have a virtual function in a base class, which is then overwritten by a function of the same name in a publically derived class. When I call the function using a pointer to the derived class (ClassB* b; b->func(); ) the base-class function is called instead of the new function in the derived class. All other similar functions (virtual in...
0
2013
by: Refky Wahib | last post by:
Hi I need Technical Support I finished a Great project using .Net and SQL Server and .Net Mobile Control My Business case is to implement this Program to accept about 1 Million concurrent users So I designed the project as master Node that has all administration
9
2230
by: Sudesh Sawant | last post by:
Hello, We have an application which communicates using remoting. There is a server which is a Windows Service. The server exposes an object which is a singleton. The client is a Web Application which makes calls to the service. We are using tcp channel which is using binaryformatter by default. The problem is that after a certain number of...
117
7113
by: Peter Olcott | last post by:
www.halting-problem.com
28
5183
by: Jon Davis | last post by:
If I have a class with a virtual method, and a child class that overrides the virtual method, and then I create an instance of the child class AS A base class... BaseClass bc = new ChildClass(); .... and then call the virtual method, why is it that the base class's method is called instead of the overridden method? How do I fix this if I...
6
3790
by: Ammar | last post by:
Dear All, I'm facing a small problem. I have a portal web site, that contains articles, for each article, the end user can send a comment about the article. The problem is: I the comment length is more that 1249 bytes, then the progress bar of the browser will move too slow and then displaying that the page not found!!!! If the message is...
16
4882
by: Dany | last post by:
Our web service was working fine until we installed .net Framework 1.1 service pack 1. Uninstalling SP1 is not an option because our largest customer says service packs marked as "critical" by Microsoft must be installed on their servers. Now german Umlaute (ä, ü, ö) and quotes are returned incorrectly in SOAP fault responses. This can be...
2
4536
by: Mike Collins | last post by:
I cannot get the correct drop down list value from a drop down I have on my web form. I get the initial value that was loaded in the list. It was asked by someone else what the autopostback was set to...it is set to false. Can someone show me what I am doing wrong and tell me the correct way? Thank you. In the page load event, I am...
0
7686
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main...
0
7588
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language...
1
7650
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For...
0
6272
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then...
1
5488
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes...
0
3648
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in...
0
3630
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
2093
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
0
930
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.