Re: CPU Pegged at 100%
Hello Igor Thank you for the info. I got the following script from one of the articals that Thomas Day point to in the message With subject Oracle windows. It shows the threads that oracle is using. The first column has a call to some function that probably did some formating but I deleted it. create or replace view NT_threads as select p.spid ID_THREAD, p.background BACKGROUND, b.name NAME, s.sid SID, s.serial# SERIAL#, s.username USERNAME, s.status STATUS, s.osuser OSUSER, s.program PROGRAM from v$process p, v$bgprocess b, v$session s where s.paddr = p.addr and b.paddr(+) = p.addr; - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Tuesday, April 30, 2002 9:21 PM You can see process threads in NT Performance Monitor. Igor Neyman, OCP DBA [EMAIL PROTECTED] -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Yechiel Adar INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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: CPU Pegged at 100%
Do you know how to see the threads in NT? Yechiel Adar Mehish - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Monday, April 29, 2002 8:24 PM oracle.exe spawns out threads, which you will not see running in the task manager. Each DBWR process then will be a thread of oracle.exe. RF -Original Message- Sent: Monday, April 29, 2002 1:40 PM To: Multiple recipients of list ORACLE-L I have 5 DBWR processes according to INIT.ORA on NT .. I see them on V$SESSION but There is only one process ORACLE.EXE in the processes list of NT still. I do not understand the behaviour of ORacle on NT neither the Oracle I think. Bunyamin K. Karadeniz Oracle DBA / Developer Civilian IT Department Havelsan A.S. Eskisehir yolu 7.km Ankara Turkey Phone: +90 312 2873565 / 1217 Mobile : +90 535 3357729 - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Monday, April 29, 2002 5:53 PM On Mon, Apr 29, 2002 at 05:59:18AM -0800, Thomas Day wrote: I don't think that increasing the db_writer_processes will help. NT is, as noted elsewhere, multi-threaded. Increasing the db_writer_processes will not start a new process. My experience with Oracle on NT is that when the CPU is pegged at 100% it is because the OS is constantly writing and fetching the contents of RAM to the swapfile. -- If this is the case for this problem, we have found that changing the location of the os paging file onto another disk and controller can help performance. See your windows clicking friends to find out where to click. It is somewhere under my computer...read the man page on it ;) === Ray Stell [EMAIL PROTECTED] (540) 231-4109 KE4TJC28^D -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Ray Stell INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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: Bunyamin K. Karadeniz INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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: Freeman, Robert INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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: Yechiel Adar INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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: CPU Pegged at 100%
You can see process threads in NT Performance Monitor. Igor Neyman, OCP DBA [EMAIL PROTECTED] - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Tuesday, April 30, 2002 8:28 AM Do you know how to see the threads in NT? Yechiel Adar Mehish - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Monday, April 29, 2002 8:24 PM oracle.exe spawns out threads, which you will not see running in the task manager. Each DBWR process then will be a thread of oracle.exe. RF -Original Message- Sent: Monday, April 29, 2002 1:40 PM To: Multiple recipients of list ORACLE-L I have 5 DBWR processes according to INIT.ORA on NT .. I see them on V$SESSION but There is only one process ORACLE.EXE in the processes list of NT still. I do not understand the behaviour of ORacle on NT neither the Oracle I think. Bunyamin K. Karadeniz Oracle DBA / Developer Civilian IT Department Havelsan A.S. Eskisehir yolu 7.km Ankara Turkey Phone: +90 312 2873565 / 1217 Mobile : +90 535 3357729 - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Monday, April 29, 2002 5:53 PM On Mon, Apr 29, 2002 at 05:59:18AM -0800, Thomas Day wrote: I don't think that increasing the db_writer_processes will help. NT is, as noted elsewhere, multi-threaded. Increasing the db_writer_processes will not start a new process. My experience with Oracle on NT is that when the CPU is pegged at 100% it is because the OS is constantly writing and fetching the contents of RAM to the swapfile. -- If this is the case for this problem, we have found that changing the location of the os paging file onto another disk and controller can help performance. See your windows clicking friends to find out where to click. It is somewhere under my computer...read the man page on it ;) === Ray Stell [EMAIL PROTECTED] (540) 231-4109 KE4TJC28^D -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Ray Stell INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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: Bunyamin K. Karadeniz INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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: Freeman, Robert INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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: Yechiel Adar INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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
MS process/thread monitoring tools / RE: CPU Pegged at 100%
consider using qslice, or an alternative?: http://www.microsoft.com/windows2000/techinfo/reskit/tools/existing/qslice-o.asp - http://www.microsoft.com/windows2000/techinfo/reskit/tools/existing/pstat-o.asp --- http://www.win2000mag.com/Articles/Index.cfm?ArticleID=3023 ... | Process Explode (pview.exe) monitors all aspects of a process, | such as the number of threads the process is using and the type | and amount of committed mapped memory. This tool might interest\ | developers. However, Process Explode is not useful for general | users or administrators who want an overview of system resource | use. | | Quick Slice (qslice.exe) is a simple application that graphically | displays the percentage of CPU that each active process uses. | This tool gives only basic information, but it is useful for a | quick graphical overview of per-process CPU use. | | The resource kit includes Process Explode and Quick Slice. | Several of Microsoft's Visual Development tools (e.g., Visual | C++) include Process Explode. ... --- http://www.blueneptune.com/~maznliz/marius/software.shtml ORACLE-L Digest -- Volume 2002, Number 120 -- From: Freeman, Robert [EMAIL PROTECTED] Date: Mon, 29 Apr 2002 13:28:56 -0400 Subject: RE: CPU Pegged at 100% oracle.exe spawns out threads, which you will not see running in the task manager. Each DBWR process then will be a thread of oracle.exe. RF -Original Message- Sent: Monday, April 29, 2002 1:40 PM To: Multiple recipients of list ORACLE-L I have 5 DBWR processes according to INIT.ORA on NT .. I see them on V$SESSION but There is only one process ORACLE.EXE in the processes list of NT still. I do not understand the behaviour of ORacle on NT neither the Oracle I think. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Eric D. Pierce INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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: CPU Pegged at 100%
I don't think that increasing the db_writer_processes will help. NT is, as noted elsewhere, multi-threaded. Increasing the db_writer_processes will not start a new process. My experience with Oracle on NT is that when the CPU is pegged at 100% it is because the OS is constantly writing and fetching the contents of RAM to the swapfile. Meghraj Thakkar from Quest has a good paper on running Oracle 9i on Windows NT/2000. A search on Yahoo will probably find it for you. I don't have the URL. The following points are taken from that paper. Decrease the size of SGA so that all of the SGA and the OS will fit in physical RAM. This will decrease the use of the swapfile. Choose Maximum throughput for network applications in the control panel. Oracle does it's own memory management. Trying to let Windows memory manage on top of that adds to swapfile use. From the Services panel, disable all unneeded services. This includes License logging service, plug and play, remote access autodial manager, remote access connection manager, remote access server, and telephony service. You should not touch alerter, browser, eventlog, messenger, Oracle Service, Oracle TNSListener, Server, spooler and workstation. If you have 9i, set PRE_PAGE_SGA = TRUE. This tells Windows to keep the SGA in physical memory (RAM) as much as possible. It will get paged out --- that's the nature of Windows --- but not as often. Windows does IO buffering. However, Oracle does its own IO buffering apart from the OS. Performance can be increased and more of the RAM made available to the SGA by using REGEDIT and editing the registry. Go to \HKEY_LOCAL_MACHINE\Control\SessionManager\MemoryManagement and setting LargeSystemCache to 0. Be sure to back up the Registry before editing. HTH -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Thomas Day INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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: CPU Pegged at 100%
Thanks Thomas for sending this to the list. Lisa Koivu Oracle Database Baby Oven Fairfield Resorts, Inc. 5259 Coconut Creek Parkway Ft. Lauderdale, FL, USA 33063 -Original Message- From: Thomas Day [SMTP:[EMAIL PROTECTED]] Sent: Monday, April 29, 2002 9:59 AM To: Multiple recipients of list ORACLE-L Subject: RE: CPU Pegged at 100% I don't think that increasing the db_writer_processes will help. NT is, as noted elsewhere, multi-threaded. Increasing the db_writer_processes will not start a new process. My experience with Oracle on NT is that when the CPU is pegged at 100% it is because the OS is constantly writing and fetching the contents of RAM to the swapfile. Meghraj Thakkar from Quest has a good paper on running Oracle 9i on Windows NT/2000. A search on Yahoo will probably find it for you. I don't have the URL. The following points are taken from that paper. Decrease the size of SGA so that all of the SGA and the OS will fit in physical RAM. This will decrease the use of the swapfile. Choose Maximum throughput for network applications in the control panel. Oracle does it's own memory management. Trying to let Windows memory manage on top of that adds to swapfile use. From the Services panel, disable all unneeded services. This includes License logging service, plug and play, remote access autodial manager, remote access connection manager, remote access server, and telephony service. You should not touch alerter, browser, eventlog, messenger, Oracle Service, Oracle TNSListener, Server, spooler and workstation. If you have 9i, set PRE_PAGE_SGA = TRUE. This tells Windows to keep the SGA in physical memory (RAM) as much as possible. It will get paged out --- that's the nature of Windows --- but not as often. Windows does IO buffering. However, Oracle does its own IO buffering apart from the OS. Performance can be increased and more of the RAM made available to the SGA by using REGEDIT and editing the registry. Go to \HKEY_LOCAL_MACHINE\Control\SessionManager\MemoryManagement and setting LargeSystemCache to 0. Be sure to back up the Registry before editing. HTH -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Thomas Day INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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: Koivu, Lisa INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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: CPU Pegged at 100%
On Mon, Apr 29, 2002 at 05:59:18AM -0800, Thomas Day wrote: I don't think that increasing the db_writer_processes will help. NT is, as noted elsewhere, multi-threaded. Increasing the db_writer_processes will not start a new process. My experience with Oracle on NT is that when the CPU is pegged at 100% it is because the OS is constantly writing and fetching the contents of RAM to the swapfile. -- If this is the case for this problem, we have found that changing the location of the os paging file onto another disk and controller can help performance. See your windows clicking friends to find out where to click. It is somewhere under my computer...read the man page on it ;) === Ray Stell [EMAIL PROTECTED] (540) 231-4109 KE4TJC28^D -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Ray Stell INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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: CPU Pegged at 100%
I have 5 DBWR processes according to INIT.ORA on NT .. I see them on V$SESSION but There is only one process ORACLE.EXE in the processes list of NT still. I do not understand the behaviour of ORacle on NT neither the Oracle I think. Bunyamin K. Karadeniz Oracle DBA / Developer Civilian IT Department Havelsan A.S. Eskisehir yolu 7.km Ankara Turkey Phone: +90 312 2873565 / 1217 Mobile : +90 535 3357729 - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Monday, April 29, 2002 5:53 PM On Mon, Apr 29, 2002 at 05:59:18AM -0800, Thomas Day wrote: I don't think that increasing the db_writer_processes will help. NT is, as noted elsewhere, multi-threaded. Increasing the db_writer_processes will not start a new process. My experience with Oracle on NT is that when the CPU is pegged at 100% it is because the OS is constantly writing and fetching the contents of RAM to the swapfile. -- If this is the case for this problem, we have found that changing the location of the os paging file onto another disk and controller can help performance. See your windows clicking friends to find out where to click. It is somewhere under my computer...read the man page on it ;) === Ray Stell [EMAIL PROTECTED] (540) 231-4109 KE4TJC28^D -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Ray Stell INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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: Bunyamin K. Karadeniz INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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: CPU Pegged at 100%
The DBWRs are implemented as threads inside the ORACLE process (multi-threaded). This is beginning to sound like a Pern novel. Bunyamin K. Karadeniz To: Multiple recipients of list ORACLE-L bunyamink [EMAIL PROTECTED] @havelsan.comcc: .tr Subject: Re: CPU Pegged at 100% Sent by: root 04/29/2002 01:39 PM Please respond to ORACLE-L I have 5 DBWR processes according to INIT.ORA on NT .. I see them on V$SESSION but There is only one process ORACLE.EXE in the processes list of NT still. I do not understand the behaviour of ORacle on NT neither the Oracle I think. Bunyamin K. Karadeniz Oracle DBA / Developer Civilian IT Department Havelsan A.S. Eskisehir yolu 7.km Ankara Turkey Phone: +90 312 2873565 / 1217 Mobile : +90 535 3357729 - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Monday, April 29, 2002 5:53 PM On Mon, Apr 29, 2002 at 05:59:18AM -0800, Thomas Day wrote: I don't think that increasing the db_writer_processes will help. NT is, as noted elsewhere, multi-threaded. Increasing the db_writer_processes will not start a new process. My experience with Oracle on NT is that when the CPU is pegged at 100% it is because the OS is constantly writing and fetching the contents of RAM to the swapfile. -- If this is the case for this problem, we have found that changing the location of the os paging file onto another disk and controller can help performance. See your windows clicking friends to find out where to click. It is somewhere under my computer...read the man page on it ;) === Ray Stell [EMAIL PROTECTED] (540) 231-4109 KE4TJC28^D -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Ray Stell INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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: Bunyamin K. Karadeniz INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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: Thomas Day INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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: CPU Pegged at 100%
oracle.exe spawns out threads, which you will not see running in the task manager. Each DBWR process then will be a thread of oracle.exe. RF -Original Message- Sent: Monday, April 29, 2002 1:40 PM To: Multiple recipients of list ORACLE-L I have 5 DBWR processes according to INIT.ORA on NT .. I see them on V$SESSION but There is only one process ORACLE.EXE in the processes list of NT still. I do not understand the behaviour of ORacle on NT neither the Oracle I think. Bunyamin K. Karadeniz Oracle DBA / Developer Civilian IT Department Havelsan A.S. Eskisehir yolu 7.km Ankara Turkey Phone: +90 312 2873565 / 1217 Mobile : +90 535 3357729 - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Monday, April 29, 2002 5:53 PM On Mon, Apr 29, 2002 at 05:59:18AM -0800, Thomas Day wrote: I don't think that increasing the db_writer_processes will help. NT is, as noted elsewhere, multi-threaded. Increasing the db_writer_processes will not start a new process. My experience with Oracle on NT is that when the CPU is pegged at 100% it is because the OS is constantly writing and fetching the contents of RAM to the swapfile. -- If this is the case for this problem, we have found that changing the location of the os paging file onto another disk and controller can help performance. See your windows clicking friends to find out where to click. It is somewhere under my computer...read the man page on it ;) === Ray Stell [EMAIL PROTECTED] (540) 231-4109 KE4TJC28^D -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Ray Stell INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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: Bunyamin K. Karadeniz INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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: Freeman, Robert INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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: CPU Pegged at 100%
Thomas, If you could locate that paper, we would all be grateful. I have been unable to find it. Thanks, Jared Thomas Day [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 04/29/2002 06:59 AM Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject:RE: CPU Pegged at 100% I don't think that increasing the db_writer_processes will help. NT is, as noted elsewhere, multi-threaded. Increasing the db_writer_processes will not start a new process. My experience with Oracle on NT is that when the CPU is pegged at 100% it is because the OS is constantly writing and fetching the contents of RAM to the swapfile. Meghraj Thakkar from Quest has a good paper on running Oracle 9i on Windows NT/2000. A search on Yahoo will probably find it for you. I don't have the URL. The following points are taken from that paper. Decrease the size of SGA so that all of the SGA and the OS will fit in physical RAM. This will decrease the use of the swapfile. Choose Maximum throughput for network applications in the control panel. Oracle does it's own memory management. Trying to let Windows memory manage on top of that adds to swapfile use. From the Services panel, disable all unneeded services. This includes License logging service, plug and play, remote access autodial manager, remote access connection manager, remote access server, and telephony service. You should not touch alerter, browser, eventlog, messenger, Oracle Service, Oracle TNSListener, Server, spooler and workstation. If you have 9i, set PRE_PAGE_SGA = TRUE. This tells Windows to keep the SGA in physical memory (RAM) as much as possible. It will get paged out --- that's the nature of Windows --- but not as often. Windows does IO buffering. However, Oracle does its own IO buffering apart from the OS. Performance can be increased and more of the RAM made available to the SGA by using REGEDIT and editing the registry. Go to \HKEY_LOCAL_MACHINE\Control\SessionManager\MemoryManagement and setting LargeSystemCache to 0. Be sure to back up the Registry before editing. HTH -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Thomas Day INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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: CPU Pegged at 100%
All background processes are threads within the oracle.exe process on NT/2000 platform. -Original Message- Sent: Monday, April 29, 2002 1:40 PM To: Multiple recipients of list ORACLE-L I have 5 DBWR processes according to INIT.ORA on NT .. I see them on V$SESSION but There is only one process ORACLE.EXE in the processes list of NT still. I do not understand the behaviour of ORacle on NT neither the Oracle I think. Bunyamin K. Karadeniz Oracle DBA / Developer Civilian IT Department Havelsan A.S. Eskisehir yolu 7.km Ankara Turkey Phone: +90 312 2873565 / 1217 Mobile : +90 535 3357729 - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Monday, April 29, 2002 5:53 PM On Mon, Apr 29, 2002 at 05:59:18AM -0800, Thomas Day wrote: I don't think that increasing the db_writer_processes will help. NT is, as noted elsewhere, multi-threaded. Increasing the db_writer_processes will not start a new process. My experience with Oracle on NT is that when the CPU is pegged at 100% it is because the OS is constantly writing and fetching the contents of RAM to the swapfile. -- If this is the case for this problem, we have found that changing the location of the os paging file onto another disk and controller can help performance. See your windows clicking friends to find out where to click. It is somewhere under my computer...read the man page on it ;) === Ray Stell [EMAIL PROTECTED] (540) 231-4109 KE4TJC28^D -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Ray Stell INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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: Bunyamin K. Karadeniz INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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: Ji, Richard INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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: CPU Pegged at 100%
Try http://www.quest.com/presentations/pdfs/MT_maxprod9i.pdf or the link is on http://www.quest.com/presentations/openworld_2001.asp -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]] Sent: Monday, April 29, 2002 3:29 PM To: Multiple recipients of list ORACLE-L Subject: RE: CPU Pegged at 100% Thomas, If you could locate that paper, we would all be grateful. I have been unable to find it. Thanks, Jared -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Murray, Margaret INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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: CPU Pegged at 100%
Thanks for the link. This is not actually a paper though, just a high level power point presentation in a PDF file. It is lacking a lot of detail. It also perpetuates some tuning myths, thereby contributing to CTD. Jared Murray, Margaret [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 04/29/2002 01:43 PM Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject:RE: CPU Pegged at 100% Try http://www.quest.com/presentations/pdfs/MT_maxprod9i.pdf or the link is on http://www.quest.com/presentations/openworld_2001.asp -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]] Sent: Monday, April 29, 2002 3:29 PM To: Multiple recipients of list ORACLE-L Subject: RE: CPU Pegged at 100% Thomas, If you could locate that paper, we would all be grateful. I have been unable to find it. Thanks, Jared -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Murray, Margaret INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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: CPU Pegged at 100%
That's not the one that I was using but it is a very good and useful presentation. Murray, MargaretTo: Multiple recipients of list ORACLE-L mamurray[EMAIL PROTECTED] @husseyseatincc: g.com Subject: RE: CPU Pegged at 100% Sent by: root 04/29/2002 04:43 PM Please respond to ORACLE-L Try http://www.quest.com/presentations/pdfs/MT_maxprod9i.pdf or the link is on http://www.quest.com/presentations/openworld_2001.asp -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]] Sent: Monday, April 29, 2002 3:29 PM To: Multiple recipients of list ORACLE-L Subject: RE: CPU Pegged at 100% Thomas, If you could locate that paper, we would all be grateful. I have been unable to find it. Thanks, Jared -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Murray, Margaret INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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: Thomas Day INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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: CPU Pegged at 100%
Raghu I am also chasing a ghost like yours. Even though I have much larger number of cache Buffer chain waits, I look at the total picture. Looking at my statspack report, I noticed that application spends 30% of the time in buffer cache contention. How about the other 70%? Looking at the buffer gets reports, I found each of my processes having 2.2 Billion buffer gets. Looking at my application, I should not have more than 250Million buffer gets for each process. Off course the problem is with the SQL. So I am working first on my SQL and then if buffer cache chains problem still exist, I will work on it. So check the buffer gets and find out SQL that are doing it and work on them. Shakir = Mohammed Shakir CompuSoft, Inc. 11 Heather Way East Brunswick, NJ 08816-2825 (732) 672-0464 (Cell) (732) 257-6001 (Home) __ Do You Yahoo!? Yahoo! Health - your guide to health and wellness http://health.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Mohammed Shakir INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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: CPU Pegged at 100%
From SVRMGRL SVRMGR @/u01/app/oracle/product/8.1.7/rdbms/admin/utlbstat.sql -- let it collect data for 12 - 15 minutes SVRMGR @/u01/app/oracle/product/8.1.7/rdbms/admin/utlestat.sql Upload the resultant report.txt file to www.oraperf.com Review the results to identify where the bottleneck really is. HTH YMMV! Raghu Banaji wrote: Hi, I have a customer who runs an ERP application written using ORACLE. Almost all code is written using PL/SQL packages. There are about 80-100 users at any given point of time(6 AM - 11 PM). Oracle version is 8.1.7.1.1 Standard Edition. All the four CPU's on their server is pegged at 100% for most of the time. This is resulting in end users complaning about slow performance and slower log-ons to the application. This issue has been occuring consistently for over 2-3 weeks now. Archive logging is enabled and the database is normally shutdown once a week for cold backup. The Admin guys have been monitoring the server using Performance monitor and find that Oracle.exe is the process consuming 99% of the resource. Memory usage is constant and there is plenty of it free. Hard disk drives show no activity. A normal assumption would be that disk drives would be going crazy with all the CPU activity going on, but that does not seem to be the case. Oracle software and one PRODuction database exist on this server. Their Server configuration consists of: Windows 2000 OS (Advanced Server), with 4 Pentium III Xeon processors (each 700 Mhz), 4 Gb RAM and 2 disk drives on seperate disk controllers. Hard drives are of 15,000 RPM's. MY APPROACH SO FAR: Step 1: I started looking into this issue since last week and the first area I concentrated was on finding SQL statements that were taking too many hits or taking too long to complete. I was able to tune almost 10 of the top SQL statements last week. In most of the previous performance issues I have been involved with this has resulted in huge performance gains and life went on. In this situation, tuning these top 10 SQL's did result in small gains, but did not make any difference to the CPU contention. They still continued to be pegged at 100% most of the times. Step 2: The next step I took was to find out if there were hard parsing going on. As mentioned in a number of articles here, this would cause the CPU to work extra harder. My check resulted in only 2 SQL statement that were hard parsed. Based on the fact that there are a number of very big jobs, user queries and other activities that go on a daily basis, should I really worry about it ? Step 3: Next step was to increase the number of rollback segments from 5 to 35. Previously, there were 5 big rollback segments. Now, there are 35 medium sized rollback segments spread over 2 rollback tablespaces. This step was taken yesterday and so far there has been no improvement as far as CPU pegging goes. It is still pegged at 100% Step 4: Certain articles in Metalink suggested that one of the reasons that would make the CPU spin continously would be SMON working overtime to clean up a large number of temporary extents, or to coalesce a large number of free extents. This can manifest itself by SMON appearing to spin, consuming a high percentage of CPU for long periods. I really dont know how to test this statement. PCT_INCREASE is set to 0 for all the tablespaces except SYSTEM and one ROLLBACK tablespace. I will re-set PCT_INCREASE to 0 for the second ROLLBACK tablespace also. Apart from this I really dont know what else I can look for. My temporary tablespace is around 3 GB. Is there a SQL statement that I can run and find out if there is a problem with SMON? Step 5: The final step was to run Statspack on this instance for about 25 minutes. I have copied extracts from some of the data that would be of interest. Hard parses and the Top 5 waits seem to be the issue atleast in this report. Could some one share some of your experience in the area of tuning these wait events and what you would recomend me to do next. My CPU is still pegged at 100%. STATSPACK report for DB Name DB IdInstance Inst Num Release OPS Host --- --- --- PROD 3888465932 prod1 8.1.7.1.1 NO ERP1 Snap Id Snap Time Sessions --- -- Begin Snap: 31 26-Apr-02 09:21:38 169 End Snap: 41 26-Apr-02 09:46:03 169 Elapsed: 24.42 (mins) Cache Sizes ~~~ db_block_buffers: 131072 log_buffer: 163840 db_block_size: 8192shared_pool_size: 314572800 Load Profile Per Second Per Transaction --- --- Redo
RE: CPU Pegged at 100%
Title: RE: CPU Pegged at 100% Raghu, Do not depend on Performance Monitor alone for your CPU statistics. At least in NT4, it quite often reports 100% usage with nothing going on. Right now on my workstation I have one little Access database open with no activity. Performance Monitor is saying CPU is at 100% and has been for the last 5 minutes. Jerry Whittle ACIFICS DBA NCI Information Systems Inc. [EMAIL PROTECTED] 618-622-4145 -Original Message- From: Raghu Banaji [SMTP:[EMAIL PROTECTED]] Hi, I have a customer who runs an ERP application written using ORACLE. Almost all code is written using PL/SQL packages. There are about 80-100 users at any given point of time(6 AM - 11 PM). Oracle version is 8.1.7.1.1 Standard Edition. All the four CPU's on their server is pegged at 100% for most of the time. This is resulting in end users complaning about slow performance and slower log-ons to the application. This issue has been occuring consistently for over 2-3 weeks now. Archive logging is enabled and the database is normally shutdown once a week for cold backup. The Admin guys have been monitoring the server using Performance monitor and find that Oracle.exe is the process consuming 99% of the resource. Memory usage is constant and there is plenty of it free. Hard disk drives show no activity. A normal assumption would be that disk drives would be going crazy with all the CPU activity going on, but that does not seem to be the case. Oracle software and one PRODuction database exist on this server. Their Server configuration consists of: Windows 2000 OS (Advanced Server), with 4 Pentium III Xeon processors (each 700 Mhz), 4 Gb RAM and 2 disk drives on seperate disk controllers. Hard drives are of 15,000 RPM's. snipped Thanks, Raghu
RE: CPU Pegged at 100%
Hi Raghu, Can you trace a couple of your top sessions by setting 10046 and see which SQL statements are causing the most resource consumption. This will give us an idea of the cause. The effect that you are seeing is a very high level of CPU consumption and waits for the cache buffers chains latches. From the look of it (and we need to confirm this with the trace data), your SQL statements are probably performing more logical I/O than what is required. The report you shared with us, reveals 5,806 Logical I/Os per second over a 24.42 minutes. With 1482 seconds in this time period, the amount of logical I/O performed is 8,604,492 blocks. How many concurrent sessions did you have then? The symptom of cache buffers chains contention may corroborate that there could be more logical I/O performed than what is required. You need to investigate what is causing this and whether or not this is reasonable? One of the potential reasons for this could be over-indexing of the tables and/or forceful use of indexes for many queries, which may do quite well with full-table scans. Use the 10046 trace data as your starting point to take you down the right path for your problem solving process. Cheers, Gaja --- Raghu Banaji [EMAIL PROTECTED] wrote: All users waiting for latch cache buffer chains -Original Message- Sent: Friday, April 26, 2002 11:59 AM To: Multiple recipients of list ORACLE-L From the statspack report it shows that u have lots of latchfree waits which is event timing out can u see in the session_Wait which is the user and what latch he is waiting on -Original Message- Sent: Friday, April 26, 2002 2:24 PM To: Multiple recipients of list ORACLE-L Hi, I have a customer who runs an ERP application written using ORACLE. Almost all code is written using PL/SQL packages. There are about 80-100 users at any given point of time(6 AM - 11 PM). Oracle version is 8.1.7.1.1 Standard Edition. All the four CPU's on their server is pegged at 100% for most of the time. This is resulting in end users complaning about slow performance and slower log-ons to the application. This issue has been occuring consistently for over 2-3 weeks now. Archive logging is enabled and the database is normally shutdown once a week for cold backup. The Admin guys have been monitoring the server using Performance monitor and find that Oracle.exe is the process consuming 99% of the resource. Memory usage is constant and there is plenty of it free. Hard disk drives show no activity. A normal assumption would be that disk drives would be going crazy with all the CPU activity going on, but that does not seem to be the case. Oracle software and one PRODuction database exist on this server. Their Server configuration consists of: Windows 2000 OS (Advanced Server), with 4 Pentium III Xeon processors (each 700 Mhz), 4 Gb RAM and 2 disk drives on seperate disk controllers. Hard drives are of 15,000 RPM's. MY APPROACH SO FAR: Step 1: I started looking into this issue since last week and the first area I concentrated was on finding SQL statements that were taking too many hits or taking too long to complete. I was able to tune almost 10 of the top SQL statements last week. In most of the previous performance issues I have been involved with this has resulted in huge performance gains and life went on. In this situation, tuning these top 10 SQL's did result in small gains, but did not make any difference to the CPU contention. They still continued to be pegged at 100% most of the times. Step 2: The next step I took was to find out if there were hard parsing going on. As mentioned in a number of articles here, this would cause the CPU to work extra harder. My check resulted in only 2 SQL statement that were hard parsed. Based on the fact that there are a number of very big jobs, user queries and other activities that go on a daily basis, should I really worry about it ? Step 3: Next step was to increase the number of rollback segments from 5 to 35. Previously, there were 5 big rollback segments. Now, there are 35 medium sized rollback segments spread over 2 rollback tablespaces. This step was taken yesterday and so far there has been no improvement as far as CPU pegging goes. It is still pegged at 100% Step 4: Certain articles in Metalink suggested that one of the reasons that would make the CPU spin continously would be SMON working overtime to clean up a large number of temporary extents, or to coalesce a large number of free extents. This can manifest itself by SMON appearing to spin, consuming a high percentage of CPU for long periods. I really dont know how to test this statement. PCT_INCREASE is set to 0 for all the tablespaces except SYSTEM and one ROLLBACK tablespace. I will re-set PCT_INCREASE to 0 for the second ROLLBACK
RE: CPU Pegged at 100%
Thank you Gaja, for not forward 240k of extraneous junk that is appearing in every other message in this thread. Jared Gaja Krishna Vaidyanatha [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 04/26/2002 02:43 PM Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject:RE: CPU Pegged at 100% Hi Raghu, Can you trace a couple of your top sessions by setting 10046 and see which SQL statements are causing the most resource consumption. This will give us an idea of the cause. The effect that you are seeing is a very high level of CPU consumption and waits for the cache buffers chains latches. From the look of it (and we need to confirm this with the trace data), your SQL statements are probably performing more logical I/O than what is required. The report you shared with us, reveals 5,806 Logical I/Os per second over a 24.42 minutes. With 1482 seconds in this time period, the amount of logical I/O performed is 8,604,492 blocks. How many concurrent sessions did you have then? The symptom of cache buffers chains contention may corroborate that there could be more logical I/O performed than what is required. You need to investigate what is causing this and whether or not this is reasonable? One of the potential reasons for this could be over-indexing of the tables and/or forceful use of indexes for many queries, which may do quite well with full-table scans. Use the 10046 trace data as your starting point to take you down the right path for your problem solving process. Cheers, Gaja --- Raghu Banaji [EMAIL PROTECTED] wrote: All users waiting for latch cache buffer chains -Original Message- Sent: Friday, April 26, 2002 11:59 AM To: Multiple recipients of list ORACLE-L From the statspack report it shows that u have lots of latchfree waits which is event timing out can u see in the session_Wait which is the user and what latch he is waiting on -Original Message- Sent: Friday, April 26, 2002 2:24 PM To: Multiple recipients of list ORACLE-L Hi, I have a customer who runs an ERP application written using ORACLE. Almost all code is written using PL/SQL packages. There are about 80-100 users at any given point of time(6 AM - 11 PM). Oracle version is 8.1.7.1.1 Standard Edition. All the four CPU's on their server is pegged at 100% for most of the time. This is resulting in end users complaning about slow performance and slower log-ons to the application. This issue has been occuring consistently for over 2-3 weeks now. Archive logging is enabled and the database is normally shutdown once a week for cold backup. The Admin guys have been monitoring the server using Performance monitor and find that Oracle.exe is the process consuming 99% of the resource. Memory usage is constant and there is plenty of it free. Hard disk drives show no activity. A normal assumption would be that disk drives would be going crazy with all the CPU activity going on, but that does not seem to be the case. Oracle software and one PRODuction database exist on this server. Their Server configuration consists of: Windows 2000 OS (Advanced Server), with 4 Pentium III Xeon processors (each 700 Mhz), 4 Gb RAM and 2 disk drives on seperate disk controllers. Hard drives are of 15,000 RPM's. MY APPROACH SO FAR: Step 1: I started looking into this issue since last week and the first area I concentrated was on finding SQL statements that were taking too many hits or taking too long to complete. I was able to tune almost 10 of the top SQL statements last week. In most of the previous performance issues I have been involved with this has resulted in huge performance gains and life went on. In this situation, tuning these top 10 SQL's did result in small gains, but did not make any difference to the CPU contention. They still continued to be pegged at 100% most of the times. Step 2: The next step I took was to find out if there were hard parsing going on. As mentioned in a number of articles here, this would cause the CPU to work extra harder. My check resulted in only 2 SQL statement that were hard parsed. Based on the fact that there are a number of very big jobs, user queries and other activities that go on a daily basis, should I really worry about it ? Step 3: Next step was to increase the number of rollback segments from 5 to 35. Previously, there were 5 big rollback segments. Now, there are 35 medium sized rollback segments spread over 2 rollback tablespaces. This step was taken yesterday and so far there has been no improvement as far as CPU pegging goes. It is still pegged at 100% Step 4: Certain articles in Metalink suggested that one of the reasons that would make the CPU spin continously would be SMON working overtime to clean up a large number
Re: CPU Pegged at 100%
Is it necessary to copy the entire report.txt every time you make a short response to this question. So far I think I have received 10 copies of a 155K report. (And it wasn't all that exciting the first time around ;) Jonathan Lewis http://www.jlcomp.demon.co.uk Author of: Practical Oracle 8i: Building Efficient Databases Next Seminar - Australia - July/August http://www.jlcomp.demon.co.uk/seminar.html Host to The Co-Operative Oracle Users' FAQ http://www.jlcomp.demon.co.uk/faq/ind_faq.html -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jonathan Lewis INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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: CPU Pegged at 100%
This could be the problem if his mail server is living with Oracle on the same box. Regards, Waleed -Original Message- Sent: Friday, April 26, 2002 6:53 PM To: Multiple recipients of list ORACLE-L Is it necessary to copy the entire report.txt every time you make a short response to this question. So far I think I have received 10 copies of a 155K report. (And it wasn't all that exciting the first time around ;) Jonathan Lewis http://www.jlcomp.demon.co.uk Author of: Practical Oracle 8i: Building Efficient Databases Next Seminar - Australia - July/August http://www.jlcomp.demon.co.uk/seminar.html Host to The Co-Operative Oracle Users' FAQ http://www.jlcomp.demon.co.uk/faq/ind_faq.html -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jonathan Lewis INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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: Khedr, Waleed INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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: CPU Pegged at 100%
List, Before we start talking of RAID, etc. we need to consider this 'ERP application. If this is Oracle Apps, please mention the version. Changing standard SQL code in Apps can be done either via specific patches or by raising a TAR and crying constantly until it gets resolved. CPUs being pegged on an NT box is nothing new - ORACLE.EXE is a Multi-threaded Monolithic Monster (omigosh - was that three Ms!!) on NT and I wouldn't really recommend running anything other than just the DB (and one single instance please!) on an NT box. I am not sure how threads can be picked up by another CPU on context switch, .etc. All this leads to inefficient mode of operation of Oracle on NT... Also are these programs Custom or Standard? Any deviations? What is the Patch level, etc.? Trying to apply your 'normal' tuning methodology on Oracle Apps will 'normally' not work, as you need to work in conjunction with Oracle Apps Support. I am afraid the advice being given may be misleading (including mine!). John Kanagaraj Oracle Applications DBA DBSoft Inc (W): 408-970-7002 ** The opinions and statements above are entirely my own and not those of my employer or clients ** -Original Message- Sent: Friday, April 26, 2002 3:29 PM To: Multiple recipients of list ORACLE-L Arun, Yes, it is on raid-array. Not really sure about the details. Thanks, Raghu -Original Message- Sent: Friday, April 26, 2002 02:54 PM To: Multiple recipients of list ORACLE-L is ur database sitting on raid -Original Message- Sent: Friday, April 26, 2002 5:30 PM To: Multiple recipients of list ORACLE-L To everyone who has replied: My sincere THANKS Currently db_block_lru_latches has a value of 2 (default) and db_writer_processes has a value of 1 (default) This server has 4 CPU's. Would it make any sense or would it help my cause in reducing contention for latch cache buffer chains if I increased db_block_lru_latches to 12 ( 4 CPU's * 3) and db_writer_processes to 4. Are there any other harmful things that I should be aware of before making the above 2 changes ? Thanks to everyone, Raghu -Original Message- Sent: Friday, April 26, 2002 01:31 PM To: Multiple recipients of list ORACLE-L A quick search on Steve Adams site ( http://www.ixora.com.au/q+a/cache.htm http://www.ixora.com.au/q+a/cache.htm ) shows the following Q) By looking at V$LATCH_CHILDREN, I have found that less than 1% of the cache buffers chains latches account for 30% of the gets and 90% of the misses. Obviously, the operations on these latches are highly skewed. One latch is particularly bad. However, given that the hit rate on cache buffers chains latches is about 99.3%, should I be concerned about the highly skewed distribution of misses? A) If the sleeps represent a large proportion of all latch sleeps, and if you have significant latch free waits in V$SYSTEM_EVENT, then the problem should be investigated further, regardless of the hit ratio. Another reason why you should pursue this further is that most such distributions of gets and sleeps against these latches are in fact a symptom of bad SQL performing huge numbers of buffer gets. If you can find and fix that, you may get a much bigger performance boost than that which relates merely to avoiding the latch sleeps. John -Original Message- Sent: 26 April 2002 20:44 To: Multiple recipients of list ORACLE-L All users waiting for latch cache buffer chains -Original Message- Sent: Friday, April 26, 2002 11:59 AM To: Multiple recipients of list ORACLE-L From the statspack report it shows that u have lots of latchfree waits which is event timing out can u see in the session_Wait which is the user and what latch he is waiting on -Original Message- Sent: Friday, April 26, 2002 2:24 PM To: Multiple recipients of list ORACLE-L Hi, I have a customer who runs an ERP application written using ORACLE. Almost all code is written using PL/SQL packages. There are about 80-100 users at any given point of time(6 AM - 11 PM). Oracle version is 8.1.7.1.1 Standard Edition. All the four CPU's on their server is pegged at 100% for most of the time. This is resulting in end users complaning about slow performance and slower log-ons to the application. This issue has been occuring consistently for over 2-3 weeks now. Archive logging is enabled and the database is normally shutdown once a week for cold backup. The Admin guys have been monitoring the server using Performance monitor and find that Oracle.exe is the process consuming 99% of the resource. Memory usage is constant and there is plenty of it free. Hard disk drives show no activity. A normal assumption would be that disk drives would be going crazy with all the CPU activity going on, but that does not seem to be the case. Oracle software and one PRODuction database exist on this server. Their Server configuration consists of: Windows 2000 OS (Advanced Server), with
Re: CPU Pegged at 100%
thank you. I've just been deleting any message with this subject line that is greater than about 10K --- Jonathan Lewis [EMAIL PROTECTED] wrote: Is it necessary to copy the entire report.txt every time you make a short response to this question. So far I think I have received 10 copies of a 155K report. (And it wasn't all that exciting the first time around ;) Jonathan Lewis http://www.jlcomp.demon.co.uk Author of: Practical Oracle 8i: Building Efficient Databases Next Seminar - Australia - July/August http://www.jlcomp.demon.co.uk/seminar.html Host to The Co-Operative Oracle Users' FAQ http://www.jlcomp.demon.co.uk/faq/ind_faq.html -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jonathan Lewis INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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). __ Do You Yahoo!? Yahoo! Games - play chess, backgammon, pool and more http://games.yahoo.com/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Rachel Carmichael INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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).
