转载

一次数据库问题处理（11g自动维护任务，log file sync）

联系： QQ(5163721)

标题：一次数据库问题处理（11g自动维护任务，log file sync）

看到问题发生前30分钟的awr显示，系统负载超高：

查询数据库当前等待事件：

SQL> set pages 999 SQL> set linesize 1000 SQL> SELECT inst_id,event,count(*) FROM gv$session WHERE wait_class#<>6 group by inst_id,event order by 1,3;    INST_ID EVENT              COUNT(*) ---------- ---------------------------------------------------------------- ----------  1 DBWR slave I/O            1  1 enq: TX - row lock contention     1  1 db file parallel write            1  1 asynch descriptor resize          1  1 read by other session             1  1 log file sync             3  1 direct path write temp            3  1 db file sequential read           6  1 enq: TX - index contention      193  1 resmgr:cpu quantum      226  1 buffer busy waits       822 11 rows selected. SQL>

跟awr中的信息一致：

直觉是resmgr:cpu quantum一起了其他一系列问题，这个已经不是第一次遇到了。

等待事件 resmgr:cpu quantum 是11.2引入的oracle资源管理引起的，这个东西一般问题很多，大部分时候，我们装机时，都是直接禁用的。

11.2的oracle资源管理中有一项最坑的是周末的维护任务，这个遇到好几次了。

禁用方法：

1，关闭一些不需要的维护任务：

exec dbms_scheduler.disable( 'ORACLE_OCM.MGMT_CONFIG_JOB' ); exec dbms_scheduler.disable( 'ORACLE_OCM.MGMT_STATS_CONFIG_JOB' );

关闭数据库的空间Advisor，避免消耗过多的IO：

BEGIN DBMS_AUTO_TASK_ADMIN.DISABLE( client_name => 'auto space advisor', operation => NULL, window_name => NULL); END; /

关闭数据库的SQL自动调整Advisor：

BEGIN DBMS_AUTO_TASK_ADMIN.DISABLE( client_name => 'sql tuning advisor', operation => NULL, window_name => NULL); END; /

关闭数据库的weekday窗口的DEFAULT_MAINTENANCE_PLAN：

alter system set resource_manager_plan='' scope=both; execute dbms_scheduler.set_attribute('WEEKNIGHT_WINDOW','RESOURCE_PLAN',''); execute dbms_scheduler.set_attribute('WEEKEND_WINDOW','RESOURCE_PLAN',''); execute dbms_scheduler.set_attribute('MONDAY_WINDOW','RESOURCE_PLAN',''); execute dbms_scheduler.set_attribute('TUESDAY_WINDOW','RESOURCE_PLAN',''); execute dbms_scheduler.set_attribute('WEDNESDAY_WINDOW','RESOURCE_PLAN',''); execute dbms_scheduler.set_attribute('THURSDAY_WINDOW','RESOURCE_PLAN',''); execute dbms_scheduler.set_attribute('FRIDAY_WINDOW','RESOURCE_PLAN',''); execute dbms_scheduler.set_attribute('SATURDAY_WINDOW','RESOURCE_PLAN',''); execute dbms_scheduler.set_attribute('SUNDAY_WINDOW','RESOURCE_PLAN',''); Commit;

查看当前active的resource plan ，确认已经关闭：

SQL> select * from v$rsrc_plan;  ID NAME        IS_TO CPU INS PARALLEL_SERVERS_ACTIVE PARALLEL_SERVERS_TOTAL ---------- -------------------------------- ----- --- --- ----------------------- ---------------------- PARALLEL_EXECUTION_MANAGED --------------------------------      12540 INTERNAL_PLAN      TRUE  OFF OFF         0       64 FIFO SQL>

此时awr可以看到系统负载已经下降一点点，但是依然很高：

上述修改后的数据库等待事件已经变味“log file sync”，具体如下：

相应的前台等待事件：

后台等待时间：

在LGWR的trace中，可能会出现类似如下的信息：

kcrfw_update_adaptive_sync_mode: poll> post current_sched_delay=0 switch_sched_delay=154 current_sync_count_delta=887 switch_sync_count_delta=837

这是11.2的新特性，Adaptive Switching Between Log Write Methods（LGWR写模式自动切换），该功能bug一堆，经常导致commit缓慢而带来的“log file sync”。通过设置如下隐含参数，禁用11.2这个LGWR写模式切换功能：

ALTER SYSTEM SET "_use_adaptive_log_file_sync"=FALSE"; ALTER SYSTEM SET "_use_adaptive_log_file_sync"=FALSE scope=sfile sid='*';

修改后，数据库等待事件已经变为正常的应用日常的等待事件了：

INST_ID EVENT              COUNT(*) ---------- ---------------------------------------------------------------- ----------  1 db file parallel write            1  1 direct path write temp            1  1 DBWR slave I/O            1  1 SQL*Net message to client         2  1 direct path read temp             3  1 read by other session            18  1 db file sequential read          70  1 enq: TX - index contention      118 8 rows selected. Elapsed: 00:00:00.01 SQL> /    INST_ID EVENT              COUNT(*) ---------- ---------------------------------------------------------------- ----------  1 db file parallel write            1  1 direct path write temp            1  1 buffer busy waits         1  1 SQL*Net message to client         2  1 direct path read temp             3  1 DBWR slave I/O            4  1 log file sync             9  1 read by other session            12  1 enq: TX - index contention       35  1 db file sequential read          67 SQL>

此时的AWR看到，系统负载已经降下来了：

该应用正常时间，数据库连接数如下：

INST_ID MACHINE     COUNT(*) ---------- ------------------------- ----------  1 800BEST/CLIENTLOG01        1  1 800BEST/LUNARSET01         43  1 800BEST/LUNARSET102        76  1 800BEST/TERMINAL   2  1 800BEST/WX01      57  1 IIS APPPOOL/LUNARSET01      5  1 IIS APPPOOL/LUNARSET02    666  1 IIS APPPOOL/LUNARSETBAK01  64  1 LUNARDB  1 9 rows selected. SQL> /    INST_ID MACHINE     COUNT(*) ---------- ------------------------- ----------  1 800BEST/CLIENTLOG01        1  1 800BEST/LUNARSET01         43  1 800BEST/LUNARSET102        76  1 800BEST/TERMINAL   3  1 800BEST/WX01      56  1 IIS APPPOOL/LUNARSET01      5  1 IIS APPPOOL/LUNARSET02    450  1 IIS APPPOOL/LUNARSETBAK01  64  1 LUNARDB  1

故障暂时缓解了，应用反映已经正常，但是可以看到，上面的信息告诉我们，对于这个应用来说，需要调整的地方还很多：

1，系统参数全面检查

2，SQL优化（SQL语句的写法，index，统计信息检查等）

3，系统IO（比如系统IO的配置，数据库filesystem_io的配置等等）

4，控制文件的检查（我一直怀疑这个有问题）

5，enq: TX – index contention

不过目前日程已经安排到5月底了，估计这个事情如果不太着急的话，应用先自己tuning，等到6月份，如果需要我再介入吧，O(∩_∩)O哈哈~。

正文到此结束