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

上一篇介绍了一些基本的概念,那么回顾一下SPM的三大“招数”:那就是SQL plan baseline capture,SQL plan baseline selection,SQL plan baseline evolution。我们首先来看SQL plan baseline capture。这里有多种方式可以帮助我们把执行计划保存到repository,做为基线。
1.自动捕捉

这里首先要设置参数Optimizer_capture_sql_plan_baselines为true,开启这个功能之后,当sql第一次进行解析的时候,生成的计划并不会被加入到BaseLine里面。而只是做一次标记(Marked)。添加到sqllog$日志当中(放到日志中主要是为了标识在下再一次运行该语句的时候知道这是一条重复的语句)。而这个计划最终会被使用。而第二次再次运行这条SQL的时候,该计划才会被加入到BaseLine里面。并标记成Accecpted。下面我们来做几个测试。

SQL> create table t as select * from dba_objects; 

Table created. 

SQL> variable x number; 

SQL> exec : x:=1001 

PL/SQL procedure successfully completed. 

SQL> show parameter optimizer_capture_sql_plan_baselines 

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
optimizer_capture_sql_plan_baselines boolean     FALSE 

SQL> alter session set optimizer_capture_sql_plan_baselines=true; 

Session altered. 

SQL> set autotrace traceonly 

SQL> select * from t where object_id=:x; 

SQL> select * from sqllog$; 

SIGNATURE     BATCH#
---------- ----------
1.0826E+19          1 

SQL> select SQL_HANDLE,PLAN_NAME from dba_sql_plan_baselines; 

no rows selected

这里可以看到第一次执行完这条语句后,只是把语句放到sqllog$里面,并没有放到baseline里面。再次运行该语句。

SQL> select * from t where object_id=:x; 

SQL> select SQL_HANDLE,PLAN_NAME,ENABLED,ACCEPTED,FIXED,OPTIMIZER_COST from dba_sql_plan_baselines; 

SQL_HANDLE                     PLAN_NAME                      ENA ACC FIX OPTIMIZER_COST
------------------------------ ------------------------------ --- --- --- --------------
SQL_963f1e89b2c86438           SQL_PLAN_9cgsyj6tcht1s94ecae5c YES YES NO             240

那么我们在改变一下执行计划,我们创建一个索引,我们看看会发生什么。

SQL> create index idx_t on t(object_id); 

Index created. 

SQL> select * from t where object_id=:x; 

SQL> select SQL_HANDLE,PLAN_NAME,ENABLED,ACCEPTED,FIXED,OPTIMIZER_COST from dba_sql_plan_baselines; 

SQL_HANDLE                     PLAN_NAME                      ENA ACC FIX OPTIMIZER_COST
------------------------------ ------------------------------ --- --- --- --------------
SQL_963f1e89b2c86438           SQL_PLAN_9cgsyj6tcht1s880b6daf YES NO  NO               2
SQL_963f1e89b2c86438           SQL_PLAN_9cgsyj6tcht1s94ecae5c YES YES NO             240

这里我们看到,同样的SQL_HANDLE,新产生的计划进入到了SQL Plan History里面,但是它并没有进入到SQL BaseLine里面。因为它还是处于No Accecpted的状态。虽然它的成本更低。所以这一次的SQL语句还是只能走以前被Accecpted的计划。新的计划必须得到验证才能够被Accecpted.

SQL>  explain plan for select * from t where object_id=:x; 

Explained. 

SQL> select * from table(dbms_xplan.display()); 

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 1601196873 

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

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

PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------- 

   1 - filter("OBJECT_ID"=TO_NUMBER(:X)) 

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

17 rows selected.

这里得说明一下,参数Optimizer_use_sql_plan_baselines默认设置是True.这个参数控制了优化器对基线的使用。如果设置成False,优化器就不会考虑基线。也就是SPM的开关。试验一下,关闭该参数后,可以看到优化器选择使用了索引。

SQL> alter session set Optimizer_use_sql_plan_baselines=false; 

Session altered. 

SQL> explain plan for select * from t where object_id=:x; 

Explained.

SQL> select * from table(dbms_xplan.display()); 

PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 1594971208 

-------------------------------------------------------------------------------------
| Id  | Operation                   | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |       |     1 |    98 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T     |     1 |    98 |     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"=TO_NUMBER(:X)) 

14 rows selected.

