运维工程师都知道,在运维工作中,最重要的原则之一就是“稳定压倒一切”,为了保证系统的稳定运行,变更要最小化。
一方面,这导致了大量的官方已经不支持老版本Oracle系统,依然在运行,没有充足的理由和充分的测试,客户不会轻易升级,另一方面,在这些系统的日常维护或者优化的工作中,我们要尽量降低变更的规模以及其影响的范围。
下面这个例子,就是一个在老版本数据库上控制优化器,优化特定会话的案例。
某客户反馈当前某生产系统跑批作业需要运行四个小时,在每天业务开始之前,这个跑批作业还没有完成,影响到了正常的业务。系统经过硬件升级后,跑批作业时间降低到三个小时左右,但是效果仍不理想。客户想就此问题进行优化以减少跑批作业对正常业务的影响。
当前此数据库使用的是Oracle10.2.0.5 RAC架构,大部分业务在节点二运行,部分查询被分到了节点一上。当前数据库的优化器版本为9.2,优化器模式为RULE,绝大多数SQL都运行在此模式上。
解决方案
当前数据库的优化器版本为9.2,优化器模式为RULE,绝大多数SQL都运行在此模式上,为了获得更好的SQL执行计划以及相关特性支持,应该将优化器版本设置为更高的版本及使用CBO优化模式,但是由于该优化器模式更改对生产数据库影响较大,未经严格测试,不建议调整,因此优化仅针对跑批作业调整相关优化器参数,白天生产交易将保持原有优化器模式。我们知道跑批作业的用户及应用模块与白天的业务模块不同,我们可以利用这个来区分正常业务会话和跑批会话,进而针对不同的会话,利用logon trigger设置不同的会话级优化器参数。
针对跑批作业将进行如下参数的调整:
参数名当前参数值调整后参数值
optimizer_modeRULEALL_ROWS
optimizer_features_enable9.2.010.2.0.5
针对该需求,通过触发器完成对跑批作业调整两个参数:
CREATEOR REPLACE TRIGGER trig_modify_sess_params
after logon ON database
DECLARE
l_username VARCHAR2(50);
l_module VARCHAR2(100);
v_sql1 varchar2(400);
v_sql2 varchar2(400);
BEGIN
l_username := SYS_CONTEXT('USERENV','SESSION_USER');
l_module := LOWER(SYS_CONTEXT('USERENV', 'MODULE'));
v_sql1 := q'[alter session set optimizer_mode='ALL_ROWS']';
v_sql2 := q'[alter session set optimizer_features_enable='10.2.0.5']';
IF l_module LIKE '%batchjob%'AND l_username IN ('BJ_USER') THEN
execute immediate v_sql1;
execute immediate v_sql2;
END IF;
ENDtrig_modify_dealdata_optimode;
/
重启跑批功能模块,通过下列SQL,验证我们针对3个跑批会话的修改已经生效:
SQL> r
1 select sid, name, value from V$SES_OPTIMIZER_ENV
2 where name in ('optimizer_mode','optimizer_features_enable')
3 and sid in (select sid from v$session where username='BJ_USER'
4 and module like '%batchjob%'
5* )
SID NAME VALUE
------- -------------------------------------------------------
1647 optimizer_features_enable10.2.0.5
1647 optimizer_mode all_rows
1856 optimizer_features_enable10.2.0.5
1856 optimizer_mode all_rows
2102 optimizer_features_enable10.2.0.5
2102 optimizer_mode all_rows
6 rows selected.
当然,要想达到理想的优化效果,要定时收集相关表的统计信息。
优化效果
通过上述修改,跑批作业的运行时间缩短到1小时左右,速度提升了3倍,这次优化将修改范围控制在预期优化的跑批作业内, 并未对交易系统产生影响,跑批作业及日常交易运行正常。