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

Interesting; but would the specific performance affect of a high number of session_cached_cursors involve the LGWR process?

At 11:25 AM 11/26/2002 -0800, you wrote:

>In some of our benchmarks with our hybrid application on Oracle 8.1.7 ,
>Oversizing session_cached_cursors would HARM performance greatly . Our
>Optimal Value is 50
>
>
>-----Original Message-----
>Sent: Wednesday, November 27, 2002 12:20 AM
>To: Multiple recipients of list ORACLE-L
>
>
>Deborah,
>
>First, don't remove Oracle's RedoLog duplexing, you may regret about it
>later (see recent thread on this issue).
>
>Second, if what you are telling ("logs are about 100 MB in 2 groups of 20
>members each") is accurate, then this is your main problem. If you have
>your log switches on avg 2.5 per day, change your RedoLog configuration to
>be: 3 (or 4) groups, 3 members each (if you can put them on separate
>"physical" devices, if not - 2 members should suffice), and you can make
>them smaller, like 50Mb (or even smaller). You will have more log switches
>per day, but it's perfectly fine as long, as don't have them every 5 min.
>
>And "old school" is still right about not putting RedoLogs onto RAID5.
>
>Igor Neyman, OCP DBA
>ineyman_at_perceptron.com
>
>
>
>----- Original Message -----
>To: "Multiple recipients of list ORACLE-L" <ORACLE-L_at_fatcity.com>
>Sent: Tuesday, November 26, 2002 1:00 PM
>
>
> > 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).