Oracle案例:分析10053跟踪文件-技术开发专区

Oracle案例:分析10053跟踪文件

作者：IT168 hxcqu 编辑：晓熊 2008-12-29 10:28 来源：IT168�

　　5. 分析小结

　　分析10053的过程，一般是从跟踪文件的底部往上看，再到感兴趣的区域。感兴趣的区域就是那些属于看起来有问题的执行计划的区域。

　　在这里列举进行10053跟踪文件分析的步骤：

　　1) 从文件末尾开始

　　确认你感兴趣的sql语句存在于跟踪文件中。如果跟踪文件中有EXPLAIN PLAN，编辑一下跟踪文件尽量便于阅读。

　　2)查看所选执行计划的最终成本。

　　3)查看产生最终成本的连接顺序

　　4)查看感兴趣的引起成本的连接顺序部分

　　5)查看连接顺序产生成本的连接类型

　　6)检查在步骤5中找到的连接类型的成本

　　a)确认用到何种访问路径

　　b)检查其他被拒绝的访问路径(仅适用于嵌套循环连接，在对内部行集的访问路径中，有几个访问路径产生成本)

　　 详细分析

　　1.从结尾开始，从执行计划开始

　　在10053中获得准确的执行计划输出，很重要。有些信息在10053中会丢失，我们需要使用执行计划来得出成本计算是如何进行的结论。有时候我们会使用执行计划来调查10053或者检查我们的分析。

　　注意，根据parent ID来编排执行计划的步骤，使得执行计划的层次具有可读性。

　　请注意，包含在跟踪文件中的执行计划的输出会随着版本的变化而不同，而且经常不会出现。在10g中，可以通过调整10053跟踪信息，然后执行一次EXPLAIN PLAN FOR....命令，来获取一个格式化好了的执行计划。然而，绑定变量的出现可能会影响实际产生的执行计划，EXPLAIN PLAN命令发现不了这一点，因为不同的绑定变量值有不同的访问路径。

　　2.查看执行计划的最终成本

　Final:

　　CST: 20762 CDN: 1 RSC: 83447 RSP: 20762 BYTES: 173

　　IO-RSC: 20658 IO-RSP: 82626 CPU-RSC: 101017010 CPU-RSP: 801120184

　　PLAN

　　Cost of plan: 20762

　　Operation...........Object name.....Options.........Id...Pid..

　　SELECT STATEMENT 0

　　SORT GROUP BY 1

　　TABLE ACCESS CERTIFICATE BY LOCAL INDEX R 2 1

　　NESTED LOOPS 3 2

　　NESTED LOOPS 4 3

　　TABLE ACCESS PREMIUM_PLAN_COD FULL 5 4

　　INDEX PK_CIPBF_IX FULL SCAN 6 4

　　INDEX XPKCERTIFICATE RANGE SCAN 7 3

　　好的执行计划 (在另外一个跟踪文件中)，用到了NO_INDEX提示，看起来像下面这样:

　Cost of plan: 58201

　　Operation...........Object name.....Options.........Id...Pid..

　　SELECT STATEMENT 0

　　SORT GROUP BY 1

　　TABLE ACCESS CERTIFICATE BY LOCAL INDEX R 2 1

　　NESTED LOOPS 3 2

　　HASH JOIN 4 3 <== hash join instead of NL

　　TABLE ACCESS PREMIUM_PLAN_COD FULL 5 4 <== full table scan

　　instead of index

　　full scan

　　TABLE ACCESS CERT_INSURED_PLA FULL 6 4

　　INDEX XPKCERTIFICATE RANGE SCAN 7 3

　　3.查找产生最终成本的连接顺序

　　使用cost (20762)作为关键字，查找产生此成本的连接顺序，我们找到如下：

Join result: cost: 20762 cdn: 1 rcz: 173

　　Best so far: TABLE#: 0 CST: 15810 CDN: 1 BYTES: 122

　　Best so far: TABLE#: 2 CST: 20266 CDN: 1981 BYTES: 277340 Best so far: TABLE#: 1 CST: 20762 CDN: 1 BYTES: 173

　　它属于序号为2的连接顺序.... 上翻到连接顺序部分的开始处，可以看到:

