Determining Log Switch Frequency

17 Jul

Posted by: olegar in: Administration, DBA, Oracle

Too much log switching is no good for database, you should see numbers not bigger than 6 per hour. Increasing the size of redo log groups is one way to lower log switch frequency, please refer to Oracle documentation on how to lower log switch frequency.

You can use the following query to determine your log switch frequency past 7 days (by hour):

SELECT * FROM (
SELECT * FROM (
SELECT   TO_CHAR(FIRST_TIME, 'DD/MM') AS "DAY"
       , TO_NUMBER(SUM(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '00', 1, 0)), '99') "00:00"
       , TO_NUMBER(SUM(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '01', 1, 0)), '99') "01:00"
       , TO_NUMBER(SUM(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '02', 1, 0)), '99') "02:00"
       , TO_NUMBER(SUM(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '03', 1, 0)), '99') "03:00"
       , TO_NUMBER(SUM(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '04', 1, 0)), '99') "04:00"
       , TO_NUMBER(SUM(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '05', 1, 0)), '99') "05:00"
       , TO_NUMBER(SUM(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '06', 1, 0)), '99') "06:00"
       , TO_NUMBER(SUM(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '07', 1, 0)), '99') "07:00"
       , TO_NUMBER(SUM(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '08', 1, 0)), '99') "08:00"
       , TO_NUMBER(SUM(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '09', 1, 0)), '99') "09:00"
       , TO_NUMBER(SUM(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '10', 1, 0)), '99') "10:00"
       , TO_NUMBER(SUM(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '11', 1, 0)), '99') "11:00"
       , TO_NUMBER(SUM(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '12', 1, 0)), '99') "12:00"
       , TO_NUMBER(SUM(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '13', 1, 0)), '99') "13:00"
       , TO_NUMBER(SUM(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '14', 1, 0)), '99') "14:00"
       , TO_NUMBER(SUM(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '15', 1, 0)), '99') "15:00"
       , TO_NUMBER(SUM(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '16', 1, 0)), '99') "16:00"
       , TO_NUMBER(SUM(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '17', 1, 0)), '99') "17:00"
       , TO_NUMBER(SUM(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '18', 1, 0)), '99') "18:00"
       , TO_NUMBER(SUM(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '19', 1, 0)), '99') "19:00"
       , TO_NUMBER(SUM(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '20', 1, 0)), '99') "20:00"
       , TO_NUMBER(SUM(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '21', 1, 0)), '99') "21:00"
       , TO_NUMBER(SUM(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '22', 1, 0)), '99') "22:00"
       , TO_NUMBER(SUM(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '23', 1, 0)), '99') "23:00"
    FROM V$LOG_HISTORY
    WHERE extract(year FROM FIRST_TIME) = extract(year FROM sysdate)
GROUP BY TO_CHAR(FIRST_TIME, 'DD/MM')
) ORDER BY TO_DATE(extract(year FROM sysdate) || DAY, 'YYYY DD/MM') DESC
) WHERE ROWNUM < 8
/

Bookmark to:

Add 'Determining Log Switch Frequency' to Del.icio.us

Add 'Determining Log Switch Frequency' to digg

Add 'Determining Log Switch Frequency' to FURL

Add 'Determining Log Switch Frequency' to blinklist

Add 'Determining Log Switch Frequency' to reddit

Add 'Determining Log Switch Frequency' to Feed Me Links

Add 'Determining Log Switch Frequency' to Technorati

Add 'Determining Log Switch Frequency' to Yahoo My Web

Add 'Determining Log Switch Frequency' to Newsvine

Add 'Determining Log Switch Frequency' to Socializer

Add 'Determining Log Switch Frequency' to Ma.gnolia

Add 'Determining Log Switch Frequency' to Stumble Upon

Add 'Determining Log Switch Frequency' to Google Bookmarks

Add 'Determining Log Switch Frequency' to RawSugar

Add 'Determining Log Switch Frequency' to Squidoo

Add 'Determining Log Switch Frequency' to Spurl

Add 'Determining Log Switch Frequency' to BlinkBits

Add 'Determining Log Switch Frequency' to Netvouz

Add 'Determining Log Switch Frequency' to Rojo

Add 'Determining Log Switch Frequency' to Blogmarks

Add 'Determining Log Switch Frequency' to Shadows

Add 'Determining Log Switch Frequency' to Simpy

Add 'Determining Log Switch Frequency' to Co.mments

Add 'Determining Log Switch Frequency' to Scuttle

Add 'Determining Log Switch Frequency' to Bloglines

Add 'Determining Log Switch Frequency' to Tailrank

6 Responses

Jon Doe
28|Mar|2008 1

I’m getting an INVALID NUMBER………
gato
28|Mar|2008 2

What version of Oracle are you using?
youngb912
20|Apr|2008 3

It work perfect!!
Mohammad
22|May|2008 4

Thanks for the query.

How can I can extend the retention of this information. Currently, 10G is holding only 30 days. I want to retain the information for a year.
gato
22|May|2008 5

Don’t know if that’s possible If I really wanted that info I’d just preserve if in a regular table using a job or something.
Mike
07|Nov|2008 6

You need to replace all the TO_NUMBERs with TO_CHARs !

OracleLog

Determining Log Switch Frequency

6 Responses

Leave a reply

Search

Categories

Archives

Blogroll

Recent Comments

Meta

Buy me a Coffee

A design creation of Design Disease