Debi,
A log file sync event usually indicates that the application is probably
committing too often, and LGWR cannot keep pace with it. You might be
experiencing contention or I/O issues, on the disks where the redo log
files are placed. Moving your redo log files away from RAID 5 is a step in
the right direction. Else, try reducing the number of commits in the
application. If thats not the problem, then you probably have a large value
for log buffer.
My experience is that the top 5 events can be misleading. Sometimes, you
resolve an event at a lower level, and the top ones resolve on their own.
Regards
Raj
"Deshpande, Kirti"
<kirti.deshpande@ve To: Multiple recipients of list
ORACLE-L <[EMAIL PROTECTED]>
rizon.com> cc:
Sent by: Subject: RE: LGWR using lots of CPU
time, low CPU usage
[EMAIL PROTECTED]
November 26, 2002
02:15 PM
Please respond to
ORACLE-L
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
[EMAIL PROTECTED]
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author:
INET: [EMAIL PROTECTED]
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: [EMAIL PROTECTED] (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).