　　Join order[2]: PREMIUM_PLAN_CODE [A13] CERT_INSURED_PLAN_BENEFIT_FACT [A11] CERTIFICATE [A12]

　　在9206中查找选定的执行计划的连接顺序，在10g中，会更简单，我们在10053中有如下的跟踪信息：

JOIN ORDER: 2

　　CST: ... CDN: ... RSC: ... RSP: ... BYTES: ...

　　此例中，被选择的"JOIN ORDER"是 2.

　　4. 查找"好的"执行计划与"不好的"执行计划中不一样的连接顺序部分

　　本例中，它们第二张表的连接顺序不同:

　　"好的"执行计划

　Join result: cost: 58201 cdn: 1 rcz: 173

　　Best so far: TABLE#: 0 CST: 15810 CDN: 1 BYTES: 122

　　Best so far: TABLE#: 2 CST: 57706 CDN: 1981 BYTES: 277340

　　Best so far: TABLE#: 1 CST: 58201 CDN: 1 BYTES: 173

　　"不好的"执行计划　

Join result: cost: 20762 cdn: 1 rcz: 173

　　Best so far: TABLE#: 0 CST: 15810 CDN: 1 BYTES: 122

　　Best so far: TABLE#: 2 CST: 20266 CDN: 1981 BYTES: 277340 <== this cost is different (20266 vs 57706)

　　Best so far: TABLE#: 1 CST: 20762 CDN: 1 BYTES: 173

　　5.在连接顺序部分查找连接，它们是第四步中找到的产生成本的连接.

　　跟踪文件中往上查找成本20266，你会找到如下行：　

Join result: cost: 20266 cdn: 1981 rcz: 140

　　继续查找是哪个连接产生了这个(最低)成本　

Best NL cost: 20266 resp: 20266

　　从执行计划中，我们知道，它将是个NL连接，并且在这里得到了确认。在NL连接部分继续查找此成本。

NL Join

　　Outer table: cost: 15810 cdn: 1 rcz: 122 resp: 15809

　　Inner table: CERT_INSURED_PLAN_BENEFIT_FACT

　　...

　　OPTIMIZER PERCENT INDEX CACHING = 70

　　Access path: index (no sta/stp keys)

　　Index: PK_CIPBF_IX

　　TABLE: CERT_INSURED_PLAN_BENEFIT_FACT

　　RSC_CPU: 116668803 RSC_IO: 17885

　　IX_SEL: 1.0000e+00 TB_SEL: 2.1281e-04

　　Join: resc: 81471 resp: 20266

　　这里，我们看到"resp"成本的非常好的成本被找到。这就是"response time"成本，例如，一个执行计划使用PX在尽可能短的时间内找到答案所消耗的成本。"resc" 成本就是 "resource cost"。当串行执行查询的时候，这就是资源消耗成本。完整的执行计划输出中会显示PX是否被使用。

　　我们需要弄清楚这个成本是怎么计算出来的。为此，我们要看看CBO是如何计算连接成本的。下面是基本计算公式，用实际值进行替换：

Basic NL join cost formula: COST(outer) + [ CARD(outer) * COST(inner) ]

　　注意：在下面的公式中，"RESC(outer)"指访问内层表的资源消耗。"RESP(outer)"指外层表的响应成本(使用PX)　

resc = RESC(outer) + [CARD(outer) * RESC(inner)]

　　= 63646 + [ 1 * (rsc_cpu / cpu_factor + rsc_io) * index_cost_adj ]

　　= 63646 + [ 1 * ( 116668803 / 975563.49 + 17885) * 0.99 ]

　　= 63646 + 17824.5

　　= 81470.5 ~ 81471: OK

　　Resp = RESP(outer) + (CARD(outer) * RESC(inner) )

　　= 15809 + [1 * (rsc_cpu / cpu_factor + rsc_io)/(deg of join parallelism * parallel scaling factor) * index_cost_adj ]

　　= 15809 + [1 * (116668803 / 975563.49 + 17885)/(4 * 0.9) * 0.99 ]

　　= 15809 + 18004.59 / 3.6 * 0.99 = 20602.17 vs. 20266, close...but not exact...costing has fudge factors?

　　这种连接排列为外层表和并行内层表的并行子表使用并行操作，但每个子表对内层表使用完全索引访问路径。既然是个NL连接，很可能使用了"broadcast"PX行分布。如果没有执行计划，很难知道这是否是CBO的选择。

