Wow, thanks for all the feedback, guys. The latest fixpack applied on
the server is FP5, which technically makes it V7.3, I suppose.
Unfortunately, this version does not allow the 'ON ALL COLUMNS' clause
on the RUNSTATS command. From the doc for V7 found here:
http://www-306.ibm.com/cgi-bin/db2ww....htm#HDRCMD098
I have to assume the runstats command I listed above is the most
comprehensive data gathering flavor of the command. Is this correct?
Many of the responses implied that not all of the necessary statistics
had been gathered, but I am unaware of any means beyond fudging the
stats tables directly, that could give me more accurate statistics.
Below I've tried to supply some of the info mentioned in replies so
far. I was surprised to see so few entries for CLIENTID in the sysstat
table (see below). Could this be the problem?
Here is a more detailed description of the data distribution:
Number of distinct CLIENTID's: 9223
Number ORDERs with a CLIENTID of '277475': 11730
Number of distinct SEARCHCODEs: 73
I was obviously off the mark with my estimate of this cardinality,
however tha majority of the distribution is across a small subset of
the values. The following data is from a query directly against the
table, with count(*) and group by.
Breakdown of SEARCHCODE distribution:
SEARCHCODE 2
---------- -----------
08 2039829
11 649924
12 608037
03 433443
41 307586
60 215766
05 111580
10 89033
51 64315
01 59991
09 54754
88 40005
04 38697
78 37235
81 34786
50 28932
83 26164
68 20312
74 20242
06 17615
80 15499
37 12063
48 10271
71 10217
47 9288
62 9093
33 9048
02 8133
34 8042
28 7392
13 6687
63 4969
27 4778
66 4201
07 2710
35 2547
61 2044
39 1605
52 1558
32 1150
44 1068
15 793
92 779
36 667
53 606
38 605
67 534
86 439
77 281
64 259
16 183
49 180
14 174
73 147
65 119
21 99
23 98
76 85
89 70
25 63
17 49
99 42
20 32
69 30
19 27
24 13
87 13
56 10
18 9
22 7
84 4
54 3
91 3
Below are the two output reports from the DB2EXFMT utility for the two
queries in question, followed by a query against the SYSSTAT table for
the releveant columns:
Here is the output of the db2exfmt utility for the first query:
DB2 Universal Database Version 7.2, 5622-044 (c) Copyright IBM Corp.
1991, 2001
Licensed Material - Program Property of IBM
IBM DATABASE 2 Explain Table Format Tool
******************** EXPLAIN INSTANCE ********************
DB2_VERSION: 07.02.3
SOURCE_NAME: SQLLF000
SOURCE_SCHEMA: NULLID
EXPLAIN_TIME: 2004-02-09-09.34.00.750001
EXPLAIN_REQUESTER: DB2ADMIN
Database Context:
----------------
Parallelism: None
CPU Speed: 6.179837e-007
Comm Speed: 0
Buffer Pool size: 151136
Sort Heap size: 1024
Database Heap size: 4096
Lock List size: 1024
Maximum Lock List: 20
Average Applications: 50
Locks Available: 23142
Package Context:
---------------
SQL Type: Dynamic
Optimization Level: 5
Blocking: Block All Cursors
Isolation Level: Cursor Stability
---------------- STATEMENT 1 SECTION 65 ----------------
QUERYNO: 100
QUERYTAG:
Statement Type: Select
Updatable: No
Deletable: No
Query Degree: 1
Original Statement:
------------------
Select Distinct DATA2
from ORDERS
where SEARCHCODE = '04' and CLIENTID = 277475
With UR
Optimized Statement:
-------------------
SELECT DISTINCT Q1.DATA2 AS "DATA2"
FROM DB2ADMIN.ORDERS AS Q1
WHERE (Q1.CLIENTID = 277475) AND (Q1.SEARCHCODE = '04')
Access Plan:
-----------
Total Cost: 265.69
Query Degree: 1
Rows
RETURN
( 1)
Cost
I/O
|
3.16741
TBSCAN
( 2)
265.69
68.747
|
3.16741
SORT
( 3)
265.688
68.747
|
3.16741
FETCH
( 4)
265.686
68.747
/----+---\
3.16741 5.0301e+006
IXAND TABLE: DB2ADMIN
( 5) ORDERS
218.723
64.0549
/------+-----\
412.105 38661
IXSCAN IXSCAN
( 6) ( 7)
20.4841 197.392
2 62.0549
| |
5.0301e+006 5.0301e+006
INDEX: DB2ADMIN INDEX: DB2ADMIN
ORDERS02 ORDERS13
1) RETURN: (Return Result)
Cumulative Total Cost: 265.69
Cumulative CPU Cost: 6.92502e+007
Cumulative I/O Cost: 68.747
Cumulative Re-Total Cost: 42.4248
Cumulative Re-CPU Cost: 6.86503e+007
Cumulative Re-I/O Cost: 0
Cumulative First Row Cost: 265.689
Estimated Bufferpool Buffers: 0
Arguments:
---------
BLDLEVEL: (Build level)
DB2 v7.1.0.55 : n011211
ENVVAR : (Environment Variable)
DB2_HASH_JOIN = ON
Input Streams:
-------------
9) From Operator #2
Estimated number of rows: 3.16741
Number of columns: 1
Subquery predicate ID: Not Applicable
Column Names:
------------
+DATA2(A)
2) TBSCAN: (Table Scan)
Cumulative Total Cost: 265.69
Cumulative CPU Cost: 6.92502e+007
Cumulative I/O Cost: 68.747
Cumulative Re-Total Cost: 42.4248
Cumulative Re-CPU Cost: 6.86503e+007
Cumulative Re-I/O Cost: 0
Cumulative First Row Cost: 265.689
Estimated Bufferpool Buffers: 0
Arguments:
---------
MAXPAGES: (Maximum pages for prefetch)
ALL
PREFETCH: (Type of Prefetch)
NONE
SCANDIR : (Scan Direction)
FORWARD
Input Streams:
-------------
8) From Operator #3
Estimated number of rows: 3.16741
Number of columns: 1
Subquery predicate ID: Not Applicable
Column Names:
------------
+DATA2(A)
Output Streams:
--------------
9) To Operator #1
Estimated number of rows: 3.16741
Number of columns: 1
Subquery predicate ID: Not Applicable
Column Names:
------------
+DATA2(A)
3) SORT : (Sort)
Cumulative Total Cost: 265.688
Cumulative CPU Cost: 6.92479e+007
Cumulative I/O Cost: 68.747
Cumulative Re-Total Cost: 42.4233
Cumulative Re-CPU Cost: 6.8648e+007
Cumulative Re-I/O Cost: 0
Cumulative First Row Cost: 265.688
Estimated Bufferpool Buffers: 305.376
Arguments:
---------
DUPLWARN: (Duplicates Warning flag)
TRUE
NUMROWS : (Estimated number of rows)
4
ROWWIDTH: (Estimated width of rows)
16
SORTKEY : (Sort Key column)
1: DATA2(A)
TEMPSIZE: (Temporary Table Page Size)
4096
UNIQUE : (Uniqueness required flag)
TRUE
Input Streams:
-------------
7) From Operator #4
Estimated number of rows: 3.16741
Number of columns: 4
Subquery predicate ID: Not Applicable
Column Names:
------------
+$RID$+DATA2+CLIENTID+SEARCHCODE
Output Streams:
--------------
8) To Operator #2
Estimated number of rows: 3.16741
Number of columns: 1
Subquery predicate ID: Not Applicable
Column Names:
------------
+DATA2(A)
4) FETCH : (Fetch)
Cumulative Total Cost: 265.686
Cumulative CPU Cost: 6.92433e+007
Cumulative I/O Cost: 68.747
Cumulative Re-Total Cost: 42.4233
Cumulative Re-CPU Cost: 6.8648e+007
Cumulative Re-I/O Cost: 0
Cumulative First Row Cost: 106.805
Estimated Bufferpool Buffers: 305.376
Arguments:
---------
MAXPAGES: (Maximum pages for prefetch)
4
MAXPAGES: (Maximum pages for prefetch)
4
PREFETCH: (Type of Prefetch)
NONE
ROWLOCK : (Row Lock intent)
NONE
TABLOCK : (Table Lock intent)
INTENT NONE
Predicates:
----------
2) Residual Predicate
Relational Operator: Equal (=)
Subquery Input Required: Yes
Filter Factor: 8.19278e-005
Predicate Text:
--------------
(Q1.CLIENTID = 277475)
3) Residual Predicate
Relational Operator: Equal (=)
Subquery Input Required: Yes
Filter Factor: 0.00768594
Predicate Text:
--------------
(Q1.SEARCHCODE = '04')
Input Streams:
-------------
5) From Operator #5
Estimated number of rows: 3.16741
Number of columns: 1
Subquery predicate ID: Not Applicable
Column Names:
------------
+$RID$
6) From Object DB2ADMIN.ORDERS
Estimated number of rows: 5.0301e+006
Number of columns: 3
Subquery predicate ID: Not Applicable
Column Names:
------------
+DATA2+CLIENTID+SEARCHCODE
Output Streams:
--------------
7) To Operator #3
Estimated number of rows: 3.16741
Number of columns: 4
Subquery predicate ID: Not Applicable
Column Names:
------------
+$RID$+DATA2+CLIENTID+SEARCHCODE
5) IXAND : (Index ANDing)
Cumulative Total Cost: 218.723
Cumulative CPU Cost: 6.91745e+007
Cumulative I/O Cost: 64.0549
Cumulative Re-Total Cost: 42.4185
Cumulative Re-CPU Cost: 6.86402e+007
Cumulative Re-I/O Cost: 0
Cumulative First Row Cost: 96.7953
Estimated Bufferpool Buffers: 143.435
Input Streams:
-------------
2) From Operator #6
Estimated number of rows: 412.105
Number of columns: 2
Subquery predicate ID: Not Applicable
Column Names:
------------
+$RID$+CLIENTID
4) From Operator #7
Estimated number of rows: 38661
Number of columns: 2
Subquery predicate ID: Not Applicable
Column Names:
------------
+$RID$+SEARCHCODE
Output Streams:
--------------
5) To Operator #4
Estimated number of rows: 3.16741
Number of columns: 1
Subquery predicate ID: Not Applicable
Column Names:
------------
+$RID$
6) IXSCAN: (Index Scan)
Cumulative Total Cost: 20.4841
Cumulative CPU Cost: 783315
Cumulative I/O Cost: 2
Cumulative Re-Total Cost: 0.446094
Cumulative Re-CPU Cost: 721854
Cumulative Re-I/O Cost: 0
Cumulative First Row Cost: 20.0458
Estimated Bufferpool Buffers: 3
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) Start Key Predicate
Relational Operator: Equal (=)
Subquery Input Required: Yes
Filter Factor: 8.19278e-005
Predicate Text:
--------------
(Q1.CLIENTID = 277475)
2) Stop Key Predicate
Relational Operator: Equal (=)
Subquery Input Required: Yes
Filter Factor: 8.19278e-005
Predicate Text:
--------------
(Q1.CLIENTID = 277475)
Input Streams:
-------------
1) From Object DB2ADMIN.ORDERS02
Estimated number of rows: 5.0301e+006
Number of columns: 2
Subquery predicate ID: Not Applicable
Column Names:
------------
+$RID$+CLIENTID
Output Streams:
--------------
2) To Operator #5
Estimated number of rows: 412.105
Number of columns: 2
Subquery predicate ID: Not Applicable
Column Names:
------------
+$RID$+CLIENTID
7) IXSCAN: (Index Scan)
Cumulative Total Cost: 197.392
Cumulative CPU Cost: 6.70214e+007
Cumulative I/O Cost: 62.0549
Cumulative Re-Total Cost: 41.1259
Cumulative Re-CPU Cost: 6.65486e+007
Cumulative Re-I/O Cost: 0
Cumulative First Row Cost: 20.046
Estimated Bufferpool Buffers: 140.435
Arguments:
---------
MAXPAGES: (Maximum pages for prefetch)
60
PREFETCH: (Type of Prefetch)
SEQUENTIAL
ROWLOCK : (Row Lock intent)
NONE
SCANDIR : (Scan Direction)
FORWARD
TABLOCK : (Table Lock intent)
INTENT NONE
Predicates:
----------
3) Start Key Predicate
Relational Operator: Equal (=)
Subquery Input Required: Yes
Filter Factor: 0.00768594
Predicate Text:
--------------
(Q1.SEARCHCODE = '04')
3) Stop Key Predicate
Relational Operator: Equal (=)
Subquery Input Required: Yes
Filter Factor: 0.00768594
Predicate Text:
--------------
(Q1.SEARCHCODE = '04')
Input Streams:
-------------
3) From Object DB2ADMIN.ORDERS13
Estimated number of rows: 5.0301e+006
Number of columns: 2
Subquery predicate ID: Not Applicable
Column Names:
------------
+$RID$+SEARCHCODE
Output Streams:
--------------
4) To Operator #5
Estimated number of rows: 38661
Number of columns: 2
Subquery predicate ID: Not Applicable
Column Names:
------------
+$RID$+SEARCHCODE
Objects Used in Access Plan:
---------------------------
Schema: DB2ADMIN
Name: ORDERS02
Type: Index
Time of creation: 2003-11-30-12.17.19.593001
Last statistics update: 2004-02-06-12.58.42.468000
Number of columns: 1
Number of rows: 5030097
Width of rows: -1
Number of buffer pool pages: 1134599
Distinct row values: No
Tablespace name: ORDERSI
Tablespace overhead: 9.700000
Tablespace transfer rate: 0.300000
Prefetch page count: 8
Container extent page count: 64
Index clustering statistic: 0.738917
Index leaf pages: 7149
Index tree levels: 3
Index full key cardinality: 9217
Index first key cardinality: 9217
Index first 2 keys cardinality: -1
Index first 3 keys cardinality: -1
Index first 4 keys cardinality: -1
Index sequential pages: 7073
Index page density: 99
Base Table Schema: DB2ADMIN
Base Table Name: ORDERS
Columns in index:
CLIENTID
Schema: DB2ADMIN
Name: ORDERS13
Type: Index
Time of creation: 2003-11-30-12.36.55.343001
Last statistics update: 2004-02-06-12.58.42.468000
Number of columns: 3
Number of rows: 5030097
Width of rows: -1
Number of buffer pool pages: 1134599
Distinct row values: No
Tablespace name: ORDERSI
Tablespace overhead: 9.700000
Tablespace transfer rate: 0.300000
Prefetch page count: 8
Container extent page count: 64
Index clustering statistic: 0.342897
Index leaf pages: 7871
Index tree levels: 3
Index full key cardinality: 184626
Index first key cardinality: 73
Index first 2 keys cardinality: 408
Index first 3 keys cardinality: 184626
Index first 4 keys cardinality: -1
Index sequential pages: 7720
Index page density: 99
Base Table Schema: DB2ADMIN
Base Table Name: ORDERS
Columns in index:
SEARCHCODE
STATUS
TNUMBER
Schema: DB2ADMIN
Name: ORDERS
Type: Table
Time of creation: 2002-09-22-10.50.24.437001
Last statistics update: 2004-02-06-12.58.42.468000
Number of columns: 89
Number of rows: 5030097
Width of rows: 30
Number of buffer pool pages: 1134599
Distinct row values: No
Tablespace name: ORDERST
Tablespace overhead: 9.700000
Tablespace transfer rate: 0.300000
Prefetch page count: 128
Container extent page count: 64
Table overflow record count: 567
And here's the output of DB2EXFMT for the second query:
DB2 Universal Database Version 7.2, 5622-044 (c) Copyright IBM Corp.
1991, 2001
Licensed Material - Program Property of IBM
IBM DATABASE 2 Explain Table Format Tool
******************** EXPLAIN INSTANCE ********************
DB2_VERSION: 07.02.3
SOURCE_NAME: SQLLF000
SOURCE_SCHEMA: NULLID
EXPLAIN_TIME: 2004-02-09-09.34.41.203001
EXPLAIN_REQUESTER: DB2ADMIN
Database Context:
----------------
Parallelism: None
CPU Speed: 6.179837e-007
Comm Speed: 0
Buffer Pool size: 151136
Sort Heap size: 1024
Database Heap size: 4096
Lock List size: 1024
Maximum Lock List: 20
Average Applications: 50
Locks Available: 23142
Package Context:
---------------
SQL Type: Dynamic
Optimization Level: 5
Blocking: Block All Cursors
Isolation Level: Cursor Stability
---------------- STATEMENT 1 SECTION 65 ----------------
QUERYNO: 200
QUERYTAG:
Statement Type: Select
Updatable: No
Deletable: No
Query Degree: 1
Original Statement:
------------------
Select Distinct DATA2
from ORDERS
where SEARCHCODE = '04' || '' and CLIENTID = 277475
With UR
Optimized Statement:
-------------------
SELECT DISTINCT Q1.DATA2 AS "DATA2"
FROM DB2ADMIN.ORDERS AS Q1
WHERE (Q1.CLIENTID = 277475) AND (Q1.SEARCHCODE = ('04' || ''))
Access Plan:
-----------
Total Cost: 274.691
Query Degree: 1
Rows
RETURN
( 1)
Cost
I/O
|
5.64527
TBSCAN
( 2)
274.691
163.773
|
5.64527
SORT
( 3)
274.689
163.773
|
5.64527
FETCH
( 4)
274.685
163.773
/----+---\
412.105 5.0301e+006
RIDSCN TABLE: DB2ADMIN
( 5) ORDERS
20.7172
2
|
412.105
SORT
( 6)
20.7164
2
|
412.105
IXSCAN
( 7)
20.4841
2
|
5.0301e+006
INDEX: DB2ADMIN
ORDERS02
1) RETURN: (Return Result)
Cumulative Total Cost: 274.691
Cumulative CPU Cost: 3.24999e+006
Cumulative I/O Cost: 163.773
Cumulative Re-Total Cost: 1.17398
Cumulative Re-CPU Cost: 1.89969e+006
Cumulative Re-I/O Cost: 0
Cumulative First Row Cost: 274.69
Estimated Bufferpool Buffers: 0
Arguments:
---------
BLDLEVEL: (Build level)
DB2 v7.1.0.55 : n011211
ENVVAR : (Environment Variable)
DB2_HASH_JOIN = ON
Input Streams:
-------------
8) From Operator #2
Estimated number of rows: 5.64527
Number of columns: 1
Subquery predicate ID: Not Applicable
Column Names:
------------
+DATA2(A)
2) TBSCAN: (Table Scan)
Cumulative Total Cost: 274.691
Cumulative CPU Cost: 3.24999e+006
Cumulative I/O Cost: 163.773
Cumulative Re-Total Cost: 1.17398
Cumulative Re-CPU Cost: 1.89969e+006
Cumulative Re-I/O Cost: 0
Cumulative First Row Cost: 274.69
Estimated Bufferpool Buffers: 0
Arguments:
---------
MAXPAGES: (Maximum pages for prefetch)
ALL
PREFETCH: (Type of Prefetch)
NONE
SCANDIR : (Scan Direction)
FORWARD
Input Streams:
-------------
7) From Operator #3
Estimated number of rows: 5.64527
Number of columns: 1
Subquery predicate ID: Not Applicable
Column Names:
------------
+DATA2(A)
Output Streams:
--------------
8) To Operator #1
Estimated number of rows: 5.64527
Number of columns: 1
Subquery predicate ID: Not Applicable
Column Names:
------------
+DATA2(A)
3) SORT : (Sort)
Cumulative Total Cost: 274.689
Cumulative CPU Cost: 3.24704e+006
Cumulative I/O Cost: 163.773
Cumulative Re-Total Cost: 1.17215
Cumulative Re-CPU Cost: 1.89674e+006
Cumulative Re-I/O Cost: 0
Cumulative First Row Cost: 274.689
Estimated Bufferpool Buffers: 178.431
Arguments:
---------
DUPLWARN: (Duplicates Warning flag)
TRUE
NUMROWS : (Estimated number of rows)
6
ROWWIDTH: (Estimated width of rows)
16
SORTKEY : (Sort Key column)
1: DATA2(A)
TEMPSIZE: (Temporary Table Page Size)
4096
UNIQUE : (Uniqueness required flag)
TRUE
Input Streams:
-------------
6) From Operator #4
Estimated number of rows: 5.64527
Number of columns: 4
Subquery predicate ID: Not Applicable
Column Names:
------------
+$RID$+DATA2+CLIENTID+SEARCHCODE
Output Streams:
--------------
7) To Operator #2
Estimated number of rows: 5.64527
Number of columns: 1
Subquery predicate ID: Not Applicable
Column Names:
------------
+DATA2(A)
4) FETCH : (Fetch)
Cumulative Total Cost: 274.685
Cumulative CPU Cost: 3.24046e+006
Cumulative I/O Cost: 163.773
Cumulative Re-Total Cost: 1.17215
Cumulative Re-CPU Cost: 1.89674e+006
Cumulative Re-I/O Cost: 0
Cumulative First Row Cost: 73.3488
Estimated Bufferpool Buffers: 178.431
Arguments:
---------
MAX RIDS: (Maximum RIDs per list prefetch request)
512
MAXPAGES: (Maximum pages for prefetch)
149
PREFETCH: (Type of Prefetch)
LIST
ROWLOCK : (Row Lock intent)
NONE
TABLOCK : (Table Lock intent)
INTENT NONE
Predicates:
----------
2) Sargable Predicate
Relational Operator: Equal (=)
Subquery Input Required: Yes
Filter Factor: 8.19278e-005
Predicate Text:
--------------
(Q1.CLIENTID = 277475)
3) Sargable Predicate
Relational Operator: Equal (=)
Subquery Input Required: Yes
Filter Factor: 0.0136986
Predicate Text:
--------------
(Q1.SEARCHCODE = ('04' || ''))
Input Streams:
-------------
4) From Operator #5
Estimated number of rows: 412.105
Number of columns: 1
Subquery predicate ID: Not Applicable
Column Names:
------------
+$RID$(A)
5) From Object DB2ADMIN.ORDERS
Estimated number of rows: 5.0301e+006
Number of columns: 3
Subquery predicate ID: Not Applicable
Column Names:
------------
+DATA2+CLIENTID+SEARCHCODE
Output Streams:
--------------
6) To Operator #3
Estimated number of rows: 5.64527
Number of columns: 4
Subquery predicate ID: Not Applicable
Column Names:
------------
+$RID$+DATA2+CLIENTID+SEARCHCODE
5) RIDSCN: (Row Identifier Scan)
Cumulative Total Cost: 20.7172
Cumulative CPU Cost: 1.1605e+006
Cumulative I/O Cost: 2
Cumulative Re-Total Cost: 0.510712
Cumulative Re-CPU Cost: 826417
Cumulative Re-I/O Cost: 0
Cumulative First Row Cost: 20.7164
Estimated Bufferpool Buffers: 3
Arguments:
---------
NUMROWS : (Estimated number of rows)
413
Input Streams:
-------------
3) From Operator #6
Estimated number of rows: 412.105
Number of columns: 1
Subquery predicate ID: Not Applicable
Column Names:
------------
+$RID$(A)
Output Streams:
--------------
4) To Operator #4
Estimated number of rows: 412.105
Number of columns: 1
Subquery predicate ID: Not Applicable
Column Names:
------------
+$RID$(A)
6) SORT : (Sort)
Cumulative Total Cost: 20.7164
Cumulative CPU Cost: 1.15922e+006
Cumulative I/O Cost: 2
Cumulative Re-Total Cost: 0.446094
Cumulative Re-CPU Cost: 721854
Cumulative Re-I/O Cost: 0
Cumulative First Row Cost: 20.7164
Estimated Bufferpool Buffers: 3
Arguments:
---------
DUPLWARN: (Duplicates Warning flag)
FALSE
NUMROWS : (Estimated number of rows)
413
ROWWIDTH: (Estimated width of rows)
12
SORTKEY : (Sort Key column)
1: $RID$(A)
TEMPSIZE: (Temporary Table Page Size)
4096
UNIQUE : (Uniqueness required flag)
FALSE
Input Streams:
-------------
2) From Operator #7
Estimated number of rows: 412.105
Number of columns: 2
Subquery predicate ID: Not Applicable
Column Names:
------------
+$RID$+CLIENTID
Output Streams:
--------------
3) To Operator #5
Estimated number of rows: 412.105
Number of columns: 1
Subquery predicate ID: Not Applicable
Column Names:
------------
+$RID$(A)
7) IXSCAN: (Index Scan)
Cumulative Total Cost: 20.4841
Cumulative CPU Cost: 783315
Cumulative I/O Cost: 2
Cumulative Re-Total Cost: 0.446094
Cumulative Re-CPU Cost: 721854
Cumulative Re-I/O Cost: 0
Cumulative First Row Cost: 20.0458
Estimated Bufferpool Buffers: 3
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) Start Key Predicate
Relational Operator: Equal (=)
Subquery Input Required: Yes
Filter Factor: 8.19278e-005
Predicate Text:
--------------
(Q1.CLIENTID = 277475)
2) Stop Key Predicate
Relational Operator: Equal (=)
Subquery Input Required: Yes
Filter Factor: 8.19278e-005
Predicate Text:
--------------
(Q1.CLIENTID = 277475)
Input Streams:
-------------
1) From Object DB2ADMIN.ORDERS02
Estimated number of rows: 5.0301e+006
Number of columns: 2
Subquery predicate ID: Not Applicable
Column Names:
------------
+$RID$+CLIENTID
Output Streams:
--------------
2) To Operator #6
Estimated number of rows: 412.105
Number of columns: 2
Subquery predicate ID: Not Applicable
Column Names:
------------
+$RID$+CLIENTID
Objects Used in Access Plan:
---------------------------
Schema: DB2ADMIN
Name: ORDERS02
Type: Index
Time of creation: 2003-11-30-12.17.19.593001
Last statistics update: 2004-02-06-12.58.42.468000
Number of columns: 1
Number of rows: 5030097
Width of rows: -1
Number of buffer pool pages: 1134599
Distinct row values: No
Tablespace name: ORDERSI
Tablespace overhead: 9.700000
Tablespace transfer rate: 0.300000
Prefetch page count: 8
Container extent page count: 64
Index clustering statistic: 0.738917
Index leaf pages: 7149
Index tree levels: 3
Index full key cardinality: 9217
Index first key cardinality: 9217
Index first 2 keys cardinality: -1
Index first 3 keys cardinality: -1
Index first 4 keys cardinality: -1
Index sequential pages: 7073
Index page density: 99
Base Table Schema: DB2ADMIN
Base Table Name: ORDERS
Columns in index:
CLIENTID
Schema: DB2ADMIN
Name: ORDERS
Type: Table
Time of creation: 2002-09-22-10.50.24.437001
Last statistics update: 2004-02-06-12.58.42.468000
Number of columns: 89
Number of rows: 5030097
Width of rows: 30
Number of buffer pool pages: 1134599
Distinct row values: No
Tablespace name: ORDERST
Tablespace overhead: 9.700000
Tablespace transfer rate: 0.300000
Prefetch page count: 128
Container extent page count: 64
Table overflow record count: 567
Here is the result of a query against the sysstat.coldist table:
COLNAME TYPE SEQNO COLVALUE VALCOUNT
DISTCOUNT
-- ---------------------- ---- ------ -------------
-------------------- --------------------
CLIENTID F 1 107562 440999
NULL
CLIENTID F 2 110467 158281
NULL
CLIENTID F 3 111042 53261
NULL
CLIENTID F 4 109716 50826
NULL
CLIENTID F 5 110021 47744
NULL
CLIENTID F 6 111035 43653
NULL
CLIENTID F 7 101756 42033
NULL
CLIENTID F 8 101242 39025
NULL
CLIENTID F 9 110042 38558
NULL
CLIENTID F 10 123108 38087
NULL
CLIENTID F 11 108670 33470
NULL
CLIENTID F 12 103183 32132
NULL
CLIENTID F 13 129480 30152
NULL
CLIENTID F 14 106756 30058
NULL
CLIENTID F 15 106556 29187
NULL
CLIENTID F 16 112353 28990
NULL
CLIENTID F 17 113847 25966
NULL
CLIENTID F 18 101285 25965
NULL
CLIENTID F 19 107110 25852
NULL
CLIENTID F 20 101111 25730
NULL
CLIENTID Q 1 0 1
1
CLIENTID Q 2 101687 270811
211
CLIENTID Q 3 102943 530102
548
CLIENTID Q 4 104914 796270
948
CLIENTID Q 5 106556 1070221
1313
CLIENTID Q 6 107537 1323810
1641
CLIENTID Q 7 107562 1765616
1655
CLIENTID Q 8 107923 1853835
1806
CLIENTID Q 9 109024 2118271
2272
CLIENTID Q 10 109940 2389924
2628
CLIENTID Q 11 110467 2728422
2839
CLIENTID Q 12 111035 2921972
3132
CLIENTID Q 13 112427 3177245
3408
CLIENTID Q 14 113463 3442454
3954
CLIENTID Q 15 123794 3706458
4504
CLIENTID Q 16 125045 3971596
5103
CLIENTID Q 17 127497 4236265
6127
CLIENTID Q 18 129164 4500992
6931
CLIENTID Q 19 131184 4768675
8000
CLIENTID Q 20 280958 5030097
9217
SEARCHCODE F 1 '08' 2037561
NULL
SEARCHCODE F 2 '11' 649388
NULL
SEARCHCODE F 3 '12' 607238
NULL
SEARCHCODE F 4 '03' 432470
NULL
SEARCHCODE F 5 '41' 307201
NULL
SEARCHCODE F 6 '60' 215041
NULL
SEARCHCODE F 7 '05' 111406
NULL
SEARCHCODE F 8 '10' 88874
NULL
SEARCHCODE F 9 '51' 64305
NULL
SEARCHCODE F 10 '01' 59969
NULL
SEARCHCODE F 11 '09' 54702
NULL
SEARCHCODE F 12 '88' 39891
NULL
SEARCHCODE F 13 '04' 38661
NULL
SEARCHCODE F 14 '78' 37147
NULL
SEARCHCODE F 15 '81' 34724
NULL
SEARCHCODE F 16 '50' 28932
NULL
SEARCHCODE F 17 '83' 25904
NULL
SEARCHCODE F 18 '68' 20260
NULL
SEARCHCODE F 19 '74' 20186
NULL
SEARCHCODE F 20 '06' 17602
NULL
SEARCHCODE Q 1 '01' 59969
1
SEARCHCODE Q 2 '03' 500572
3
SEARCHCODE Q 3 '04' 539233
4
SEARCHCODE Q 4 '08' 670949
8
SEARCHCODE Q 5 '08' 2708510
8
SEARCHCODE Q 6 '11' 2852086
11
SEARCHCODE Q 7 '11' 3501474
11
SEARCHCODE Q 8 '12' 3501474
12
SEARCHCODE Q 9 '12' 4108712
12
SEARCHCODE Q 10 '41' 4164805
36
SEARCHCODE Q 11 '41' 4472006
36
SEARCHCODE Q 12 '51' 4586044
42
SEARCHCODE Q 13 '60' 4588221
47
SEARCHCODE Q 14 '60' 4803262
47
SEARCHCODE Q 15 '68' 4844718
55
SEARCHCODE Q 16 '78' 4912791
62
SEARCHCODE Q 17 '80' 4928286
63
SEARCHCODE Q 18 '81' 4963010
64
SEARCHCODE Q 19 '88' 5029259
69
SEARCHCODE Q 20 '99' 5030097
73
"Fan Ruo Xin" <fa*****@sbcglobal.net> wrote in message news:<rd****************@newssvr26.news.prodigy.co m>...
Even you gave us a lot of description. There is still one thing that no one
here can know clearly - the distribution of data values for column -
CLIENTID. I thought the value of this column might rather unevenly
distribute. You can change the default db parameter ***NUM_QUANTILES*** , to
help the optimizer to understand the data distribution.
BTW, I don't think INDEX-ANDing is always the better index access.
"Fan Ruo Xin" <fa*****@sbcglobal.net> wrote in message
news:tG***************@newssvr26.news.prodigy.com. .. 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*****@sbcglobal.net> wrote in message
news:Ui****************@newssvr26.news.prodigy.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.google.c om...
> 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.DYNEXPLN
> 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.ORDERS02 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.ORDERS13 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.DYNEXPLN
> 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.ORDERS02 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
