能不能避免sql硬解析

zkbucciarati

Yong Huang 发表于 2012-10-18 00:07
I still don't see how you come to the conclusion that (1) your parse time is high, (2) your first fe ...

感谢回复！
你提供的方法很有效，我已经将测试结果添加到了一楼，请查看。
根据以上结果我觉得可以回答你提出的两个问题。
先回答第二个 (2) your first few parses are hard.根据上面的测试结果，确实表明了前面6次是hard parsing。

然后是第一个 (1) your parse time is high
针对这个问题，我是这么测试的
执行alter system flush buffer_cache，再执行sql，对查询速度基本没有影响，6次之前还是慢，6次之后还是快。
执行alter system flush shared_pool, 再执行sql，本来执行6次后会变快，此时会变慢，重新执行6 次后，再变快。
根据以上现象，buffer cache对速度没影响，shared pool对速度有直接影响。加上根据新的测试结果，软解析的时候sql速度变快，所以我认为该sql的主要执行时间花在了hard parsing上面。

不知道我理解得对不对，请继续发表你的见解，谢谢！

Yong Huang

I think the first few parses are hard because of CBO's feedback control, which you can disable by

alter session set "_optimizer_use_feedback" = false;

Setting optimizer_features_enable to anything before 11.2 (not necessarily 10.2) can of course disable that too. I've seen quite a number of reports and anecdotes about the trouble caused by this 11gR2 new feature. Disabling it is advised in all the cases. Are you sure v$sql_shared_cursor has no column with value 'Y', including use_feedback_stats?

Parse time statistics exist in v$sesstat. Why not check their values? You can compare them with elapsed_time to see the ratio or percentage parsing takes.

zkbucciarati

Yong Huang 发表于 2012-10-19 00:53
I think the first few parses are hard because of CBO's feedback control, which you can disable by

...

你好，问题基本解决，你的分析基本是正确的，非常谢谢！最终的效果是sql执行只产生一个父游标一个子游标，执行计划得到重用。
但是还是有些奇怪的地方，总结如下：

在windows 32bit oracle企业版11.2.0.1.0上面，v$sql_shared_cursor视图是没有use_feedback_stats这个字段的。这是我们的测试系统，我之前的测试都是在这个环境下做的。修改_optimizer_use_feedback=false之后，问题解决，虽然use_feedback_stats这个字段是没有的，这个很奇怪。

在hp ux 64bit oracle企业版11.2.0.2.0上面，v$sql_shared_cursor视图有use_feedback_stats这个字段，这个字段列在了最后面，估计是这个版本才加入的。修改_optimizer_use_feedback=false之后，问题也解决。

Yong Huang

Good to know the problem was indeed caused by CBO's feedback control. That's a feature introduced in 11.2.0.1, judging from the fact that parameters _optimizer_feedback_control and _optimizer_use_feedback both started to appear in x$ksppi in that version. V$sql_shared_cursor in 11.2.0.1 apparently didn't catch up with this new technology. If you really want, you can try the SQL underneath this view, i.e. the SQL shown in the output of this query:

SQL> select view_definition from v$fixed_view_definition where view_name = 'GV$SQL_SHARED_CURSOR';

VIEW_DEFINITION
--------------------------------------------------------------------------------------------------
select inst_id, sql_id, kglhdpar, kglhdadr, childno, decode(bitand(bitvector, POWER(2,0)), POWER(2
...

in 11.2.0.1 when you logon as sys. The only problem is that you need to get that SQL from 11.2.0.1 first and find the fixed table (fixed view) to be x$kkscs because 11.2.0.2 (and above) has the long definition of the view cut off. But the bit in bitvector column representing use_feedback_stats is not in 11.2.0.1. You just have to guess that bit by comparing this long SQL in 11.2.0.1 and 11.2.0.2.

Actually, there's an easier way. Run this command on your 11.2.0.2 server:

strings $ORACLE_HOME/bin/oracle | grep x\$kkscs

You should be able to see the full SQL that queries x$kkscs. Run this SQL in your 11.2.0.1 database when you logon as sys. You still need to find the bit that corresponds to use_feedback_stats.

But if you don't want to pursue further, that's fine. The problem has been fixed anyway. I was just trying to give you a way to check use_feedback_stats in 11.2.0.1.

xiaoxiao_biti

学习了哇

sundog315

很好的案例，充分体现了Yong Huang版主经验的丰富程度。

anlinew

看起来Cardinality Feedback bug不少呢
Bug 12557401 - Suboptimal plan on 2nd execution caused by cardinality feedback
Bug 14673831 - Suboptimal plan due to bad cardinality feedback with multiple similar schemas and SQL
Bug 9465425 - New cursors generated after cardinality feedback
Bug 8608703 - SubOptimal Execution Plan created by Cardinality Feedback

mckingstar

11.2的版本，在优化器中有一些新特性，比如：在使用绑定变量时，之前的版本，是不能根据绑定变量的实际值，来调整选择best的plan的，也就是一次产生，后面都用这个，尽管有时候会因为一些绑定值，选择比较烂的执行计划。
新版本中，会在选择执行计划的时候，偷窥绑定变量的值，决定选择比较优化的执行计划，或者重新解析。
所以，想要重现，是比较困难。
6个子游标，这个还是可以的。至于执行计划，能否保存，这个貌似没有，profile也只是影响优化器的选择倾向，并不能解决你的问题。

Yong Huang

> 至于执行计划，能否保存，这个貌似没有

I may have lost context in this part of the discussion. But if you use dbms_shared_pool.keep to keep the child cursor you want (based on the plan for this child, and of course relatively low buffer_gets per execution), does it count as 保存执行计划 ?

jssqyh

也就是说，这条sql的主要瓶颈在paring sql上面，时间主要花在了生成子游标上面，游标能够重用的话，真正取数据是非常快的，由于我们报表中有多条类似的sql，总的执行时间就比较长了。

用set autotrace查看执行计划和统计信息，发现recursive calls这个指标比较异常。第一次执行sql时，recursive calls为13529，之后5次执行，recursive calls都为512，第7次执行开始，recursive calls都为0。

目前解决该问题是暂时将optimizer_features_enable设为10.2.0.5。