[20130123]spm與sql profile的主要區別在那裡.txt

SPM是11G的新特性，而10g下sql profile一定程度代替store ountlined來穩定執行計劃，oracle為什麼11G下還要推出SPM？
兩者區別主要在那裡呢？

我舉一個例子來說明:

1.建立測試環境:

select * from v$version where rownum<=1;

BANNER

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

Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production

create table t1 as select mod(rownum,100) id ,lpad('x',80,'x') name from dual connect by level<=10000;

insert into t1 values (100,lpad('y',80,'y')) ;

commit ;

create index i_t1_id on t1(id);


exec dbms_stats.gather_table_stats(user,'T1',method_opt=>'for all columns size 1');


create table t2 as select mod(rownum,100) id ,lpad('x',80,'x') name from dual connect by level<=10000;

insert into t2 values (100,lpad('y',80,'y')) ;

commit ;

create index i_t2_id on t2(id);

exec dbms_stats.gather_table_stats(user,'T2',method_opt=>'for all columns size 1');

--我沒有建立直方圖！
--t1,t2資訊一樣，id數值是從0-99均勻分佈，而id=100僅僅1個,理論講上面使用索引效果更加。但是由於索引的聚集因子很大，幾乎接
--近記錄大小。這樣oracle認為資料很離散，執行計劃並不會使用索引 [注意：我並沒有建立直方圖。]

SQL> select clustering_factor  from dba_indexes  where wner=user and index_name in ('I_T1_ID','I_T2_ID');


CLUSTERING_FACTOR

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


--看看一下語句的sql語句:

select * from t1 where id=100;

SQL> select * from t1 where id=100;


        ID NAME

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

       100 yyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyy

           yyyyyyyyyyyyyyyyyyyyyyyyyyyyyy

SQL> @dpc ''


PLAN_TABLE_OUTPUT

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

SQL_ID  c9bya8ajdfsg3, child number 0

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

select * from t1 where id=100


Plan hash value: 3617692013


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

| Id  | Operation         | Name | E-Rows | Cost (%CPU)|

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

|   0 | SELECT STATEMENT  |      |        |    53 (100)|

|*  1 |  TABLE ACCESS FULL| T1   |     99 |    53   (0)|

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


Predicate Information (identified by operation id):

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


   1 - filter("ID"=100)


--可以發現oracle認為資料很離散，但是系統並不知道id=100很少[並沒有建立直方圖]，僅僅1條，按照道理使用索引效率很高，而實際

--的執行計劃是全表掃描.


2.測試使用sql profile來最佳化看看:


DECLARE

  ret_val VARCHAR2(4000);

BEGIN

  ret_val := DBMS_SQLTUNE.CREATE_TUNING_TASK(

                sql_id          => 'c9bya8ajdfsg3',

                plan_hash_value => NULL,

                scope       => 'COMPREHENSIVE',

                time_limit  => 1800,

                task_name   => 'test',

                description => 'study');

  Dbms_Sqltune.EXECUTE_TUNING_TASK('test');

END;


select Dbms_Sqltune.REPORT_TUNING_TASK('test', 'TEXT', 'all') report from dual;


GENERAL INFORMATION SECTION

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

Tuning Task Name   : test

Tuning Task Owner  : SCOTT

Tuning Task ID     : 8871

Workload Type      : Single SQL Statement

Execution Count    : 1

Current Execution  : EXEC_9150

Execution Type     : TUNE SQL

Scope              : COMPREHENSIVE

Time Limit(seconds): 1800

Completion Status  : COMPLETED

Started at         : 01/23/2013 09:45:04

Completed at       : 01/23/2013 09:45:04


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

Schema Name: SCOTT

SQL ID     : c9bya8ajdfsg3

SQL Text   : select * from t1 where id=100


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

FINDINGS SECTION (1 finding)

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


