| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> RE: RE: LGWR using lots of CPU time, low CPU usage
TOTAL Time Taken for a fixed Number of Application Transactions to Complete , (mostly OLTP in nature to Complete ) , Rose by about 3 TIMES the Normal Benchmarked Time .
We found that somehow the session_cached_cursor had been oversized to 200 .
Immediately thereafter , I reduced the session_cached_cursors to 50 , Bounced the Database & did a RE-Run
The performance (TOTAL Time Taken) returned to Normal
Benchmark was Done on Oracle 8.1.7 on Solaris 8
We unfortunately do NOT have any more Details of now
HTH
-----Original Message-----
Sent: Wednesday, November 27, 2002 7:39 AM
To: Multiple recipients of list ORACLE-L
VIVEK_SHARMA,
Can i know how did you get your result of oversizing the session_cached_cursors do harm to performance? My applications do a lot of softparse with pro*C and i used session_cached_cursors=200 in my db. I want to know How did you find it out and can you share your experience? And another add on: lgwr using a lot of cpu time,low cpu usage, does it mean that it look like my profile? I think It is because lgwr is consistantly using cpu , and the database have been up for a long time.So, from ps/top, the total cpu is high,but cpu usage is low?main-db1# /usr/ucb/ps -aux|grep ora_ |grep -v grep |sort +8nr
oracle 1078 0.1 46.857277125697408 ? S Oct 30 220:00 ora_lgwr_biddb oracle 1076 0.1 46.857317205701200 ? S Oct 30 92:37 ora_dbw0_biddb oracle 1086 0.0 46.957328325709560 ? S Oct 30 47:02 ora_snp0_biddb oracle 1088 0.0 46.957335925710896 ? S Oct 30 25:04 ora_snp1_biddb oracle 1094 0.0 46.857275685696760 ? S Oct 30 20:54 ora_arc0_biddb oracle 2662 0.0 46.857275685697472 ? S Oct 30 20:20 ora_arc2_biddb oracle 1597 0.0 46.857275685697456 ? S Oct 30 19:42 ora_arc1_biddb oracle 1092 0.0 46.957323765709312 ? S Oct 30 17:19 ora_snp3_biddb oracle 1090 0.0 46.957344965709648 ? S Oct 30 10:23 ora_snp2_biddb oracle 1096 0.0 47.257831605745720 ? S Oct 30 8:22 ora_p000_biddb oracle 1101 0.0 47.257781605743520 ? S Oct 30 7:36 ora_p002_biddb oracle 1098 0.0 47.257781765743904 ? S Oct 30 6:34 ora_p001_biddb oracle 1103 0.0 47.257781525743576 ? S Oct 30 6:42 ora_p003_biddb oracle 1080 0.0 46.857277125697440 ? S Oct 30 4:21 ora_ckpt_biddb oracle 1107 0.0 47.157770005741416 ? S Oct 30 4:18 ora_p005_biddb oracle 1105 0.0 47.057646325730624 ? S Oct 30 3:20 ora_p004_biddb oracle 1109 0.0 47.157729045736024 ? S Oct 30 2:25 ora_p006_biddb oracle 1111 0.0 47.157728485735952 ? S Oct 30 2:28 ora_p007_biddb oracle 1074 0.0 46.857268085698184 ? S Oct 30 0:00 ora_pmon_biddb oracle 1082 0.0 46.857259365700096 ? S Oct 30 0:59 ora_smon_biddb oracle 1084 0.0 46.857258165699392 ? S Oct 30 0:31 ora_reco_biddb
Regards
zhu chao
Eachnet DBA
86-21-32174588-667
chao_ping_at_vip.163.com
www.happyit.net
www.cnoug.org(Chinese Oracle User Group)
>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).
>>
>
>--
>Please see the official ORACLE-L FAQ: http://www.orafaq.com
>--
>Author: Igor Neyman
> INET: ineyman_at_perceptron.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: VIVEK_SHARMA
> INET: VIVEK_SHARMA_at_infosys.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: chao_ping INET: chao_ping_at_vip.163.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: VIVEK_SHARMA INET: VIVEK_SHARMA_at_infosys.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).Received on Wed Nov 27 2002 - 10:44:35 CST
 |
 |