上一篇介绍了一些基本的概念，那么回顾一下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