1- SQL Profile Finding (see explain plans section below)

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

  為此語句找到了效能更好的執行計劃。


  Recommendation (estimated benefit: 98.46%)

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

  - 考慮接受推薦的 SQL 概要檔案。

    execute dbms_sqltune.accept_sql_profile(task_name => 'test', task_owner

            => 'SCOTT', replace => TRUE);


  Validation results

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

  已對 SQL profile 進行測試, 方法為執行其計劃和原始計劃並測量與計劃相對應的執行統計資訊。如果其中一個計劃執行在很短的時間內就完成,

  則另一計劃可能只執行了一部分。


                           Original Plan  With SQL Profile  % Improved

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

  Completion Status:            COMPLETE          COMPLETE

  Elapsed Time(us):                 832                72      91.34 %

  CPU Time(us):                     899               100      88.87 %

  User I/O Time(us):                  0                 0

  Buffer Gets:                      131                 2      98.47 %

  Physical Read Requests:             0                 0

  Physical Write Requests:            0                 0

  Physical Read Bytes:                0                 0

  Physical Write Bytes:               0                 0

  Rows Processed:                     1                 1

  Fetches:                            1                 1

  Executions:                         1                 1


  Notes

  -----

  1. original plan 已首先執行以預熱緩衝區快取記憶體。

  2. original plan 的統計資訊是後面的 9 執行的平均值。

  3. SQL profile plan 已首先執行以預熱緩衝區快取記憶體。

  4. the SQL profile plan 的統計資訊是後面的 9 執行的平均值。


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

EXPLAIN PLANS SECTION

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


1- Original

-----------

Plan hash value: 3617692013

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

| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |

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

|   0 | SELECT STATEMENT  |      |    99 |  8316 |    53   (0)| 00:00:01 |

|*  1 |  TABLE ACCESS FULL| T1   |    99 |  8316 |    53   (0)| 00:00:01 |

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

Query Block Name / Object Alias (identified by operation id):

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

   1 - SEL$1 / T1@SEL$1

Predicate Information (identified by operation id):

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

   1 - filter("ID"=100)

Column Projection Information (identified by operation id):

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

   1 - "ID"[NUMBER,22], "T1"."NAME"[VARCHAR2,80]


2- Original With Adjusted Cost

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

Plan hash value: 3617692013

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

| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |

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

|   0 | SELECT STATEMENT  |      |     1 |    84 |    53   (0)| 00:00:01 |

|*  1 |  TABLE ACCESS FULL| T1   |     1 |    84 |    53   (0)| 00:00:01 |

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

Query Block Name / Object Alias (identified by operation id):

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

   1 - SEL$1 / T1@SEL$1

Predicate Information (identified by operation id):

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

   1 - filter("ID"=100)

Column Projection Information (identified by operation id):

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

   1 - "ID"[NUMBER,22], "T1"."NAME"[VARCHAR2,80]


3- Using SQL Profile

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

Plan hash value: 1111474805

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

| Id  | Operation                   | Name    | Rows  | Bytes | Cost (%CPU)| Time     |

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

|   0 | SELECT STATEMENT            |         |     1 |    84 |     2   (0)| 00:00:01 |

|   1 |  TABLE ACCESS BY INDEX ROWID| T1      |     1 |    84 |     2   (0)| 00:00:01 |

|*  2 |   INDEX RANGE SCAN          | I_T1_ID |     1 |       |     1   (0)| 00:00:01 |

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

Query Block Name / Object Alias (identified by operation id):

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

   1 - SEL$1 / T1@SEL$1

   2 - SEL$1 / T1@SEL$1

Predicate Information (identified by operation id):

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

   2 - access("ID"=100)

Column Projection Information (identified by operation id):

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

   1 - "ID"[NUMBER,22], "T1"."NAME"[VARCHAR2,80]

   2 - "T1".ROWID[ROWID,10], "ID"[NUMBER,22]


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


--可以發現提示使用索引效果更加,執行如下來穩定計劃.

execute dbms_sqltune.accept_sql_profile(task_name => 'test', task_owner=> 'SCOTT', replace => TRUE);


SQL> column signature format 999999999999999999999

SQL> set serveroutput on

SQL> exec print_table('select * from dba_sql_profiles');

NAME                          : SYS_SQLPROF_013c6516f3f90000

CATEGORY                      : DEFAULT

SIGNATURE                     : 10770288213799319469

SQL_TEXT                      : select * from t1 where id=100

CREATED                       : 2013-01-23 09:47:48.000000

LAST_MODIFIED                 : 2013-01-23 09:47:48.000000

DESCRIPTION                   :

TYPE                          : MANUAL

STATUS                        : ENABLED

FORCE_MATCHING                : NO

TASK_ID                       : 8871

