V1.0
|
Revision |
Author/Modifier | Comments | |
|
No. |
Date |
||
| 1.0 | 2012-02 | 魏兴华 | 初稿 |
一、 目的
在遭遇执行计划不稳定或者执行计划错误的情况下，通过baseline来固定SQL执行计划以确保执行计划稳定性、提高性能。baseline是oracle 11G提供的稳固sql执行计划的功能，是spm功能的一部分。本人更建议通过10G的sql profile来实现baseline的这些功能，因为baseline用起来稍微繁琐点，sql profile的使用规范请参照我写的其他文档。
二、 适用范围
l 执行计划走错，导致查询性能降低，数据库压力飙升
l 执行计划不稳定，有走错的风险。
l 执行计划错误，需要增加hint，应用来不及修正发布，临时用baseline固定。
l 数据库升级，可以在源库生成baseline，然后把baseline迁移至新库，这样能确保升级前后执行计划性能不变。详情参阅ORACLE 11G SPM部分。
三、 风险评估
l outline、sql profile、baseline这三种固定执行计划的方式都依赖于SQL的文本，如果SQL文本变了，就失去了固定的作用。因此对于并发量非常大、应用修改代码容易的SQL，应该确保通过添加hint的方式来矫正执行计划，以免通过baseline方式修正执行计划的SQL文本变化，导致baseline失效，查询计划走错，导致数据库压力飙升。
l 在做数据迁移过程中，如果原系统中存在已经建立过的baseline，请不要在数据迁移过程中忘记在新系统中安装他们。
l 需要注意，表对象被删除时，baseline并不会被删除，当然这个应该也不是很严重的问题。如果想要删除baseline，必须采取显式的用命令删除。
l baseline是依赖sql文本来进行匹配的，这意味着一份SQL计划基线可能同时被用于两张有相同名称但分属于不同schema下的表。
四、 操作流程
1）FUNCTION LOAD_PLANS_FROM_CURSOR_CACHE RETURNS BINARY_INTEGER
Argument Name Type In/Out Default?
—————————— ———————– —— ——–
SQL_ID VARCHAR2 IN
PLAN_HASH_VALUE NUMBER IN DEFAULT
FIXED VARCHAR2 IN DEFAULT
ENABLED VARCHAR2 IN DEFAULT
2）FUNCTION LOAD_PLANS_FROM_CURSOR_CACHE RETURNS BINARY_INTEGER
Argument Name Type In/Out Default?
—————————— ———————– —— ——–
SQL_ID VARCHAR2 IN
PLAN_HASH_VALUE NUMBER IN DEFAULT
SQL_TEXT CLOB IN
FIXED VARCHAR2 IN DEFAULT
ENABLED VARCHAR2 IN DEFAULT
3）FUNCTION LOAD_PLANS_FROM_CURSOR_CACHE RETURNS BINARY_INTEGER
Argument Name Type In/Out Default?
—————————— ———————– —— ——–
SQL_ID VARCHAR2 IN
PLAN_HASH_VALUE NUMBER IN DEFAULT
SQL_HANDLE VARCHAR2 IN
FIXED VARCHAR2 IN DEFAULT
ENABLED VARCHAR2 IN DEFAULT
DBMS_SPM包里有三个同名的LOAD_PLANS_FROM_CURSOR_CACHE函数，作用各不同。第一个函数用于直接对特定sql_id、plan_hash_value的共享池对象创建baseline。一般用于当前sql执行计划已经是正确的，只是为了通过baseline来进一步固定。也可以用于sql对应多个执行计划，只有一个执行计划是需要通过baseline来稳固的情况。这种情况可以根据plan_hash_value来确定期望的执行计划。
第二个函数一般用于修正执行计划，当然也能实现第一个函数的功能。修正执行计划的情况，sql_id,plan_hash_value要是添加过hint的sql的，不是原始sql的。sql_text是原始sql的。这个函数是我建议在修正执行计划的时候使用的，不推荐使用后面将要介绍的第三种。
第三个函数一般用于修正执行计划，也能够实现第一个函数的功能。修正执行计划的情况，sql_id,plan_hash_value要是添加过hint的sql的，不是原始sql的。SQL_HANDLE是依据原始sql创建的baseline的SQL_HANDLE。
上面三个函数的FIXED，ENABLED统一都设置成YES.FIXED为YES,代表我们创建的baseline禁止演化，演化的意思是，当ORACLE发现一个比目前执行计划更高效的执行计划时，自动创建一个不可接受状态的baseline.ENABLED的意思是，让baseline立即起效。
1. 准备工作
a) 建议以system用户来执行创建baseline，这样普通用户就不需要赋予administer sql management object权限了。
b) 通过HINT构造出产生正确执行计划的sql 文本
2. 执行过程
我们以如下查询为例。object_id列上存在索引。查询默认的执行计划走了 object_id列上的索引。
| sql 文本 | select count(*) from wxh_tbd where object_id=:a |
| sql_id | 85f05qy1aq0dr |
| plan_hash_value | 1501268522 |
我们可能对于这个查询计划的固化有两种需求：
1）想继续用走索引的执行计划，为确保执行计划不走错，通过baseline来固化执行计划。步骤如下：
declare
l_pls number;
begin
l_pls := DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE(
sql_id=> ‘ bmrc3akp0v4uh ‘,
plan_hash_value => 1501268522,
enabled => ‘YES’);
end;
/
2)不想用走索引的执行计划，想让执行计划走全表扫描。可以通过如下方式操作：
步骤一：通过添加HINT，构造出需要的执行计划。 需要注意的是，这一步的意义是需要在共享池里产生出正确的执行计划，后面需要跟它做关联，需要确保这个执行计划不要被刷新出共享池。否则第二步的关联会无效。
select /*+ full(wxh_tbd) */count(*) from wxh_tbd where object_id=1;
查询v$sql获得这个sql的sql_id和plan_hash_value
| sql文本 | select /*+ full(wxh_tbd) */count(*) from wxh_tbd where object_id=:a |
| sql_id | bmrc3akp0v4uh |
| plan_hash_value | 853361775 |
步骤二：依据原始sql文本与正确的执行计划做关联。关联前最好再执行一下，步骤一里添加过HINT的sql语句，以免执行计划已经被刷出共享池。
declare
m_clob clob;
begin
select sql_fulltext
into m_clob
from v$sql
where sql_id = ‘9jnx2cjukjtu7′————–原始sql的 sql_id
and child_number = 0;—————–为了让sql只返回一行，也可以rownum=1代替
dbms_output.put_line(m_clob);
dbms_output.put_line(dbms_spm.load_plans_from_cursor_cache(
sql_id => ‘bmrc3akp0v4uh’,————HINT SQL_ID
plan_hash_value => 853361775,——————-HINT PLAN_HASH_VALUE
sql_text => m_clob,————————-原始SQL文本
fixed => ‘YES’, ———————禁止演化baseline
enabled => ‘YES’));
end;
/
3. 验证方案
另开一个SESSION，确定已经用到了baseline。
explain plan for select count(*) from wxh_tbd where object_id=:a;
select * from table(dbms_xplan.display);
————————————–
| Id | Operation | Name |
————————————–
| 0 | SELECT STATEMENT | |
| 1 | SORT AGGREGATE | |
|* 2 | TABLE ACCESS FULL| WXH_TBD |
————————————–
Note
—–
- SQL plan baseline “SYS_SQL_PLAN_41832bd9cca3d082″ used for this statement
执行计划的note部分显示已经用到了baseline ，执行计划也由索引改为了全表扫描。
（另外一种baseline常用的方式，步骤比较复杂。参照：
http://space.itpub.net/22034023/viewspace-697568）
五、 核心对象风险
理论上baseline影响到的只是一条特定的SQL,因此相对风险比较低。对于核心表，请选择业务低峰期来进行创建baseline.
六、 回退方案
declare
l_pls number;
begin
l_pls := DBMS_SPM.DROP_SQL_PLAN_BASELINE(
sql_handle => ‘SYS_SQL_e38995be286a1fb3′,
plan_name => ‘ SYS_SQL_PLAN_41832bd9cca3d082′
);
end;
/
通过如上方式删除创建的baseline。需要查询dba_sql_plan_baselines来获取sql_handle和plan_name.
七、 历史故障及教训
无
