以前写过几个使用该包进行sql优化的博客，再拿出来看，连自己都有点不懂。下面复习一下：
（一）首先演示如何创建单条sql的基线以及出现比基线更好的执行计划时如何替换基线。
创建一张表t3
SQL> create table t3 as select * from dba_users;
Table created.
执行查询命令，观察执行计划。由于没有创建索引，所以现在是全表扫描。
SQL> set autotrace traceonly
SQL> select username from t3 where username like 'S%';
8 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 4161002650
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 8 | 136 | 3 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| T3 | 8 | 136 | 3 (0)| 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("USERNAME" LIKE 'S%')
Note
-----
- dynamic sampling used for this statement (level=2)
Statistics
----------------------------------------------------------
28 recursive calls
0 db block gets
10 consistent gets
1 physical reads
0 redo size
578 bytes sent via SQL*Net to client
419 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
8 rows processed
查看一下该语句的sql_id：
SQL> set autot off
SQL> select sql_id from v$sql where sql_text like 'select username from t3%';
SQL_ID
-------------
6m3xk1x265n6d
根据sql_id，将该语句从缓存中加载到SPM中：
SQL> declare
my_plans pls_integer;
begin
my_plans:=dbms_spm.load_plans_from_cursor_cache(sql_id=>'6m3xk1x265n6d');
end;
6 /
PL/SQL procedure successfully completed.
查看当前的SPM：
SQL> select sql_handle,sql_text,plan_name from dba_sql_plan_baselines t;
SQL_HANDLE SQL_TEXT PLAN_NAME
------------------------------ -------------------------------------------------------------------------------- ------------------------------
SYS_SQL_5e619ab63c95eb1a select * from t where object_id<100 SQL_PLAN_5wscuqsy9busu18bdfb6a
SYS_SQL_8ea0875210675d20 select * from t2 where object_name like 'D%' SQL_PLAN_8x847a886fr90b860bcf2
SYS_SQL_9507a735c4df7898 select /*+full(t)*/* from t where object_id<100 SQL_PLAN_9a1x76r2dyy4s94ecae5c
SYS_SQL_d8d9fa9d93478f2a select username from t3 where username like 'S%' SQL_PLAN_djqgumq9ng3ta2dae97d6
根据sql_name查看SPM具体信息：
SQL> select * from table(dbms_xplan.display_sql_plan_baseline(plan_name=>'SQL_PLAN_djqgumq9ng3ta2dae97d6',format=>'basic'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
SQL handle: SYS_SQL_d8d9fa9d93478f2a
SQL text: select username from t3 where username like 'S%'
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
Plan name: SQL_PLAN_djqgumq9ng3ta2dae97d6 Plan id: 766416854
Enabled: YES Fixed: NO Accepted: YES Origin: MANUAL-LOAD
--------------------------------------------------------------------------------
Plan hash value: 4161002650
----------------------------------
| Id | Operation | Name |
----------------------------------
| 0 | SELECT STATEMENT | |
| 1 | TABLE ACCESS FULL| T3 |
----------------------------------
19 rows selected
创建匹配的索引，使语句select username from t3 where username like 'S%';有更优的执行计划，再次查看该语句：
SQL> create index idx_t3_username on t3(username);
Index created.
SQL> set autot trace
SQL> select username from t3 where username like 'S%';
8 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 4161002650
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 16 | 272 | 3 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| T3 | 16 | 272 | 3 (0)| 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("USERNAME" LIKE 'S%')
Note
-----
- SQL plan baseline "SQL_PLAN_djqgumq9ng3ta2dae97d6" used for this statement
从上面一行看到：由于创建了基线，该语句在产生更优执行计划的情况下仍会使用原基线中的计划。
Statistics
----------------------------------------------------------
615 recursive calls
41 db block gets
182 consistent gets
1 physical reads
18172 redo size
578 bytes sent via SQL*Net to client
419 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
7 sorts (memory)
0 sorts (disk)
8 rows processed
查看当前基线，可看到新增了SQL_PLAN_djqgumq9ng3tada556d36：
SQL> select sql_handle,sql_text,plan_name
2 from dba_sql_plan_baselines t;
SQL_HANDLE SQL_TEXT PLAN_NAME
------------------------------ -------------------------------------------------------------------------------- ------------------------------
SYS_SQL_5e619ab63c95eb1a select * from t where object_id<100 SQL_PLAN_5wscuqsy9busu18bdfb6a
SYS_SQL_8ea0875210675d20 select * from t2 where object_name like 'D%' SQL_PLAN_8x847a886fr90b860bcf2
SYS_SQL_9507a735c4df7898 select /*+full(t)*/* from t where object_id<100 SQL_PLAN_9a1x76r2dyy4s94ecae5c
SYS_SQL_d8d9fa9d93478f2a select username from t3 where username like 'S%' SQL_PLAN_djqgumq9ng3ta2dae97d6
SYS_SQL_d8d9fa9d93478f2a select username from t3 where username like 'S%' SQL_PLAN_djqgumq9ng3tada556d36
这时虽然语句没有使用新的计划，却将更优的计划保留下来，作为accepted的可参考方案。
下面通过dbms_spm.evolve_sql_plan_baseline查看是否有更优的执行计划：
SQL> set autot off
SQL> SET SERVEROUTPUT ON
SET LONG 10000
DECLARE
sql_handle => 'SYS_SQL_d8d9fa9d93478f2a',time_limit=>dbms_spm.no_limit,verify=>'NO');
report clob;
BEGIN
report := DBMS_SPM.EVOLVE_SQL_PLAN_BASELINE(
sql_handle => 'SYS_SQL_d8d9fa9d93478f2a',time_limit=>dbms_spm.no_limit,verify=>'NO');
DBMS_OUTPUT.PUT_LINE(report);
END;
8 /
-------------------------------------------------------------------------------
Evolve SQL Plan Baseline Report
-------------------------------------------------------------------------------
Inputs:
-------
SQL_HANDLE = SYS_SQL_d8d9fa9d93478f2a
PLAN_NAME =
TIME_LIMIT = DBMS_SPM.NO_LIMIT
VERIFY = NO
COMMIT = YES
Plan:
SQL_PLAN_djqgumq9ng3tada556d36
这里指出：oracle发现了更优的执行计划，计划名称是SQL_PLAN_djqgumq9ng3tada556d36
------------------------------------
Plan was changed to an accepted plan.
-------------------------------------------------------------------------------
Report Summary
-------------------------------------------------------------------------------
Number of plans verified: 0
Number of plans accepted: 1
由于此计划还未验证，因此verified是0；由于此计划的cost值更小，是可接受的，所以accepted为1.
PL/SQL procedure successfully completed.
下面根据plan_name查看该计划：
SQL>select * from table(dbms_xplan.display_sql_plan_baseline(plan_name=>'SQL_PLAN_djqgumq9ng3tada556d36',format=>'basic'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
SQL handle: SYS_SQL_d8d9fa9d93478f2a
SQL text: select username from t3 where username like 'S%'
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
Plan name: SQL_PLAN_djqgumq9ng3tada556d36 Plan id: 3663031606
Enabled: YES Fixed: NO Accepted: YES Origin: AUTO-CAPTURE
--------------------------------------------------------------------------------
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 46342040
--------------------------------------------
| Id | Operation | Name |
--------------------------------------------
| 0 | SELECT STATEMENT | |
| 1 | INDEX RANGE SCAN| IDX_T3_USERNAME |
--------------------------------------------
19 rows selected.
这时已经接受了更优的计划SQL_PLAN_djqgumq9ng3tada556d36。
因此再次执行语句时，使用到了索引（即使用SQL_PLAN_djqgumq9ng3tada556d36）：
SQL> set autot trace
SQL> select username from t3 where username like 'S%';
8 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 46342040
------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 8 | 136 | 1 (0)| 00:00:01 |
|* 1 | INDEX RANGE SCAN| IDX_T3_USERNAME | 8 | 136 | 1 (0)| 00:00:01 |
------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("USERNAME" LIKE 'S%')
filter("USERNAME" LIKE 'S%')
Note
-----
- dynamic sampling used for this statement (level=2)
- SQL plan baseline "SQL_PLAN_djqgumq9ng3tada556d36" used for this statement
Statistics
----------------------------------------------------------
22 recursive calls
14 db block gets
18 consistent gets
0 physical reads
4952 redo size
578 bytes sent via SQL*Net to client
419 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
8 rows processed
以上演示了如何创建和更新基线。下面删除基线：
1）删除SQL_PLAN_djqgumq9ng3tada556d36
SQL> SET SERVEROUTPUT ON
SQL> SET LONG 10000
SQL> declare
2 n pls_integer;
3 begin
4 n:=dbms_spm.drop_sql_plan_baseline(plan_name=>'SQL_PLAN_djqgumq9ng3tada556d36');
5 dbms_output.put_line(n);
6 end;
7 /
1
PL/SQL procedure successfully completed
返回的值“1”是删除的SPM数量。
该基线删除后，语句转为使用原基线
另外，表删除后基线不会被删除；如果使用到了索引，索引删除后再重建，不会再识别原基线。
=========================================================================
根据官方文档，总结dbms_spm包的主要内容如下：
1.功能
通过dbms_spm包，可以使用sql plan management（SPM）来管理sql执行计划。
SPM能避免由于执行计划改变而出现潜在的性能问题，
记录并评估语句的执行计划，创建基线（baseline），
基线中包含了一系列高效的执行计划。
2.通常有以下方面会改变sql性能：
1）数据库升级，使用了新的优化器造成部分sql语句的执行计划修改。
这种情况一般会提高或不改变sql的性能，但绝少数情况可能出现性能下降。
2）正在运行的系统和数据修改，造成sql语句的计划修改。
3）新的数据模式部署，引入了新的sql语句。
3.需要ADMINISTER SQL MANAGEMENT OBJECT 系统权限才能使用dbms_spm包
4.各个包的作用：
alter_sql_plan_baseline 通过属性名、属性值来修改某个或所有计划的属性
configure 以参数/值的格式设置sql management base的选项
create_stgtab_baseline 创建stage表用来将SPM传输到另一个系统
drop_sql_plan_baseline 删除与某个sql相关的某个计划或所有计划
evolve_sql_plan_baseline 更改一个或多个语句的SPM
load_plans_from_cursor_cache 将缓存中的一个或多个计划加载进来
load_plans_from_sqlset 从sql tuning set加载计划到基线中
migrate_stored_outline 从sql management base导出sql基线到stage表
unpack_stgtab_baseline 将sql基线从stage表导入到sql management base
以上信息覆盖了基线创建、更新和删除。
导入导出操作参考 http://blog.itpub.net/26451536/viewspace-752989/
来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/26451536/viewspace-1175712/，如需转载，请注明出处，否则将追究法律责任。
转载于:http://blog.itpub.net/26451536/viewspace-1175712/
675
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
