Event 10053 执行计划 绑定变量 Bind peeking

 

相关的准备知识，请参考我的Blog：

Oracle 跟踪事件 set event

http://blog.csdn.net/tianlesoftware/archive/2009/12/13/4977827.aspx

Oracle SQL的硬解析和软解析

http://blog.csdn.net/tianlesoftware/archive/2010/04/08/5458896.aspx

Oracle 绑定变量

http://blog.csdn.net/tianlesoftware/archive/2009/10/17/4678335.aspx

 

在oracle 9i之后引入了bind peeking，在第一次分析的时候，优化器会根据绑定变量来确定执行计划。BIND PEEKING只有当该SQL第一次执行的时候，并且在进行HARD PARSE的时候才进行，第二次调用该SQL，就不会再次进行BIND PEEKING，继续使用上一次产生的执行计划。

我们可以通过隐含的参数来调整数据库默认的bind peeking行为：_OPTIM_PEEK_USER_BINDS。 如果我们想关闭Bind Variable Peeking，我们可以设置该参数为 False 即可。

SQL>alter session set "_optim_peek_user_binds"=false

使用了Bind Var能提高性能主要是因为这样做可以尽量避免不必要的硬分析(Hard Parse)而节约了时间，同时节约了大量的CPU资源。

    当一个Client提交一条Sql给Oracle后，Oracle 首先会对其进行解析(Parse)，然后将解析结果提交给优化器(Optimiser)来进行优化而取得Oracle认为的最优的Query Plan，然后再按照这个最优的Plan来执行这个Sql语句(当然在这之中如果只需要软解析的话会少部分步骤)。

当Oracle接到 Client提交的Sql后会首先在共享池(Shared Pool)里面去查找是否有之前已经解析好的与刚接到的这一个Sql完全相同的Sql(注意这里说的是完全相同，既要求语句上的字符级别的完全相同，又要求涉及的对象也必须完全相同)。当发现有相同的以后解析器就不再对新的Sql在此解析而直接用之前解析好的结果了。这里就节约了解析时间以及解析时候消耗的CPU资源。尤其是在OLTP中运行着的大量的短小Sql，效果就会比较明显了。因为一条两条Sql的时间可能不会有多少感觉，但是当量大了以后就会有比较明显的感觉了。

但是，使用绑定变量的一个缺点是，给出的执行计划并不一定就是SQL在真正应用程序里所使用的执行计划。这时我们就可以通过 event 10053 事件来查看。

 

补充知识：如何在SQL PLUS 中查看执行计划：
1） SQL>EXPLAIN PLAN FOR 你的sql语句;
如 SQL>EXPLAIN PLAN FOR SELECT * FROM EMP WHERE EMPNO=7369;
2）SELECT plan_table_output FROM TABLE(DBMS_XPLAN.DISPLAY('PLAN_TABLE'));

10053 event 对于性能调优是一个很好的辅助工具。它产生的trace文件提供了Oracle如何选择执行计划，为何如此这般得到执行计划的信息。有时对于一个SQL语句，很明显oracle应该使用索引，但是执行计划却没有使用索引。这时10053事件可以提供一些帮助。可以让我们了解为什么没有用索引。

注意: 10053只对CBO有效，而且如果一个sql语句已经解析过，就不会产生新的trace信息。

下面这段引用详细的讲了Event 10053 如何使用，急分几个level。 每个level 所包含的内容。

HOW TO trace the CBO working out the execution path (event 10053)

To start the CBO trace enter the following command:

ALTER SESSION SET EVENTS='10053 trace name context forever, level 1';

Run the SQL that you wanto trace the CBO optimizer on, e.g.

SELECT * 
FROM oe_order_headers_v
WHERE header_id = 999
/
When the query has completed, run the following command to switch the trace off:

ALTER SESSION SET EVENTS '10053 trace name context off';

There appear to 2 levels to the trace:

ALTER SESSION SET EVENTS='10053 trace name context forever, level 1';
ALTER SESSION SET EVENTS='10053 trace name context forever, level 2';

Level 2 is a subset of Level 1 and includes:

Column statistics

Single Access Paths

Join Costs

Table Joins Considered

Join Methods Considered (NL/MS/HA)

but Level 1 is the more detailed of the two; as well as ALL of level 2, it also includes:

Parameters used by the optimizer

Index statistics

要强调的一点，sqlplus中打开autotrace看到的执行计划实际上是用explain plan 命令得到的，explain plan 命令不会进行bind peeking。应该通过v$sql_plan查看SQL的真实的执行计划。

 

 

 

The Execution Plan shows the SQL optimizer's query execution path. Execution Plan output is generated using EXPLAIN PLAN and DBMS_XPLAN.

EXPLAIN PLAN output shows how Oracle runs the SQL statement when the statement was explained. This can differ from the plan during actual execution for a SQL statement, because of differences in the execution environment and explain plan environment

Oracle does not support EXPLAIN PLAN for statements performing implicit type conversion of date bind variables. With bind variables in general, the EXPLAIN PLAN output might not represent the real execution plan.

After the statement has executed, you can display the plan by querying the V$SQL_PLAN view. V$SQL_PLAN contains the execution plan for every statement stored in the cursor cache.