SQL和PL/SQL的性能优化之二--执行计划管理
1、提示--它们仅是建议,优化器可以选择忽略他们。事实上,优化器将尽可能地遵从优化提示,哪怕会导致空难性的性能影响。一般来说,只有当你用尽非直接方法(收集统计信息,创建柱状图及设置配置参数等)之后,才可以考虑使用提示。
下面是一些更常见的提示:
ALL_ROWS 使用ALL_ROWS优化器目标
AND_EQUALS(表名... 索引名 索引名 ···) 合并指定索引来提取指定的表的记录,与下一个类似,但是不使用企业版才支持的位置转换策略。
INDEX_COMBINE(表名,索引名 索引名···) 与上面的类似,但是使用只有在企业版中才有的位置转换策略
APPEND 使用直接模式插入
CACHE(表名) 当执行全表扫描操作时,促进表在ORACLE共享内存中的缓存(NOCACHE反效)
FACT(表名) 考虑使用指定的表作为星型模式的事实表
FIRST_ROWS(N) 使用FIRST_ROWS优化器目标
FULL(表名)
HASH(表名) 对指定的表使用基于散列聚簇的访问方式(显然,只有与表是散列聚簇表时才有效)
INDEX(表名[索引名]) 使用指定表上的指定索引。如果没指定索引,则使用表上带来最低开销的那个索引
INDEX_SS(表名 索此名) 使用索引“跳跃扫描”访问路径
LEADING(表名···) 指定表作为联结顺序中驱动表,并指定顺序进行连接
PARALLEL(表名并行度) NOPARALLED
ORDERED 使用FROM子句中表出现的顺序作为联结的顺序,它将覆写普通的基于成本计算得出来的联结顺序
USE_HASH(表名) 散列联结技术
USE_MERGE(表名) 排序合并方法
USE_NL(表名) 嵌套循环方法
2、存储提纲:是在特定的时间点为特定的SQL语句记录执行计划的方法。然后你可以激活存储提纲以保证即使统计信息在未来发生改变,特定的执行计划依然会被使用。
存储提纲将被SQL计划基线取代,但10G,11G标准版完全支持存储提纲
创建存储提纲:
Create outline customer_yob_qry for category outlines2 on
select max(cust_income_level)
from customers c
where cust_year_of_birth >1985;
每个存储提纲都属于一个特定的类目;上面的类目OUTLINES2,可以在会话级或系统级使用参数USE_STORED_OUTLINES来激活。
ALTER SESSION SET USE_STORED_OUTLINES=OUTLINES2;
侵入存储提纲:
不能编辑SQL语句的情况下,强制SQL采用另一个不同的计划。
A>首先我们对未改变的SQL语句创建一个存储提纲;
Create outline customer_yob_otln for category outlines2 on
select min(cust_income_level)
from customers c
where cust_year_of_birth >1985;
B>我们由公共存储提纲创建一个私有存储提纲
Create Private outline original_oln from cust_yob_otln;
C>现在我们创建一个拥有相同SQL语句但包含任何我们想指定的提示;
Create private outline hinted_oln on
select /*+ index(c)*/ min(cust_income_level)
from customers c
where cust_year_of_birth>1985;
查询全局临时表ol$hints(它拥有私有存储提纲的定义),可以看到应用于每个存储提纲的提示,注意到original_oln有一个FULL提示,而hinted_oln有一个index提示。
select on_name,hint_text from ol$hints;
D>我们现在想做的是将HINTED_OLN中的提示复制到ORIGINAL_OLN中。ol$hints表包含提示的内容,而它的父表,ol$包含提示的数量,它也必须被更新。
下面的SQL语句交换存储提纲:
update ol$hints
set ol_name = case ol_name
when 'HINTED_OLN'
then 'ORIGINAL_OLN'
when 'ORIGINAL_OLN'
then 'HINTED_OLN'
else ol_name
end
where ol_name in('HINTED_OLN','ORIGINAL_OLN');
update ol$ ol2
set hintcount=(select hintcount from ol$ ol2
where ol2.ol_name in('HINTED_OLN','ORIGINAL_OLN')
and ol2.ol_name != ol1.ol_name)
where ol1.ol_name in('HINTED_OLN','ORIGINAL_OLN');
现在,激活私有存储提纲,可以看到原始SQL语句成功强制使用了索引
ALTER SESSION SET USE_PRIVATE_OUTLINES=TRUE;
E>最后,我们要做的事就是把私有存储提纲复制回原有的公有存储提纲里。
Create or replace outline cust_yob_otln from
private original_oln for category outlines2;
现在,如果激活了存储提纲类目OUTLINES2,SQL语句将会使用索引,就如其中包含了索引提示一样。
3、SQL概要--为增加优化器的灵活性,当环境变化时,为新的SQL创建更优执行计划的能力,SQL概要及相关的SQL调优顾问是10G引入的,需ORACLE调优包授权许可。
SQL概要是一个由SQL调优任务创建的与该SQL相关的特定统计信息的集合,它可以在随后被SQL调优顾问用来决定一个最优的计划,调优顾问可理解为一个离线优化器(offline optimizer)
4、SQL基线---为补充SQL概要并最终替代存储提纲,11G中引入了SQL基线(灵活性与稳定性的结合)
只有在新的计划被证实好于任何已存在的计划时,它才允许计划改变。
4、1 创建基线
DECLARE
V_SQL_ID v$sql.sql_id%type;
V_PLAN_COUNT NUMBER;
BEGIN
SELECT SQL_ID INTO V_SQL_ID FROM V$SQL
WHERE SQL_TEXT LIKE 'SELECT /*GHBASELINES1*/%';
V_PLAN_COUNT := DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE(SQL_ID => V_SQL_ID);
DBMS_OUTPUT.PUT_LINE(V_PLAN_COUNT||' PLANS LOADED');
END;
表dba_sql_plan_baselines可查基线状态,ACCEPTED列为YES表示这个基线被激活。
查基线对应的计划
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_SQL_PLAN_BASELINE(:V_SQL_HANDEL,NULL,'BASIC'));
进化基线
当发现新的执行计划开销低时,优化器就会创建新的基线。然而新的基线在经过核实之前不会被接受。
BEGIN
:V_REPORT := dbms_spm.evolve_sql_plan_baseline
(sql_handle=>:v_sql_handle,
verify=>'YES',--只有当未接受的计划可带来显著提升,才会被接受,如果设置为NO,则所有未接受的计划都会被接受。
commit => 'YES');--控制实际真正接受,或仅仅报告计划是否符合标准
END;
自动化和配置基线
基线的使用主要由两个数据库参数控制
OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES 这个参数控制基线的自动收集,默认设置为FALSE,当将基设为TRUE时,基线会在SQL第一次执行时被自动创建。这样,就没必要再使用DBMS_SPM包来手工创建基线了。
OPTIMIZER_USE_SQL_PLAN_BASELINES 控制优化器对SQL基线的使用,默认值为TRUE,当为FALSE时,在优化器决定执行计划时,就不会考虑基线。
固化基线
只需在前面的过程中做个小的修改:
...
v_plan-count := dbms_spm.load_plans_from_cursor_cache(sql_id=>v_sql_id,fixed=>'YES');
...
当有固化的基线存在时,即使有计划发生变化,优化器也不会为这个SQL语句增加新的基线,一定是优化使用固化的基线。