鉴于目前业务数据库系统归档模式的开启,计划进行参数修改然后重启数据库,但开启前必须检查目前redo log切换的频率、redo log的大小、redo log的日志组数等方面,通过如下了解基本的情况:
1)查询日志的大小,日志组情况
SELECT L.GROUP#,
LF.MEMBER,
L.ARCHIVED,
L.BYTES / 1024 / 1024 "SIZE(M)",
L.MEMBERS
FROM V$LOG L, V$LOGFILE LF
WHERE L.GROUP# = LF.GROUP#;
GROUP# MEMBER ARCHIVED SIZE(M) MEMBERS
1 D:\APP\ORADATA\ZDSDORCL\REDO01.LOG NO 50 1
2 D:\APP\ORADATA\ZDSDORCL\REDO02.LOG NO 50 1
3 D:\APP\ORADATA\ZDSDORCL\REDO03.LOG NO 50 1
从结果可以看到,目前只有3组日志,每个日志组的成员为1个,每个日志的大小为50M,按数据库正常的运行需求,建议,日志组为5个,每个日志组多添加一个成员,命令如下:
2)日志切换频率
SELECT TO_CHAR(FIRST_TIME, 'yyyymmdd') DATETIME, COUNT(*) CNT
FROM V$LOG_HISTORY
WHERE TO_CHAR(FIRST_TIME, 'yyyymmdd') IN
('20110618',
'20110619',
'20110620',
'20110621',
'20110622',
'20110623')
GROUP BY TO_CHAR(FIRST_TIME, 'yyyymmdd')
ORDER BY TO_CHAR(FIRST_TIME, 'yyyymmdd') DESC;
日期 | 切换次数 | 每个日志大小(M) | 每天产生日志大小(G) | 切换间隔(分钟) | 建议切换间隔 |
20110618 | 4502 | 50 | 219.82 | 3.1 | 30 |
20110619 | 4504 | 50 | 219.92 | 3.1 | 30 |
20110620 | 4521 | 50 | 220.75 | 3.1 | 30 |
20110621 | 4589 | 50 | 224.07 | 3.2 | 30 |
20110622 | 4643 | 50 | 226.71 | 3.2 | 30 |
20110623 | 4673 | 50 | 228.17 | 3.2 | 30 |
通过上面的结果可以看到,每天日志平均切换次数达到4500次,每三分钟切换一次,每天产生的日志量平均达到220G左右,如果开启归档的话,处理归档的空间还有消耗的性能对整个业务数据库会影响比较大,特别是I/O方面。根据ORACLE官方的建议,日志平均30分钟切换一次最好,因此最重要的是找到是什么原因引起产生如此大的日志量,才能解决问题的根本,然后进行合理的分配日志大小和日志组。
3)AWR报告分析
通过收集连续从2011-6-18至2011-6-23早上9点到晚18点的AWR报告,主要存在的问题有如下:
这一部分统计了整个数据库的负载情况,在标红色区域的是二个目前影响比较大的应用情况,其中“Redo size”每秒产生的日志比较大,平均每个事务产生的日志也比较多,从而可以知道数据库频率的进行“INSERT/UPDATE/DELETE”操作,W/A MB processed来自v$pgastat(单位其实也是Byte,不是MB),反映了当前PGA的使用情况,从上面也可以看到,说明在PGA中的操作也相对比较多,可以通过下面的分析继续得到具体的问题。
这个是记载数据库常见的TOP 5等待事件,从影响最大的三个事件可以看到,都是和redo log有关,其中local write wait是日志写等待,log file switch(checkpoint incomplete)日志切换产生的检查点未完成,一般是日志组少,每个日志太小及日志频率切换有关,这里就反映了当前日志存在的问题,而end RO-fast object reuse是指对象过快的被重新使用,这个是应用程序的问题,一般和TRUNCATE TABLE有关,继续进行下面的报告分析可以更详细的了解到情况。
![oracle 日志切换频率优化案例 - 小志 - 常在心](http://img.ph.126.net/wkoRd31CyxFkk3JazDLsng==/1530660923353350408.jpg)
![oracle 日志切换频率优化案例 - 小志 - 常在心](http://img.ph.126.net/A2b13fdZPPnmzIj-IDNcVg==/3394306744155267978.jpg)
上面二个报表是关于SQL的统计情况,反映了消耗CPU的解析时间以及占用SGA中的database buffer的情况,从上面的情况可以得到,主要是二个JOB和w3wp.exe应用影响性能比较大,然后再查询目前那二个JOB的情况
4)查看对应的JOB
SELECT J.LOG_USER,
J.PRIV_USER,
J.SCHEMA_USER,
J.LAST_DATE,
J.NEXT_DATE,
J.INTERVAL,
J.WHAT
FROM DBA_JOBS J
WHERE J.LOG_USER = 'PDBSERVER'
LOG_USER | SCHEMA_USER | INTERVAL | WHAT |
PDBSERVER | PDBSERVER | sysdate + 1 | pck_salaryloan.AutoSalaryCheck(sysdate); |
PDBSERVER | PDBSERVER | SYSDATE+1 | CONVERTCUSTDUE30; |
PDBSERVER | PDBSERVER | sysdate+1 | update salaryloanaudit s |
PDBSERVER | PDBSERVER | sysdate+1 | update salaryloanaudit s |
PDBSERVER | PDBSERVER | sysdate+1 | UPDATE customerrelation |
PDBSERVER | PDBSERVER | sysdate+1/86400 | P_REFLESHIRRLRECORD; |
PDBSERVER | PDBSERVER | sysdate+1/86400 | P_REFLESHIRROFFDATA; |
可以看到标示红色的JOB执行是比较频率,对应的是用户下的PDBSERVER用户的JOB引起的, P_REFLESHIRRLRECORD和P_REFLESHIRROFFDATA存储过程的代码如下:
P_REFLESHIRRLRECORD、P_REFLESHIRROFFDATA代码
从上面的JOB的interval(间隔)可以看到关于这二个存储过程的JOB,是每隔一秒执行一次,而里面的代码都是先进行TRUNCATE TABLE的操作,然后插入新的数据都是从查询得到的,于是进行里面查询语句的分析可以知道:
P_REFLESHIRRLRECORD代码中的部分代码:
SELECT *
FROM (SELECT SLRECORD.LOANFROMGUID,
SLRECORD.CUSTOMERGUID,
SLRECORD.LOANBACKDATE,
SLRECORD.BACKMONEY,
SLRECORD.DISTILL,
SLRECORD.BACKTIME,
SLRECORD.CUSTOMERSTATE,
NVL((SELECT NEWGUID
FROM DEPARTCONVERT
WHERE OLDGUID = SLRECORD.DEPARTMENTGUID
AND CONVERTDATE <= SLRECORD.LOANBACKDATE),
NVL((SELECT NEWDEPARTMENTGUID
FROM DEPARTMENTCOALITION
WHERE LOANGUID = SLRECORD.LOANFROMGUID),
SLRECORD.DEPARTMENTGUID)) DEPARTMENTGUID, --如果为分行转公司,则为公司Guid,否则为原GUID
SLRECORD.ISACCEPTHANDMONEY,
SLRECORD.BANKGUID,
SLRECORD.DATASTATE,
SLRECORD.TRANSFERDATE,
SLRECORD.TRANSACTORGUID,
SLRECORD.BACKMONEY IRRBACKMONEY,
'{C4511E7D-96A7-44B5-BD37-9D7CDF231AC3}' PRODUCTTYPE,
'消费贷' PRODUCTNAME,
SLRECORD.DEPARTMENTGUID ROOTDEPARTGUID, --原分行Guid,
SLRECORD.ORGEMPLOYEEINFOGUID,
SLRECORD.CREATEDATE
FROM SALARYLOANRECODE SLRECORD
UNION ALL
SELECT HLRECORD.LOANFROMGUID,
HLRECORD.CUSTOMERGUID,
HLRECORD.LOANBACKDATE,
HLRECORD.BACKMONEY,
HLRECORD.DISTILL,
HLRECORD.BACKTIME,
HLRECORD.CUSTOMERSTATE,
NVL((SELECT NEWGUID
FROM DEPARTCONVERT
WHERE OLDGUID = HLRECORD.DEPARTMENTGUID
AND CONVERTDATE <= HLRECORD.LOANBACKDATE),
NVL((SELECT NEWDEPARTMENTGUID
FROM DEPARTMENTCOALITION
WHERE LOANGUID = HLRECORD.LOANFROMGUID),
HLRECORD.DEPARTMENTGUID)) DEPARTMENTGUID,
HLRECORD.ISACCEPTHANDMONEY,
HLRECORD.BANKGUID,
HLRECORD.DATASTATE,
HLRECORD.TRANSFERDATE,
HLRECORD.TRANSACTORGUID,
HLRECORD.BACKMONEY IRRBACKMONEY,
'{4C69EF7B-B8AB-4281-B440-F764B583AC41}' PRODUCTTYPE,
'事业贷' PRODUCTNAME,
HLRECORD.DEPARTMENTGUID ROOTDEPARTGUID, --原分行Guid,
HLRECORD.ORGEMPLOYEEINFOGUID,
HLRECORD.CREATEDATE
FROM HEADLOANRECODE HLRECORD);
这个查询总共有3297行,通过执行以及看执行计划,需要的时间为19.25秒,也就是说每隔一秒去执行TUNCATE TABLE的时候就会等待直到数据插入完,也就会出现上面的关于“end RO-fast object reuse“的等待事件的情况,这个也是本身设计的就不合理,还有就是没有合适的索引引起的原因造成的,同理,分析P_REFLESHIRROFFDATA代码情况类似,关于
SELECT *
FROM (SELECT LOANGUID, LOANBACKDATE, 0, INTRECEIVABLE, COMPRECEIVABLE
FROM IRRCDBPAYRECORD
WHERE RECORDTYPE = '2'
AND DATASTATE = '2'
UNION ALL
SELECT LOANGUID,
LOANBACKDATE,
0,
NVL(INTFACT, 0) - NVL(INTRECEIVABLE, 0) INTRECEIVABLE,
NVL(COMPFACT, 0) - NVL(COMPRECEIVABLE, 0) COMPRECEIVABLE
FROM IRRCDBPAYRECORD
WHERE RECORDTYPE = '1'
AND ISSETTLE = '1'
AND DATASTATE != '2'
AND DATASTATE != '6');
这段代码也会随着业务数据表IRRCDBPAYRECORD的数据的增加而消耗更多的时间和产生更多的记录,目前是59条记录,因此单这二个JOB一天产生的redo log记录 (3297+59)*86400= 289958400接近快3亿的记录,可见产生的日志有多少,频率切换也就很正常了。
5)总结
l 合理优化这二个JOB,减少日志的产生量,及带来I/O的性能问题
l 优化后观察后续的情况,看是否还有其它的性能问题
l 合理安排审计的策略
l 合理进行归档模式的修改