Re: 100% CPU utilization, urgent

[Tim Gorman]

Title: 100% CPU utilization, urgent

It would be more appropriate to determine whether SMON is consuming all the CPU first, before sending someone on some irrelevant wild-goose chase for "fragmented tablespaces".  More effective to first look for basic facts than to go off chasing after any theories at this point;  you might be right, but if you're wrong than a lot of time has been wasted and confusion sown...   ---   Since the symptom is CPU usage, then we should look at some of the information about CPU usage that Oracle stores internally.  A quick and dirty starting point would be to query V$SESSTAT for the STATISTIC# related to the statistic "CPU used by this session", which you can find in V$STATNAME: SELECT   S.SID, S.PROGRAM, NVL(S.USERNAME, 'SYS'), S.TYPE, T.VALUE FROM     V$SESSION S, V$SESSTAT T, V$STATNAME N WHERE    N.NAME = 'CPU used by this session' AND      T.STATISTIC# = N.STATISTIC# AND      S.SID = T.SID ORDER    BY T.VALUE DESC; Feel free to post the first 5-10 lines of output from this query back as a reply to the list, if you'd like us to help interpret it?   ---   The very best approach is to take a STATSPACK level-5 (default level) reading during the period of 100% utilization and submit the resulting "sp_*.lst" file to http://www.oraperf.com for further analysis.  If SMON is the culprit, then you'll see SQL statements consuming tons of time updating the FET$ and UET$ tables in the SYS schema.  Otherwise, follow the hyperlinks to the "big" percentages to find out what is consuming most of the resources, especially CPU...   If you don't have STATSPACK installed, the next best thing would be to take a BSTAT/ESTAT report over the same time period when CPU is pegged at 100% and (again) submit that to www.oraperf.com to get the full YAPP report.  However, BSTAT/ESTAT will not provide information about SQL statements, so you'll need to find that separately.  Best to just install STATSPACK;  there is plenty of information on MetaLink...   ---   Also, Oracle is supposed to supply some utility called "Performance Monitor for Windows" which is supposed to be helpful on that platform.  I searched MetaLink briefly but couldn't find explanatory information;  perhaps the standard doc-set on "otn.oracle.com" would have something?   Hope this helps... ----- Original Message ----- From: Nikunj Gupta To: Multiple recipients of list ORACLE-L Sent: Monday, January 20, 2003 12:38 AM Subject: Re: 100% CPU utilization, urgent Check for fragmented tablespaces... There are chances that SMON is active and coalescing tablespaces.   ----- Original Message ----- From: Naveen Nahata To: Multiple recipients of list ORACLE-L Sent: Sunday, January 19, 2003 10:48 PM Subject: RE: 100% CPU utilization, urgent Hussain,   We have a similar environment and we also hit 100%CPU utilization at times. But we never face problem because of tht. the DB keeps working fine during 100% CPU utilization also.   Are you having a problem of logging into the DB during tht time?   Regards Naveen -----Original Message----- From: Hussain Ahmed Qadri [mailto:[EMAIL PROTECTED]] Sent: Monday, January 20, 2003 11:14 AM To: Multiple recipients of list ORACLE-L Subject: 100% CPU utilization, urgent HI all We have a consistent problem of CPU utilization 100%. We have had this problem since Saturday, but it automatically subsided, I mean went back to normal after a few hours, and remained normal on Sunday as well. But its back to 100% since morning, that is when the load on the server has gone up again to 100% and over all work is non-existent. Our machine is Compaq Proliant ML350, 900 MB ram, 933 single Processor, Database size of roughly 5 GB. WINNT4.0, Oracle 8.1.7. I have checked the temporary tablespaces, they are normal. We have a 24x7 environment, a hospital, so please can you suggest the areas to look in to, its really very urgent. Regards, Hussain DISCLAIMER: This message (including attachment if any) is confidential and may be privileged. Before opening attachments please check them for viruses and defects. MindTree Consulting Private Limited (MindTree) will not be responsible for any viruses or defects or any forwarded attachments emanating either from within MindTree or outside. If you have received this message by mistake please notify the sender by return e-mail and delete this message from your system. Any unauthorized use or dissemination of this message in whole or in part is strictly prohibited. Please note that e-mails are susceptible to change and MindTree shall not be liable for any improper, untimely or incomplete transmission.