使用DBMS_SPM包进行SQL基线的生成和更新

xiaozhuaia

　　创建一个测试表用于演示基线生成对SQL语句的影响：

　　我第一采用的范围查找：

　　通过V$SQL找到SQL的sql_id，并将其载入基线：

　　查看基线的执行计划，可以看到基线的执行计划走的是全表扫：

　　添加索引后，再次尝试执行同样的语句，发现执行计划走的是索引扫：

　　尝试用匹配查找的方式看看会有什么不同呢？删掉索引，重新执行发现执行计划与范围查找一样走了全表扫

　　获取SQL的ID号，并且生成基线
　　SCOTT> col sql_text for a60
　　SCOTT> select sql_id,sql_text from v$sql where sql_text like 'select object_id from test where object_id like%';

　　通过DBMS_SPM包将SQL载入基线，并查看基线名称：
　　SCOTT> declare
　　xplan pls_integer;
　　begin
　　xplan:=dbms_spm.load_plans_from_cursor_cache(sql_id=>'7g2ycppbr43tu');
　　end;
　　/
　　SCOTT> col plan_name for a30
　　SCOTT> col sql_handle for a30
　　SCOTT> select sql_handle,sql_text,plan_name from dba_sql_plan_baselines;

　　查看基线的执行计划可以看到走的同样是全表扫：
　　SCOTT> select * from table(dbms_xplan.display_sql_plan_baseline(plan_name=>'SQL_PLAN_dw5mnstphcg0597bbe3d0',format=>'basic'));

　　创建索引，继续观察SQL语句，这是可以看到SQL选择的是基线生成的执行计划，而不走索引。

　　SCOTT> create index>

　　查看基线字典可以发现同一条语句存在两个执行计划的基线：

　　查看新基线的执行计划：

　　SCOTT> set serveroutpu on
　　SCOTT> set long 1000
　　SCOTT> declare
　　report varchar2(1000);
　　begin
　　report:=dbms_spm.evolve_sql_plan_baseline(sql_handle=>'SYS_SQL_de1674c66b063c05',time_limit=>dbms_spm.no_limit,verify=>'NO');
　　dbms_output.put_line(report);
　　end;
　　/

　　上面的报告内容显示，SQL处理'SYS_SQL_de1674c66b063c05'中有一个可选的（更优的）执行计划'SQL_PLAN_dw5mnstphcg0560e1006c'，因为更优，因此该执行计划被转变成了可接受的状态。再次查看该基线的执行计划：

　　可以看到该最优的基线计划Accepted已经变为YES。说明执行计划已经接受了这个基线计划。查看语句执行计划，已经选择走新的执行计划：

　　删除执行基线计划：
　　SCOTT> declare
　　dplan pls_integer;
　　begin
　　dplan:=dbms_spm.drop_sql_plan_baseline(plan_name=>'SQL_PLAN_dw5mnstphcg0560e1006c');
　　end;
　　/
　　删除最优的基线计划后，再次查询时Oracle会选择使用原来的基线（SQL_PLAN_dw5mnstphcg0597bbe3d0）作为执行计划使用。
　　DBMS_SPM包的使用需要用户有ADMINISTER SQL MANAGEMENT OBJECT 权限
　　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