2.批量导入
批量导入一般比较适合在数据库升级、或者是新应用部署上线的时候,批量主要有四种方式:从SQL Tuning Set导入(这需要安装调优包)、使用游标缓存加载当前执行计划。从中转表中加载执行计划。从存储大纲批量加载。
a.从SQL Tuning Set导入
SQL Tuning Set是Oracle提供的一个SQL调优工具,需要安装调优包。我们可以从STS中捕获一些SQL的执行计划,将其导入做成基线。我们可以通过EM界面或者是DBMS_SPM.LOAD_PLANS_FROM_SQLSET函数来实现。再一次这些语句执行的时候,将会使用基线中的计划。STS批量加载是保证数据库升级后,执行计划保障不变的一种方法。例如从Oracle10g升级到Oracle11g一般是下面四个步骤:

  • 在Oracle Database 10g中创建一个包括每个SQL执行计划的STS。
  • 将STS导入到中转表中,然后生成平面文件。
  • 将平面文件导入到11g当中,然后还原成STS。
  • 使用EM或者是DBMS_SPM.LOAD_PLANS_FROM_SQLSET函数将执行计划导入到Baseline。然后就可以保证执行计划不变。

如果11g的数据库的优化器产生了不同的执行计划,那么只会把这个计划添加的SQL Plan History当中等待Verify,而不会立马执行该计划。只有该计划的性能等同或者优于我们的10g的计划,才会将其标记为Accecped的计划。不过我们一般在升级的过程中都是用SPA来做性能保障的。下面我们就来做个简单测试,把本地的执行计划通过dbms_sqltune.load_sqlset先导入到STS,因为环境的原因我就不把STS迁移到另外一台数据库了,我直接把本地STS数据导入到BaseLine里面。

SQL> connect t1/t1
Connected.

SQL> exec sys.dbms_sqltune.create_sqlset(sqlset_name => 'TEST_STS', sqlset_owner => 'T1'); 

PL/SQL procedure successfully completed.

SQL> variable x number; 

SQL> exec : x:=1001;

PL/SQL procedure successfully completed.

SQL> select * from t where object_id=:x; 

SQL> select sql_id,hash_value from v$sql where sql_text ='select * from t where object_id=:x';

SQL_ID        HASH_VALUE
------------- ----------
9r2gykqt5j1ss 2992146200

SQL> DECLARE 
  2  cur sys_refcursor; 
  3  BEGIN 
  4  open cur for 
  5  select value(p) from table(dbms_sqltune.select_cursor_cache('sql_id = ''9r2gykqt5j1ss''')) p; 
  6  dbms_sqltune.load_sqlset('TEST_STS', cur); 
  7  close cur;
  8  END; 
  9  /

PL/SQL procedure successfully completed.

SQL> select sql_id, substr(sql_text,1, 15) text  from dba_sqlset_statements where sqlset_name = 'TEST_STS' order by sql_id;

SQL_ID        TEXT
------------- --------------------------------------------------------------------------------
9r2gykqt5j1ss select * from t

SQL> SELECT * FROM table (DBMS_XPLAN.DISPLAY_SQLSET( 'TEST_STS','9r2gykqt5j1ss'));

PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL Tuning Set Name: TEST_STS
SQL Tuning Set Owner: T1
SQL_ID: 9r2gykqt5j1ss
SQL Text: select * from t where object_id=:x
--------------------------------------------------------------------------------

Plan hash value: 1594971208

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

16 rows selected.

SQL> select * from dba_sql_plan_baselines;

no rows selected

SQL> declare 
  2  x int; 
  3  begin 
  4  x := dbms_spm.load_plans_from_sqlset ( 
  5  sqlset_name => 'TEST_STS', 
  6  sqlset_owner => 'T1', 
  7  fixed => 'YES', 
  8  enabled => 'YES'); 
  9  end; 
 10  /

PL/SQL procedure successfully completed.

SQL> select SQL_HANDLE,PLAN_NAME,ENABLED,ACCEPTED,FIXED from dba_sql_plan_baselines;

SQL_HANDLE                     PLAN_NAME                      ENA ACC FIX
------------------------------ ------------------------------ --- --- ---
SQL_963f1e89b2c86438           SQL_PLAN_9cgsyj6tcht1s880b6daf YES YES YES

b.使用游标缓存加载当前执行计划
每一条执行过后的SQL语句,都会以Shared Cursor保存在Library Cache里面。我们可以使用DBMS_SPM.LOAD_PLANS_FROM_CURSOR或者EM将这些语句的PLAN导入到SQL Managment Base中做成基线。下面是一个演示:

SQL> select SQL_HANDLE,PLAN_NAME,ENABLED,ACCEPTED,FIXED,OPTIMIZER_COST from dba_sql_plan_baselines; 

no rows selected 

SQL> variable x number; 

SQL> exec : x:=1001 

PL/SQL procedure successfully completed. 

SQL> select * from t where object_id=:x; 

SQL> select sql_id,PLAN_HASH_VALUE,id,OPERATION from v$sql_plan where PLAN_HASH_VALUE='1594971208'; 

