查看日志文件切换频率语句

查看每次日志切换的时间

select   sequence#,

prior_time,

first_time,

round(((first_time-prior_time)*24)*60,2) diff 

from (select  sequence#,

first_time,

lag(first_time) over(order by sequence#) prior_time 

from v$log_history where thread#=1) 

order by sequence# desc;

或 

直接查看平均切换时间

select avg(diff) avg_switch_time_min

from (select  sequence#,

prior_time,

first_time,round(((first_time-prior_time)*24)*60,2) diff 

from (select  sequence#,

first_time,

lag(first_time) over(order by sequence#) prior_time 

from v$log_history where thread#=1) 

order by sequence# desc);

或

查看一天内日志切换总次数及所生成的归档日志大小

select max (first_time) max_first_time,

         to_char (first_time, 'yyyy-mm-dd') day,

         count (recid) count_number,

         count (recid) * 200 size_mb

 from v$log_history

group by to_char (first_time, 'yyyy-mm-dd')

order by 1；

修改日志切换频率：

alter system set archive_lag_target=1800;

--修改日志切换为每半小时做一次归档

关于参数 ARCHIVE_LAG_TARGET

ARCHIVE_LAG_TARGET forces a log switch after the specified amount of time elapses.

A 0 value disables the time-based thread advance feature; otherwise, the value represents the number of seconds. Values larger than 7200 seconds are not of much use in maintaining a reasonable lag in the standby database. The typical, orrecommended value is 1800 (30 minutes). Extremely low values can result in frequent log switches, which could degrade performance; such values can also make the archiver process too busy to archive the continuously generated logs.

--日志切换过于频繁可能会引起性能下降，而日志切换频度过低，则可能会对数据库恢复有一定影响。比如实例恢复时间过长，或数据丢失量增加

关于分析函数lag

 

LAG  is an analytic function. It provides access to more than one row of a table at the same time  without a self join . Given a series of rows returned from a query and a position of the cursor,  LAG  provides access to a row at a given physical offset  prior  to that position.

--lag函数给出的是一个 早于/先于 当前位置的一个偏移值

For the optional offset argument, specify an integer that is greater than zero. If you do not specify offset, then its default is 1. The optional default value is returned if the offset goes beyond the scope of the window. If you do not specify default, then its default is null.

{RESPECT | IGNORE} NULLS determines whether null values of value_expr are included in or eliminated from the calculation. The default is RESPECT NULLS.

You cannot nest analytic functions by using LAG or any other analytic function for value_expr. However, you can use other built-in function expressions forvalue_expr.

Reference：

http://blog.itpub.net/519536/viewspace-683993   access12-23-2013

http://docs.oracle.com/cd/E11882_01/server.112/e41084/functions082.htm#SQLRF51463  access12-23-2013