redo大小的设置

redo设置好坏对数据库性能影响不言而喻，如何设置，可以通过两个视图一个是 V$log_history和 V$archived_log视图。

 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 RECID                                              NUMBER  控制文件记录ID                         
 STAMP                                              NUMBER 控制文件记录时间戳
 THREAD#                                            NUMBER 归档日志线程号
 SEQUENCE#                                          NUMBER 归档日志序列号
 FIRST_CHANGE#                                      NUMBER 日志中最低SCN
 FIRST_TIME                                         DATE 最低SCN变更时间
 NEXT_CHANGE#                                       NUMBER  日志中最高SCN
 RESETLOGS_CHANGE#                                  NUMBER  不完全恢复resetlogsSCN号
 RESETLOGS_TIME                                     DATE  记录不完全恢复resetlogs时间

1.可以看先v$log视图看先当前的日志组大小

SQL> select group#,members,bytes/1024/1024 from v$log;

    GROUP#    MEMBERS BYTES/1024/1024
---------- ---------- ---------------
         1          2             500
         2          2             500
         3          2             500

2.查看每天日志切换次数大致情况，决定是否要调整

Select round(FIRST_TIME,'DD'),THREAD#,Count(SEQUENCE#) From v$log_history  Group By round(FIRST_TIME,'DD'),THREAD# Order By 1,2

再细的话 具体到每小时（IT pub）

SELECT  trunc(first_time) "Date",
        to_char(first_time, 'Dy') "Day",
        count(1) "Total",
        SUM(decode(to_char(first_time, 'hh24'),'00',1,0)) "h0",
        SUM(decode(to_char(first_time, 'hh24'),'01',1,0)) "h1",
        SUM(decode(to_char(first_time, 'hh24'),'02',1,0)) "h2",
        SUM(decode(to_char(first_time, 'hh24'),'03',1,0)) "h3",
        SUM(decode(to_char(first_time, 'hh24'),'04',1,0)) "h4",
        SUM(decode(to_char(first_time, 'hh24'),'05',1,0)) "h5",
        SUM(decode(to_char(first_time, 'hh24'),'06',1,0)) "h6",
        SUM(decode(to_char(first_time, 'hh24'),'07',1,0)) "h7",
        SUM(decode(to_char(first_time, 'hh24'),'08',1,0)) "h8",
        SUM(decode(to_char(first_time, 'hh24'),'09',1,0)) "h9",
        SUM(decode(to_char(first_time, 'hh24'),'10',1,0)) "h10",
        SUM(decode(to_char(first_time, 'hh24'),'11',1,0)) "h11",
        SUM(decode(to_char(first_time, 'hh24'),'12',1,0)) "h12",
        SUM(decode(to_char(first_time, 'hh24'),'13',1,0)) "h13",
        SUM(decode(to_char(first_time, 'hh24'),'14',1,0)) "h14",
        SUM(decode(to_char(first_time, 'hh24'),'15',1,0)) "h15",
        SUM(decode(to_char(first_time, 'hh24'),'16',1,0)) "h16",
        SUM(decode(to_char(first_time, 'hh24'),'17',1,0)) "h17",
        SUM(decode(to_char(first_time, 'hh24'),'18',1,0)) "h18",
        SUM(decode(to_char(first_time, 'hh24'),'19',1,0)) "h19",
        SUM(decode(to_char(first_time, 'hh24'),'20',1,0)) "h20",
        SUM(decode(to_char(first_time, 'hh24'),'21',1,0)) "h21",
        SUM(decode(to_char(first_time, 'hh24'),'22',1,0)) "h22",
        SUM(decode(to_char(first_time, 'hh24'),'23',1,0)) "h23"
FROM    V$log_history
group by trunc(first_time), to_char(first_time, 'Dy')

Order by 1切换次数*日志组大小/统计时间（单位分钟）=每分钟产生的日志量  取 高峰期内的一段时间 X分钟，Y行数据就是切换次数做参考  Y*500M/X~=Z M/分钟
标准的可以是高峰期3-5分钟 5Z就是调整的大小， 一般15-20分钟切换一次
 

alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss';

prompt ‘--------------enter the value of the most recent rows you wanna take the example for reference to tune the size of redo log--------------------’

select * from (select b.recid,b.first_time,a.first_time last_time,round((a.first_time-b.first_time)*24*60,2) minutes 
from v$log_history a,v$log_history b 
where a.recid=b.recid+1 order by a.first_time desc)
where rownum<&num;