[20130109]SPM与sql profile.txt

[20130109]SPM与sql profile.txt

SPM是11G有的新特性，而sql profile在10g早已经存在。如果在11G，两者都使用的话，执行会选择那个呢？
自己做一个测试来说明情况:


1.建立测试环境:

SQL> select * from v$version where rownum

BANNER

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

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

SQL> create table t as select rownum id ,lpad('x',100,'x') name  from dual connect by level

Table created.

SQL> create unique index i_t_id on t(id);

SQL> exec dbms_stats.gather_table_stats(USER,'T',cascade => true);

SQL> variable a number ;

SQL> exec :a := 100;

SQL> select /*+ full(t) */ * from t where id = :a ;

2.我在执行计划中使用full提示，执行计划一定选择全表扫描:

我建立sql profile,方法如下:

DECLARE

  ret_val VARCHAR2(4000);

BEGIN

  ret_val := DBMS_SQLTUNE.CREATE_TUNING_TASK(

                sql_id          => '89u57qt97ftca',

                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

--提示执行如下：

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

--这样执行就是使用索引。先暂时禁用sql profile。

BEGIN

  DBMS_SQLTUNE.ALTER_SQL_PROFILE (

   name             => 'SYS_SQLPROF_013c1cf4e9920000',

   attribute_name   => 'STATUS',

   value            => 'DISABLED');

END;

3.建立sql baseline：

SQL> alter session set optimizer_capture_sql_plan_baselines=true ;

SQL> select /*+ full(t) */ * from t where id = :a ;

SQL> select /*+ full(t) */ * from t where id = :a ;

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_443d558815eb01e6       SQL_PLAN_48gapj0ayq0g694ecae5c select /*+ full(t) */ * from t where id = :a             YES YES NO  AUTO-CAPTURE       4917180411130085862

--可以发现已经建立了sql baseline。使能sql profile。

BEGIN

  DBMS_SQLTUNE.ALTER_SQL_PROFILE (

   name             => 'SYS_SQLPROF_013c1cf4e9920000',

   attribute_name   => 'STATUS',

   value            => 'ENABLED');

END;

4.两者都存在的情况下，会使用那个呢?

SQL> select /*+ full(t) */ * from t where id = :a ;

        ID NAME

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

       100 xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx

           xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx

SQL> @dpc ''

PLAN_TABLE_OUTPUT

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

SQL_ID  89u57qt97ftca, child number 0

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

select /*+ full(t) */ * from t where id = :a

Plan hash value: 1601196873

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

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

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

|   0 | SELECT STATEMENT  |      |        |    47 (100)|

|*  1 |  TABLE ACCESS FULL| T    |      1 |    47   (0)|

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

Peeked Binds (identified by position):

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

   1 - (NUMBER): 100

Predicate Information (identified by operation id):

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

   1 - filter("ID"=:A)

Note

-----

   - SQL profile SYS_SQLPROF_013c1cf4e9920000 used for this statement

   - SQL plan baseline SQL_PLAN_48gapj0ayq0g694ecae5c 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

31 rows selected.

--可以从Note中发现，提示两者都使用，但是实际上执行计划使用的是全表扫描。也就是讲在两者都存在的情况下，SPM优先。

5.如果关闭SPM，看看：

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_443d558815eb01e6       SQL_PLAN_48gapj0ayq0g694ecae5c select /*+ full(t) */ * from t where id = :a             YES YES NO  AUTO-CAPTURE       4917180411130085862

SYS_SQL_443d558815eb01e6       SQL_PLAN_48gapj0ayq0g6e3e62b6b select /*+ full(t) */ * from t where id = :a             YES NO  NO  AUTO-CAPTURE       4917180411130085862

variable v_basenum number;

exec :v_basenum := dbms_spm.alter_sql_plan_baseline(sql_handle=>'SYS_SQL_443d558815eb01e6',plan_name=>'SQL_PLAN_48gapj0ayq0g694ecae5c',attribute_name=>'ENABLED',attribute_value=>'NO');

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_443d558815eb01e6       SQL_PLAN_48gapj0ayq0g694ecae5c select /*+ full(t) */ * from t where id = :a             NO  YES NO  AUTO-CAPTURE       4917180411130085862

SYS_SQL_443d558815eb01e6       SQL_PLAN_48gapj0ayq0g6e3e62b6b select /*+ full(t) */ * from t where id = :a             YES NO  NO  AUTO-CAPTURE       4917180411130085862

SQL> select /*+ full(t) */ * from t where id = :a ;

        ID NAME

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

       100 xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx

           xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx

SQL> @dpc ''

PLAN_TABLE_OUTPUT

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

SQL_ID  89u57qt97ftca, child number 0

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

select /*+ full(t) */ * from t where id = :a

Plan hash value: 1149237570

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

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

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

|   0 | SELECT STATEMENT            |        |        |     2 (100)|

|   1 |  TABLE ACCESS BY INDEX ROWID| T      |      1 |     2   (0)|

|*  2 |   INDEX UNIQUE SCAN         | I_T_ID |      1 |     1   (0)|

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

Peeked Binds (identified by position):

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

   1 - (NUMBER): 100

Predicate Information (identified by operation id):

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

   2 - access("ID"=:A)

Note

-----

   - SQL profile SYS_SQLPROF_013c1cf4e9920000 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

--再次证明两者存在的情况下SPM优先，而不管那个执行效率高。