Determine Avg. Log Switch Time Interval

[jtanner]

Hello,

I use the following SQL to determine how often a Redo Log Switch occurs:

SELECT group#,status,first_time FROM v\$log ORDER BY group#;

Can someone recommend SQL that takes this a step further and actually determines the average log switch interval time for the last n hours (say 24)?

I have a hunch some of you guys already do something like this.


Thanks,

JT

 

[SantaMufasa]

JT,

Your database instance's alert log documents each log switch. Therefore, I recommend that you create an "organization external" table of your alert log, then query your alert log for entries that relate to log switches. Those entries contain date/time stamps from which you can calculate not only mean/average times between log switches, but you can also determine under what circumstances your log switches become "hot" (i.e., when log switches become more rapid than normal).

There are other threads to which we have posted that show how to turn your alert log into an externally organised table, but if you need/want additional guidance on how to do this, then please reply either to this thread or create a new thread asking "How do I turn my alert log into an external table."

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]