更好的索引访问=更差的性能？ [英] Better index access = worse performance??
问题描述
有用的人，
我之前使用DB2的大多数经验都是在s390大型机系统上使用
而且这个平台上的优化器总是看起来非常可预测而且
一致。自从迁移到WinNT / UDB 7.2环境以来，优化器所做的选择往往看起来很脆弱。但最后这个例子确实让我感到非常震惊。我希望有人可以解释为什么我变得更糟
优化器使用两个索引时的响应时间比使用
一时。一些上下文：
订单表：500万+行
此表包含
CLIENTID - 标识客户放置的整数列
订单
SEARCHCODE - 定义订单风格的双字符列
CLIENTID拥有自己的专用索引ORDERS02和SEARCHCODE是一个三列索引的高阶列的OR />
。（是的，这个％＃$ @ !!表中有
是13个索引）。
SEARCHCODE的基数非常低，大约10
不同的值分布在5密耳的行中。
CLIENTID的基数相对较高（以及与SEARCHCODE关系非常高的
），有数千个不同的值。
当我解释这个SQL语句时：
选择Distinct DATA2
来自ORDERS的
其中SEARCHCODE =''04''和CLIENTID = 277475
UR
我被告知这两个指数s用于表示查询，
timeron资源编号非常低。实际上有7行
符合WHERE子句中的条件，并且在使用
DISTINCT后返回两行。但是，时钟响应时间超过3分钟，
这是不可接受的。
当我修改查询以避免在SEARCHCODE上使用索引时，使用
$ b由Bonnie Baker描述的$ ba技术，EXPLAIN确实显示
仅使用了CLIENTID索引，响应时间低于10
秒：
从ORDERS中选择Distinct DATA2
其中SEARCHCODE =''04''|| ''''和CLIENTID = 277475
随着UR
为什么会这样？如果优化器可以确定两个索引
是有用的，为什么它不能有效地使用它们？当然，它有可用于查看
SEARCHCODE索引的基数的分布统计数据的所有
？是的，runstats之前运行了4天，然后
然后在重新测试之前再次使用此命令：
RUNSTATS ON TABLE DB2ADMIN.ORDERS with DISTRIBUTION AND Detailed
指数所有重大变化
我已经包含了下面两个解释的输出。
有谁可以对此有所了解？是否会有不同的指数
安排改善这种情况，以便优化器不会做出这么糟糕的选择？
任何反馈都会赞赏。
Sean
包名= DB2ADMIN.DYNEXPLN
预备日期= 2004/02/06
准备时间= 10:59:53
绑定时间戳= 2004-02-06-11.09.04.781000
隔离级别=游标稳定性
阻塞=阻止无模糊游标
查询优化级= 5
分区并行=否
内部分区并行=否
功能路径=SYSIBM，SYSFUN，DB2ADMIN
-------------------- SECTION ------------------------- --------------
Section = 1
SQL语句：
选择Distinct DATA2
来自ORDERS的
其中SEARCHCODE =''04''a和CLIENTID = 277475
随UR
估计费用= 254
估计基数= 3
（5）索引ANDing
|设置大小的优化器估计：3
|索引ANDing位图构建
| |设置大小的优化器估计：3
（6）| |访问表名称= DB2ADMIN.ORDERS ID = 6,4
| | | #Columns = 1
| | |索引扫描：名称= DB2ADMIN.ORDERS02 ID = 3
| | | |索引列：
| | | | | 1：CLIENTID（升序）
| | | | #Key Columns = 1
| | | | |开始关键：包容性价值
| | | | | | 1：277475
| | | | |停止键：包含价值
| | | | | | 1：277475
| | | |仅限索引访问
| | | |索引预取：无
| | |隔离级别：未提交读取
| | |锁定意图
| | | |表：意图无
| | | |行：无
|索引ANDing位图探测器
| |设置大小的优化器估计：3
（7）| |访问表名称= DB2ADMIN.ORDERS ID = 6,4
| | | #Columns = 1
| | |索引扫描：名称= DB2ADMIN.ORDERS13 ID = 14
| | | |索引列：
| | | | | 1：SEARCHCODE（升序）
| | | | | 2：状态（升序）
| | | | | 3：TNUMBER（升序）
| | | | #Key Columns = 1
| | | | |开始关键：包容性价值
| | | | | | 1：''04''
| | | | |停止键：包含价值
| | | | | | 1：''04''
| | | |仅限索引访问
| | | | Index Prefetch：符合条件59
| | |隔离级别：未提交读取
| | |锁定意图
| | | |表：意图无
| | | |行：无
（4）访问表名= DB2ADMIN.ORDERS ID = 6,4
| #Columns = 3
|直接取票
|隔离级别：未提交读取
|锁定意图
| |表：意图无
| |行：无
|剩余谓词
| | #Predicates = 2
（3）插入分类临时表ID = t1
| #Columns = 1
| #Sort Key Columns = 1
| |关键1：DATA2（升序）
| Sortheap分配参数：
| | #Rows = 4
| |行宽= 16
| Piped
|重复消除
（2）访问临时表ID = t1
| #Columns = 1
|关系扫描
| |预取：符合条件
（2）|将数据返回给应用程序
| | #Columns = 1
（1）返回数据完成
结束部分
优化计划：
返回
（1）
|
TBSCAN
（2）
|
SORT
（3）
|
FETCH
（4）
/ - / \
IXAND表：
（5）DB2ADMIN
/ - / \ - \ ORDERS
IXSCAN IXSCAN
（6）（7）
/ \ / \
索引：表：索引：表：
DB2ADMIN DB2ADMIN DB2ADMIN DB2ADMIN
ORDERS02 ORDERS ORDERS13订单
>
DB2通用数据库版本7.2,5622-044（c）版权所有IBM Corp.
1991,2001
许可材料 - IBM的程序属性
IBM DATABASE 2 SQL解释工具
********************包装****** ******* ************************** *
包名= DB2ADMIN.DYNEXPLN
准备日期= 2004/02/06
准备时间= 11:04:05
绑定时间戳= 2004-02-06-11.13.16.859000
隔离级别=游标稳定性
阻塞=阻止无模糊游标
查询优化级别= 5
分区并行=否
内部分区并行=否
功能路径=SYSIBM，SYSFUN， DB2ADMIN"
-------------------- SECTION --------------- ------------------------
Section = 1
SQL语句：
选择Distinct DATA2
来自ORDERS的
其中SEARCHCODE =''04''|| ''''和CLIENTID = 277475
带UR
估计费用= 274
估计基数= 6
（7）访问表名= DB2ADMIN.ORDERS ID = 6,4
| #Columns = 1
|索引扫描：名称= DB2ADMIN.ORDERS02 ID = 3
| |索引列：
| | | 1：CLIENTID（升序）
| | #Key Columns = 1
| | |开始关键：包容性价值
| | | | 1：277475
| | |停止键：包含价值
| | | | 1：277475
| |仅限索引访问
| |索引预取：无
（7）| | |插入分类临时表ID = t1
| | | | #Columns = 1
| | | | #Sort Key Columns = 1
| | | | |关键1 :(升序）
| | | | Sortheap分配参数：
| | | | | #Rows = 410
| | | | |行宽= 12
| | | | Piped
|隔离级别：未提交读取
|锁定意图
| |表：意图无
| |行：无
（6）排序临时表完成ID = t1
（5）列表预取RID准备
（3）插入排序临时表ID = t2
| #Columns = 1
| #Sort Key Columns = 1
| |关键1：DATA2（升序）
| Sortheap分配参数：
| | #Rows = 6
| |行宽= 16
| Piped
|重复消除
（2）访问临时表ID = t2
| #Columns = 1
|关系扫描
| |预取：符合条件
（2）|将数据返回给应用程序
| | #Columns = 1
（1）返回数据完成
结束部分
优化计划：
返回
（1）
|
TBSCAN
（2）
|
SORT
（3）
|
RIDSCN
（5）
|
SORT
（6）
|
IXSCAN
（7）
/ \
索引：表：
DB2ADMIN DB2ADMIN
ORDERS02订单
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
推荐答案
@ !!表）。
SEARCHCODE的基数非常低，大约10
不同的值分布在5密耳的行中。
CLIENTID的基数相对较高（并且与SEARCHCODE的关系非常高
），有数千个不同的值。
当我解释这个SQL语句时：
选择Distinct DATA2
来自ORDERS的
其中SEARCHCODE =''04''和CLIENTID = 277475
随着UR
我被告知这两个索引都用于表示查询，并且
timeron资源编号非常低。实际上有7行
符合WHERE子句中的条件，并且在使用
DISTINCT后返回两行。但是，时钟响应时间超过3分钟，
这是不可接受的。
当我修改查询以避免在SEARCHCODE上使用索引时，使用
$ b由Bonnie Baker描述的$ ba技术，EXPLAIN确实显示
仅使用了CLIENTID索引，响应时间低于10
秒：
从ORDERS中选择Distinct DATA2
其中SEARCHCODE =''04''|| ''''和CLIENTID = 277475
随着UR
为什么会这样？如果优化器可以确定两个索引
是有用的，为什么它不能有效地使用它们？当然，它有可用于查看
SEARCHCODE索引的基数的分布统计数据的所有
？是的，runstats之前运行了4天，然后
然后在重新测试之前再次使用此命令：
RUNSTATS ON TABLE DB2ADMIN.ORDERS with DISTRIBUTION AND Detailed
指数所有重大变化
我已经包含了下面两个解释的输出。
有谁可以对此有所了解？是否会有不同的指数
安排改善这种情况，以便优化器不会做出这么糟糕的选择？
任何反馈都会赞赏。
Sean
包名= DB2ADMIN.DYNEXPLN
预备日期= 2004/02/06
准备时间= 10:59:53
绑定时间戳= 2004-02-06-11.09.04.781000
隔离级别=游标稳定性
阻塞=阻止无模糊游标
查询优化级= 5
分区并行=否
内部分区并行=否
功能路径=SYSIBM，SYSFUN，DB2ADMIN
-------------------- SECTION ------------------------- --------------
Section = 1
SQL语句：
选择Distinct DATA2
来自ORDERS的
其中SEARCHCODE =''04''a和CLIENTID = 277475
随UR
估计费用= 254
估计基数= 3
（5）索引ANDing
|设置大小的优化器估计：3
|索引ANDing位图构建
| |设置大小的优化器估计：3
（6）| |访问表名称= DB2ADMIN.ORDERS ID = 6,4
| | | #Columns = 1
| | |索引扫描：名称= DB2ADMIN.ORDERS02 ID = 3
| | | |索引列：
| | | | | 1：CLIENTID（升序）
| | | | #Key Columns = 1
| | | | |开始关键：包容性价值
| | | | | | 1：277475
| | | | |停止键：包含价值
| | | | | | 1：277475
| | | |仅限索引访问
| | | |索引预取：无
| | |隔离级别：未提交读取
| | |锁定意图
| | | |表：意图无
| | | |行：无
|索引ANDing位图探测器
| |设置大小的优化器估计：3
（7）| |访问表名称= DB2ADMIN.ORDERS ID = 6,4
| | | #Columns = 1
| | |索引扫描：名称= DB2ADMIN.ORDERS13 ID = 14
| | | |索引列：
| | | | | 1：SEARCHCODE（升序）
| | | | | 2：状态（升序）
| | | | | 3：TNUMBER（升序）
| | | | #Key Columns = 1
| | | | |开始关键：包容性价值
| | | | | | 1：''04''
| | | | |停止键：包含价值
| | | | | | 1：''04''
| | | |仅限索引访问
| | | | Index Prefetch：符合条件59
| | |隔离级别：未提交读取
| | |锁定意图
| | | |表：意图无
| | | |行：无
（4）访问表名= DB2ADMIN.ORDERS ID = 6,4
| #Columns = 3
|直接取票
|隔离级别：未提交读取
|锁定意图
| |表：意图无
| |行：无
|剩余谓词
| | #Predicates = 2
（3）插入分类临时表ID = t1
| #Columns = 1
| #Sort Key Columns = 1
| |关键1：DATA2（升序）
| Sortheap分配参数：
| | #Rows = 4
| |行宽= 16
| Piped
|重复消除
（2）访问临时表ID = t1
| #Columns = 1
|关系扫描
| |预取：符合条件
（2）|将数据返回给应用程序
| | #Columns = 1
（1）返回数据完成
结束部分
优化计划：
返回
（1）
|
TBSCAN
（2）
|
SORT
（3）
|
FETCH
（4）
/ - / \
IXAND表：
（5）DB2ADMIN
/ - / \ - \ ORDERS
IXSCAN IXSCAN
（6）（7）
/ \ / \
索引：表：索引：表：
DB2ADMIN DB2ADMIN DB2ADMIN DB2ADMIN
ORDERS02 ORDERS ORDERS13订单
>
DB2通用数据库版本7.2,5622-044（c）版权所有IBM Corp.
1991,2001
许可材料 - IBM的程序属性
IBM DATABASE 2 SQL解释工具
********************包装****** ******* ************************** *
包名= DB2ADMIN.DYNEXPLN
准备日期= 2004/02/06
准备时间= 11:04:05
绑定时间戳= 2004-02-06-11.13.16.859000
隔离级别=游标稳定性
阻塞=阻止无模糊游标
查询优化级别= 5
分区并行=否
内部分区并行=否
功能路径=SYSIBM，SYSFUN， DB2ADMIN"
-------------------- SECTION --------------- ------------------------
Section = 1
SQL语句：
选择Distinct DATA2
来自ORDERS的
其中SEARCHCODE =''04''|| ''''和CLIENTID = 277475
带UR
估计费用= 274
估计基数= 6
（7）访问表名= DB2ADMIN.ORDERS ID = 6,4
| #Columns = 1
|索引扫描：名称= DB2ADMIN.ORDERS02 ID = 3
| |索引列：
| | | 1：CLIENTID（升序）
| | #Key Columns = 1
| | |开始关键：包容性价值
| | | | 1：277475
| | |停止键：包含价值
| | | | 1：277475
| |仅限索引访问
| |索引预取：无
（7）| | |插入分类临时表ID = t1
| | | | #Columns = 1
| | | | #Sort Key Columns = 1
| | | | |关键1 :(升序）
| | | | Sortheap分配参数：
| | | | | #Rows = 410
| | | | |行宽= 12
| | | | Piped
|隔离级别：未提交读取
|锁定意图
| |表：意图无
| |行：无
（6）排序临时表完成ID = t1
（5）列表预取RID准备
（3）插入排序临时表ID = t2
| #Columns = 1
| #Sort Key Columns = 1
| |关键1：DATA2（升序）
| Sortheap分配参数：
| | #Rows = 6
| |行宽= 16
| Piped
|重复消除
（2）访问临时表ID = t2
| #Columns = 1
|关系扫描
| |预取：符合条件
（2）|将数据返回给应用程序
| | #Columns = 1
（1）返回数据完成
结束部分
优化计划：
返回
（1）
|
TBSCAN
（2）
|
SORT
（3）
|
RIDSCN
（5）
|
SORT
（6）
|
IXSCAN
（7）
/ \
索引：表：
DB2ADMIN DB2ADMIN
ORDERS02 ORDERS
@!! 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
" Sean C." <分贝***** @ yahoo.com>在留言中写道
新闻：2f ************************** @ posting.google.c om ...
"Sean C." <db*****@yahoo.com> wrote in message
news:2f**************************@posting.google.c om...
有帮助的人，
我之前使用DB2的大部分经验都是在s390大型机系统上进行的，而且这个平台上的优化器总是看起来非常可预测。
是一致的。自从迁移到WinNT / UDB 7.2环境以来，优化器的选择通常看起来很脆弱。但最后一个例子真的让我感到震惊。我希望有人可以解释为什么当优化器使用两个索引时，比使用
时更糟糕的响应时间。一些上下文：
订单表：500万+行
此表包含
CLIENTID - 整数列标识客户放置
订单
SEARCHCODE - 两个定义订单风格的字符列
CLIENTID拥有自己的专用索引ORDERS02，而SEARCHCODE是三列索引的高阶列ORDERS13。（是的，那里
这个％＃
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的13个索引。
SEARCHCODE的基数非常低，大约有10个不同的值传播跨越5密尔行。
CLIENTID的基数相对较高（与SEARCHCODE的关系非常高），有数千个不同的值。
当我解释这个SQL语句时：
从ORDERS中选择Distinct DATA2
其中SEARCHCODE =''04''和CLIENTID = 277475
随着UR
我'我告诉两个索引都用来表示查询，以及
timeron reso urce数量非常低。实际上有7行符合WHERE子句中的条件，并且在使用
DISTINCT后返回两行。但是，时钟响应时间超过3分钟，这是不可接受的。
当我修改查询以避免在SEARCHCODE上使用索引时，使用Bonnie Baker描述的技术，EXPLAIN确实只显示了正在使用的CLIENTID索引，响应时间不到10秒：
从ORDERS中选择Distinct DATA2
其中SEARCHCODE =''04''|| ''''和CLIENTID = 277475
随着UR
为什么会这样？如果优化器可以确定两个索引是否有用，为什么它们不能有效地使用它们呢？当然，它有所有的分布统计数据可用于查看
SEARCHCODE索引的基数？是的，runstats之前运行了4天，然后在重新测试之前再次使用此命令：
RUNSTATS ON TABLE DB2ADMIN.ORDERS with DISTRIBUTION AND DETAILED
INDEXES ALL SHRLEVEL CHANGE
我已将下面两个解释的输出包括在内。
任何人都可以对此有所了解吗？是否有不同的索引安排改善这种情况，以便优化器不会做出如此糟糕的选择？
任何反馈都会受到赞赏。
肖恩
@!! 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
执行runstats实用程序时，最好使用
以下选项：
RUNSTATS ON TABLE table_name
包含所有列的分布
和详细的索引所有
这将告诉DB2有关SEARCHCODE的低基数。
出于某种疯狂的原因（我认为IBM有时会有自杀倾向），命令参考中的
示例都没有显示使用上述所有选项。它应该是默认的
。
这将告诉DB2有关SEARCHCODE的低基数。
您可以尝试的另一件事是更改查询优化级别。
默认值通常设置为5，但有时7会更好。
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.
这篇关于更好的索引访问=更差的性能？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