　(deg of join parallelism * parallel scaling factor)= 4 * 0.9 = 3.6

　　因此，不是成本除以4，而是除以3.6

　　外层表的全表扫描的并行度可以在这里看到。连接的并行度设置为其中任何一张表的最高并行度。本例中，设置为表PREMIUM_PLAN_CODE的并行度。一个完整的执行计划输出对于验证这一点非常有帮助。

　　6. 表"CERT_INSURED_PLAN_BENEFIT_FACT"的其他NL连接成本

　　 使用FTS：

　NL Join

　　Outer table: cost: 15810 cdn: 1 rcz: 122 resp: 15809 (Note: serial tsc cost = 63646)

　　Inner table: CERT_INSURED_PLAN_BENEFIT_FACT

　　Access path: tsc Resc: 167233 (NOTE: parallel tsc cost = 41309)

　　Join: Resc: 230879 Resp: 57618 <== cost = 15809 + 41309 = 57118

　　Resc = Resc(outer) + [Card(outer) * Resc(inner) ]

　　= 63646 + [1 * 167233 ] (Note: Resc(Inner) is close to the value in single table access path, but not exact)

　　= 230879 (exact)

　　Resp = Resp(outer) + [Card(outer) * Resp(inner) ]

　　= 15809 + [ 1 * 41309 ]

　　= 57118 vs. 57618 (close, not exact)

　　 使用索引快速全扫描：

　NL Join

　　Outer table: cost: 15810 cdn: 1 rcz: 122 resp: 15809 (Note: serial tsc cost = 63646)

　　Inner table: CERT_INSURED_PLAN_BENEFIT_FACT

　　...

　　Access path: index (iff)

　　Index: PK_CIPBF_IX

　　TABLE: CERT_INSURED_PLAN_BENEFIT_FACT

　　RSC_CPU: 2865229980 RSC_IO: 52693

　　IX_SEL: 0.0000e+00 TB_SEL: 1.0000e+00

　　Inner table: CERT_INSURED_PLAN_BENEFIT_FACT

　　Access path: iff Resc: 55630

　　Join: Resc: 119276 Resp: 29717

　　Resc = Resc(outer) + [ Card(outer) * Resc(Inner) ]

　　= 63646 + [ 1 * 55630 ]

　　= 119276 (exact)

　　Resp = Resp(outer) + [Card(outer) * Resp(inner) / (degree of join parallelism) ]

　　= 15809 + [ 1 * 27815 / 2 ]

　　= 29716.5 = 29717 (exact)

　　 其他连接类型的成本(SMJ 与 HJ)　　

SM Join

　　Outer table:

　　resc: 63646 cdn: 1 rcz: 122 deg: 4 resp: 15809

　　Inner table: CERT_INSURED_PLAN_BENEFIT_FACT

　　resc: 55630 cdn: 22878070 rcz: 18 deg: 2 resp: 27815

　　using join:1 distribution:2 #groups:1

　　SORT resource Sort statistics

　　Sort width: 598 Area size: 1048576 Max Area size: 104857600 Degree: 1

　　Blocks to Sort: 1 Row size: 145 Rows: 1

　　Initial runs: 1 Merge passes: 0 IO Cost / pass: 0

　　Total IO sort cost: 0

　　Total CPU sort cost: 975652

　　Total Temp space used: 0

　　SORT response Sort statistics

　　Sort width: 598 Area size: 20971520 Max Area size: 104857600 Degree: 4

　　Blocks to Sort: 1 Row size: 145 Rows: 1

　　Initial runs: 1 Merge passes: 0 IO Cost / pass: 0

　　Total IO sort cost: 0

　　Total CPU sort cost: 1084058

　　SORT resource Sort statistics

　　Sort width: 598 Area size: 1048576 Max Area size: 104857600 Degree: 1

　　Blocks to Sort: 84029 Row size: 30 Rows: 22878070

　　Initial runs: 7 Merge passes: 1 IO Cost / pass: 149862

　　Total IO sort cost: 233891

　　Total CPU sort cost: 27269777197

　　Total Temp space used: 1288102000

　　SORT response Sort statistics

　　Sort width: 598 Area size: 20971520 Max Area size: 104857600 Degree: 2

