ITPub博客

首页 > Linux操作系统 > Linux操作系统 > [20120111] Dynamic Sampling And Shared Cursors.txt

[20120111] Dynamic Sampling And Shared Cursors.txt

原创 Linux操作系统 作者:lfree 时间:2012-01-12 09:07:29 0 删除 编辑
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 global temporary table t1 (id number not null, name varchar2(20)) on commit preserve rows;
SQL> create index i_t1_id on t1 (id);

1.插入数据会话1:

insert into t1 select rownum id ,'test1' from dual connect by level <=1000;
commit ;

SQL> select * from t1 where id=45;
        ID NAME
---------- --------------------
        45 test1

SQL> @dpc
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID  4g7xbqgs6u665, child number 0
-------------------------------------
select * from t1 where id=45

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 |
--------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("ID"=45)
Note
-----
   - dynamic sampling used for this statement (level=2)
   ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
   - 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
40 rows selected.

2.打开会话2:
SQL> insert into t1 select 45 id ,'test2' from dual connect by level <=1000;
SQL> commit ;

SQL> select * from t1 where id=45;
SQL> @dpc
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID  4g7xbqgs6u665, child number 0
-------------------------------------
select * from t1 where id=45

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 |
--------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("ID"=45)
Note
-----
   - dynamic sampling used for this statement (level=2)
   - 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


40 rows selected.

3.对比发现,执行计划没有变化!

注意设置set autot traceonly可能存在错误!


很明显这样不合理,仅仅第1次分析,以后再调用相同sql语句,执行计划不变.


在会话2修改如下:
SQL> Select * from t1 where id=45
 SQL> @dpc
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID  0amnv2bcps35x, child number 0
-------------------------------------
Select * from t1 where id=45

Plan hash value: 3617692013

-------------------------------------------
| Id  | Operation         | Name | E-Rows |
-------------------------------------------
|   0 | SELECT STATEMENT  |      |        |
|*  1 |  TABLE ACCESS FULL| T1   |   1000 |
-------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter("ID"=45)
Note
-----
   - dynamic sampling used for this statement (level=2)
   - 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
39 rows selected.

正确选择合理的执行计划!

4.很明显像这样情况最佳的方式是每次都分析取样.
如何实现呢?
======================================================================================
http://oracle-randolf.blogspot.com/2011/10/volatile-data-dynamic-sampling-and.html
http://dioncho.wordpress.com/2009/02/06/making-sql-always-hard-parsed-using-row-level-security/

    By adding the following code and RLS policy I can drive Oracle to perform. a re-optimization only in those cases where it
is appropriate. This limits the damage that the general approach does to the Shared Pool by generating potentially numerous
child cursors unconditionally.

======================================================
drop sequence s1;
create sequence s1 cache 1000;

create or replace package pkg_rls_hard_parse is
  function force_hard_parse (in_schema varchar2, in_object varchar2)
      return varchar2;
end pkg_rls_hard_parse;
/

create or replace package body pkg_rls_hard_parse is
  function force_hard_parse (in_schema varchar2, in_object varchar2)
      return varchar2
  is
    s_predicate varchar2(100);
    n_random pls_integer;
  begin
    select s1.nextval || ' = ' || s1.currval into s_predicate from dual;
    return s_predicate;
  end force_hard_parse;
end pkg_rls_hard_parse;
/
exec dbms_rls.add_policy (user, 't1', 'hard_parse_policy', user, 'pkg_rls_hard_parse.force_hard_parse', 'select');  
--exec DBMS_RLS.drop_POLICY (USER, 't1', 'hard_parse_policy');

=================================================================
SQL> @dpc
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID  4g7xbqgs6u665, child number 3
                                   
-------------------------------------
select * from t1 where id=45

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 |
--------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("ID"=45)

不过这样带来另外的问题,会生成一大堆的child cursor.^_^.其它方法我自己也没有想到.


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

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

注册时间:2008-01-03

  • 博文量
    2349
  • 访问量
    6091532