ITPub博客

首页 > Linux操作系统 > Linux操作系统 > [20120220]Adaptive Cursor Sharing 与hints.txt

[20120220]Adaptive Cursor Sharing 与hints.txt

原创 Linux操作系统 作者:lfree 时间:2012-02-21 11:07:49 0 删除 编辑
11G的新特性里面Adaptive Cursor Sharing采用新特性来解决PEEKED BIND的问题,但是必须要经过一次执行后,来改变执行计划。能否绕过这个步骤,选择合理的执行计划呢?从11G开始也提供一个提示BIND_AWARE来绕过这个特性,直接选择合理的执行计划。

例子如下:

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


--建立测试例子:
create table t as select rownum id ,'test' name  from dual connect by level<=1000;
insert into t select 1001,'aaaa' from dual connect by level<=1000;
commit ;
create index i_t_id on t(id);
exec dbms_stats.gather_table_stats(OWNNAME =>user,TABNAME =>'T',Method_Opt=>'FOR COLUMNS id SIZE 254');

SQL> column data_type format a20
SQL> SELECT table_name, column_name, data_type, histogram FROM dba_tab_cols WHERE table_name ='T' ;
TABLE_NAME                     COLUMN_NAME                    DATA_TYPE            HISTOGRAM
------------------------------ ------------------------------ -------------------- ---------------
T                              NAME                           CHAR                 NONE
T                              ID                             NUMBER               FREQUENCY

我建立的表id分布很不均匀。

cat dpc.sql
select * from table(dbms_xplan.display_cursor(NULL,NULL,'ALLSTATS LAST PEEKED_BINDS cost'));

2.开始测试:
SQL> variable a number;
SQL> exec :a := 42;
SQL> select /*+ BIND_AWARE */ * from t where id = :a ;
SQL> @dpc
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID  cx60c99054skh, child number 0
-------------------------------------
select /*+ BIND_AWARE */ * from t where id = :a
Plan hash value: 4153437776
--------------------------------------------------------------------
| Id  | Operation                   | Name   | E-Rows | Cost (%CPU)|
--------------------------------------------------------------------
|   0 | SELECT STATEMENT            |        |        |     2 (100)|
|   1 |  TABLE ACCESS BY INDEX ROWID| T      |      1 |     2   (0)|
|*  2 |   INDEX RANGE SCAN          | I_T_ID |      1 |     1   (0)|
--------------------------------------------------------------------
Peeked Binds (identified by position):
--------------------------------------
   1 - (NUMBER): 42
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.

SQL> exec :a := 1001;
SQL> select /*+ BIND_AWARE */ * from t where id = :a ; 
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID  cx60c99054skh, child number 1
-------------------------------------
select /*+ BIND_AWARE */ * from t where id = :a

Plan hash value: 1601196873

--------------------------------------------------------
| Id  | Operation         | Name | E-Rows | Cost (%CPU)|
--------------------------------------------------------
|   0 | SELECT STATEMENT  |      |        |     4 (100)|
|*  1 |  TABLE ACCESS FULL| T    |   1012 |     4   (0)|
--------------------------------------------------------

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

29 rows selected.
--可以发现执行计划在:a :=1001的情况下选择了全表扫描。

3.看看v$sql视图:

SQL> select sql_id,child_number,plan_hash_value,executions, is_bind_aware, is_shareable from v$sql 
where sql_text like 'select /*+ BIND_AWARE */ %' and sql_id='cx60c99054skh';

SQL_ID        CHILD_NUMBER PLAN_HASH_VALUE EXECUTIONS I I
------------- ------------ --------------- ---------- - -
cx60c99054skh            0      4153437776          1 Y Y
cx60c99054skh            1      1601196873          1 Y Y

--看看是什么原因产生子光标:
SQL> @ share.sql cx60c99054skh
old  15:           and q.sql_id like ''&1''',
new  15:           and q.sql_id like ''cx60c99054skh''',
SQL_TEXT                       = select /*+ BIND_AWARE */ * from t where id = :a
SQL_ID                         = cx60c99054skh
ADDRESS                        = 000000008D298DC0
CHILD_ADDRESS                  = 000000008D29A050
CHILD_NUMBER                   = 0
--------------------------------------------------
SQL_TEXT                       = select /*+ BIND_AWARE */ * from t where id = :a
SQL_ID                         = cx60c99054skh
ADDRESS                        = 000000008D298DC0
CHILD_ADDRESS                  = 000000008F702708
CHILD_NUMBER                   = 1
BIND_EQUIV_FAILURE             = Y
--------------------------------------------------

PL/SQL procedure successfully completed.


来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/267265/viewspace-716844/,如需转载,请注明出处,否则将追究法律责任。

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

注册时间:2008-01-03

  • 博文量
    2323
  • 访问量
    6049252