繫結變數引數關閉之後，oracle會如何操作

http://blog.csdn.net/huangchao_sky/article/details/8615148

首先介紹下什麼是繫結變數窺探?

使用SQL首次執行時的值來生成執行計劃。後續再次執行該SQL語句則使用首次執行計劃來執行。

影響的版本：Oracle 9i, Oracle 10g

對於繫結變數列中的特殊值或非均勻分佈列上的繫結變數會造成非高效的執行計劃被選擇並執行。

要注意的是，Bind Peeking只發生在硬分析的時候，即SQL被第一次執行的時候，之後的變數將不會在做peeking。我們可以看出，Bind

peeking並不能最終解決不同謂詞導致選擇不同執行計劃的問題，它只能讓SQL第一次執行的時候，執行計劃選擇更加準確，並不能幫助OLAP

系統解決繫結變數導致執行計劃選擇錯誤的問題。這也是OLAP不應該使用繫結變數的一個原因。在第一次解析SQL時，按照窺探變數的值生成執行計劃，以後這樣的SQL都按照這個執行。隱藏引數_optim_peek_user_binds=true則啟用繫結變數窺探，否則CBO認為統計列是均勻的。

其次對這個問題分析處理思路如下:

這裡我認為首先如果繫結變數窺探關閉了，那麼是否有直方圖對執行計劃就影響不大了，因為oracle都把它作為一個引數帶入，而不會去檢視他的值，是的，連第一次都不檢視。如果不窺探，那麼按照正常的值進行成本分析，然後oracle不考慮具體的值，把這個值作為一個均勻分佈帶入。

如果窺探了，僅僅是第一次會去窺探，試想下，如果每次都窺探，就變成硬解析了，

硬解析對oltp有多大的危害，這裡就不討論，有興趣的朋友可以去看下繫結變數那部分內容：

下面我簡單的用實驗說明下這個問題：

結論1： 繫結變數窺探一定要有直方圖的時候才會起效果，否則一樣窺探沒效果。

SQL> create table daodao as select object_id,object_name from dba_objects;

SQL> create index daodao_index on daodao(object_id);

SQL> insert into daodao select '0' ,0 from dba_objects;

Commit; ----在這裡已經構造出object_id =0 佔了一半的資料量:

SQL>begin

dbms_stats.gather_table_stats(

ownname=> user,

tabname=>'DAODAO',

estimate_percent=>100,

method_opt=>'for all columns size 1'); --注意這裡沒有分析直方圖:

end;

/

SQL> select table_name,column_name,num_distinct,histogram from dba_tab_col_statistics where table_name='DAODAO' and column_name='OBJECT_ID';

TABLE_NAME COLUMN_NAME NUM_DISTINCT HISTOGRAM

------------------------------ ------------------------------ ------------ ---------------

DAODAO OBJECT_ID 50745 NONE

從這裡看出直方圖是沒有分析的:

如果這個時候執行

select * from dba_objects where object_id = 0 ，大家認為會走什麼？

肯定會走索引：

為什麼呢?

SQL> var a number;

SQL> execute :a:=0;

PL/SQL procedure successfully completed.

SQL> select count(object_name) from daodao where object_id=:a;

COUNT(OBJECT_NAME)

------------------

50745

SQL> select * from table(dbms_xplan.display_cursor());

PLAN_TABLE_OUTPUT

---------------------------------------------------------------------------------------------------------------------------------------

SQL_ID cqh9z6m8u1035, child number 0

-------------------------------------

select count(object_name) from daodao where object_id=:a

Plan hash value: 1136156284

---------------------------------------------------------------------------------------------

---------------------------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | | | 2 (100)| |

| 1 | SORT AGGREGATE | | 1 | 16 | | |

| 2 | TABLE ACCESS BY INDEX ROWID| DAODAO | 2 | 32 | 2 (0)| 00:00:01 |

|* 3 | INDEX RANGE SCAN | DAODAO_INDEX | 2 | | 1 (0)| 00:00:01 |

---------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

3 - access("OBJECT_ID"=:A)

20 rows selected.

實際上確實是走索引,主要原因是雖然使用了繫結變數窺探，但是由於沒有直方圖，即使窺探了，也是採用均勻分佈分析，走索引的執行計劃。

那現在加入有直方圖，我們繼續試驗下:

SQL> begin

2 dbms_stats.gather_table_stats(

3 ownname=> user,

4 tabname=>'DAODAO',

5 estimate_percent=>100

6 )

7 ;

8 end;

9 /

PL/SQL procedure successfully completed.

SQL> select table_name,column_name,num_distinct,histogram from dba_tab_col_statistics where table_name='DAODAO' and column_name='OBJECT_ID';

