CBO带来的一个语句执行计划差异问题
这两天以前的一个dba遇到一个性能问题来问我，他的程序性能很低，但主机负载也很低。AWR报告发现有条语句的IO非常大，应该是主要问题所在。检查等待事件，发现大量的read by other session，这个事件就是一位的buffer busy wait，也就是说其它的session在等待某个session将要读取的相同数据块从disk放到内存中，这个问题通常是低效语句或者热块造成的。所以，问题就显得很简单了，调整那个sql即可，可调优时发现，这条语句单独拿出来在sqlplus里面trace，发现执行计划完全正确，效率很很高，这就奇怪了，AWR里的情况和单独执行的竟然不一样。<div> 开始我以为这是个bug，或者是有不良程序并发造成，后来突然想到10g都是CBO，和以前9i的思考方式已经不一样了，固定语句绑定变量后的执行计划就不变了，会不会是因为单独trace看到的执行计划和程序里面的不一样所致，于是通过v$sql_plan查了下，发现果然不同，索引用错了，所使用的sql如下：</div><div><div>select operation,options,object_name,cost from v$sql_plan where</div><div>(address,hash_value) in (</div><div>select sql_address,hash_value from v$session</div><div>where (p1,p2) in (select p1,p2 from v$session where event='read by other session')</div><div>and event ='db file sequential read')</div></div><div><br></div><div>这里因为导致read by other session是db file sequential read事件，所以直接在条件里面指定了。</div><div><br></div><div>自此问题找到，解决办法就多样了，检查并重新收集统计信息、重建相关索引、用hint或outline（不推荐）等等。导致问题的原因怀疑是之前数据量小的时候执行计划被固定了，以至于后面数据量增大，统计信息更新不及时造成原计划一直未变所致，不过按说自动收集信息后应该更正执行计划才对，这里还是个疑问，因为看不到系统其他情况也就作罢了。</div><div> 总之CBO的问题还是不少，感觉增加了dba的工作量，以前RBO的工作在开发，现在反倒扔给后台了，麻烦。</div><div><br></div><div>另外对于v$sql_plan有个格式化用法：</div><div><span class="Apple-style-span" style="font-family: Arial, Helvetica, sans-serif; line-height: 21px; background-color: rgb(237, 235, 218); ">select '| Operation | PHV/Object Name | Rows | Bytes| Cost |' <br style="word-break: break-all; line-height: normal !important; ">as "Optimizer Plan:" from dual<br style="word-break: break-all; line-height: normal !important; ">union all<br style="word-break: break-all; line-height: normal !important; ">select<br style="word-break: break-all; line-height: normal !important; "> rpad('| '||substr(lpad(' ',1*(depth-1))||operation||<br style="word-break: break-all; line-height: normal !important; "> decode(options, null,'',' '||options), 1, 35), 36, ' ')||'|'||<br style="word-break: break-all; line-height: normal !important; "> rpad(decode(id, 0, '------------- '<br style="word-break: break-all; line-height: normal !important; "> , substr(decode(substr(object_name, 1, 7), 'SYS_LE_', null, object_name)<br style="word-break: break-all; line-height: normal !important; "> ||' ',1, 30)), 31, ' ')||'|'||<br style="word-break: break-all; line-height: normal !important; "> lpad(decode(cardinality,null,' ',<br style="word-break: break-all; line-height: normal !important; "> decode(sign(cardinality-1000), -1, cardinality||' ',<br style="word-break: break-all; line-height: normal !important; "> decode(sign(cardinality-1000000), -1, trunc(cardinality/1000)||'K',<br style="word-break: break-all; line-height: normal !important; "> decode(sign(cardinality-1000000000), -1, trunc(cardinality/1000000)||'M',<br style="word-break: break-all; line-height: normal !important; "> trunc(cardinality/1000000000)||'G')))), 7, ' ') || '|' ||<br style="word-break: break-all; line-height: normal !important; "> lpad(decode(bytes,null,' ',<br style="word-break: break-all; line-height: normal !important; "> decode(sign(bytes-1024), -1, bytes||' ',<br style="word-break: break-all; line-height: normal !important; "> decode(sign(bytes-1048576), -1, trunc(bytes/1024)||'K',<br style="word-break: break-all; line-height: normal !important; "> decode(sign(bytes-1073741824), -1, trunc(bytes/1048576)||'M',<br style="word-break: break-all; line-height: normal !important; "> trunc(bytes/1073741824)||'G')))), 6, ' ') || '|' ||<br style="word-break: break-all; line-height: normal !important; "> lpad(decode(cost,null,' ', decode(sign(cost-10000000), -1, cost||' ',<br style="word-break: break-all; line-height: normal !important; "> decode(sign(cost-1000000000), -1, trunc(cost/1000000)||'M',<br style="word-break: break-all; line-height: normal !important; "> trunc(cost/1000000000)||'G'))), 8, ' ') || '|' as "Explain plan"<br style="word-break: break-all; line-height: normal !important; ">from v$sql_plan sp<br style="word-break: break-all; line-height: normal !important; ">where sp.address=$sql_address and sp.hash_value=&SQL_HASH_VALUE;</span></div>
页:
[1]