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

[20120806]11G SPM的學習3.txt

相關文章