Helpful folks,
Most of my previous experience with DB2 was on s390 mainframe systems
and the optimizer on this platform always seemed very predictable and
consistent. Since moving to a WinNT/UDB 7.2 environment, the choices
the optimizer makes often seem flaky. But this last example really
floored me. I was hoping someone could explain why I get worse
response time when the optimizer uses two indexes, than when it uses
one. Some context:
Orders table: 5 million+ rows
This table contains
CLIENTID - integer column identifying the the client placing the
order
SEARCHCODE - two-character column defining a flavor of order
CLIENTID has it's own dedicated index, ORDERS02, and SEARCHCODE is the
high-order column of a three-column index, ORDERS13.(And yes, there
are 13 indexes on this %#$@!! table).
The cardinality of SEARCHCODE is extremely low, with roughly 10
distinct values spread across 5 mil rows.
The cardinality of CLIENTID is relatively high (and very high in
relation to SEARCHCODE), with thousands of distinct values.
When I EXPLAIN this SQL statement:
Select Distinct DATA2
from ORDERS
where SEARCHCODE = '04' and CLIENTID = 277475
With UR
I'm told that both indexes are used to honor the query, and the
timeron resource number is very low. There are actually 7 rows that
match the criteria in the WHERE clause, and two are returned after the
DISTINCT is employed. However, clock response time is over 3 minutes,
which is unacceptable.
When I modify the query to avoid using the index on SEARCHCODE, using
a technique described by Bonnie Baker, the EXPLAIN does indeed show
only the CLIENTID index being used, and response time is under 10
seconds:
Select Distinct DATA2
from ORDERS
where SEARCHCODE = '04' || '' and CLIENTID = 277475
With UR
Why should this be? If the optimizer can determine that two indexes
would be useful, why can't it use them efficiently? Surely it has all
of the distribution statistics available to see the cardinality of the
SEARCHCODE index? And yes, runstats was run 4 days previously, and
then again just before retesting, using this command:
RUNSTATS ON TABLE DB2ADMIN.ORDERS WITH DISTRIBUTION AND DETAILED
INDEXES ALL SHRLEVEL CHANGE
I've included the output of both explains below.
Can anyone shed some light on this? Would a different index
arrangement improve this situation such that the optimizer would not
make such poor choices?
Any feedback would be appreciated.
Sean
Package Name = DB2ADMIN.DYNEXP LN
Prep Date = 2004/02/06
Prep Time = 10:59:53
Bind Timestamp = 2004-02-06-11.09.04.781000
Isolation Level = Cursor Stability
Blocking = Block Unambiguous Cursors
Query Optimization Class = 5
Partition Parallel = No
Intra-Partition Parallel = No
Function Path = "SYSIBM", "SYSFUN", "DB2ADMIN"
-------------------- SECTION ---------------------------------------
Section = 1
SQL Statement:
Select Distinct DATA2
from ORDERS
where SEARCHCODE = '04' and CLIENTID = 277475
With UR
Estimated Cost = 254
Estimated Cardinality = 3
( 5) Index ANDing
| Optimizer Estimate of Set Size: 3
| Index ANDing Bitmap Build
| | Optimizer Estimate of Set Size: 3
( 6) | | Access Table Name = DB2ADMIN.ORDERS ID = 6,4
| | | #Columns = 1
| | | Index Scan: Name = DB2ADMIN.ORDERS 02 ID = 3
| | | | Index Columns:
| | | | | 1: CLIENTID (Ascending)
| | | | #Key Columns = 1
| | | | | Start Key: Inclusive Value
| | | | | | 1: 277475
| | | | | Stop Key: Inclusive Value
| | | | | | 1: 277475
| | | | Index-Only Access
| | | | Index Prefetch: None
| | | Isolation Level: Uncommitted Read
| | | Lock Intents
| | | | Table: Intent None
| | | | Row : None
| Index ANDing Bitmap Probe
| | Optimizer Estimate of Set Size: 3
( 7) | | Access Table Name = DB2ADMIN.ORDERS ID = 6,4
| | | #Columns = 1
| | | Index Scan: Name = DB2ADMIN.ORDERS 13 ID = 14
| | | | Index Columns:
| | | | | 1: SEARCHCODE (Ascending)
| | | | | 2: STATUS (Ascending)
| | | | | 3: TNUMBER (Ascending)
| | | | #Key Columns = 1
| | | | | Start Key: Inclusive Value
| | | | | | 1: '04'
| | | | | Stop Key: Inclusive Value
| | | | | | 1: '04'
| | | | Index-Only Access
| | | | Index Prefetch: Eligible 59
| | | Isolation Level: Uncommitted Read
| | | Lock Intents
| | | | Table: Intent None
| | | | Row : None
( 4) Access Table Name = DB2ADMIN.ORDERS ID = 6,4
| #Columns = 3
| Fetch Direct
| Isolation Level: Uncommitted Read
| Lock Intents
| | Table: Intent None
| | Row : None
| Residual Predicate(s)
| | #Predicates = 2
( 3) Insert Into Sorted Temp Table ID = t1
| #Columns = 1
| #Sort Key Columns = 1
| | Key 1: DATA2 (Ascending)
| Sortheap Allocation Parameters:
| | #Rows = 4
| | Row Width = 16
| Piped
| Duplicate Elimination
( 2) Access Temp Table ID = t1
| #Columns = 1
| Relation Scan
| | Prefetch: Eligible
( 2) | Return Data to Application
| | #Columns = 1
( 1) Return Data Completion
End of section
Optimizer Plan:
RETURN
( 1)
|
TBSCAN
( 2)
|
SORT
( 3)
|
FETCH
( 4)
/--/ \
IXAND Table:
( 5) DB2ADMIN
/--/ \--\ ORDERS
IXSCAN IXSCAN
( 6) ( 7)
/ \ / \
Index: Table: Index: Table:
DB2ADMIN DB2ADMIN DB2ADMIN DB2ADMIN
ORDERS02 ORDERS ORDERS13 ORDERS
DB2 Universal Database Version 7.2, 5622-044 (c) Copyright IBM Corp.
1991, 2001
Licensed Material - Program Property of IBM
IBM DATABASE 2 SQL Explain Tool
*************** ***** PACKAGE *************** *************** *********
Package Name = DB2ADMIN.DYNEXP LN
Prep Date = 2004/02/06
Prep Time = 11:04:05
Bind Timestamp = 2004-02-06-11.13.16.859000
Isolation Level = Cursor Stability
Blocking = Block Unambiguous Cursors
Query Optimization Class = 5
Partition Parallel = No
Intra-Partition Parallel = No
Function Path = "SYSIBM", "SYSFUN", "DB2ADMIN"
-------------------- SECTION ---------------------------------------
Section = 1
SQL Statement:
Select Distinct DATA2
from ORDERS
where SEARCHCODE = '04' || '' and CLIENTID = 277475
With UR
Estimated Cost = 274
Estimated Cardinality = 6
( 7) Access Table Name = DB2ADMIN.ORDERS ID = 6,4
| #Columns = 1
| Index Scan: Name = DB2ADMIN.ORDERS 02 ID = 3
| | Index Columns:
| | | 1: CLIENTID (Ascending)
| | #Key Columns = 1
| | | Start Key: Inclusive Value
| | | | 1: 277475
| | | Stop Key: Inclusive Value
| | | | 1: 277475
| | Index-Only Access
| | Index Prefetch: None
( 7) | | | Insert Into Sorted Temp Table ID = t1
| | | | #Columns = 1
| | | | #Sort Key Columns = 1
| | | | | Key 1: (Ascending)
| | | | Sortheap Allocation Parameters:
| | | | | #Rows = 410
| | | | | Row Width = 12
| | | | Piped
| Isolation Level: Uncommitted Read
| Lock Intents
| | Table: Intent None
| | Row : None
( 6) Sorted Temp Table Completion ID = t1
( 5) List Prefetch RID Preparation
( 3) Insert Into Sorted Temp Table ID = t2
| #Columns = 1
| #Sort Key Columns = 1
| | Key 1: DATA2 (Ascending)
| Sortheap Allocation Parameters:
| | #Rows = 6
| | Row Width = 16
| Piped
| Duplicate Elimination
( 2) Access Temp Table ID = t2
| #Columns = 1
| Relation Scan
| | Prefetch: Eligible
( 2) | Return Data to Application
| | #Columns = 1
( 1) Return Data Completion
End of section
Optimizer Plan:
RETURN
( 1)
|
TBSCAN
( 2)
|
SORT
( 3)
|
RIDSCN
( 5)
|
SORT
( 6)
|
IXSCAN
( 7)
/ \
Index: Table:
DB2ADMIN DB2ADMIN
ORDERS02 ORDERS
14  5412 
"Sean C." <db*****@yahoo. com> wrote in message
news:2f******** *************** ***@posting.goo gle.com... Helpful folks,
Most of my previous experience with DB2 was on s390 mainframe systems
and the optimizer on this platform always seemed very predictable and
consistent. Since moving to a WinNT/UDB 7.2 environment, the choices
the optimizer makes often seem flaky. But this last example really
floored me. I was hoping someone could explain why I get worse
response time when the optimizer uses two indexes, than when it uses
one. Some context:
Orders table: 5 million+ rows
This table contains
CLIENTID - integer column identifying the the client placing the
order
SEARCHCODE - two-character column defining a flavor of order
CLIENTID has it's own dedicated index, ORDERS02, and SEARCHCODE is the
high-order column of a three-column index, ORDERS13.(And yes, there
are 13 indexes on this %#$@!! table).
The cardinality of SEARCHCODE is extremely low, with roughly 10
distinct values spread across 5 mil rows.
The cardinality of CLIENTID is relatively high (and very high in
relation to SEARCHCODE), with thousands of distinct values.
When I EXPLAIN this SQL statement:
Select Distinct DATA2
from ORDERS
where SEARCHCODE = '04' and CLIENTID = 277475
With UR
I'm told that both indexes are used to honor the query, and the
timeron resource number is very low. There are actually 7 rows that
match the criteria in the WHERE clause, and two are returned after the
DISTINCT is employed. However, clock response time is over 3 minutes,
which is unacceptable.
When I modify the query to avoid using the index on SEARCHCODE, using
a technique described by Bonnie Baker, the EXPLAIN does indeed show
only the CLIENTID index being used, and response time is under 10
seconds:
Select Distinct DATA2
from ORDERS
where SEARCHCODE = '04' || '' and CLIENTID = 277475
With UR
Why should this be? If the optimizer can determine that two indexes
would be useful, why can't it use them efficiently? Surely it has all
of the distribution statistics available to see the cardinality of the
SEARCHCODE index? And yes, runstats was run 4 days previously, and
then again just before retesting, using this command:
RUNSTATS ON TABLE DB2ADMIN.ORDERS WITH DISTRIBUTION AND DETAILED
INDEXES ALL SHRLEVEL CHANGE
I've included the output of both explains below.
Can anyone shed some light on this? Would a different index
arrangement improve this situation such that the optimizer would not
make such poor choices?
Any feedback would be appreciated.
Sean
When you execute the runstats utility it would be preferable to use the
following options:
RUNSTATS ON TABLE table_name
WITH DISTRIBUTION ON ALL COLUMNS
AND DETAILED INDEXES ALL
This will tell DB2 about the low cardinality of the SEARCHCODE.
For some insane reason (I think IBM is sometimes suicidal), none of the
examples in the Command Reference shows using all the above options. It
should be the default.
This will tell DB2 about the low cardinality of the SEARCHCODE.
Another thing you could try is change the query optimization level. The
default is usually set to 5, but sometimes 7 does better.
To remove the index anding, you have to lower the opt level...
You may see sort problems (sheapthres, sortheap) if it's trying index anding
on 500,000 rows/searchcode. (5M/10)
There is obviously something 'wrong' with the stats.
SEARCHCODE doesn't have a good filter factor. We know but the optimizer
doesn't seem to know.
gui explain should provide more info about the perceived cost of each
operation.
In explain no 2, there is a sort allocation of 410 rows. (for an equality
predicate)
5M/400 +- = 12500 Do you really have +- 400 records per CLIENTID?
Do you have roughly 12500 CLIENTID's? (possible from what you said)
When something weird happens, see the sysstat.* tables and try to find
what obvious things the optimizer may not see.
Mark A did the research on how to fix the sysstat data so i won't redo it.
PM
In article <uF************ *********@news2 0.bellglobal.co m>, ("PM
\(pm3iinc-nospam\)" <PM(pm3iinc-nospam)@sympati co.ca>) says... To remove the index anding, you have to lower the opt level...
You may see sort problems (sheapthres, sortheap) if it's trying index anding
on 500,000 rows/searchcode. (5M/10)
There is obviously something 'wrong' with the stats.
SEARCHCODE doesn't have a good filter factor. We know but the optimizer
doesn't seem to know.
gui explain should provide more info about the perceived cost of each
operation.
In explain no 2, there is a sort allocation of 410 rows. (for an equality
predicate)
5M/400 +- = 12500 Do you really have +- 400 records per CLIENTID?
Do you have roughly 12500 CLIENTID's? (possible from what you said)
When something weird happens, see the sysstat.* tables and try to find
what obvious things the optimizer may not see.
Mark A did the research on how to fix the sysstat data so i won't redo it.
PM
Another trick that changes the search path is to add the 'optimize
for x rows' option.
If the queries are executed from a client it might help to specify
the number of rows which fits in one requester block (DBM CFG -->
RQRIOBLK), although I'm not sure if it still makes any difference.
Most likely it depends on the network speed. db*****@yahoo.c om (Sean C.) wrote in message news:<2f******* *************** ****@posting.go ogle.com>... I've included the output of both explains below.
Can anyone shed some light on this? Would a different index
arrangement improve this situation such that the optimizer would not
make such poor choices?
db2exfmt of the problem query would give much more detail, including
cardinality estimates at each step, which might help us see better
what DB2 thinks it's doing.
DG
replacing an index on (CLIENTID) with
an index on (CLIENTID, SEARCHCODE) might help, and yes, this is an
expensive solution
P.S. Oracle's bitmap indexes might be very useful in this situation
"AK" <ak************ @yahoo.com> wrote in message
news:46******** *************** ***@posting.goo gle.com... replacing an index on (CLIENTID) with
an index on (CLIENTID, SEARCHCODE) might help, and yes, this is an
expensive solution
P.S. Oracle's bitmap indexes might be very useful in this situation
DB2 will sometimes use bitmaps (automatically) when multiple indexes are
used. In this situation, better statistics are the answer.
Very good observation ...
I don't know the exact cardinality of CLIENTID. This is one of the most
importants thing the optimizer will determine to use INDEX (ONLY) SCAN or
use INDEX ANDing for this case.
If I were you, I would
- Apply the latest FixPak for UDB Version 7.2.
- Runstats again (full collect, not updatable collect)
- Capture the access plan and compare them. (Please don't change dbm/db cfg,
optimization level ...)
- Run both queries ...
- If it is still the same problem, please CONTACT IBM Support ...
"Sean C." <db*****@yahoo. com> wrote in message
news:2f******** *************** ***@posting.goo gle.com... Helpful folks,
Most of my previous experience with DB2 was on s390 mainframe systems
and the optimizer on this platform always seemed very predictable and
consistent. Since moving to a WinNT/UDB 7.2 environment, the choices
the optimizer makes often seem flaky. But this last example really
floored me. I was hoping someone could explain why I get worse
response time when the optimizer uses two indexes, than when it uses
one. Some context:.
Orders table: 5 million+ rows
This table contains
CLIENTID - integer column identifying the the client placing the
order
SEARCHCODE - two-character column defining a flavor of order
CLIENTID has it's own dedicated index, ORDERS02, and SEARCHCODE is the
high-order column of a three-column index, ORDERS13.(And yes, there
are 13 indexes on this %#$@!! table).
The cardinality of SEARCHCODE is extremely low, with roughly 10
distinct values spread across 5 mil rows.
The cardinality of CLIENTID is relatively high (and very high in
relation to SEARCHCODE), with thousands of distinct values.
When I EXPLAIN this SQL statement:
Select Distinct DATA2
from ORDERS
where SEARCHCODE = '04' and CLIENTID = 277475
With UR
I'm told that both indexes are used to honor the query, and the
timeron resource number is very low. There are actually 7 rows that
match the criteria in the WHERE clause, and two are returned after the
DISTINCT is employed. However, clock response time is over 3 minutes,
which is unacceptable.
When I modify the query to avoid using the index on SEARCHCODE, using
a technique described by Bonnie Baker, the EXPLAIN does indeed show
only the CLIENTID index being used, and response time is under 10
seconds:
Select Distinct DATA2
from ORDERS
where SEARCHCODE = '04' || '' and CLIENTID = 277475
With UR
Why should this be? If the optimizer can determine that two indexes
would be useful, why can't it use them efficiently? Surely it has all
of the distribution statistics available to see the cardinality of the
SEARCHCODE index? And yes, runstats was run 4 days previously, and
then again just before retesting, using this command:
RUNSTATS ON TABLE DB2ADMIN.ORDERS WITH DISTRIBUTION AND DETAILED
INDEXES ALL SHRLEVEL CHANGE
I've included the output of both explains below.
Can anyone shed some light on this? Would a different index
arrangement improve this situation such that the optimizer would not
make such poor choices?
Any feedback would be appreciated.
Sean
Package Name = DB2ADMIN.DYNEXP LN
Prep Date = 2004/02/06
Prep Time = 10:59:53
Bind Timestamp = 2004-02-06-11.09.04.781000
Isolation Level = Cursor Stability
Blocking = Block Unambiguous Cursors
Query Optimization Class = 5
Partition Parallel = No
Intra-Partition Parallel = No
Function Path = "SYSIBM", "SYSFUN", "DB2ADMIN"
-------------------- SECTION ---------------------------------------
Section = 1
SQL Statement:
Select Distinct DATA2
from ORDERS
where SEARCHCODE = '04' and CLIENTID = 277475
With UR
Estimated Cost = 254
Estimated Cardinality = 3
( 5) Index ANDing
| Optimizer Estimate of Set Size: 3
| Index ANDing Bitmap Build
| | Optimizer Estimate of Set Size: 3
( 6) | | Access Table Name = DB2ADMIN.ORDERS ID = 6,4
| | | #Columns = 1
| | | Index Scan: Name = DB2ADMIN.ORDERS 02 ID = 3
| | | | Index Columns:
| | | | | 1: CLIENTID (Ascending)
| | | | #Key Columns = 1
| | | | | Start Key: Inclusive Value
| | | | | | 1: 277475
| | | | | Stop Key: Inclusive Value
| | | | | | 1: 277475
| | | | Index-Only Access
| | | | Index Prefetch: None
| | | Isolation Level: Uncommitted Read
| | | Lock Intents
| | | | Table: Intent None
| | | | Row : None
| Index ANDing Bitmap Probe
| | Optimizer Estimate of Set Size: 3
( 7) | | Access Table Name = DB2ADMIN.ORDERS ID = 6,4
| | | #Columns = 1
| | | Index Scan: Name = DB2ADMIN.ORDERS 13 ID = 14
| | | | Index Columns:
| | | | | 1: SEARCHCODE (Ascending)
| | | | | 2: STATUS (Ascending)
| | | | | 3: TNUMBER (Ascending)
| | | | #Key Columns = 1
| | | | | Start Key: Inclusive Value
| | | | | | 1: '04'
| | | | | Stop Key: Inclusive Value
| | | | | | 1: '04'
| | | | Index-Only Access
| | | | Index Prefetch: Eligible 59
| | | Isolation Level: Uncommitted Read
| | | Lock Intents
| | | | Table: Intent None
| | | | Row : None
( 4) Access Table Name = DB2ADMIN.ORDERS ID = 6,4
| #Columns = 3
| Fetch Direct
| Isolation Level: Uncommitted Read
| Lock Intents
| | Table: Intent None
| | Row : None
| Residual Predicate(s)
| | #Predicates = 2
( 3) Insert Into Sorted Temp Table ID = t1
| #Columns = 1
| #Sort Key Columns = 1
| | Key 1: DATA2 (Ascending)
| Sortheap Allocation Parameters:
| | #Rows = 4
| | Row Width = 16
| Piped
| Duplicate Elimination
( 2) Access Temp Table ID = t1
| #Columns = 1
| Relation Scan
| | Prefetch: Eligible
( 2) | Return Data to Application
| | #Columns = 1
( 1) Return Data Completion
End of section
Optimizer Plan:
RETURN
( 1)
|
TBSCAN
( 2)
|
SORT
( 3)
|
FETCH
( 4)
/--/ \
IXAND Table:
( 5) DB2ADMIN
/--/ \--\ ORDERS
IXSCAN IXSCAN
( 6) ( 7)
/ \ / \
Index: Table: Index: Table:
DB2ADMIN DB2ADMIN DB2ADMIN DB2ADMIN
ORDERS02 ORDERS ORDERS13 ORDERS
DB2 Universal Database Version 7.2, 5622-044 (c) Copyright IBM Corp.
1991, 2001
Licensed Material - Program Property of IBM
IBM DATABASE 2 SQL Explain Tool
*************** ***** PACKAGE *************** *************** *********
Package Name = DB2ADMIN.DYNEXP LN
Prep Date = 2004/02/06
Prep Time = 11:04:05
Bind Timestamp = 2004-02-06-11.13.16.859000
Isolation Level = Cursor Stability
Blocking = Block Unambiguous Cursors
Query Optimization Class = 5
Partition Parallel = No
Intra-Partition Parallel = No
Function Path = "SYSIBM", "SYSFUN", "DB2ADMIN"
-------------------- SECTION ---------------------------------------
Section = 1
SQL Statement:
Select Distinct DATA2
from ORDERS
where SEARCHCODE = '04' || '' and CLIENTID = 277475
With UR
Estimated Cost = 274
Estimated Cardinality = 6
( 7) Access Table Name = DB2ADMIN.ORDERS ID = 6,4
| #Columns = 1
| Index Scan: Name = DB2ADMIN.ORDERS 02 ID = 3
| | Index Columns:
| | | 1: CLIENTID (Ascending)
| | #Key Columns = 1
| | | Start Key: Inclusive Value
| | | | 1: 277475
| | | Stop Key: Inclusive Value
| | | | 1: 277475
| | Index-Only Access
| | Index Prefetch: None
( 7) | | | Insert Into Sorted Temp Table ID = t1
| | | | #Columns = 1
| | | | #Sort Key Columns = 1
| | | | | Key 1: (Ascending)
| | | | Sortheap Allocation Parameters:
| | | | | #Rows = 410
| | | | | Row Width = 12
| | | | Piped
| Isolation Level: Uncommitted Read
| Lock Intents
| | Table: Intent None
| | Row : None
( 6) Sorted Temp Table Completion ID = t1
( 5) List Prefetch RID Preparation
( 3) Insert Into Sorted Temp Table ID = t2
| #Columns = 1
| #Sort Key Columns = 1
| | Key 1: DATA2 (Ascending)
| Sortheap Allocation Parameters:
| | #Rows = 6
| | Row Width = 16
| Piped
| Duplicate Elimination
( 2) Access Temp Table ID = t2
| #Columns = 1
| Relation Scan
| | Prefetch: Eligible
( 2) | Return Data to Application
| | #Columns = 1
( 1) Return Data Completion
End of section
Optimizer Plan:
RETURN
( 1)
|
TBSCAN
( 2)
|
SORT
( 3)
|
RIDSCN
( 5)
|
SORT
( 6)
|
IXSCAN
( 7)
/ \
Index: Table:
DB2ADMIN DB2ADMIN
ORDERS02 ORDERS
Just think of another point:
Since you said - The cardinality of CLIENTID is relatively high (and very high in
relation to SEARCHCODE), with thousands of distinct values.
The card(CLIENTID) is RELATIVELY HIGH ... and another case is that it is
possible
"count(*) where CLIENTID = 277475" far less than "COUNT(*) where clientid =
#####"
It is impossible for db2 optimizer that it will choose use one index (only)
scan for searching clientid=277475 and choose index anding for searching
clientid=###### -:(
The same thing for DB2 optimizer on OS/390.
"Fan Ruo Xin" <fa*****@sbcglo bal.net> wrote in message
news:Ui******** ********@newssv r26.news.prodig y.com... Very good observation ...
I don't know the exact cardinality of CLIENTID. This is one of the most
importants thing the optimizer will determine to use INDEX (ONLY) SCAN or
use INDEX ANDing for this case.
If I were you, I would
- Apply the latest FixPak for UDB Version 7.2.
- Runstats again (full collect, not updatable collect)
- Capture the access plan and compare them. (Please don't change dbm/db
cfg, optimization level ...)
- Run both queries ...
- If it is still the same problem, please CONTACT IBM Support ...
"Sean C." <db*****@yahoo. com> wrote in message
news:2f******** *************** ***@posting.goo gle.com... Helpful folks,
Most of my previous experience with DB2 was on s390 mainframe systems
and the optimizer on this platform always seemed very predictable and
consistent. Since moving to a WinNT/UDB 7.2 environment, the choices
the optimizer makes often seem flaky. But this last example really
floored me. I was hoping someone could explain why I get worse
response time when the optimizer uses two indexes, than when it uses
one. Some context:.
Orders table: 5 million+ rows
This table contains
CLIENTID - integer column identifying the the client placing the
order
SEARCHCODE - two-character column defining a flavor of order
CLIENTID has it's own dedicated index, ORDERS02, and SEARCHCODE is the
high-order column of a three-column index, ORDERS13.(And yes, there
are 13 indexes on this %#$@!! table).
The cardinality of SEARCHCODE is extremely low, with roughly 10
distinct values spread across 5 mil rows.
The cardinality of CLIENTID is relatively high (and very high in
relation to SEARCHCODE), with thousands of distinct values.
When I EXPLAIN this SQL statement:
Select Distinct DATA2
from ORDERS
where SEARCHCODE = '04' and CLIENTID = 277475
With UR
I'm told that both indexes are used to honor the query, and the
timeron resource number is very low. There are actually 7 rows that
match the criteria in the WHERE clause, and two are returned after the
DISTINCT is employed. However, clock response time is over 3 minutes,
which is unacceptable.
When I modify the query to avoid using the index on SEARCHCODE, using
a technique described by Bonnie Baker, the EXPLAIN does indeed show
only the CLIENTID index being used, and response time is under 10
seconds:
Select Distinct DATA2
from ORDERS
where SEARCHCODE = '04' || '' and CLIENTID = 277475
With UR
Why should this be? If the optimizer can determine that two indexes
would be useful, why can't it use them efficiently? Surely it has all
of the distribution statistics available to see the cardinality of the
SEARCHCODE index? And yes, runstats was run 4 days previously, and
then again just before retesting, using this command:
RUNSTATS ON TABLE DB2ADMIN.ORDERS WITH DISTRIBUTION AND DETAILED
INDEXES ALL SHRLEVEL CHANGE
I've included the output of both explains below.
Can anyone shed some light on this? Would a different index
arrangement improve this situation such that the optimizer would not
make such poor choices?
Any feedback would be appreciated.
Sean
Package Name = DB2ADMIN.DYNEXP LN
Prep Date = 2004/02/06
Prep Time = 10:59:53
Bind Timestamp = 2004-02-06-11.09.04.781000
Isolation Level = Cursor Stability
Blocking = Block Unambiguous Cursors
Query Optimization Class = 5
Partition Parallel = No
Intra-Partition Parallel = No
Function Path = "SYSIBM", "SYSFUN", "DB2ADMIN"
-------------------- SECTION ---------------------------------------
Section = 1
SQL Statement:
Select Distinct DATA2
from ORDERS
where SEARCHCODE = '04' and CLIENTID = 277475
With UR
Estimated Cost = 254
Estimated Cardinality = 3
( 5) Index ANDing
| Optimizer Estimate of Set Size: 3
| Index ANDing Bitmap Build
| | Optimizer Estimate of Set Size: 3
( 6) | | Access Table Name = DB2ADMIN.ORDERS ID = 6,4
| | | #Columns = 1
| | | Index Scan: Name = DB2ADMIN.ORDERS 02 ID = 3
| | | | Index Columns:
| | | | | 1: CLIENTID (Ascending)
| | | | #Key Columns = 1
| | | | | Start Key: Inclusive Value
| | | | | | 1: 277475
| | | | | Stop Key: Inclusive Value
| | | | | | 1: 277475
| | | | Index-Only Access
| | | | Index Prefetch: None
| | | Isolation Level: Uncommitted Read
| | | Lock Intents
| | | | Table: Intent None
| | | | Row : None
| Index ANDing Bitmap Probe
| | Optimizer Estimate of Set Size: 3
( 7) | | Access Table Name = DB2ADMIN.ORDERS ID = 6,4
| | | #Columns = 1
| | | Index Scan: Name = DB2ADMIN.ORDERS 13 ID = 14
| | | | Index Columns:
| | | | | 1: SEARCHCODE (Ascending)
| | | | | 2: STATUS (Ascending)
| | | | | 3: TNUMBER (Ascending)
| | | | #Key Columns = 1
| | | | | Start Key: Inclusive Value
| | | | | | 1: '04'
| | | | | Stop Key: Inclusive Value
| | | | | | 1: '04'
| | | | Index-Only Access
| | | | Index Prefetch: Eligible 59
| | | Isolation Level: Uncommitted Read
| | | Lock Intents
| | | | Table: Intent None
| | | | Row : None
( 4) Access Table Name = DB2ADMIN.ORDERS ID = 6,4
| #Columns = 3
| Fetch Direct
| Isolation Level: Uncommitted Read
| Lock Intents
| | Table: Intent None
| | Row : None
| Residual Predicate(s)
| | #Predicates = 2
( 3) Insert Into Sorted Temp Table ID = t1
| #Columns = 1
| #Sort Key Columns = 1
| | Key 1: DATA2 (Ascending)
| Sortheap Allocation Parameters:
| | #Rows = 4
| | Row Width = 16
| Piped
| Duplicate Elimination
( 2) Access Temp Table ID = t1
| #Columns = 1
| Relation Scan
| | Prefetch: Eligible
( 2) | Return Data to Application
| | #Columns = 1
( 1) Return Data Completion
End of section
Optimizer Plan:
RETURN
( 1)
|
TBSCAN
( 2)
|
SORT
( 3)
|
FETCH
( 4)
/--/ \
IXAND Table:
( 5) DB2ADMIN
/--/ \--\ ORDERS
IXSCAN IXSCAN
( 6) ( 7)
/ \ / \
Index: Table: Index: Table:
DB2ADMIN DB2ADMIN DB2ADMIN DB2ADMIN
ORDERS02 ORDERS ORDERS13 ORDERS
DB2 Universal Database Version 7.2, 5622-044 (c) Copyright IBM Corp.
1991, 2001
Licensed Material - Program Property of IBM
IBM DATABASE 2 SQL Explain Tool
*************** ***** PACKAGE *************** *************** *********
Package Name = DB2ADMIN.DYNEXP LN
Prep Date = 2004/02/06
Prep Time = 11:04:05
Bind Timestamp = 2004-02-06-11.13.16.859000
Isolation Level = Cursor Stability
Blocking = Block Unambiguous Cursors
Query Optimization Class = 5
Partition Parallel = No
Intra-Partition Parallel = No
Function Path = "SYSIBM", "SYSFUN", "DB2ADMIN"
-------------------- SECTION ---------------------------------------
Section = 1
SQL Statement:
Select Distinct DATA2
from ORDERS
where SEARCHCODE = '04' || '' and CLIENTID = 277475
With UR
Estimated Cost = 274
Estimated Cardinality = 6
( 7) Access Table Name = DB2ADMIN.ORDERS ID = 6,4
| #Columns = 1
| Index Scan: Name = DB2ADMIN.ORDERS 02 ID = 3
| | Index Columns:
| | | 1: CLIENTID (Ascending)
| | #Key Columns = 1
| | | Start Key: Inclusive Value
| | | | 1: 277475
| | | Stop Key: Inclusive Value
| | | | 1: 277475
| | Index-Only Access
| | Index Prefetch: None
( 7) | | | Insert Into Sorted Temp Table ID = t1
| | | | #Columns = 1
| | | | #Sort Key Columns = 1
| | | | | Key 1: (Ascending)
| | | | Sortheap Allocation Parameters:
| | | | | #Rows = 410
| | | | | Row Width = 12
| | | | Piped
| Isolation Level: Uncommitted Read
| Lock Intents
| | Table: Intent None
| | Row : None
( 6) Sorted Temp Table Completion ID = t1
( 5) List Prefetch RID Preparation
( 3) Insert Into Sorted Temp Table ID = t2
| #Columns = 1
| #Sort Key Columns = 1
| | Key 1: DATA2 (Ascending)
| Sortheap Allocation Parameters:
| | #Rows = 6
| | Row Width = 16
| Piped
| Duplicate Elimination
( 2) Access Temp Table ID = t2
| #Columns = 1
| Relation Scan
| | Prefetch: Eligible
( 2) | Return Data to Application
| | #Columns = 1
( 1) Return Data Completion
End of section
Optimizer Plan:
RETURN
( 1)
|
TBSCAN
( 2)
|
SORT
( 3)
|
RIDSCN
( 5)
|
SORT
( 6)
|
IXSCAN
( 7)
/ \
Index: Table:
DB2ADMIN DB2ADMIN
ORDERS02 ORDERS
> > P.S. Oracle's bitmap indexes might be very useful in this situation
DB2 will sometimes use bitmaps (automatically) when multiple indexes are
used. In this situation, better statistics are the answer.
there seems to be a big performance difference between using a
pre-existing Oracle's bitmap index and DB2 creating a similar
structure on the fly nad discarding it after the query has completed This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics | |
by: bill |
last post by:
I am trying to convince a client that dotNet is preferable to an Access
project (ADP/ADE).
This client currently has a large, pure Access MDB solution with 30+ users,
which needs to be upgraded.
I believe a dotNet solution is better, but I'm trying to be as convincing as
possible -- and maybe I'm wrong!
I would appreciate any input or references which could help me.
|
by: Gaurav |
last post by:
http://www.sys-con.com/story/print.cfm?storyid=45250
Any comments?
Thanks
Gaurav
|
by: WalterR |
last post by:
This is my first time here, so there may be earlier relevant threads of
which I am unaware. Though my experience with DB2 is not extensive,
such as it is was under OS/390 or equ. My main experience is IMS DB,
which leads to my question.
In IMS, there is an HDAM access method which can find a record without
using an index as such. At initial database load, it first formats the
entire space allocation into blocks of the given size. ...
|
by: James Fortune |
last post by:
I have a lot of respect for David Fenton and Allen Browne, but I don't
understand why people who know how to write code to completely replace
a front end do not write something that will automate the code that
implements managing unbound controls on forms given the superior
performance of unbound controls in a client/server environment. I can
easily understand a newbie using bound controls or someone with a
tight deadline. I guess I need...
|
by: bettervssremoting |
last post by:
To view the full article, please visit
http://www.BetterVssRemoting.com
Better VSS Remote Access Tool including SourceOffSite, SourceAnyWhere
and VSS Remoting
This article makes a detailed comparison among SourceAnyWhere,
SourceOffSite, VSS Remoting and possible others.
| | |
by: Sean Kirkpatrick |
last post by:
I have a very ugly problem and I need some sincere guidance.
My legacy VB6 application depends heavily on DAO, especially as it
relates to custom properties on database objects. These custom
properties are, as I understand it, not avabilable with SQL Server,
which we need to migrate to in the not too distant future.
My boss, the owner of the company, requires that we provide for a
transition plan that minimizes (he really wants none)...
|
by: bernhard.willems |
last post by:
We are using DB2 UDB version 8.1 fp7 & fp9 on AIX 5.2.
On our databases we do massive inserts and deletes (millions of rows).
The performance is dramatically reduced because of pseudo deleted keys.
In a 10 minutes it goes from 18000 transaction per minute to 9000 and
lower.
When an index reorg with CLEANUP ONLY PAGES is executed, the number of
transactions climbs again. But a few minutes later it drops again to
9000.
We have to run the...
|
by: shumaker |
last post by:
I'm wondering if adding an autonumber primary key will improve the
performance of a multiuser access database on a network share. I have
a website that lists many tips for improving performance of access, but
doesn't mention primary keys.
However, it seems logical to think that having no primary key means
that when a user updates a record, the database has to do comparisons
on multiple fields to identify the specific record being...
|
by: dev |
last post by:
Several years ago I developed an Access 97 runtime application. I
have rewritten this application using Visual Basic 6 with an Access
2003 database. When I run the VB application with the database on the
same PC, the performance is good. When I move the database to a
different PC on my two PC network, the performance is slow. This is
not the case with the original Access 97 application; it performs fast
regardless of where the...
|
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 usage, and What is the difference between ONU and Router. Let’s take a closer look !
Part I. Meaning of...
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed.
This is as boiled down as I can make it.
Here is my compilation command:
g++-12 -std=c++20 -Wnarrowing bit_field.cpp
Here is the code in...
| | |
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth.
The Art of Business Website Design
Your website is...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
|
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 launch it, all on its own....
Now, this would greatly impact the work of software developers. The idea...
|
by: conductexam |
last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one.
At the time of converting from word file to html my equations which are in the word document file was convert into image.
Globals.ThisAddIn.Application.ActiveDocument.Select();...
|
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 the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
|
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
| | |
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 effective websites that not only look great but also perform exceptionally well. In this comprehensive...
| |
