| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> Re: 100% CPU utilization, urgent
100% CPU utilization, urgentIt 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
---
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...
Check for fragmented tablespaces... There are chances that SMON is active and coalescing tablespaces.
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:hussain_at_skm.org.pk]
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.
-- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Tim Gorman INET: Tim_at_SageLogix.com Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting servicesReceived on Mon Jan 20 2003 - 08:55:46 CST
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
 |
 |