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