TABLE_NAME COLUMN_NAME NUM_DISTINCT HISTOGRAM

------------------------------ ------------------------------ ------------ ---------------

DAODAO OBJECT_ID 50745 HEIGHT BALANCED

有了直方圖了，然後繼續執行。

SQL> var b number;

SQL> execute :b:=0;

PL/SQL procedure successfully completed.

SQL> select count(object_name) from daodao where object_id=:b;

COUNT(OBJECT_NAME)

------------------

50745

SQL> select * from table(dbms_xplan.display_cursor());

PLAN_TABLE_OUTPUT

----------------------------------------------------------------------------------

SQL_ID 1frj18k6608ur, child number 0

-------------------------------------

select count(object_name) from daodao where object_id=:b

Plan hash value: 3103463029

-----------------------------------------------------------------------------

-----------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | | | 66 (100)| |

| 1 | SORT AGGREGATE | | 1 | 16 | | |

|* 2 | TABLE ACCESS FULL| DAODAO | 50345 | 786K| 66 (5)| 00:00:01 |

-----------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

2 - filter("OBJECT_ID"=:B)

19 rows selected.

這個時候繫結變數窺探才發揮了他的作用，各位看官明白沒？

結論2：關閉了繫結變數窺探，以後每次也是軟解析，而且關閉了繫結變數窺探之後，即使你分析了直方圖還是執行均勻分佈，按照cost計算出成本，然後走oracle認為的執行計劃:

(1) 按照上面的結論，繼續執行：

SQL> alter session set "_optim_peek_user_binds"=false;

Session altered.

SQL> var d number;

SQL> execute :d:=0;

PL/SQL procedure successfully completed.

SQL> select a.value,b.value from v$sysstat a,v$sysstat b where a.name='parse count (hard)' and b.name='parse count (total)';

VALUE VALUE

---------- ----------

21818 577593

SQL> select count(object_name) from daodao where object_id=:d;

COUNT(OBJECT_NAME)

------------------

50745

SQL> select a.value,b.value from v$sysstat a,v$sysstat b where a.name='parse count (hard)' and b.name='parse count (total)';

VALUE VALUE

---------- ----------

21819 577595

--=記錄當前的硬解析次數，實驗環境，就我一個單使用者操作:

SQL> select count(object_name) from daodao where object_id=:d;

COUNT(OBJECT_NAME)

------------------

50745

SQL> select a.value,b.value from v$sysstat a,v$sysstat b where a.name='parse count (hard)' and b.name='parse count (total)';

VALUE VALUE

---------- ----------

21819 577597

硬解析沒有改變，所以關閉了繫結變數，還是使用了軟解析:

這個時候object_id這列是有直方圖的:

SQL> select table_name,column_name,num_distinct,histogram from dba_tab_col_statistics where table_name='DAODAO' and column_name='OBJECT_ID';

TABLE_NAME COLUMN_NAME NUM_DISTINCT HISTOGRAM

------------------------------ ------------------------------ ------------ ---------------

DAODAO OBJECT_ID 50745 HEIGHT BALANCED

這個時候再執行操作：

SQL> select count(object_name) from daodao where object_id=:d;

COUNT(OBJECT_NAME)

------------------

50745

SQL> select * from table(dbms_xplan.display_cursor());

PLAN_TABLE_OUTPUT

----------------------------------------------------------------------------------------------------------------------

SQL_ID 0k0snturu73qm, child number 1

-------------------------------------

select count(object_name) from daodao where object_id=:d

Plan hash value: 1136156284

---------------------------------------------------------------------------------------------

---------------------------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | | | 2 (100)| |

| 1 | SORT AGGREGATE | | 1 | 16 | | |

| 2 | TABLE ACCESS BY INDEX ROWID| DAODAO | 2 | 32 | 2 (0)| 00:00:01 |

|* 3 | INDEX RANGE SCAN | DAODAO_INDEX | 2 | | 1 (0)| 00:00:01 |

---------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

3 - access("OBJECT_ID"=:D)

20 rows selected.

看到沒有，即使是一半數量的0，還是走索引操作，可見這個時候的繫結變數窺探功能關閉了，Oracle把這個0當成是帶入引數了。

總結：

繫結變數窺探功能要在有直方圖的列分析裡面才能起到第一次的窺探作用，

但是如果你是傾斜列，也會導致執行計劃走錯，關閉了繫結變數窺探功能，會導致連第一次窺探都沒有，後續所有的sql依然會軟解析。

繫結變數引數關閉之後，oracle會如何操作

繫結變數引數關閉之後，oracle會如何操作

相關文章