11G执行计划管理SPM(SQL PLAN MANAGEMENT)的研究(三)

这一篇的主旨是如何把存储大纲outline迁移到SPM中,顺便我们来测试一把存储大纲和SPM谁的优先级更高。那么首先我们要理解一下,为什么我们要迁移存储大纲?是因为11g之后不推荐使用?不推荐使用是因为有以下原因。

1.当我们用存储大纲的时候,我们就只能使用一个执行计划。即使我调整了参数或者统计信息值,它也是一样。而SPM则不一样,在一个深度使用SPM的系统里面,往往会有多个PLAN,当我们调整了参数和统计信息值,它能够灵活的去匹配、选择我们最理想的执行计划。
2.SPM能够允许优化器去发现更好的执行计划,然后经过验证,并将其添加到新的基线里面,然后提供使用。
具体来看一个迁移outline的例子吧。

SQL> show user
USER is "T1"
SQL> create table t as select * from dba_objects;

Table created.

SQL> create outline t_outline for category category_t on select * from t where object_id=1001;

Outline created.

SQL> create index idx_t on t (object_id);

Index created.

SQL> set autotrace traceonly explain;
SQL> select * from t where object_id=1001;

Execution Plan
----------------------------------------------------------
Plan hash value: 1594971208

-------------------------------------------------------------------------------------
| Id  | Operation                   | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |       |     1 |   207 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T     |     1 |   207 |     2   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | IDX_T |     1 |       |     1   (0)| 00:00:01 |
-------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("OBJECT_ID"=1001)

Note
-----
   - dynamic sampling used for this statement (level=2)

SQL> alter session set use_stored_outlines=CATEGORY_T;

Session altered.

SQL> select * from t where object_id=1001;

Execution Plan
----------------------------------------------------------
Plan hash value: 1601196873

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |   888 |   179K|   240   (1)| 00:00:03 |
|*  1 |  TABLE ACCESS FULL| T    |   888 |   179K|   240   (1)| 00:00:03 |
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("OBJECT_ID"=1001)

Note
-----
   - outline "T_OUTLINE" used for this statement

我们的outline已经建完了,下面我们会使用到dbms_spm.migrate_stored_outline来进行迁移。

SQL> select name,sql_text from user_outlines;

NAME                           SQL_TEXT
------------------------------ --------------------------------------------------------------------------------
T_OUTLINE                      select * from t where object_id=1001

SQL> execute :migrate_out:=dbms_spm.migrate_stored_outline(ATTRIBUTE_NAME=>'OUTLINE_NAME',ATTRIBUTE_VALUE=>'T_OUTLINE',fixed=>'NO');

PL/SQL procedure successfully completed.

迁移成功后,我们来查看下我们的Baseline信息。

SQL> select name,sql_text,migrated from user_outlines;

NAME                           SQL_TEXT                                                                         MIGRATED
------------------------------ -------------------------------------------------------------------------------- ------------
T_OUTLINE                      select * from t where object_id=1001                                             MIGRATED

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

SQL_HANDLE                     SQL_TEXT                                 PLAN_NAME                      ORIGIN         ENA ACC
------------------------------ ---------------------------------------- ------------------------------ -------------- --- ---
SQL_8c80d71e722464a7           select * from t where object_id=1001     T_OUTLINE                      STORED-OUTLINE YES YES

outline被迁移后,此时会使用SPM。

SQL> set autotrace traceonly explain;
SQL> select * from t where OBJECT_ID=1001;

Execution Plan
----------------------------------------------------------
Plan hash value: 1601196873

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |   888 |   179K|   240   (1)| 00:00:03 |
|*  1 |  TABLE ACCESS FULL| T    |   888 |   179K|   240   (1)| 00:00:03 |
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("OBJECT_ID"=1001)

Note
-----
   - SQL plan baseline "T_OUTLINE" used for this statement

重新创建新的outline,比比看谁的优先级高。

SQL> create outline t1_outline for category category_t1 on select * from t where object_id=1001;

Outline created.

SQL> alter session set use_stored_outlines=CATEGORY_T1;

Session altered.

SQL> set autotrace traceonly explain;
SQL> select * from t where OBJECT_ID=1001;

Execution Plan
----------------------------------------------------------
Plan hash value: 1594971208

-------------------------------------------------------------------------------------
| Id  | Operation                   | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |       |   888 |   179K|     6   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T     |   888 |   179K|     6   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | IDX_T |   355 |       |     1   (0)| 00:00:01 |
-------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("OBJECT_ID"=1001)

Note
-----
   - outline "T1_OUTLINE" used for this statement

总结:
1.SPM相对于outline来说,具备更多的灵活性。
2.我们可以使用dbms_spm.migrate_stored_outline将outline迁移到Baseline当中。
3.outline的优先级高于SPM。

分享到: 更多

Post a Comment

Your email is never published nor shared. Required fields are marked *