[20120327]Adaptive Cursor SharingG 的问题
11G的新特性里面Adaptive Cursor Sharing采用新特性来解决PEEKED BIND的问题,但是必须要经过一次执行后,来改变执行计划。
但是如果在一些工具里面,它执行并不是提取全部的信息,而是取一部分就会遭遇执行计划不改变的问题。
如下站点:
http://jonathanlewis.wordpress.com/2012/03/21/acs/
按照讲解是一个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的问题,但是必须要经过一次执行后,来改变执行计划。
但是如果在一些工具里面,它执行并不是提取全部的信息,而是取一部分就会遭遇执行计划不改变的问题。
如下站点:
http://jonathanlewis.wordpress.com/2012/03/21/acs/
按照讲解是一个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/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/267265/viewspace-719598/