ITPub博客

首页 > Linux操作系统 > Linux操作系统 > [20120327]Adaptive Cursor Sharing 的问题

[20120327]Adaptive Cursor Sharing 的问题

原创 Linux操作系统 作者:lfree 时间:2012-03-27 09:57:15 0 删除 编辑
[20120327]Adaptive Cursor SharingG 的问题

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/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论
熟悉oracle相关技术,擅长sql优化,rman备份与恢复,熟悉linux shell编程。

注册时间:2008-01-03

  • 博文量
    2322
  • 访问量
    6047683