oracle jobs跑批,实战案例 —— 使用Oracle Trigger优化特定会话

运维工程师都知道，在运维工作中，最重要的原则之一就是“稳定压倒一切”，为了保证系统的稳定运行，变更要最小化。

一方面，这导致了大量的官方已经不支持老版本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倍，这次优化将修改范围控制在预期优化的跑批作业内, 并未对交易系统产生影响，跑批作业及日常交易运行正常。