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

Maybe the following observation is relevant, maybe not...

Fyi, each 'log file sync' event duration includes the time consumed by the following actions:

1. The delay between when the foreground process posts LGWR and when LGWR wakes up and gets busy.
2. The duration that LGWR consumes doing its job.
3. The delay between when LGWR finishes and when the foreground clears the CPU runqueue awaiting its return to User Mode.

The reflex action is to assume that B is the cause of long 'log file sync' durations. However, because a process that post()s gets a low priority in the CPU runqueue, it's often C that's the culprit. And C can take a long time if the CPU runqueue is long, regardless of whether there's an I/O latency issue or not.

Cary Millsap
Hotsos Enterprises, Ltd.
http://www.hotsos.com

Upcoming events:

- Hotsos Clinic, Dec 9-11 Honolulu
- Hotsos Clinic 101, Jan 7-9 Knoxville
- Steve Adams's Miracle Master Class, Jan 13-15 Copenhagen
- 2003 Hotsos Symposium, Feb 9-12 Dallas

-----Original Message-----
Waleed
Sent: Tuesday, November 26, 2002 5:05 PM To: Multiple recipients of list ORACLE-L

Slow I/O will not increase the CPU time for the lgwr, since this will be I/O
wait time.
Did you try to truss the lgwr process?
What is the size of log_buffer?

Waleed

-----Original Message-----
Sent: Tuesday, November 26, 2002 3:35 PM To: Multiple recipients of list ORACLE-L

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).
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Khedr, Waleed
  INET: Waleed.Khedr_at_FMR.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: Cary Millsap
  INET: cary.millsap_at_hotsos.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).