SQL_ID        PLAN_HASH_VALUE         ID OPERATION
------------- --------------- ---------- ------------------------------------------------------------
9r2gykqt5j1ss      1594971208          0 SELECT STATEMENT
9r2gykqt5j1ss      1594971208          1 TABLE ACCESS
9r2gykqt5j1ss      1594971208          2 INDEX 

SQL> declare
  2  y int;
  3  begin
  4  y:=dbms_spm.LOAD_PLANS_FROM_CURSOR_CACHE(SQL_ID=>'9r2gykqt5j1ss',PLAN_HASH_VALUE=>'1594971208');
  5  end;
  6  / 

PL/SQL procedure successfully completed. 

SQL> select SQL_HANDLE,PLAN_NAME,ENABLED,ACCEPTED,FIXED,OPTIMIZER_COST from dba_sql_plan_baselines; 

SQL_HANDLE                     PLAN_NAME                      ENA ACC FIX OPTIMIZER_COST
------------------------------ ------------------------------ --- --- --- --------------
SQL_963f1e89b2c86438           SQL_PLAN_9cgsyj6tcht1s880b6daf YES YES NO               2

不是说批量加载吗?这只是一条一条的加载啊。如果你这么想就错了,其实我们可以写个循环来添加。很方便的。这里就不演示了。
c.从中转表中加载执行计划
这招一般都用在升级或者迁移上面。比如你的一部分应用在一个测试系统上测试,现在你要迁移到生产系统上。需要在测试系统的数据库中将BaseLine打包成到一个表里面,然后在生成平面文件,或者是exp和expdp,然后在导入到生产系统中的一个表里面,最后在还原到Baseline里面。那我们来演示一下这个步骤:

SQL> select SQL_HANDLE,PLAN_NAME,ENABLED,ACCEPTED,FIXED,OPTIMIZER_COST from dba_sql_plan_baselines; 

SQL_HANDLE                     PLAN_NAME                      ENA ACC FIX OPTIMIZER_COST
------------------------------ ------------------------------ --- --- --- --------------
SQL_963f1e89b2c86438           SQL_PLAN_9cgsyj6tcht1s880b6daf YES YES NO               2

SQL> exec dbms_spm.create_stgtab_baseline(table_name => 'BASELINE_STG',table_owner => 'SYS');
BEGIN dbms_spm.create_stgtab_baseline(table_name => 'BASELINE_STG',table_owner => 'SYS'); END; 

*
ERROR at line 1:
ORA-19381: cannot create staging table in SYS schema
ORA-06512: at "SYS.DBMS_SMB", line 313
ORA-06512: at "SYS.DBMS_SPM", line 3003
ORA-06512: at line 1 

SQL> exec dbms_spm.create_stgtab_baseline(table_name => 'BASELINE_STG',table_owner => 'T1'); 

PL/SQL procedure successfully completed.

SQL> desc BASELINE_STG
 Name                                                                                               Null?    Type
 -------------------------------------------------------------------------------------------------- -------- -----------------------------
 VERSION                                                                                                     NUMBER
 SIGNATURE                                                                                                   NUMBER
 SQL_HANDLE                                                                                                  VARCHAR2(30)
 OBJ_NAME                                                                                                    VARCHAR2(30)
 OBJ_TYPE                                                                                                    VARCHAR2(30)
 PLAN_ID                                                                                                     NUMBER
 SQL_TEXT                                                                                                    CLOB
 CREATOR                                                                                                     VARCHAR2(30)
 ORIGIN                                                                                                      VARCHAR2(30)
 DESCRIPTION                                                                                                 VARCHAR2(500)
 DB_VERSION                                                                                                  VARCHAR2(64)
 CREATED                                                                                                     TIMESTAMP(6)
 LAST_MODIFIED                                                                                               TIMESTAMP(6)
 LAST_EXECUTED                                                                                               TIMESTAMP(6)
 LAST_VERIFIED                                                                                               TIMESTAMP(6)
 STATUS                                                                                                      NUMBER
 OPTIMIZER_COST                                                                                              NUMBER
 MODULE                                                                                                      VARCHAR2(64)
 ACTION                                                                                                      VARCHAR2(64)
 EXECUTIONS                                                                                                  NUMBER
 ELAPSED_TIME                                                                                                NUMBER
 CPU_TIME                                                                                                    NUMBER
 BUFFER_GETS                                                                                                 NUMBER
 DISK_READS                                                                                                  NUMBER
 DIRECT_WRITES                                                                                               NUMBER
 ROWS_PROCESSED                                                                                              NUMBER
 FETCHES                                                                                                     NUMBER
 END_OF_FETCH_COUNT                                                                                          NUMBER
 CATEGORY                                                                                                    VARCHAR2(30)
 SQLFLAGS                                                                                                    NUMBER
 TASK_ID                                                                                                     NUMBER
 TASK_EXEC_NAME                                                                                              VARCHAR2(30)
 TASK_OBJ_ID                                                                                                 NUMBER
 TASK_FND_ID                                                                                                 NUMBER
 TASK_REC_ID                                                                                                 NUMBER
 INUSE_FEATURES                                                                                              NUMBER
 PARSE_CPU_TIME                                                                                              NUMBER
 PRIORITY                                                                                                    NUMBER
 OPTIMIZER_ENV                                                                                               RAW(2000)
 BIND_DATA                                                                                                   RAW(2000)
 PARSING_SCHEMA_NAME                                                                                         VARCHAR2(30)
 COMP_DATA                                                                                                   CLOB

