[20120806]11G SPM的学习3.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.理解FIXED=yes|NO

 如果FIXED=NO,如果存在多个执行计划(当然enabled=yes,aceepted=yes),oracle应该选择cosing最小的那个.
如果存在多个计划并且仅仅1个FIXED=yes,oracle将使用这个计划.
如果存在多个计划并且仅仅多个FIXED=yes,oracle将使用costing最小的执行计划.

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

exec :v_report := DBMS_SPM.EVOLVE_SQL_PLAN_BASELINE(sql_handle => :v_sql_handle);

exec :v_basenum := DBMS_SPM.ALTER_SQL_PLAN_BASELINE (sql_handle =>:v_sql_handle',plan_name=> :v_plan_name_full,attribute_name=>'FIXED',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 YES NO  AUTO-CAPTURE      11842951964357158308

SYS_SQL_a45a9e109f85e5a4       SQL_PLAN_a8qny22gsbtd494ecae5c YES YES YES 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

--执行计划并没有选择索引扫描。把走index的基线也设置fixed=yes

exec :v_basenum := DBMS_SPM.ALTER_SQL_PLAN_BASELINE (sql_handle =>:v_sql_handle,plan_name=> :v_plan_name_index,attribute_name=>'FIXED',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 YES YES AUTO-CAPTURE      11842951964357158308

SYS_SQL_a45a9e109f85e5a4       SQL_PLAN_a8qny22gsbtd494ecae5c YES YES YES 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: 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

-----

   - SQL plan baseline SQL_PLAN_a8qny22gsbtd40893a4b2 used for this statement

2.如果任何一个计划fixed=yes,oracle将停止捕获和evolv新的计划.把使用索引的执行计划fixed=NO,accepted=NO

--    Also, if any of the plan is marked as fixed, oracle will stop capturing and evolving new plans.To simulate the test, lets

--again mark the plan(using index) as fixed=no and accepted=no.

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

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

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 YES YES YES MANUAL-LOAD       11842951964357158308

--视图dba_sql_plan_baselines里面仅仅一条记录。

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

select * from t where id=:x;

exec :v_basenum := DBMS_SPM.ALTER_SQL_PLAN_BASELINE (sql_handle =>:v_sql_handle,plan_name=> :v_plan_name_full,attribute_name=>'FIXED',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 YES MANUAL-LOAD       11842951964357158308

variable v_report clob;

exec :v_report := DBMS_SPM.EVOLVE_SQL_PLAN_BASELINE(sql_handle => :v_sql_handle);

SQL> print :v_report

V_REPORT

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

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

                        Evolve SQL Plan Baseline Report

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

Inputs:

-------

  SQL_HANDLE = SYS_SQL_a45a9e109f85e5a4

  PLAN_NAME  =

  TIME_LIMIT = DBMS_SPM.AUTO_LIMIT

  VERIFY     = YES

  COMMIT     = YES

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

                                 Report Summary

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

There were no SQL plan baselines that required processing.

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 YES MANUAL-LOAD       11842951964357158308

--走索引的基线accepted依旧是NO。

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

exec :v_report := DBMS_SPM.EVOLVE_SQL_PLAN_BASELINE(sql_handle => :v_sql_handle);

exec :v_basenum := DBMS_SPM.ALTER_SQL_PLAN_BASELINE (sql_handle =>:v_sql_handle,plan_name=> :v_plan_name_full,attribute_name=>'FIXED',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 YES NO  AUTO-CAPTURE      11842951964357158308

SYS_SQL_a45a9e109f85e5a4       SQL_PLAN_a8qny22gsbtd494ecae5c YES YES YES 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

--可以发现执行计划选择的全表扫描。并没有选择index range scan。

Here's the quick summary,
1.  ENABLED=YES (For any of the plan) :- Oracle will start capturing new plans for those queries.
2. Enabled=NO :-  Than plan won't we used.
3.  Accepted=Yes (Any one Plan) :- That plan will be used for execution
4.  Accepted=YES (For Multiple plans):- Any one plan can be used if fixed=no for all plans.
5. Accepted=No: Plan won't be used.
6.  FIXED=YES (for only one plan) Only that plan will be used and oracle will stop capturing/evolving new plans.
7.  FIXED=YES (for many plans) Oracle will chose execution plan only from that pool.