最近在想一个问题,当我在新安装的一个数据库上怎么来评估和监控这个数据库的log是否设置的合理那,应该怎么去优化log那。因为一个不良的log设置会是数据库性能大大受影响。默认情况下log是50mb,在绝大多数的系统中这个是不能满足的,设置过大又会导致自动恢复时间变长,怎么权衡这个问题那。尽量要保证每小时日志切换不能超过5次。
我个人观点就是看wait等待事件,如果有如下的等待事件就应该引起我们对log的注意了
log file parallel write log file sync control file parallel write
现在就怎么去处理这一类问题说下
alter database add logfile group 4 ('/u01/app/oracle/oradata/cks/redo04.log') size 500m;添加一个新的组,并且加大日志大小降低日志切换频率,将所有的日志组都改到500mb(这个500mb是更具我先有系统上的问题给出来的一个值,具体的还需要自己估量)
删除过去的日志
alter database drop logfile group 2;
到操作系统层删除对应的物理log文件
alter system switch logfile;
alter system checkpoint;
后期监控日志的切换频率可以用如下sql
SELECT
to_char(first_time,'YYYY-MM-DD') day,
to_char(sum(decode(to_char(first_time,'HH24'),'00',1,0)),'999') "00",
to_char(sum(decode(to_char(first_time,'HH24'),'01',1,0)),'999') "01",
to_char(sum(decode(to_char(first_time,'HH24'),'02',1,0)),'999') "02",
to_char(sum(decode(to_char(first_time,'HH24'),'03',1,0)),'999') "03",
to_char(sum(decode(to_char(first_time,'HH24'),'04',1,0)),'999') "04",
to_char(sum(decode(to_char(first_time,'HH24'),'05',1,0)),'999') "05",
to_char(sum(decode(to_char(first_time,'HH24'),'06',1,0)),'999') "06",
to_char(sum(decode(to_char(first_time,'HH24'),'07',1,0)),'999') "07",
to_char(sum(decode(to_char(first_time,'HH24'),'08',1,0)),'999') "08",
to_char(sum(decode(to_char(first_time,'HH24'),'09',1,0)),'999') "09",
to_char(sum(decode(to_char(first_time,'HH24'),'10',1,0)),'999') "10",
to_char(sum(decode(to_char(first_time,'HH24'),'11',1,0)),'999') "11",
to_char(sum(decode(to_char(first_time,'HH24'),'12',1,0)),'999') "12",
to_char(sum(decode(to_char(first_time,'HH24'),'13',1,0)),'999') "13",
to_char(sum(decode(to_char(first_time,'HH24'),'14',1,0)),'999') "14",
to_char(sum(decode(to_char(first_time,'HH24'),'15',1,0)),'999') "15",
to_char(sum(decode(to_char(first_time,'HH24'),'16',1,0)),'999') "16",
to_char(sum(decode(to_char(first_time,'HH24'),'17',1,0)),'999') "17",
to_char(sum(decode(to_char(first_time,'HH24'),'18',1,0)),'999') "18",
to_char(sum(decode(to_char(first_time,'HH24'),'19',1,0)),'999') "19",
to_char(sum(decode(to_char(first_time,'HH24'),'20',1,0)),'999') "20",
to_char(sum(decode(to_char(first_time,'HH24'),'21',1,0)),'999') "21",
to_char(sum(decode(to_char(first_time,'HH24'),'22',1,0)),'999') "22",
to_char(sum(decode(to_char(first_time,'HH24'),'23',1,0)),'999') "23"
from
v$log_history
GROUP by to_char(first_time,'YYYY-MM-DD')
每月每天的日志切换次数
select to_char(trunc(first_time), 'Mon') month,
to_char(trunc(first_time), 'YYYY-MM-DD:Day') day,
count(*) count
from v$log_history
where trunc(first_time) > last_day(sysdate - 100) + 1
group by trunc(first_time)
order by 1, 2;
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/16719800/viewspace-675615/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/16719800/viewspace-675615/