　　Blocks to Sort: 42015 Row size: 30 Rows: 11439035

　　Initial runs: 4 Merge passes: 1 IO Cost / pass: 74932

　　Total IO sort cost: 129941

　　Total CPU sort cost: 14577786635

　　Merge join Cost: 381119 Resp: 188508

Resc cost = Resc(outer) + Resc(inner) + Sort_Cost(outer) + Sort_Cost(inner)

　　= Resc(outer) + Resc(inner) + [ (CPU_Cost(outer) + IO_Cost(outer) ) + (CPU_Cost(inner) + IO_Cost(inner) ]

　　= 63646 + 55630 + ( 975652 / 975563.49 + 0 ) + ( 27269777197 / 975563.49 + 233891)

　　= 381120.8 ~ 381119 (very close)

　　Resp cost = Resp(outer) + Resp(inner) + Par_Sort_Cost(outer) + Par_Sort_Cost(inner)

　　= Resp(outer) + Resp(inner) + [ (Par_CPU_Cost(outer) + Par_IO_Cost(outer) ) + ( (Par_CPU_Cost(inner) + Par_CPU_Cost(inner)) ]

　　= 15809 + 27815 + [ ( 1084058 / 975563.49 + 0 ) + ( 14577786635 / 975563.49 + 129941) ]

　　= 188509.1 ~ 188508 (very close)

　HA Join

　　Outer table:

　　resc: 63646 cdn: 1 rcz: 122 deg: 4 resp: 15809

　　Inner table: CERT_INSURED_PLAN_BENEFIT_FACT

　　resc: 55630 cdn: 22878070 rcz: 18 deg: 2 resp: 27815

　　using join:8 distribution:2 #groups:1

　　Hash join one ptn Resc: 587 Deg: 4

　　hash_area: 5120 (max=25600) buildfrag: 1 probefrag: 20946 ppasses: 1

　　buildfrag: 1 probefrag: 20946 passes: 1

　　Hash join Resc: 121625 Resp: 44212

　　Resc cost = Resc(outer) + Resc(inner) + HJ_Cost_Ser

　　= 63646 + 55630 + (HJ_Resc_Cost * HJ_Dop)

　　= 63646 + 55630 + ( 587 * 4 )

　　= 121624 ~ 121625 (very close)

　　Resp cost = Resp(outer) + Resp(inner) + HJ_Cost_Par

　　= 15809 + 27815 + 587

　　= 44211 ~ 44212 (very close)

　　注意：

　　用于SMJ 和 HA 的RESC 与RESP成本来自于每张表的单个表访问成本部分，例如：

　　RESC(inner)是IFF resc成本，RESP(inner) 是IFF resp成本。

　　7. 结论

　　本文的最终结论是，未加提示("不好的")执行计划，CBO选择一个NL连接的全索引扫描。主要受以下因素影响：

　　1. FTS的高成本(由于"multiblock read divisor"的值过低)。

　　2. 由于OPTIMIZER_INDEX_CACHING参数，导致索引访问的成本较低。这个参数极大的降低索引访问的成本，对系统来说远远超出合理的值。

　　我们知道另外一个较好的执行计划使用全表扫描，并且具有较好的性能，可以看到，"multiblock read divisor"在本例子中设置好像不是很准确。一个较准确的除数，会让FTS/IFF的成本更低，使用哈希连接会更好。另外一个更好的执行计划可能是对内部行源(与哈希连接类似，但是避免了哈希成本)使用IFF嵌套循环。本例中，嵌套循环的内部行源没有选择使用IFF，是因为它的成本计算没有考虑OPTIMIZER_INDEX_CACHING因子(只应用于执行单块索引读操作)所带来的成本减少的益处。而且，IFF的相对成本由于较低的multiblock read divisor设置而增加了。

　　后续的调查包括：

　　找出为什么多块读除数如此低(在aux_stats$视图中检查系统的统计)

　　一段时间间隔后，再次收集系统的统计信息，将这些信息放置于"stattab"中，比较与aux_stats$中的现存值。

　　了解客户为什么要设置OPTIMIZER_INDEX_CACHING为一个如此高的值。

　　8. 参考文档

　　Note: 338137.1

第1页：关于Oracle案例学习第2页：预分析工作第3页：分析小结

关注我们