[20120805]11G SPM的學習2.txt

[20120805]11G SPM的學習2.txt

繼續上面的學習:

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;

/

--建立sql plan baseline,忽略...

create index i_t_id on t(id);

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

--為了後面能操作比較快，我定義如下變數：

variable v_sql_handle      varchar2(30);

variable v_plan_name_full  varchar2(30);

variable v_plan_name_index varchar2(30);

exec :v_sql_handle      := 'SYS_SQL_a45a9e109f85e5a4'

exec :v_plan_name_full  := 'SQL_PLAN_a8qny22gsbtd494ecae5c'

exec :v_plan_name_index := 'SQL_PLAN_a8qny22gsbtd40893a4b2'

1.下面學習與理解enabled, accepted,fixed.
        enabled, accepted,fixed 值僅僅接受yes,no,每個2種變化,結合在一起就2^3=8種變化.如果sql_handle存在多個,這個變化更多,如果存在2個,
8*8=64種變化,要研究各種組合沒有太大的意思,認真理解這3個attribute_name的意思才是關鍵.

        我個人認為,accepted最好理解,就接受與不接受的問題.如果是NO,根本在執行sql不採納這個baseline.僅僅可能作為候選或者evolve.

2.ENABLED=YES|NO.

    當sql語句執行時,oracle檢查baselines是否存在,它將檢查enabled=yes.如果enabled=NO,即使accepted,fixed=yes,oracle也不考慮.
        換一句話,僅僅enabled=yes,oracle在分析與執行sql語句時採用這個執行計劃或者baseline,這個是前提條件.

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

SQL_HANDLE                     PLAN_NAME                      ENA ACC FIX ORIGIN                       SIGNATURE

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

SYS_SQL_a45a9e109f85e5a4       SQL_PLAN_a8qny22gsbtd40893a4b2 YES YES NO  MANUAL-LOAD       11842951964357158308

SYS_SQL_a45a9e109f85e5a4       SQL_PLAN_a8qny22gsbtd494ecae5c YES YES NO  MANUAL-LOAD       11842951964357158308

exec :v_basenum:=DBMS_SPM.ALTER_SQL_PLAN_BASELINE (sql_handle =>:v_sql_handle,plan_name => :v_plan_name_index,attribute_name => 'ENABLED',   attribute_value => 'NO');

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

SQL_HANDLE                     PLAN_NAME                      ENA ACC FIX ORIGIN                       SIGNATURE

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

SYS_SQL_a45a9e109f85e5a4       SQL_PLAN_a8qny22gsbtd40893a4b2 NO  YES NO  MANUAL-LOAD       11842951964357158308

SYS_SQL_a45a9e109f85e5a4       SQL_PLAN_a8qny22gsbtd494ecae5c YES YES NO  MANUAL-LOAD       11842951964357158308

select * from t where id=:x;

SQL> @dpc

PLAN_TABLE_OUTPUT

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

SQL_ID  3yxwagyspybax, child number 1

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

select * from t where id=:x

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"=:X)

Note

-----

   - SQL plan baseline SQL_PLAN_a8qny22gsbtd494ecae5c used for this statement

--由於索引的執行計劃enabled=No,oracle不接受該計劃，選擇全表掃描。

--刪除走索引的基線,以及修改全表掃描的基線enabled=NO

exec :v_basenum := DBMS_SPM.drop_sql_plan_baseline (sql_handle => :v_sql_handle,plan_name  => :v_plan_name_index);

exec :v_basenum:=DBMS_SPM.ALTER_SQL_PLAN_BASELINE (sql_handle =>:v_sql_handle,plan_name => :v_plan_name_full,attribute_name => 'ENABLED',   attribute_value => 'NO');

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

SQL_HANDLE                     PLAN_NAME                      ENA ACC FIX ORIGIN                       SIGNATURE

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

SYS_SQL_a45a9e109f85e5a4       SQL_PLAN_a8qny22gsbtd494ecae5c NO  YES NO  MANUAL-LOAD       11842951964357158308

select * from t where id=:x;

@dpc

SQL> @dpc

PLAN_TABLE_OUTPUT

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

SQL_ID  3yxwagyspybax, child number 1

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

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)

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

SQL_HANDLE                     PLAN_NAME                      ENA ACC FIX ORIGIN                       SIGNATURE

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

SYS_SQL_a45a9e109f85e5a4       SQL_PLAN_a8qny22gsbtd40893a4b2 YES NO  NO  AUTO-CAPTURE      11842951964357158308

SYS_SQL_a45a9e109f85e5a4       SQL_PLAN_a8qny22gsbtd494ecae5c NO  YES NO  MANUAL-LOAD       11842951964357158308

--可以發現即使enabled=NO,一樣可以捕獲baseline。
--修改回來：

exec :v_basenum:=DBMS_SPM.ALTER_SQL_PLAN_BASELINE (sql_handle =>:v_sql_handle,plan_name => :v_plan_name_full,attribute_name => 'ENABLED',   attribute_value => 'YES');

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

SQL_HANDLE                     PLAN_NAME                      ENA ACC FIX ORIGIN                       SIGNATURE

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

SYS_SQL_a45a9e109f85e5a4       SQL_PLAN_a8qny22gsbtd40893a4b2 YES NO  NO  AUTO-CAPTURE      11842951964357158308

SYS_SQL_a45a9e109f85e5a4       SQL_PLAN_a8qny22gsbtd494ecae5c YES YES NO  MANUAL-LOAD       11842951964357158308

要採用baseline,必須是enabled=yes,accepted=yes.
如果enabled=yes,accepted=no,僅僅是候選或者可以EVOLVE.