TASK_EXEC_NAME                : EXEC_9150

TASK_OBJ_ID                   : 1

TASK_FND_ID                   : 1

TASK_REC_ID                   : 1

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


--查詢獲得sql profile 的hint。10G使用如下命令。


SELECT attr_val

FROM sys.sqlprof$ p, sys.sqlprof$attr a

WHERE p.sp_name = 'SYS_SQLPROF_013c6516f3f90000'

AND p.signature = a.signature

AND p.category = a.category;


--注意11GR2查詢如下:


SELECT EXTRACTVALUE (VALUE (h), '.') AS hint

  FROM SYS.sqlobj$data od, SYS.sqlobj$ so, TABLE (XMLSEQUENCE (EXTRACT (XMLTYPE (od.comp_data), '/outline_data/hint'))) h

 WHERE so.NAME = 'SYS_SQLPROF_013c6516f3f90000'

   AND so.signature = od.signature

   AND so.CATEGORY = od.CATEGORY

   AND so.obj_type = od.obj_type

   AND so.plan_id = od.plan_id;


HINT

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

OPT_ESTIMATE(@"SEL$1", TABLE, "T1"@"SEL$1", SCALE_ROWS=0.0100989901)

OPT_ESTIMATE(@"SEL$1", INDEX_SCAN, "T1"@"SEL$1", "I_T1_ID", SCALE_ROWS=0.0100989901)

OPTIMIZER_FEATURES_ENABLE(default)

--可以發現sql profile實際上使用的提示如上。SCALE_ROWS=0.0100989901,表T1的總記錄10001條，id不同的鍵值是0-100,有101個。
--如果執行如下：

select /*+ gather_plan_statistics

OPT_ESTIMATE(@"SEL$1", TABLE, "T1"@"SEL$1", SCALE_ROWS=0.0100989901)

OPT_ESTIMATE(@"SEL$1", INDEX_SCAN, "T1"@"SEL$1", "I_T1_ID", SCALE_ROWS=0.0100989901) */

* from t1 where id=42;


SQL> @dpc


PLAN_TABLE_OUTPUT

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

SQL_ID  9aytbsm99q6d9, child number 0

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

select /*+ gather_plan_statistics OPT_ESTIMATE(@"SEL$1", TABLE,

"T1"@"SEL$1", SCALE_ROWS=0.0100989901) OPT_ESTIMATE(@"SEL$1",

INDEX_SCAN, "T1"@"SEL$1", "I_T1_ID", SCALE_ROWS=0.0100989901) */ * from

t1 where id=42

Plan hash value: 1111474805

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

| Id  | Operation                   | Name    | Starts | E-Rows | Cost (%CPU)| A-Rows |   A-Time   | Buffers |

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

|   0 | SELECT STATEMENT            |         |      1 |        |     2 (100)|    100 |00:00:00.01 |     103 |

|   1 |  TABLE ACCESS BY INDEX ROWID| T1      |      1 |      1 |     2   (0)|    100 |00:00:00.01 |     103 |

|*  2 |   INDEX RANGE SCAN          | I_T1_ID |      1 |      1 |     1   (0)|    100 |00:00:00.01 |       3 |

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

Predicate Information (identified by operation id):

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

   2 - access("ID"=42)

--不加提示看看：

select /*+ gather_plan_statistics */ * from t1 where id=42;

SQL> @dpc


PLAN_TABLE_OUTPUT

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

SQL_ID  7utu76jbuat60, child number 0

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

select /*+ gather_plan_statistics */ * from t1 where id=42

Plan hash value: 3617692013

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

| Id  | Operation         | Name | Starts | E-Rows | Cost (%CPU)| A-Rows |   A-Time   | Buffers |

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

|   0 | SELECT STATEMENT  |      |      1 |        |    53 (100)|    100 |00:00:00.01 |     147 |

|*  1 |  TABLE ACCESS FULL| T1   |      1 |     99 |    53   (0)|    100 |00:00:00.01 |     147 |

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

Predicate Information (identified by operation id):

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

   1 - filter("ID"=42)

--可以明顯看出提示控制了記錄返回的行數。

3.再使用SPM看看：

SQL> alter session set optimizer_capture_sql_plan_baselines=true ;

SQL> select * from t2 where id = 100 ;

SQL> select * from t2 where id = 100 ;

