PATH in 10053 Trace 的存取路径代码和解释

越来越多的DBA和Developer开始研究CBO相关的10053 Trace.
可以参考老刘(Lewis)的Cost Based Oracle Fundamentals第14章 - The 10053 Trace File.

Oracle 11g之前,PATH 用代码表示,阅读起来极不方便, 因此木匠干脆罗列出来有关表扫描的Access PATH代码含义:

# PATH in 10053 trace
-- table access path


0 parallel hint
1 no access path spec
2 table scan
3 index unique
4 index range
5 index and-equal
6 order by using an index
7 open cluster
8 hash cluster
9 rowid lookup
10 range scan backwards
11 rowid range scan
12 driving_site hint
14 cache hint
15 nocache hint
16 partitions hint
17 nopartitions hint
18 anti-join
19 index rowid range scan
20 bitmap index
21 parallel_index hint
22 noparallel_index hint
23 index fast full scan
24 swap inputs to join
25 fact table
26 not a fact table
27 merge of this view
28 do not push join predicate into this view
29 push join predicate into this view
30 no_merge of this view
31 semi-join


SQL跟踪文件里面哈希值的变化 - 启动TOP作者博客翻译

TrackBack: http://antognini.ch/2009/01/execution-plan-hash-value-in-sql-trace-files/

在征得了Christian Antognini的同意后, 木匠准备近乎实时的(near real-time)跟踪报道他的最新研究成果. 附带的好处是极大的增加了我的博客出版频率,完成年初定的每周一篇技术贴的目标.

在我理解有误的地方,欢迎各位DBA同仁给予指正. 木匠没有出钱买断Christian Antognini博客的中文翻译版权, 所以您也可以同时翻译相同的文章, 不过, 请一定指明原文的TrackBack.

我只会讲每篇的主旨和总结. 从2009年一月开始,

第一篇: SQL跟踪文件里面哈希值的变化(Execution Plan Hash Value in SQL Trace Files)

在Oracle 11.1.0.7版本, 在PARSE, EXEC 和FETCH这些行里边, 新出现了一个"plh"属性, 我猜是 Plan Hash 的缩写. plh是为了配合ACS(Adaptive cursor sharing), ACS就是根据不同的绑定变量(binding variable)产生不同的最优执行计划.

参见 http://antognini.ch/2008/12/automatic-evolution-of-sql-plan-baselines/ 第7个木匠的注释.

这里是一个SQL trace file样例,

SELECT count(pad) FROM t WHERE id < :id

PARSE #13:c=0,e=0,p=0,cr=0,cu=0, mis=0,r=0,dep=0,og=1,plh=4270555908
EXEC #13:c=0,e=0,p=0,cr=0,cu=0, mis=1,r=0,dep=0,og=1,plh=2966233522

当变量取值发生变化后,优化器决定选择一个不同的执行计划. 我们看到mis=1和plh=2966233522 出现在EXEC(执行)这一行,而且的plh数值不同于PARSE一行plh的数值 SQL优化器对游标(cursor)做了一个硬解析(hard parse), 然而PARSE部分没有硬解析.

由此改变了我们的传统认识, 不但PARSE部分会hard parse,而且EXEC部分也会hard parse.


去阿拉斯加的邮轮,在维多利亚Ogden Point港停靠.

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.


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