Solve SQL plan access path issue with 10053 trace CBO小试牛刀解决执行计划突然改变的一个办法

写完了英文版以后,耗尽了今天的能量,赶在各位回家过农历新年前,再努力一下,完成中文版.

问题
----
如下SQL查询订单细节表,两个谓词(predicate),每个谓词列上都有索引,
为什么 SQL optimizer 挑了一个选择性差的索引.

# SQL

SELECT /* */ * FROM po_item
WHERE poid = :a AND STATUS = :b
ORDER BY poitemid;

INDEX PO_ITEM_POID ON PO_ITEM(POID)
INDEX PO_ITEM_STATUS_UPDDT ON PO_ITEM(STATUS, STATUSUPDDT)

db_file_multiblock_read_count = 32
optimizer_dynamic_sampling = 2
optimizer_features_enable = 10.1.0.4
optimizer_index_cost_adj = 100
alter system set optimizer_index_caching = 25;
alter system set optimizer_mode = FIRST_ROWS_10;

我们的情况是,一般一个订单一本书, 应该选择订单号POID列,基本没有重复. 但是2007年初夏的一个夜晚,CBO突然改用了物品状态STATUS列上的索引, 物品状态总共只有20个不同数值.
幸运的是,通过构造测试案例,成功再现了当时的情况(好比侦探办案).

解决方案
--------
理解 CBO,试验,观察 and 解释说明.
With SQL session 10053 trace and dbms_xplan.display_cursor().

结果及解释
------
当列统计信息包含num_distinct和density时,少数情况下,优化器会考虑使用density.
比如这个案例,
根据谓词取值(predicate column value)在数据发布图(histogram)的水桶(bucket)里面结果集大小(cardinality) 和 num_rows * density 做比较, 理论上会取数值大的一个. 但是这个案例CBO挑了数值小的一个. 理论会随着新版本的出现而改变.

做数据库设计和管理的,英语都不赖,看官您先读一点英文吧,春节后就变成中文啦. ^_^
When database collect histogram stats on a column, it calculate a new density for the column,
and it will be extreme smaller, CBO use density as selectivity.
After we hack the column density, database will clear the histogram stats,
the CBO will use 1/NumberOfDistinct, ignore density.

When we check the output of 10053 trace,
both IO costs are same, but index selectivity (ix_sel) and table selectivity (ix_sel_with_filters)
on Index: PO_ITEM_STATUS_RANDOM_UPDDT is smaller than than it on Index: PO_ITEM_POID.

Access Path: index (equal)
Index: PO_ITEM_POID
rsc_cpu: 17832 rsc_io: 3
ix_sel: 1.2670e-05 ix_sel_with_filters: 1.2670e-05

Access Path: index (index-only)
Index: PO_ITEM_STATUS_RANDOM_UPDDT
rsc_cpu: 17632 rsc_io: 3
ix_sel: 6.2422e-06 ix_sel_with_filters: 6.2422e-06

*) 修复: 改变列的density统计信息, 使之接近真实值.

Test case setup link 构造测试案例

10053 trace output跟踪文件输出和dbms_xplan美化的执行计划.

后记
----
CBO的水很深, 一环套一环, 好似无底洞. 研究细节和深度需要适可而止,解决完问题,赶紧撤.
参考Christian Antognini提到的DBA容易犯的强迫调优失调综合症(compulsive tuning disorder)谨防重蹈覆辙.

FYI,
Once upon a time, most database administrators suffered from a disease called compulsive
tuning disorder.3 The signs of this illness were the excessive checking of many performancerelated statistics, most of them ratio-based, and the inability to focus on what was really important.


回龙观 风雅园 飘在北京住的地方