| Log Switch Frequency Map | DBA之路：OoNiceDream |

Log Switch Frequency Map

作者：OoNiceDream【转载时请务必以超链接形式标明文章原始出处和作者信息】
链接：http://www.dbaroad.me/archives/2008/12/log-switch-frequency-map.html

用TOAD自带的工具SQLMonitor，把Log Switch Frequency Map这一功能的SQL抓了出来。

Log Switch Frequency Map 显示每一天中，各个小时LOG切换的次数。用于体现数据库负载压力，还是有一定作用的。

对于archive模式的数据库，这些数据也可以理解为归档的数量，或者把V$LOG_HISTORY换成V$ARCHIVED_LOG,效果相同。

sys@DB1>@log_history
 
Date       Day     Total  h0  h1  h2  h3  h4  h5  h6  h7  h8  h9 h10 h11 h12 h13 h14 h15 h16 h17 h18 h19 h20 h21 h22 h23
---------- ------ ------ --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- ---
2008-11-28 星期五    106   1   3   2   3   4   4   3   4   4   6   5   5   6   4   6   7   6   6   6   4   4   5   3   5
2008-11-29 星期六    100   2   2   2   3   3   5   2   3   4   6   4   5   5   4   6   6   4   4   6   4   5   6   5   4
2008-11-30 星期日     93   3   1   3   3   1   4   3   3   4   5   6   4   4   5   5   5   6   4   4   3   5   3   4   5
2008-12-01 星期一     82   1   1   3   3   2   3   3   2   4   5   5   5   5   4   4   4   5   4   3   4   2   3   3   4
2008-12-02 星期二    184   2   4   5   4   6   7   6   5   6   5   6   5   5   5   5   5   8  11   7  10  10  15  19  23
2008-12-03 星期三    151  13  14   2   3   2   3   3   4   4   4   5   5   5   6   6  10   7   8  14   6   7   9   6   5
2008-12-04 星期四    107   3   3   2   9   2   3   3   4   4   5   4   6   6   5   8   6   7   4   5   4   3   4   4   3
2008-12-05 星期五     64   1   3   3   2   3   3   3   4   4   5   6   5   6   5   6   5   0   0   0   0   0   0   0   0
 
 
sys@DB1>! cat log_history.sql
alter session set nls_date_format='YYYY-MM-DD';
col Day for a6
col Total for 99999
col h0  for 99
col h1  for 99
col h2  for 99
col h3  for 99
col h4  for 99
col h5  for 99
col h6  for 99
col h7  for 99
col h8  for 99
col h9  for 99
col h10 for 99
col h11 for 99
col h12 for 99
col h13 for 99
col h14 for 99
col h15 for 99
col h16 for 99
col h17 for 99
col h18 for 99
col h19 for 99
col h20 for 99
col h21 for 99
col h22 for 99
col h23 for 99
col h24 for 99
 
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
where   trunc(first_time)>sysdate-8
group by trunc(first_time), to_char(first_time, 'Dy')
Order by 1;
 
alter session set nls_date_format='YYYY-MM-DD HH24:MI:SS';