[20120807]11G SPM的学习5.txt--第3方优化

继续上面的学习:

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 , cast(dbms_random.string('a',6 ) as varchar2(10)) name from dual connect by level <=1e5;

--分析表

BEGIN

  SYS.DBMS_STATS.GATHER_TABLE_STATS (

      OwnName        => user

     ,TabName        => 'T'

    ,Estimate_Percent  => NULL

    ,Method_Opt        => 'FOR ALL COLUMNS SIZE 1 '

    ,Degree            => 4

    ,Cascade           => TRUE

    ,No_Invalidate     => TRUE);

END;

/

create index i_t_id on t(id);

1.假设程序中的代码写成如下：
select /*+ full(t) */ * from t where id=:1;

10g下可以使用sql profiles来改变与稳定计划，而使用11G SPM如何实现呢？

建立测试例子：

declare

begin

execute immediate 'select /*+ full(t) */ * from t where id=:1' using 100;

end;

/

--我这里使用绑定变量是:1,不能像sqlplus那样定义变量，必须写一个匿名过程。

SQL> select * from table(dbms_xplan.display_cursor('1nhrs4ytd2fvq',NULL,'ALLSTATS LAST PEEKED_BINDS cost'));

PLAN_TABLE_OUTPUT

------------------------------------------------------------------------------------------------------------

SQL_ID  1nhrs4ytd2fvq, child number 0

-------------------------------------

select /*+ full(t) */ * from t where id=:1

Plan hash value: 1601196873

--------------------------------------------------------

| Id  | Operation         | Name | E-Rows | Cost (%CPU)|

--------------------------------------------------------

|   0 | SELECT STATEMENT  |      |        |    69 (100)|

|*  1 |  TABLE ACCESS FULL| T    |      1 |    69   (2)|

--------------------------------------------------------

Peeked Binds (identified by position):

--------------------------------------

   1 - (NUMBER): 100

Predicate Information (identified by operation id):

---------------------------------------------------

   1 - filter("ID"=:1)

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

--可以发现这样的语句由于使用了提示，作为第3方无法修改sql代码？如何实现呢？

var v_basenum number;

exec :v_basenum:=dbms_spm.load_plans_from_cursor_cache(sql_id => '1nhrs4ytd2fvq',plan_hash_value =>1601196873  );

SQL> column signature format 9999999999999999999999

SQL> column  FORCE_MATCHING_SIGNATURE like SIGNATURE

SQL> column  EXACT_MATCHING_SIGNATURE like SIGNATURE

SQL> select sql_handle, plan_name, enabled, accepted,fixed,autopurge,origin,signature from dba_sql_plan_baselines ;

SQL_HANDLE                     PLAN_NAME                      ENA ACC FIX AUT ORIGIN                       SIGNATURE

------------------------------ ------------------------------ --- --- --- --- -------------- -----------------------

SYS_SQL_2a9db9f54b3cfa0c       SQL_PLAN_2p7dtyp5mtyhc94ecae5c YES YES NO  YES MANUAL-LOAD        3070814984116959756

SQL> column sql_text format a60

SQL> SELECT sql_id, sql_text, sql_plan_baseline, exact_matching_signature, force_matching_signature  FROM v$sql WHERE sql_id = '1nhrs4ytd2fvq';

SQL_ID        SQL_TEXT                                                     SQL_PLAN_BASELINE              EXACT_MATCHING_SIGNATURE FORCE_MATCHING_SIGNATURE

------------- ------------------------------------------------------------ ------------------------------ ------------------------ ------------------------

1nhrs4ytd2fvq select /*+ full(t) */ * from t where id=:1                                                       3070814984116959756      3070814984116959756

SQL> variable x  number;

SQL> exec :x := 100;

PL/SQL procedure successfully completed.

SQL> select * from t where  id=:x;

        ID NAME

---------- ----------

       100 lAGCjs

SQL> @dpc

PLAN_TABLE_OUTPUT

---------------------------------------------------------------------------------

SQL_ID  89fczrmrx25j6, child number 0

-------------------------------------

select * from t where  id=:x

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): 100

Predicate Information (identified by operation id):

---------------------------------------------------

   2 - access("ID"=:X)

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> SELECT sql_id, sql_text, sql_plan_baseline, exact_matching_signature, force_matching_signature  FROM v$sql WHERE sql_id in ('1nhrs4ytd2fvq','89fczrmrx25j6');

SQL_ID        SQL_TEXT                                                     SQL_PLAN_BASELINE              EXACT_MATCHING_SIGNATURE FORCE_MATCHING_SIGNATURE

------------- ------------------------------------------------------------ ------------------------------ ------------------------ ------------------------