这里要注意一点,中转表是不能创建到SYS用户下面的。创建完了表之后,我们需要把Baseline数据填到表里面。这一步骤我开10046跟一下。

SQL> alter session set events '10046 trace name context forever,level 12';

SQL> declare
  2  y int;
  3  begin
  4  y:=dbms_spm.pack_stgtab_baseline(TABLE_NAME=>'BASELINE_STG',TABLE_OWNER=>'T1');
  5  end;
  6  / 

PL/SQL procedure successfully completed.

SQL> alter session set events '10046 trace name context off';

通过10046的跟踪,得到下列语句。可以看到我们的数据来源于这几个基表sql$,sqlobj$auxdata,sqlobj$,sql$text,sqlobj$data。

SELECT *  FROM ( SELECT
              sa.signature,
              st.sql_handle,
              so.name obj_name,
              decode(sa.obj_type, 1,'SQL_PROFILE',       decode(sa.obj_type, 2,'SQL_PLAN_BASELINE',       decode(sa.obj_type, 3,'SQL_PATCH',         null))) obj_type,    sa.plan_id,
              st.sql_text,
              sa.creator,
              dbms_smb_internal.map_origin_base_to_view(sa.origin, sa.obj_type)
                                     origin,
              sa.description,
              sa.version db_version,
              sa.created,
              sa.last_modified,
              so.last_executed,
              sa.last_verified,
              so.flags status,
              sa.optimizer_cost,
              sa.module,
              sa.action,
              sa.executions,
              sa.elapsed_time,
              sa.cpu_time,
              sa.buffer_gets,
              sa.disk_reads,
              sa.direct_writes,
              sa.rows_processed,
              sa.fetches,
              sa.end_of_fetch_count,
              sa.category,
              s.flags sqlflags,
              sa.task_id,
              sa.task_exec_name,
              sa.task_obj_id,
              sa.task_fnd_id,
              sa.task_rec_id,
              s.inuse_features,
              sa.parse_cpu_time,
              sa.priority,
              sa.optimizer_env,
              sa.bind_data,
              sa.parsing_schema_name,
              sd.comp_data
            FROM
              sql$ s,
              sqlobj$auxdata sa,
              sqlobj$ so,
              sql$text st,
              sqlobj$data sd
            WHERE
              sa.signature = s.signature and
              sa.signature = so.signature and
              sa.category = so.category and
              sa.obj_type = so.obj_type and
              sa.plan_id = so.plan_id and
              sa.signature = st.signature and
              sa.signature = sd.signature and
              sa.category = sd.category and
              sa.obj_type = sd.obj_type and
              sa.plan_id = sd.plan_id) v  WHERE obj_name is not null and :1 is not null  and category is not null and :1 is not null  and :1 is null  and obj_type = :1  and sql_text is not null and :1 is not n
ull  and :1 is null  and :1 is null  and :1 is null  and :1 is null

然后我们就可以使用expdp的方式把数据导出出来,然后传到另外一套数据库上面,在导入。

SQL> select * from dba_sql_plan_baselines; 

no rows selected

SQL> declare
  2  y int;
  3  begin
  4  y:=dbms_spm.UNPACK_STGTAB_BASELINE(TABLE_NAME=>'BASELINE_STG',TABLE_OWNER=>'T1');
  5  end;
  6  / 

PL/SQL procedure successfully completed.
SQL> select count(1) from dba_sql_plan_baselines; 

  COUNT(1)
----------
         1

d.从存储大纲批量加载
这个功能主要是应用在,假设我们现在仍然使用outline,我们可以把outline的东西迁移到SPM里面。这一功能我们将在下一篇里面介绍。

这篇介绍了如何把执行计划加入到SPM的一些常用方法,分别适合各类场景。其实我们在日常的使用过程中,一般会用到从cursor中进行加载。能够很好的自行控制。而自动捕捉的功能一般都是自动关闭的。当开启自动捕捉后,很多不需要的SQL(比如临时查询一些数据)都会被纳入进去。这是我们不想看到的。而在迁移的过程中,我们大多会使用中转表来进行迁移。跨版本升级则可以利用STS。可见Oracle的SPM提供的包还是很全面的。

分享到: 更多

Post a Comment

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