[20120327]Adaptive Cursor Sharing 的問題
[20120327]Adaptive Cursor SharingG 的問題
11G的新特性裡面Adaptive Cursor Sharing採用新特性來解決PEEKED BIND的問題，但是必須要經過一次執行後，來改變執行計劃。
但是如果在一些工具裡面，它執行並不是提取全部的資訊，而是取一部分就會遭遇執行計劃不改變的問題。
如下站點：
按照講解是一個BUG，但是沒有給出例子，自己寫個例子測試如下：
1.建立測試環境:
2.執行sql語句,為了更好測試，我在toad執行，:a :=1 :
3.在toad下執行相同的sql語句,但是帶入的引數是1001:
--執行多次，引數1001，可以發現執行計劃並沒有產生子游標。
再次執行在toad下執行相同的sql語句,但是帶入的引數是1001:
--可以發現並沒有產生新的子游標.換句話講Adaptive Cursor Sharing並沒有起作用。
現在toad開啟auto trace(具體操作是在sql編輯器裡面，點選右鍵選擇auto trace),主要目的是這樣可以提取全部查詢資訊。
再次執行以上語句。引數1001
再次執行以上語句。引數1001
--可以發現現在出現了新的子游標。
--可以發現child_number=0 的子游標IS_SHAREABLE='N'.
5.如果要避免這樣的問題，最好的方法是使用提示/*+ bind_aware */.
11G的新特性裡面Adaptive Cursor Sharing採用新特性來解決PEEKED BIND的問題，但是必須要經過一次執行後，來改變執行計劃。
但是如果在一些工具裡面，它執行並不是提取全部的資訊，而是取一部分就會遭遇執行計劃不改變的問題。
如下站點：
按照講解是一個BUG，但是沒有給出例子，自己寫個例子測試如下：
1.建立測試環境:
SQL> select * from v$version ;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
PL/SQL Release 11.2.0.1.0 - Production
CORE 11.2.0.1.0 Production
TNS for Linux: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production
SQL> create table t1 as select rownum id,'test' name from dual connect by level<=1000;
Table created.
SQL> insert into t1 select 1001 id,'book' name from dual connect by level<=1000;
1000 rows created.
SQL> commit;
Commit complete.
SQL> create index i_t1_id on t1(id);
Index created.
SQL>exec SYS.DBMS_STATS.GATHER_TABLE_STATS (user,'T1',Method_Opt => 'FOR ALL COLUMNS SIZE 254 ');
SQL> column data_type format a20
SQL> select table_name,column_name,data_type,histogram from dba_tab_cols where wner='SCOTT' and table_name='T1';
TABLE_NAME COLUMN_NAME DATA_TYPE HISTOGRAM
------------------------------ ------------------------------ -------------------- ---------------
T1 ID NUMBER HEIGHT BALANCED
T1 NAME CHAR FREQUENCY
SQL> alter system flush shared_pool;
2.執行sql語句,為了更好測試，我在toad執行，:a :=1 :
select /*+ testme */ * from t1 where id = :a;
ID NAME
---------- ----
1 test
column is_bind_sensitive format a20
column is_bind_aware format a20
column is_shareable format a20
SELECT sql_id, sql_text, child_number, plan_hash_value, executions, is_bind_sensitive, is_bind_aware, is_shareable
FROM v$sql
WHERE sql_text LIKE '%testme%' AND sql_text NOT LIKE '%sql_text%'
SQL_ID SQL_TEXT CHILD_NUMBER PLAN_HASH_VALUE EXECUTIONS IS_BIND_SENSITIVE IS_BIND_AWARE IS_SHAREABLE
------------- ------------------------------ ------------ --------------- ---------- -------------------- -------------------- --------------------
9rx9cq6x20guk select /*+ testme */ * from t1 0 1111474805 1 Y N Y
where id = :a
--知道sql_id=9rx9cq6x20guk.
SQL> select * from table(dbms_xplan.display_cursor('9rx9cq6x20guk',NULL,'allstats last peeked_binds'));
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID 9rx9cq6x20guk, child number 0
-------------------------------------
select /*+ testme */ * from t1 where id = :a
Plan hash value: 1111474805
--------------------------------------------------------
| Id | Operation | Name | E-Rows |
--------------------------------------------------------
| 0 | SELECT STATEMENT | | |
| 1 | TABLE ACCESS BY INDEX ROWID| T1 | 1 |
|* 2 | INDEX RANGE SCAN | I_T1_ID | 1 |
--------------------------------------------------------
Peeked Binds (identified by position):
--------------------------------------
1 - (NUMBER): 1
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("ID"=:A)
Note
-----
- Warning: basic plan statistics not available. These are only collected when:
* hint 'gather_plan_statistics' is used for the statement or
* parameter 'statistics_level' is set to 'ALL', at session or system level
30 rows selected.
3.在toad下執行相同的sql語句,但是帶入的引數是1001:
SQL_ID SQL_TEXT CHILD_NUMBER PLAN_HASH_VALUE EXECUTIONS IS_BIND_SENSITIVE IS_BIND_AWARE IS_SHAREABLE
------------- ------------------------------ ------------ --------------- ---------- -------------------- -------------------- --------------------
9rx9cq6x20guk select /*+ testme */ * from t1 0 1111474805 2 Y N Y
where id = :a
--執行多次，引數1001，可以發現執行計劃並沒有產生子游標。
再次執行在toad下執行相同的sql語句,但是帶入的引數是1001:
SQL_ID SQL_TEXT CHILD_NUMBER PLAN_HASH_VALUE EXECUTIONS IS_BIND_SENSITIVE IS_BIND_AWARE IS_SHAREABLE
------------- ------------------------------ ------------ --------------- ---------- -------------------- -------------------- --------------------
9rx9cq6x20guk select /*+ testme */ * from t1 0 1111474805 4 Y N Y
where id = :a
--可以發現並沒有產生新的子游標.換句話講Adaptive Cursor Sharing並沒有起作用。
現在toad開啟auto trace(具體操作是在sql編輯器裡面，點選右鍵選擇auto trace),主要目的是這樣可以提取全部查詢資訊。
再次執行以上語句。引數1001
SQL_ID SQL_TEXT CHILD_NUMBER PLAN_HASH_VALUE EXECUTIONS IS_BIND_SENSITIVE IS_BIND_AWARE IS_SHAREABLE
------------- ------------------------------ ------------ --------------- ---------- -------------------- -------------------- --------------------
9rx9cq6x20guk select /*+ testme */ * from t1 0 1111474805 5 Y N Y
where id = :a
再次執行以上語句。引數1001
SQL_ID SQL_TEXT CHILD_NUMBER PLAN_HASH_VALUE EXECUTIONS IS_BIND_SENSITIVE IS_BIND_AWARE IS_SHAREABLE
------------- ------------------------------ ------------ --------------- ---------- -------------------- -------------------- --------------------
9rx9cq6x20guk select /*+ testme */ * from t1 0 1111474805 5 Y N Y
where id = :a
9rx9cq6x20guk select /*+ testme */ * from t1 1 3617692013 1 Y Y Y
where id = :a
--可以發現現在出現了新的子游標。
SQL> select * from table(dbms_xplan.display_cursor('9rx9cq6x20guk',NULL,'allstats last peeked_binds'));PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID 9rx9cq6x20guk, child number 0
-------------------------------------
select /*+ testme */ * from t1 where id = :a
Plan hash value: 1111474805
--------------------------------------------------------
| Id | Operation | Name | E-Rows |
--------------------------------------------------------
| 0 | SELECT STATEMENT | | |
| 1 | TABLE ACCESS BY INDEX ROWID| T1 | 1 |
|* 2 | INDEX RANGE SCAN | I_T1_ID | 1 |
--------------------------------------------------------
Peeked Binds (identified by position):
--------------------------------------
1 - (NUMBER): 1
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("ID"=:A)Note
-----
- Warning: basic plan statistics not available. These are only collected when:
* hint 'gather_plan_statistics' is used for the statement or
* parameter 'statistics_level' is set to 'ALL', at session or system level
SQL_ID 9rx9cq6x20guk, child number 1
-------------------------------------
select /*+ testme */ * from t1 where id = :a
Plan hash value: 3617692013
-------------------------------------------
| Id | Operation | Name | E-Rows |
-------------------------------------------
| 0 | SELECT STATEMENT | | |
|* 1 | TABLE ACCESS FULL| T1 | 1012 |
-------------------------------------------
Peeked Binds (identified by position):
--------------------------------------
1 - (NUMBER): 1001
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("ID"=:A)Note
-----
- Warning: basic plan statistics not available. These are only collected when:
* hint 'gather_plan_statistics' is used for the statement or
* parameter 'statistics_level' is set to 'ALL', at session or system level
59 rows selected.
4.再次執行以上語句。引數1
SELECT sql_id, sql_text, child_number, plan_hash_value, executions, is_bind_sensitive, is_bind_aware, is_shareable
FROM v$sql
WHERE sql_text LIKE '%testme%' AND sql_text NOT LIKE '%sql_text%';
SQL_ID SQL_TEXT CHILD_NUMBER PLAN_HASH_VALUE EXECUTIONS IS_BIND_SENSITIVE IS_BIND_AWARE IS_SHAREABLE
------------- ------------------------------ ------------ --------------- ---------- -------------------- -------------------- --------------------
9rx9cq6x20guk select /*+ testme */ * from t1 0 1111474805 5 Y N N
where id = :a
9rx9cq6x20guk select /*+ testme */ * from t1 1 3617692013 1 Y Y Y
where id = :a
9rx9cq6x20guk select /*+ testme */ * from t1 2 1111474805 1 Y Y Y
where id = :a
--可以發現child_number=0 的子游標IS_SHAREABLE='N'.
5.如果要避免這樣的問題，最好的方法是使用提示/*+ bind_aware */.
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/267265/viewspace-719598/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- Adaptive Cursor SharingAPT
- Adaptive Cursor Sharing（第二篇）APT
- Adaptive Cursor Sharing（第三篇）APT
- Adaptive Cursor Sharing（第四篇）APT
- Adaptive Cursor Sharing（第五篇）APT
- 11G Adaptive Cursor Sharing(ACS)的研究APT
- Adaptive Cursor Sharing （第一篇）APT
- Adaptive Cursor Sharing: Worked Example (Doc ID 836256.1)APT
- 11G Adaptive Cursor Sharing(ACS)的研究 (轉)APT
- 驗證11g的adaptive cursor sharing功能APT
- 優化器革命之- adaptive cursor sharing （三）優化APT
- 優化器革命之- adaptive cursor sharing （二）優化APT
- 11GR1 新特性 adaptive cursor sharingAPT
- 最佳化器革命之- adaptive cursor sharing （三）APT
- 最佳化器革命之- adaptive cursor sharing （四）APT
- Oracle自適應共享遊標——Adaptive Cursor Sharing（上）OracleAPT
- Oracle自適應共享遊標——Adaptive Cursor Sharing（下）OracleAPT
- Oracle 11g SQL效能的新特性（一）- Adaptive Cursor SharingOracleSQLAPT
- 11gR2 新特性之（一）Adaptive Cursor Sharing（ACS）APT
- 揭秘自適應遊標共享技術(Adaptive Cursor Sharing)APT
- 繫結變數的正確使用，bind peeking，Adaptive Cursor Sharing變數APT
- 揭祕自適應遊標共享技術(Adaptive Cursor Sharing)APT
- Postgresql的CURSOR SHARINGSQL
- [20221227]Adaptive Cursor Sharing & 直方圖.txtAPT直方圖
- 繫結變數之自適應遊標共享(Adaptive Cursor Sharing)變數APT
- 11G Adaptive Cursor Sharing(ACS)自適應遊標測試APT
- 【DBA】Oracle 11g 針對SQL效能的新特性（一）- Adaptive Cursor SharingOracleSQLAPT
- V$SQL_遊標、adaptive cursor sharing、bind peeking、直方圖的概念SQLAPT直方圖
- oracle11g中SQL最佳化新特性之Adaptive Cursor Sharing (ACS)OracleSQLAPT
- [20120220]Adaptive Cursor Sharing 與hints.txtAPT
- CURSOR_SHARING=SIMILARMILA
- oracle cursor_sharing [轉]Oracle
- Oracle 11g 中 cursor_sharing 設定為SIMILAR 導致的問題OracleMILA
- Oracle 的 cursor_sharing引數Oracle
- 關於 cursor_sharing = similarMILA
- oracle引數-cursor_sharingOracle
- cursor_sharing=similar深度剖析MILA
- cursor_sharing : exact , force , similarMILA