Redo logfile切换频率计算

查看Oracle的redo日志切换频率

两个sql，原理是一样的，第二个用到了统计函数。

 

时间单位：分钟

方法一、

select * from v$log　a　where a.THREAD# = 1 ;

 

set  pagesize  49999

set wrap off 

 set line 200

select b.SEQUENCE#, b.FIRST_TIME,a.SEQUENCE#,a.FIRST_TIME,round(((a.FIRST_TIME-b.FIRST_TIME)*24)*60,2) from v$log_history a, v$log_history b where a.SEQUENCE#=b.SEQUENCE#+1 and b.THREAD#=1 order by a.SEQUENCE# desc;

        12 2013-02-25 09:42:02         13 2013-02-25 09:42:14                                           .2
        11 2013-02-25 09:40:59         12 2013-02-25 09:42:02                                        1.05
        10 2013-02-25 09:40:19         11 2013-02-25 09:40:59                                           .67
         9 2013-02-25 09:38:23         10 2013-02-25 09:40:19                                          1.93
         8 2013-02-25 09:36:19          9 2013-02-25 09:38:23                                            2.07
         7 2013-02-25 09:34:07          8 2013-02-25 09:36:19                                            2.2
         6 2013-02-25 09:30:56          7 2013-02-25 09:34:07                                            3.18
         5 2013-02-25 09:27:38          6 2013-02-25 09:30:56                                            3.3
         4 2013-02-25 09:23:55          5 2013-02-25 09:27:38                                            3.72
         3 2013-02-25 09:20:30          4 2013-02-25 09:23:55                                            3.42
         2 2013-02-25 09:13:55          3 2013-02-25 09:20:30                                            6.58
         1 2013-02-25 09:06:45          2 2013-02-25 09:13:55                                            7.17   单位：min

方法二、

   

set  pagesize  49999

set wrap off 

set line 200

select sequence#,first_time,nexttime,round(((first_time-nexttime)*24)*60,2) diff from (select sequence#,first_time,lag(first_time) over(order by sequence#) nexttime from v$log_history where thread#=1) order by sequence# desc;

 

        10 2013-02-25 09:40:19 2013-02-25 09:38:23       1.93
         9 2013-02-25 09:38:23 2013-02-25 09:36:19        2.07
         8 2013-02-25 09:36:19 2013-02-25 09:34:07        2.2
         7 2013-02-25 09:34:07 2013-02-25 09:30:56        3.18
         6 2013-02-25 09:30:56 2013-02-25 09:27:38        3.3
         5 2013-02-25 09:27:38 2013-02-25 09:23:55        3.72
         4 2013-02-25 09:23:55 2013-02-25 09:20:30        3.42
         3 2013-02-25 09:20:30 2013-02-25 09:13:55        6.58
         2 2013-02-25 09:13:55 2013-02-25 09:06:45        7.17            单位：min
         1 2013-02-25 09:06:45

 

源文档 <http://www.linuxidc.com/Linux/2012-09/69865.htm>