89fczrmrx25j6 select * from t where  id=:x                                                                    11842951964357158308     11842951964357158308

1nhrs4ytd2fvq select /*+ full(t) */ * from t where id=:1                                                       3070814984116959756      3070814984116959756

exec :v_num:=dbms_spm.load_plans_from_cursor_cache(sql_id => '89fczrmrx25j6',plan_hash_value => 4153437776 ,sql_handle => 'SYS_SQL_2a9db9f54b3cfa0c');

SQL> exec :v_basenum:=dbms_spm.load_plans_from_cursor_cache(sql_id => '89fczrmrx25j6',plan_hash_value => 4153437776 ,sql_handle => 'SYS_SQL_2a9db9f54b3cfa0c');

PL/SQL procedure successfully completed.

SQL> select sql_handle, plan_name, enabled, accepted,fixed,autopurge,origin,signature from dba_sql_plan_baselines ;

SQL_HANDLE                     PLAN_NAME                      ENA ACC FIX AUT ORIGIN                       SIGNATURE

------------------------------ ------------------------------ --- --- --- --- -------------- -----------------------

SYS_SQL_2a9db9f54b3cfa0c       SQL_PLAN_2p7dtyp5mtyhc0893a4b2 YES YES NO  YES MANUAL-LOAD        3070814984116959756

SYS_SQL_2a9db9f54b3cfa0c       SQL_PLAN_2p7dtyp5mtyhc94ecae5c YES YES NO  YES MANUAL-LOAD        3070814984116959756

SQL> alter system flush shared_pool;

declare

begin

execute immediate 'select /*+ full(t) */ * from t where id=:1' using 100;

end;

/

SQL> select * from table(dbms_xplan.display_cursor('1nhrs4ytd2fvq',NULL,'ALLSTATS LAST PEEKED_BINDS cost'));

PLAN_TABLE_OUTPUT

--------------------------------------------------------------------------------------------------------------

SQL_ID  1nhrs4ytd2fvq, child number 0

-------------------------------------

select /*+ full(t) */ * from t where id=:1

Plan hash value: 1601196873

--------------------------------------------------------

| Id  | Operation         | Name | E-Rows | Cost (%CPU)|

--------------------------------------------------------

|   0 | SELECT STATEMENT  |      |        |    69 (100)|

|*  1 |  TABLE ACCESS FULL| T    |      1 |    69   (2)|

--------------------------------------------------------

Peeked Binds (identified by position):

--------------------------------------

   1 - (NUMBER): 100

Predicate Information (identified by operation id):

---------------------------------------------------

   1 - filter("ID"=:1)

Note

-----

   - SQL plan baseline SQL_PLAN_2p7dtyp5mtyhc94ecae5c used for this statement

--执行计划并没有选择索引！

SQL> exec :v_basenum := dbms_spm.alter_sql_plan_baseline(sql_handle=>'SYS_SQL_2a9db9f54b3cfa0c',plan_name=>'SQL_PLAN_2p7dtyp5mtyhc94ecae5c',attribute_name=>'enabled',attribute_value=>'NO');

PL/SQL procedure successfully completed.

SQL> select sql_handle, plan_name, enabled, accepted,fixed,autopurge,origin,signature from dba_sql_plan_baselines ;

SQL_HANDLE                     PLAN_NAME                      ENA ACC FIX AUT ORIGIN                       SIGNATURE

------------------------------ ------------------------------ --- --- --- --- -------------- -----------------------

SYS_SQL_2a9db9f54b3cfa0c       SQL_PLAN_2p7dtyp5mtyhc0893a4b2 YES YES NO  YES MANUAL-LOAD        3070814984116959756

SYS_SQL_2a9db9f54b3cfa0c       SQL_PLAN_2p7dtyp5mtyhc94ecae5c NO  YES NO  YES MANUAL-LOAD        3070814984116959756

alter system flush shared_pool;

declare

begin

execute immediate 'select /*+ full(t) */ * from t where id=:1' using 99;

end;

/

SQL> select * from table(dbms_xplan.display_cursor('1nhrs4ytd2fvq',NULL,'ALLSTATS LAST PEEKED_BINDS cost'));

PLAN_TABLE_OUTPUT

-------------------------------------------------------------------------------------------------------------

SQL_ID  1nhrs4ytd2fvq, child number 0

-------------------------------------

select /*+ full(t) */ * from t where id=:1

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): 100

Predicate Information (identified by operation id):

---------------------------------------------------

   2 - access("ID"=:1)

Note

-----

   - SQL plan baseline SQL_PLAN_2p7dtyp5mtyhc0893a4b2 used for this statement

--可以发现执行计划采用索引范围扫描。