SQL> alter session set optimizer_capture_sql_plan_baselines=false ;


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


SQL_HANDLE                     PLAN_NAME                      SQL_TEXT                                                 ENA ACC FIX ORIGIN                       SIGNATURE

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

SYS_SQL_a69226d598cc47a0       SQL_PLAN_ad4j6uqccsjx0b860bcf2 select * from t2 where id = 100                          YES YES NO  AUTO-CAPTURE      12002698655729207200


SQL> select * from t2 where id = 100 ;


        ID NAME

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

       100 yyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyy

           yyyyyyyyyyyyyyyyyyyyyyyyyyyyyy


SQL> @dpc


PLAN_TABLE_OUTPUT

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

SQL_ID  0n657fnzb9fm0, child number 1

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

select * from t2 where id = 100

Plan hash value: 1513984157

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

| Id  | Operation         | Name | E-Rows | Cost (%CPU)|

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

|   0 | SELECT STATEMENT  |      |        |    53 (100)|

|*  1 |  TABLE ACCESS FULL| T2   |     99 |    53   (0)|

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

Predicate Information (identified by operation id):

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

   1 - filter("ID"=100)

--執行計劃是全表掃描，並沒有選擇索引。SPM更多的是穩定計劃。它並不知道有很好的執行計劃。
--抽取提示看看:

SQL> EXEC dbms_spm.create_stgtab_baseline('stage1');


SQL> variable v_basenum number ;

SQL> EXEC :v_basenum := dbms_spm.pack_stgtab_baseline('stage1', sql_handle => 'SYS_SQL_a69226d598cc47a0');

SQL> column comp_data format a100

SQL> select comp_data from stage1 where sql_handle='SYS_SQL_a69226d598cc47a0';


COMP_DATA

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

1")]]><

![CDATA[OPTIMIZER_FEATURES_ENABLE('11.2.0.1')]]>


SQL> SELECT EXTRACTVALUE (VALUE (h), '.') AS hint

  FROM stage1, TABLE (XMLSEQUENCE (EXTRACT (XMLTYPE (stage1.comp_data), '/outline_data/hint'))) h

 WHERE sql_handle = 'SYS_SQL_a69226d598cc47a0';


HINT

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

FULL(@"SEL$1" "T2"@"SEL$1")

OUTLINE_LEAF(@"SEL$1")

ALL_ROWS

DB_VERSION('11.2.0.1')

OPTIMIZER_FEATURES_ENABLE('11.2.0.1')

IGNORE_OPTIM_EMBEDDED_HINTS


6 rows selected.

--可以發現SPM記錄的提示是執行計劃，而sql profile記錄的提示記錄的返回資訊。並沒有記錄執行計劃。

4.這樣按照上面的提示,如果改變T1表的相關統計資訊，執行計劃一樣會變化：

SQL> select  num_rows from dba_tables where table_name='T1';


  NUM_ROWS

----------


SQL> exec dbms_stats.set_table_stats(ownname=>user,tabname=>'T1',numrows=>1000001);

PL/SQL procedure successfully completed.

--修改統計資訊，行記錄增加100倍。


SQL> alter system flush shared_pool;

System altered.


SQL> select * from t1 where id=100;

        ID NAME

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

       100 yyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyy

           yyyyyyyyyyyyyyyyyyyyyyyyyyyyyy

SQL> @dpc


PLAN_TABLE_OUTPUT

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

SQL_ID  c9bya8ajdfsg3, child number 0

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

select * from t1 where id=100


Plan hash value: 3617692013


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

| Id  | Operation         | Name | E-Rows | Cost (%CPU)|

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

|   0 | SELECT STATEMENT  |      |        |    74 (100)|

|*  1 |  TABLE ACCESS FULL| T1   |    100 |    74  (29)|

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

Predicate Information (identified by operation id):

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

   1 - filter("ID"=100)

Note

-----

   - SQL profile SYS_SQLPROF_013c6516f3f90000 used for this statement

   - 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


25 rows selected.

--可以發現執行計劃變成了全表掃描。

5.總結：
透過對比，可以發現SPM以及sql profile都是為了更好的維護執行計劃，但是兩者控制的方式，設計理念完全不同。其中的細節留給
大家體會。

[20130123]spm與sql profile的主要區別在那裡.txt

相關文章