Re: Rapid Log Switching

20M in 2 to 6 minutes isn't really very much - I assume the puzzle is why have things changed.

You could query v$sesstat joined to v$statname to find out which sessions are generating most redo (statistics "redo size") and then check what they are doing through v$open_cursor, enabling a trace, or sampling the currrent sql_id.

As a random thought, one way to generate lots of 'extra' redo without doing very much work is to add a bitmap index to the table that is subject to a small steady (non-concurrent) trickle of updates. Has any one changed the schema recently.

Regards

Jonathan Lewis
http://jonathanlewis.wordpress.com

Author: Cost Based Oracle: Fundamentals
http://www.jlcomp.demon.co.uk/cbo_book/ind_book.html

The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html

• Original Message ----- From: "Weatherman, John" <John.Weatherman_at_asurion.com> To: <jonathan_at_jlcomp.demon.co.uk>; <oracle-l_at_freelists.org> Sent: Wednesday, February 20, 2008 9:14 PM Subject: RE: Rapid Log Switching

This has been going on for about 19 hours now, and the system isn't being hammered. If it was, I could understand the log switches. But I have 20M logs that are switching when 2-6M in size. I did have problems with AWR before, but that is now under control....and then this starts.....

-----Original Message-----

From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Jonathan Lewis Sent: Wednesday, February 20, 2008 2:52 PM To: oracle-l_at_freelists.org
Subject: Re: Rapid Log Switching

What time did it start ?

Are you aware of the automatic stats collection that takes place in 10g ? This runs to collects stats on objects with stale statistics every night with a window that is something like midnight to 6 am.

Your system might run for weeks doing a couple of minutes of stats collection every (weekday) night, and then decide one night that most of the tables in the system have gone stale and hammers the system for hours on end.

Regards

Jonathan Lewis
http://jonathanlewis.wordpress.com

Author: Cost Based Oracle: Fundamentals
http://www.jlcomp.demon.co.uk/cbo_book/ind_book.html

The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html

• Original Message ----- From: "Weatherman, John" <John.Weatherman_at_asurion.com> To: <oracle-l_at_freelists.org> Cc: "Terrell, Paul" <Paul.Terrell_at_asurion.com>; "Shamim, Aziz" <Aziz.Shamim_at_asurion.com> Sent: Wednesday, February 20, 2008 7:58 PM Subject: Rapid Log Switching

All,

I'm on RH 2.6.9-42.ELsmp x86_64 Linux, running 10.2.0.3. I've been having a lot of log issues for the last week. Thanks to some help here, I seem to have the worst of it under control now.

However, last night I began seeing log switches every 6 minutes. The logs are 20M in size, but are switching between 2-6M. We do have log_checkpoint_timeout set, but it is 1800 seconds (30 minutes), not 6 minutes.

Does anyone have any ideas what other than the timeout or the log filling up would cause a log switch? It's not manual and it's not backups, it's been too consistent and too long for either of those to make any sense. I'm not aware of anything else that should be triggering log switches and haven't been able to find anything suspicious with Google or Metalink.

TIA, John

John P Weatherman john.weatherman_at_asurion.com

Sr Oracle DBA Desk: (615) 762-1376

Asurion, Inc                Mobile: (615) 517-2831

--

http://www.freelists.org/webpage/oracle-l

--

http://www.freelists.org/webpage/oracle-l Received on Wed Feb 20 2008 - 15:53:40 CST