[20120807]11G SPM的學習5.txt--第3方優化

lfree發表於2012-08-07
優化
[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

--可以發現執行計劃採用索引範圍掃描。


來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/267265/viewspace-740217/，如需轉載，請註明出處，否則將追究法律責任。

相關文章