一次数据库性能问题处理(11g自动维护任务,log file sync)

联系:QQ(5163721)

标题:一次数据库性能问题处理(11g自动维护任务,log file sync)

作者:Lunar©版权所有[文章允许转载,但必须以链接方式注明源地址,否则追究法律责任.]

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


1


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

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中的信息一致:


2


直觉是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可以看到系统负载已经下降一点点,但是依然很高:


3


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


4


相应的前台等待事件:


5


后台等待时间:


6


在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看到,系统负载已经降下来了:


8


该应用正常时间,数据库连接数如下:

   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哈哈~。

此条目发表在 数据库 分类目录。将固定链接加入收藏夹。

评论功能已关闭。