V1.0
|
Revision |
Author/Modifier | Comments | |
|
No. |
Date |
||
| 1.0 | 2012-02 | 魏兴华 | 初稿 |
一、 目的
在遭遇执行计划不稳定或者执行计划错误的情况下，通过sql profile来固定SQL执行计划以确保执行计划稳定性以及提高性能。sql profile相对于outline,baseline使用简单方便。可以实现outline,baseline的一切功能。使用@profile脚本，只需要提供sql_id,hint就可以起到固定执行计划的作用。
二、 适用范围
l 由于sql profile对于固化执行计划操作起来非常的迅速方便，因此对于执行计划突然走错，数据库压力飙升的情况，建议使用sql profile来修改固定执行计划。而outline和baseline相对来说，操作要复杂的多。sql profile是oracle 10G提供的一个功能。
l 对于执行计划走错，但是又来不及发布修改后的SQL，可以暂时通过sql profile 来修正执行计划，等应用发布后，再做删除。
l 需要注意的是，sql profile所接受的hint需要包含query block。因此dba需要具备query block的知识。
l sql profile是依赖sql文本进行匹配的，这意味着一份sql profile可能同时被用于两张有相同名称但分属于不同schema下的表，使用前需要确认。
三、 风险评估
l sql profile是针对sql 文本的，文本不区分大小写，空格，如果文本被修改，那么 sql profile就会不起作用。因此如果多个schema下有同样的sql语句，那么创建的sql profile将对他们都会生效。因此，请确认这种情况是否可以发生。如果不允许发生，那么需要确保不同schema下的sql文本不一样
l 在做数据迁移过程中，如果原系统中存在已经建立过的sql profile，请不要在数据迁移过程中忘记在新系统中安装他们。
l 需要注意，表对象被删除时，sql profile并不会被删除，当然这个应该也不是很严重的问题。如果想要删除sql profile，必须采取显式的删除。
四、 操作流程
1. 准备工作
a) 建议采用system用户来创建，修改，删除sql profile。创建、修改、删除sql profile的用户需要具有create any sql sprofile,alter any sql profile,drop any sql profile的权限。到oracle 11G,不再建议使用上面这3个系统权限，建议使用administer sql management object的系统权限。
b) 找出需要修改sql的sql 文本
2. 执行过程
我们以如下查询为例。object_id列上存在索引。查询默认的执行计划走了 object_i列上的索引。
select count(*) from wxh_tbd where object_id=:a
我们可能对于这个查询计划的固化有两种需求：
1）想继续用走索引的执行计划，为确保执行计划不走错，通过sql profile来固化执行计划。步骤如下：
declare
v_hints sys.sqlprof_attr;
begin
———-HINT部分
v_hints := sys.sqlprof_attr(’IND(WXH_TBD@SEL$1 WT_OI_IND)’);
———-SQL语句部分
dbms_sqltune.import_sql_profile(’select count(*) from wxh_tbd where object_id=:a’,
v_hints,
—————-PROFILE 的名字
‘SQLPROFILE_NAME3′,
force_match => true);
end;
/
2)不想用走索引的执行计划，想让执行计划走全表扫描。可以通过如下方式操作：
declare
v_hints sys.sqlprof_attr;
begin
v_hints := sys.sqlprof_attr(’full(wxh_tbd@sel$1)’);———-HINT部分
dbms_sqltune.import_sql_profile(’select count(*) from wxh_tbd where object_id=:a’,———-SQL语句部分
v_hints,
‘SQLPROFILE_NAME3′,——————————–PROFILE 的名字
force_match => true);
end;
/
除了上面介绍的方式来使用sql profile，你还可以通过我提供的脚本@profile来进行创建 sql profile。这个脚本的使用方法很简单。你只需要提供sql_id和hint就可以了。由于sql profile的hint需要指定query block,因此需要dba具备查询块的相关知识。@profile的脚本给你提供了原始执行计划的query block供你参考，绝大多数时候，这些已经能够提供给你需要的query block的name了。@profile脚本里的hint可以接受多个hint，之间用空格隔开就可以了。例如：
full(@”SEL$1″ “WXH_TBD”@”SEL$1″) full(@”SEL$2″ “T”@”SEL$2″)
3. 验证方案
explain plan for
select count(*) from wxh_tbd where object_id=:a;
Execution Plan
———————————————————-
Plan hash value: 853361775
—————————————————————————
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
—————————————————————————
| 0 | SELECT STATEMENT | | 1 | 13 | 144 (3)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 13 | | |
|* 2 | TABLE ACCESS FULL| WXH_TBD | 198 | 2574 | 144 (3)| 00:00:01 |
————————————————————————–
Note
—–
- SQL profile “SQLPROFILE_NAME3″ used for this statement
从note部分我们看到sql profile已经 起作用了
五、 核心对象风险
理论上sql profile影响到的只是一条特定的SQL,因此相对风险比较低.对于核心表对象上sql profile的创建最好放到业务低峰期操作。
六、 回退方案
exec dbms_sqltune.drop_sql_profile(’ SQLPROFILE_NAME3′);
七、 历史故障及教训
无
