RE: LGWR using lots of CPU time, low CPU usage

Thanks for this paper--I will share it with our Sun engineer.

BTW, to clarify, I have 20 redo groups with 2 members each...I think I'm saying it right:

LOGFILE Group 1 ('/disk1/log01.log',

                  '/disk2/log01.log') size 100M,
         Group 2 ('/disk1/log02.log',
                  '/disk2/log02.log') size 100M,
...
         Group 20 ('/disk1/log20.log',
                   '/disk2/log20.log') size 100M

The oldest log in the group is dated yesterday; but more than half have today's date. I had one period today where there were 4 switches less than a minute apart!!

Debi

At 11:15 AM 11/26/2002 -0800, you wrote:
>So what is discussed in this paper is outdated already....uh!
>www.sun.com/blueprints/0101/SunOracle.pdf Gosh, time flies ;) Pages 13-14
>talk about Oracle Redo Logs.
>
>As a first attempt, I would consider reducing the number of log members
>(from 20 to 4, or even 3) than removing them altogether. This will be of
>some help right away. But monitor further and decide if more Groups are
>needed to help archiver process.
>
>Do not change multiple things at the same time.
>
>Good Luck,
>
>- Kirti
>
>-----Original Message-----
>Sent: Tuesday, November 26, 2002 12:00 PM
>To: Multiple recipients of list ORACLE-L
>
>
>We are on 9.2.0.2, Solaris 8 on Sunfire 3800 with 16 GB memory and 128 MB
>on a hardware-controlled, mirrored RAID5 StorEdge T-3 Array.
>
>Periodically throughout the day the LGWR background process clocks 20+
>minutes of CPU time while actual CPU usage is quite low. I ran a statspack
>report and for a 45-minute period that included the slow LGWR process.
>
>The top 5 timed events in my 45-minute report are:
>
>CPU time 1,295 60.41
>db file sequential read 392,516 341 15.91
>db file scattered read 70,245 168 7.85
>log file sync 26,916 133 6.22
>library cache pin 22 59 2.76
>
>(Now that the top 5 is "timed" events, 3 spots almost always include CPU
>and the db file reads, so I only get two other events, usually log file
>sync, sometimes enqueue or latch free.)
>
>Statspack also shows the log file parallel write had 28,589 timeouts in
>that 45 minute period--rather typical for us.
>
>I have session_cached_cursors set to 150.
>
>I am considering the following:
>
>1. Removing my own redo log duplexing (mirroring) since redo logs are on
>the mirrored, hardware-controlled RAID5 disk array. (I know, I know)
>My sysadmin talked to the sun engineer yesterday and he said this is
>"old school" thinking that redo logs should not be on RAID5. He said
>because the RAID controller caches to memory all IO requests from
>the CPUs, all physical writes to disk are done behind the scenes
>(known as writebehind). He says the system is NOT waiting for IO.
>
>2. Increasing redo log size (again). For the most part, log switches
>average 2.5 per day, although there were 20 times in the last month of 3-7
>switches in a half hour. My logs are about 100 MB in 2 groups of 20 members
>each.
>
>3. Upping the session_cached_cursors to ? (in response to the library cache
>pin event).
>
>Or is there a better option I'm overlooking?
>
>I would appreciate some advise on the best approach to resolve the slow
>LGWR process, especially your thoughts on option 1.
>
>Thanks,
>Debi
>Deborah Lorraine, DBA
>University of California, Davis
>dlorraine_at_ucdavis.edu
>
>--
>Please see the official ORACLE-L FAQ: http://www.orafaq.com
>--
>Author: Deborah Lorraine
> INET: debil_at_ucdavis.edu
>
>Fat City Network Services -- 858-538-5051 http://www.fatcity.com
>San Diego, California -- Mailing list and web hosting services
>---------------------------------------------------------------------
>To REMOVE yourself from this mailing list, send an E-Mail message
>to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
>the message BODY, include a line containing: UNSUB ORACLE-L
>(or the name of mailing list you want to be removed from). You may
>also send the HELP command for other information (like subscribing).
>
>
>
>--
>Please see the official ORACLE-L FAQ: http://www.orafaq.com
>--
>Author: Deshpande, Kirti
> INET: kirti.deshpande_at_verizon.com
>
>Fat City Network Services -- 858-538-5051 http://www.fatcity.com
>San Diego, California -- Mailing list and web hosting services
>---------------------------------------------------------------------
>To REMOVE yourself from this mailing list, send an E-Mail message
>to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
>the message BODY, include a line containing: UNSUB ORACLE-L
>(or the name of mailing list you want to be removed from). You may
>also send the HELP command for other information (like subscribing).

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Deborah Lorraine
  INET: debil_at_ucdavis.edu

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).