Re: LGWR using lots of CPU time, low CPU usage
I am that specific customer for whom that bug was opened. If you need more information on this then let me know. This issue is still being worked on by the group which wrote 9idataguard and the problem is not diagnosed yet. What we noticed was that some archiver-rfs transfer processes become extremely slow in sending data while others were ok. So I implemented a job which runs every 10 minutes, looks for archvielog transfers which have taken more than 25 minutes. Kills those processes and then rfs spawns new processes which work just fine. We are using 3 log archive dests 1) local 2) local standby 3) DR standby 2000 miles away. We generate more than 300GB archive logs/day I do not receive messages that I send to the list, can someone help me out with this problem. Thanks Shaleen - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Tuesday, November 26, 2002 3:44 PM I see there has already been a lot of discussion on this topic. I would like to throw out one more possibility. It could be related to bug 2564886. If you read the bug on metalink, it probably won't make any sense because it is written for a specific customer. However, I have a similar problem and Oracle has classified my tar as related to this bug. Basically, if you use more than one log_arch_dest occasionally one of the archive process will just take forever. You didn't mention if you were using that parameter or if you are using a standby database so it may not apply to you. While oracle is working on this bug, we have disabled the second log_arch_dest and we have a script to manually check every minute and copy the archive logs to the other destination. This has helped us. Maybe it can help you to. We are on Sun Solaris 7 with 9.2.0.1 but the bug goes back to 9.0.1.3 so it probably applies to 9.2.0.2 also. HTH, John [EMAIL PROTECTED] 11/26/02 10:00AM 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: Deborah Lorraine 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: John Carlson 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
Re: LGWR using lots of CPU time, low CPU usage
...turn your back on this list for a few days and look what happens (besides 440 new emails to scan-and-delete)... Well Jared, I can't think of any more potent weapon than single-malt scotch. Shotglasses at sunset... - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Wednesday, November 27, 2002 12:44 PM they'll both be here in two weeks, I'm selling tickets to the fight now :) --- Fink, Dan [EMAIL PROTECTED] wrote: You best be careful, Jared. You KNOW how uptight and evil Tim can be! :) -Original Message- Sent: Wednesday, November 27, 2002 9:56 AM To: Multiple recipients of list ORACLE-L Oh boy, is my face red! I remembered that of course, as soon as I saw this. I need to keep better track of who I'm plagierizing. :) Jared Cary Millsap [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 11/26/2002 03:05 PM Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject:RE: LGWR using lots of CPU time, low CPU usage The ultimate sincerest form of flattery is for someone to attribute something smart to you that you wish you had done but, alas, did not actually do. (It was Tim Gorman who posted the excellent analogy.) 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- [EMAIL PROTECTED] Sent: Tuesday, November 26, 2002 4:07 PM To: Multiple recipients of list ORACLE-L And old school is still right about not putting RedoLogs onto RAID5. From what I'm being told, this is not your father's RAID5. This is what they tell me: The CPU hands the IO to the disk controller and rather than do the physical disk IO while the process waits, the disk controller caches it to local memory and says done. Therefore, effectively there is no wait for IO and it doesn't matter if we are RAID 5 or RAID 0+1, the system is NOT waiting for the IO. He said the only time there might be a delay is during the cache's battery refresh times. I checked your dates and it was not occurring during those times. Also, if you look at the iostat statistics under the 'wait' and '%w' headers you will see all zeros. Debi, That is true, up to a point. Think of the cache as a water tank. You have a garden hose filling up the tank. You can keep increasing the water pressure for a while. But the outlet at the other end of the tank has a fixed capacity. It flows 10 GPM, and no more. What happens when you increase the flow at the intake to 20 GPM? The tank fills up. When the tank fills up, your intake flow will need to decrease, because you can only flow 10 GPM at the outlet. Now, think of the outlet as writing to disk, the RAID5 cache is the water tank, and your database is the inlet that wants to run at 20 GPM. If your database activity will never be intensive enough to stress the cache like this, no problem. But 'never' is a very long time. If any of this sound familiar, Cary Millsap posted a very similar explanation a few weeks ago. Plagierism is the sincerest form of flattery. :) Jared -- 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Cary Millsap 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538
Re: LGWR using lots of CPU time, low CPU usage
for a front row seat at this gunfight, I will gladly buy at least one round :) --- Tim Gorman [EMAIL PROTECTED] wrote: ...turn your back on this list for a few days and look what happens (besides 440 new emails to scan-and-delete)... Well Jared, I can't think of any more potent weapon than single-malt scotch. Shotglasses at sunset... - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Wednesday, November 27, 2002 12:44 PM they'll both be here in two weeks, I'm selling tickets to the fight now :) --- Fink, Dan [EMAIL PROTECTED] wrote: You best be careful, Jared. You KNOW how uptight and evil Tim can be! :) -Original Message- Sent: Wednesday, November 27, 2002 9:56 AM To: Multiple recipients of list ORACLE-L Oh boy, is my face red! I remembered that of course, as soon as I saw this. I need to keep better track of who I'm plagierizing. :) Jared Cary Millsap [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 11/26/2002 03:05 PM Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject:RE: LGWR using lots of CPU time, low CPU usage The ultimate sincerest form of flattery is for someone to attribute something smart to you that you wish you had done but, alas, did not actually do. (It was Tim Gorman who posted the excellent analogy.) 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- [EMAIL PROTECTED] Sent: Tuesday, November 26, 2002 4:07 PM To: Multiple recipients of list ORACLE-L And old school is still right about not putting RedoLogs onto RAID5. From what I'm being told, this is not your father's RAID5. This is what they tell me: The CPU hands the IO to the disk controller and rather than do the physical disk IO while the process waits, the disk controller caches it to local memory and says done. Therefore, effectively there is no wait for IO and it doesn't matter if we are RAID 5 or RAID 0+1, the system is NOT waiting for the IO. He said the only time there might be a delay is during the cache's battery refresh times. I checked your dates and it was not occurring during those times. Also, if you look at the iostat statistics under the 'wait' and '%w' headers you will see all zeros. Debi, That is true, up to a point. Think of the cache as a water tank. You have a garden hose filling up the tank. You can keep increasing the water pressure for a while. But the outlet at the other end of the tank has a fixed capacity. It flows 10 GPM, and no more. What happens when you increase the flow at the intake to 20 GPM? The tank fills up. When the tank fills up, your intake flow will need to decrease, because you can only flow 10 GPM at the outlet. Now, think of the outlet as writing to disk, the RAID5 cache is the water tank, and your database is the inlet that wants to run at 20 GPM. If your database activity will never be intensive enough to stress the cache like this, no problem. But 'never' is a very long time. If any of this sound familiar, Cary Millsap posted a very similar explanation a few weeks ago. Plagierism is the sincerest form of flattery. :) Jared -- 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Cary Millsap 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
RE: LGWR using lots of CPU time, low CPU usage
I should have said spread across local drives. [EMAIL PROTECTED] 11/26/02 05:35PM H. I got to thinking about a previous reply which was while doingsomething else:I don't think changing the logmembers will do much goodI agree. 4 groups, they are on the local drive.BOOM!!Then got to thinking This is not right at all. I think I was making asubconscious interpretation based on the context of your usage of the terms.If your groups look likeGROUP1 redo_01a.dbf_or_log redo_01b.dbf_or_log redo_01c.dbf_or_logGROUP2 redo_02a.dbf_or_log redo_02b.dbf_or_log redo_02c.dbf_or_logetc.Then, this all this extra writing will definitely incur overhead.Now, the part about it all being on "the local drive": That still is BOOM!!-- Please see the official ORACLE-L FAQ: http://www.orafaq.com-- Author: Stephen Lee INET: [EMAIL PROTECTED]Fat City Network Services -- 858-538-5051 http://www.fatcity.comSan Diego, California -- Mailing list and web hosting services-To REMOVE yourself from this mailing list, send an E-Mail messageto: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and inthe message BODY, include a line containing: UNSUB ORACLE-L(or the name of mailing list you want to be removed from). You mayalso send the HELP command for other information (like subscribing).
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 oracle1078 0.1 46.857277125697408 ?S Oct 30 220:00 ora_lgwr_biddb oracle1076 0.1 46.857317205701200 ?S Oct 30 92:37 ora_dbw0_biddb oracle1086 0.0 46.957328325709560 ?S Oct 30 47:02 ora_snp0_biddb oracle1088 0.0 46.957335925710896 ?S Oct 30 25:04 ora_snp1_biddb oracle1094 0.0 46.857275685696760 ?S Oct 30 20:54 ora_arc0_biddb oracle2662 0.0 46.857275685697472 ?S Oct 30 20:20 ora_arc2_biddb oracle1597 0.0 46.857275685697456 ?S Oct 30 19:42 ora_arc1_biddb oracle1092 0.0 46.957323765709312 ?S Oct 30 17:19 ora_snp3_biddb oracle1090 0.0 46.957344965709648 ?S Oct 30 10:23 ora_snp2_biddb oracle1096 0.0 47.257831605745720 ?S Oct 30 8:22 ora_p000_biddb oracle1101 0.0 47.257781605743520 ?S Oct 30 7:36 ora_p002_biddb oracle1098 0.0 47.257781765743904 ?S Oct 30 6:34 ora_p001_biddb oracle1103 0.0 47.257781525743576 ?S Oct 30 6:42 ora_p003_biddb oracle1080 0.0 46.857277125697440 ?S Oct 30 4:21 ora_ckpt_biddb oracle1107 0.0 47.157770005741416 ?S Oct 30 4:18 ora_p005_biddb oracle1105 0.0 47.057646325730624 ?S Oct 30 3:20 ora_p004_biddb oracle1109 0.0 47.157729045736024 ?S Oct 30 2:25 ora_p006_biddb oracle 0.0 47.157728485735952 ?S Oct 30 2:28 ora_p007_biddb oracle1074 0.0 46.857268085698184 ?S Oct 30 0:00 ora_pmon_biddb oracle1082 0.0 46.857259365700096 ?S Oct 30 0:59 ora_smon_biddb oracle1084 0.0 46.857258165699392 ?S Oct 30 0:31 ora_reco_biddb Regards zhu chao Eachnet DBA 86-21-32174588-667 [EMAIL PROTECTED] www.happyit.net www.cnoug.org(Chinese Oracle User Group) === 2002-11-26 11:25:00 ,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 [EMAIL PROTECTED] - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] 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
RE: LGWR using lots of CPU time, low CPU usage
Oh boy, is my face red! I remembered that of course, as soon as I saw this. I need to keep better track of who I'm plagierizing. :) Jared Cary Millsap [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 11/26/2002 03:05 PM Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject:RE: LGWR using lots of CPU time, low CPU usage The ultimate sincerest form of flattery is for someone to attribute something smart to you that you wish you had done but, alas, did not actually do. (It was Tim Gorman who posted the excellent analogy.) 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- [EMAIL PROTECTED] Sent: Tuesday, November 26, 2002 4:07 PM To: Multiple recipients of list ORACLE-L And old school is still right about not putting RedoLogs onto RAID5. From what I'm being told, this is not your father's RAID5. This is what they tell me: The CPU hands the IO to the disk controller and rather than do the physical disk IO while the process waits, the disk controller caches it to local memory and says done. Therefore, effectively there is no wait for IO and it doesn't matter if we are RAID 5 or RAID 0+1, the system is NOT waiting for the IO. He said the only time there might be a delay is during the cache's battery refresh times. I checked your dates and it was not occurring during those times. Also, if you look at the iostat statistics under the 'wait' and '%w' headers you will see all zeros. Debi, That is true, up to a point. Think of the cache as a water tank. You have a garden hose filling up the tank. You can keep increasing the water pressure for a while. But the outlet at the other end of the tank has a fixed capacity. It flows 10 GPM, and no more. What happens when you increase the flow at the intake to 20 GPM? The tank fills up. When the tank fills up, your intake flow will need to decrease, because you can only flow 10 GPM at the outlet. Now, think of the outlet as writing to disk, the RAID5 cache is the water tank, and your database is the inlet that wants to run at 20 GPM. If your database activity will never be intensive enough to stress the cache like this, no problem. But 'never' is a very long time. If any of this sound familiar, Cary Millsap posted a very similar explanation a few weeks ago. Plagierism is the sincerest form of flattery. :) Jared -- 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Cary Millsap 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). -- 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).
RE: LGWR using lots of CPU time, low CPU usage
You best be careful, Jared. You KNOW how uptight and evil Tim can be! :) -Original Message- Sent: Wednesday, November 27, 2002 9:56 AM To: Multiple recipients of list ORACLE-L Oh boy, is my face red! I remembered that of course, as soon as I saw this. I need to keep better track of who I'm plagierizing. :) Jared Cary Millsap [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 11/26/2002 03:05 PM Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject:RE: LGWR using lots of CPU time, low CPU usage The ultimate sincerest form of flattery is for someone to attribute something smart to you that you wish you had done but, alas, did not actually do. (It was Tim Gorman who posted the excellent analogy.) 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- [EMAIL PROTECTED] Sent: Tuesday, November 26, 2002 4:07 PM To: Multiple recipients of list ORACLE-L And old school is still right about not putting RedoLogs onto RAID5. From what I'm being told, this is not your father's RAID5. This is what they tell me: The CPU hands the IO to the disk controller and rather than do the physical disk IO while the process waits, the disk controller caches it to local memory and says done. Therefore, effectively there is no wait for IO and it doesn't matter if we are RAID 5 or RAID 0+1, the system is NOT waiting for the IO. He said the only time there might be a delay is during the cache's battery refresh times. I checked your dates and it was not occurring during those times. Also, if you look at the iostat statistics under the 'wait' and '%w' headers you will see all zeros. Debi, That is true, up to a point. Think of the cache as a water tank. You have a garden hose filling up the tank. You can keep increasing the water pressure for a while. But the outlet at the other end of the tank has a fixed capacity. It flows 10 GPM, and no more. What happens when you increase the flow at the intake to 20 GPM? The tank fills up. When the tank fills up, your intake flow will need to decrease, because you can only flow 10 GPM at the outlet. Now, think of the outlet as writing to disk, the RAID5 cache is the water tank, and your database is the inlet that wants to run at 20 GPM. If your database activity will never be intensive enough to stress the cache like this, no problem. But 'never' is a very long time. If any of this sound familiar, Cary Millsap posted a very similar explanation a few weeks ago. Plagierism is the sincerest form of flattery. :) Jared -- 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Cary Millsap 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). -- 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Fink, Dan INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list
RE: LGWR using lots of CPU time, low CPU usage
they'll both be here in two weeks, I'm selling tickets to the fight now :) --- Fink, Dan [EMAIL PROTECTED] wrote: You best be careful, Jared. You KNOW how uptight and evil Tim can be! :) -Original Message- Sent: Wednesday, November 27, 2002 9:56 AM To: Multiple recipients of list ORACLE-L Oh boy, is my face red! I remembered that of course, as soon as I saw this. I need to keep better track of who I'm plagierizing. :) Jared Cary Millsap [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 11/26/2002 03:05 PM Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject:RE: LGWR using lots of CPU time, low CPU usage The ultimate sincerest form of flattery is for someone to attribute something smart to you that you wish you had done but, alas, did not actually do. (It was Tim Gorman who posted the excellent analogy.) 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- [EMAIL PROTECTED] Sent: Tuesday, November 26, 2002 4:07 PM To: Multiple recipients of list ORACLE-L And old school is still right about not putting RedoLogs onto RAID5. From what I'm being told, this is not your father's RAID5. This is what they tell me: The CPU hands the IO to the disk controller and rather than do the physical disk IO while the process waits, the disk controller caches it to local memory and says done. Therefore, effectively there is no wait for IO and it doesn't matter if we are RAID 5 or RAID 0+1, the system is NOT waiting for the IO. He said the only time there might be a delay is during the cache's battery refresh times. I checked your dates and it was not occurring during those times. Also, if you look at the iostat statistics under the 'wait' and '%w' headers you will see all zeros. Debi, That is true, up to a point. Think of the cache as a water tank. You have a garden hose filling up the tank. You can keep increasing the water pressure for a while. But the outlet at the other end of the tank has a fixed capacity. It flows 10 GPM, and no more. What happens when you increase the flow at the intake to 20 GPM? The tank fills up. When the tank fills up, your intake flow will need to decrease, because you can only flow 10 GPM at the outlet. Now, think of the outlet as writing to disk, the RAID5 cache is the water tank, and your database is the inlet that wants to run at 20 GPM. If your database activity will never be intensive enough to stress the cache like this, no problem. But 'never' is a very long time. If any of this sound familiar, Cary Millsap posted a very similar explanation a few weeks ago. Plagierism is the sincerest form of flattery. :) Jared -- 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Cary Millsap 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). -- 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
Re: LGWR using lots of CPU time, low CPU usage
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 [EMAIL PROTECTED] - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] 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 [EMAIL PROTECTED] -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Deborah Lorraine 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Igor Neyman 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).
RE: LGWR using lots of CPU time, low CPU usage
So what is discussed in this paper is outdated alreadyuh! 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: Deborah Lorraine 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Deshpande, Kirti 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).
RE: LGWR using lots of CPU time, low CPU usage
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 [EMAIL PROTECTED] - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] 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 [EMAIL PROTECTED] -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Deborah Lorraine 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Igor Neyman 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: VIVEK_SHARMA 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
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 [EMAIL PROTECTED] - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] 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 [EMAIL PROTECTED] -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Deborah Lorraine 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).
Re: LGWR using lots of CPU time, low CPU usage
At 10:50 AM 11/26/2002 -0800, you wrote: 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 snip You will have more log switches per day, but it's perfectly fine as long, as don't have them every 5 min. Oracle tells me to set redo logs according to the busiest period of log switching, even if they happen once a week. At least 20 times in the last month I had log switches with 1-3 minutes between switches. Accordingly to Oracle, I should increase their size again! And old school is still right about not putting RedoLogs onto RAID5. From what I'm being told, this is not your father's RAID5. This is what they tell me: The CPU hands the IO to the disk controller and rather than do the physical disk IO while the process waits, the disk controller caches it to local memory and says done. Therefore, effectively there is no wait for IO and it doesn't matter if we are RAID 5 or RAID 0+1, the system is NOT waiting for the IO. He said the only time there might be a delay is during the cache's battery refresh times. I checked your dates and it was not occurring during those times. Also, if you look at the iostat statistics under the 'wait' and '%w' headers you will see all zeros. I did run iostat while this logwriter process was clunking away and they were all zeros. I have a bottleneck, but there is no indication it is in disk. Debi -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Deborah Lorraine 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).
RE: LGWR using lots of CPU time, low CPU usage
I don't think changing the logmembers will do much good - I have a v880 with 16 GB and a Hitachi san. I have only 3 logmembers - 4 groups, they are on the local drive. I have some kind of performance issue with periodic slowdowns (47 minutes lgwr cpu in one day on a very low transaction system) - still working on the tar. A very odd scenario - an export file which takes 3 minutes to import on my laptop db - takes 8 minutes on to import on the production db if it's on the san. if I move the dmp to the local drive and do the import it takes 20 minutes. [EMAIL PROTECTED] 11/26/02 01:15PM So what is discussed in this paper is outdated alreadyuh! 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 PMTo: Multiple recipients of list ORACLE-LWe 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.41db file sequential read 392,516 341 15.91db file scattered read 70,245 168 7.85log file sync 26,916 133 6.22library 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 onthe 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 saidbecause the RAID controller caches to memory all IO requests fromthe 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,DebiDeborah Lorraine, DBAUniversity of California, Davis[EMAIL PROTECTED] -- Please see the official ORACLE-L FAQ: http://www.orafaq.com-- Author: Deborah Lorraine INET: [EMAIL PROTECTED]Fat City Network Services -- 858-538-5051 http://www.fatcity.comSan Diego, California -- Mailing list and web hosting services-To REMOVE yourself from this mailing list, send an E-Mail messageto: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and inthe message BODY, include a line containing: UNSUB ORACLE-L(or the name of mailing list you want to be removed from). You mayalso send the HELP command for other information (like subscribing).-- Please see the official ORACLE-L FAQ: http://www.orafaq.com-- Author: Deshpande, Kirti INET: [EMAIL PROTECTED]Fat City Network Services -- 858-538-5051 http://www.fatcity.comSan Diego, California -- Mailing list and web hosting services-To REMOVE yourself from this mailing list, send an E-Mail messageto: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and inthe message BODY, include a line containing: UNSUB ORACLE-L(or the name of mailing list you want to be removed from). You mayalso send the HELP command for other information (like subscribing).
RE: LGWR using lots of CPU time, low CPU usage
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@veTo: 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 alreadyuh! 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
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 alreadyuh!
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: Deborah Lorraine
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).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Deshpande, Kirti
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).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Deborah Lorraine
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
RE: LGWR using lots of CPU time, low CPU usage
These are out of left field, but have bit me in the past. Take with a big ol' grain of salt. Is the application issuing a 'COMMIT' after each 'SELECT'? This will cause a commit entry to be written to the log buffer and the buffer to be flushed. It could explain why LGWR is consuming time and not much usage. I also believe that this causes all of the process memory slots to be examined for pending commits. Has someone changed the priority of the LGWR process? Other issues (# of members, size, etc) have already been addressed accurately. -Original Message- Sent: Tuesday, November 26, 2002 11:00 AM 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: Deborah Lorraine 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Fink, Dan 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).
Re: LGWR using lots of CPU time, low CPU usage
And old school is still right about not putting RedoLogs onto RAID5. From what I'm being told, this is not your father's RAID5. This is what they tell me: The CPU hands the IO to the disk controller and rather than do the physical disk IO while the process waits, the disk controller caches it to local memory and says done. Therefore, effectively there is no wait for IO and it doesn't matter if we are RAID 5 or RAID 0+1, the system is NOT waiting for the IO. He said the only time there might be a delay is during the cache's battery refresh times. I checked your dates and it was not occurring during those times. Also, if you look at the iostat statistics under the 'wait' and '%w' headers you will see all zeros. Debi, That is true, up to a point. Think of the cache as a water tank. You have a garden hose filling up the tank. You can keep increasing the water pressure for a while. But the outlet at the other end of the tank has a fixed capacity. It flows 10 GPM, and no more. What happens when you increase the flow at the intake to 20 GPM? The tank fills up. When the tank fills up, your intake flow will need to decrease, because you can only flow 10 GPM at the outlet. Now, think of the outlet as writing to disk, the RAID5 cache is the water tank, and your database is the inlet that wants to run at 20 GPM. If your database activity will never be intensive enough to stress the cache like this, no problem. But 'never' is a very long time. If any of this sound familiar, Cary Millsap posted a very similar explanation a few weeks ago. Plagierism is the sincerest form of flattery. :) Jared -- 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).
RE: LGWR using lots of CPU time, low CPU usage
Debi,
Is there a way to even out the 'spikey' i/o activity? What processes
are doing this activity and can they be spread out? Is the slow LGWR process
causing user-visible performance problems? Is the LGWR issue a symptom and
not the real problem?
Sorry, no answers, just some questions to think about...
-Original Message-
Sent: Tuesday, November 26, 2002 1: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 alreadyuh!
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: Deborah Lorraine
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).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Deshpande, Kirti
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).
--
Please
RE: LGWR using lots of CPU time, low CPU usage
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 alreadyuh!
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: Deborah Lorraine
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).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Deshpande, Kirti
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).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Deborah Lorraine
INET: [EMAIL PROTECTED]
Fat City Network Services-- 858-538-5051
RE: LGWR using lots of CPU time, low CPU usage
I don't think changing the logmembers will do much good I agree. 4 groups, they are on the local drive. BOOM!! -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Stephen Lee 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).
RE: LGWR using lots of CPU time, low CPU usage
The ultimate sincerest form of flattery is for someone to attribute something smart to you that you wish you had done but, alas, did not actually do. (It was Tim Gorman who posted the excellent analogy.) 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- [EMAIL PROTECTED] Sent: Tuesday, November 26, 2002 4:07 PM To: Multiple recipients of list ORACLE-L And old school is still right about not putting RedoLogs onto RAID5. From what I'm being told, this is not your father's RAID5. This is what they tell me: The CPU hands the IO to the disk controller and rather than do the physical disk IO while the process waits, the disk controller caches it to local memory and says done. Therefore, effectively there is no wait for IO and it doesn't matter if we are RAID 5 or RAID 0+1, the system is NOT waiting for the IO. He said the only time there might be a delay is during the cache's battery refresh times. I checked your dates and it was not occurring during those times. Also, if you look at the iostat statistics under the 'wait' and '%w' headers you will see all zeros. Debi, That is true, up to a point. Think of the cache as a water tank. You have a garden hose filling up the tank. You can keep increasing the water pressure for a while. But the outlet at the other end of the tank has a fixed capacity. It flows 10 GPM, and no more. What happens when you increase the flow at the intake to 20 GPM? The tank fills up. When the tank fills up, your intake flow will need to decrease, because you can only flow 10 GPM at the outlet. Now, think of the outlet as writing to disk, the RAID5 cache is the water tank, and your database is the inlet that wants to run at 20 GPM. If your database activity will never be intensive enough to stress the cache like this, no problem. But 'never' is a very long time. If any of this sound familiar, Cary Millsap posted a very similar explanation a few weeks ago. Plagierism is the sincerest form of flattery. :) Jared -- 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Cary Millsap 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).
RE: LGWR using lots of CPU time, low CPU usage
H. I got to thinking about a previous reply which was while doing something else: I don't think changing the logmembers will do much good I agree. 4 groups, they are on the local drive. BOOM!! Then got to thinking This is not right at all. I think I was making a subconscious interpretation based on the context of your usage of the terms. If your groups look like GROUP1 redo_01a.dbf_or_log redo_01b.dbf_or_log redo_01c.dbf_or_log GROUP2 redo_02a.dbf_or_log redo_02b.dbf_or_log redo_02c.dbf_or_log etc. Then, this all this extra writing will definitely incur overhead. Now, the part about it all being on the local drive: That still is BOOM!! -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Stephen Lee 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).
Re: LGWR using lots of CPU time, low CPU usage
I see there has already been a lot of discussion on this topic. I would like to throw out one more possibility. It could be related to bug 2564886. If you read the bug on metalink, it probably won't make any sense because it is written for a specific customer. However, I have a similar problem and Oracle has classified my tar as related to this bug. Basically, if you use more than one log_arch_dest occasionally one of the archive process will just take forever. You didn't mention if you were using that parameter or if you are using a standby database so it may not apply to you. While oracle is working on this bug, we have disabled the second log_arch_dest and we have a script to manually check every minute and copy the archive logs to the other destination. This has helped us. Maybe it can help you to. We are on Sun Solaris 7 with 9.2.0.1 but the bug goes back to 9.0.1.3 so it probably applies to 9.2.0.2 also. HTH, John [EMAIL PROTECTED] 11/26/02 10:00AM 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: Deborah Lorraine 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: John Carlson 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).
RE: LGWR using lots of CPU time, low CPU usage
Great! Please let us know what Sun Rep has to say.
- Kirti
-Original Message-
Sent: Tuesday, November 26, 2002 2: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 alreadyuh!
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: Deborah Lorraine
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).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Deshpande, Kirti
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).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Deborah Lorraine
INET: [EMAIL PROTECTED]
Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
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:
A. The delay between when the foreground process posts LGWR and when
LGWR wakes up and gets busy.
B. The duration that LGWR consumes doing its job.
C. 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 alreadyuh!
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: Deborah Lorraine
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,
Re: RE: LGWR using lots of CPU time, low CPU usage
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 oracle1078 0.1 46.857277125697408 ?S Oct 30 220:00 ora_lgwr_biddb oracle1076 0.1 46.857317205701200 ?S Oct 30 92:37 ora_dbw0_biddb oracle1086 0.0 46.957328325709560 ?S Oct 30 47:02 ora_snp0_biddb oracle1088 0.0 46.957335925710896 ?S Oct 30 25:04 ora_snp1_biddb oracle1094 0.0 46.857275685696760 ?S Oct 30 20:54 ora_arc0_biddb oracle2662 0.0 46.857275685697472 ?S Oct 30 20:20 ora_arc2_biddb oracle1597 0.0 46.857275685697456 ?S Oct 30 19:42 ora_arc1_biddb oracle1092 0.0 46.957323765709312 ?S Oct 30 17:19 ora_snp3_biddb oracle1090 0.0 46.957344965709648 ?S Oct 30 10:23 ora_snp2_biddb oracle1096 0.0 47.257831605745720 ?S Oct 30 8:22 ora_p000_biddb oracle1101 0.0 47.257781605743520 ?S Oct 30 7:36 ora_p002_biddb oracle1098 0.0 47.257781765743904 ?S Oct 30 6:34 ora_p001_biddb oracle1103 0.0 47.257781525743576 ?S Oct 30 6:42 ora_p003_biddb oracle1080 0.0 46.857277125697440 ?S Oct 30 4:21 ora_ckpt_biddb oracle1107 0.0 47.157770005741416 ?S Oct 30 4:18 ora_p005_biddb oracle1105 0.0 47.057646325730624 ?S Oct 30 3:20 ora_p004_biddb oracle1109 0.0 47.157729045736024 ?S Oct 30 2:25 ora_p006_biddb oracle 0.0 47.157728485735952 ?S Oct 30 2:28 ora_p007_biddb oracle1074 0.0 46.857268085698184 ?S Oct 30 0:00 ora_pmon_biddb oracle1082 0.0 46.857259365700096 ?S Oct 30 0:59 ora_smon_biddb oracle1084 0.0 46.857258165699392 ?S Oct 30 0:31 ora_reco_biddb Regards zhu chao Eachnet DBA 86-21-32174588-667 [EMAIL PROTECTED] www.happyit.net www.cnoug.org(Chinese Oracle User Group) === 2002-11-26 11:25:00 ,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 [EMAIL PROTECTED] - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] 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
