Event 10053 執行計劃 繫結變數 Bind peeking

相關的準備知識，請參考我的Blog：

Oracle 跟蹤事件 set event

 http://space.itpub.net/28673746/viewspace-757325   

Oracle SQL的硬解析和軟解析

http://space.itpub.net/28673746/viewspace-757322

Oracle 繫結變數

http://space.itpub.net/28673746/viewspace-757326

在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.

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/28673746/viewspace-757328/，如需轉載，請註明出處，否則將追究法律責任。