Oracle process raches 100%

Posted on 2009-12-24
Medium Priority
Last Modified: 2013-12-18

I am running Oracle XE on Fedora Core 8. The database usually runs quite well but sometimes the machines becomes very slow to respond and when I see the running processes using top command I find oracle process takes about 100% CPU.

I want to investigate why it is doing so? Please guide me to find this.

Question by:systemsautomation
  • 3
  • 2
  • 2
  • +2
LVL 18

Expert Comment

ID: 26118814
select s.sid,P.spid "OS Thread", S.username "Name-User", S.osuser, S.program
where P.addr = S.paddr
and p.spid = pid (your process id)
and S.username is not null;
use the above sql to find the problematic session and its sql.
LVL 11

Expert Comment

ID: 26119400
LVL 11

Expert Comment

ID: 26119432
one of the best ways for cpu utilization especially if you rae using *NIX that you will see the porblamatib process:
which  is consuming theis cpu
smon,pmon arch etc,

I have these notes rewritten from metalink I found them in our knowledgebase here;but they are originally from metalink;I do not have the original doc ID!

The main reasons for PMON high CPU usage are related to specific bugs when cleaning up processes or
registering with the listener. It is best to keep the database in the latest versions and patchsets since
most of them have no workarounds. Please see:

SMON does space consolidation and transaction recovery operations and this can cause significant overhead if you are using dictionary managed tablespaces. This process can bring a database to a halt if a large table with many extents is dropped or truncated and the table exist within a dictionary-managed tablespace. Starting in 9i, locally-managed tablespaces are the default when a tablespace is first created and beginning in 9i Release 2 (9.2.x), the System tablespace can be locally-managed as well.

These two processes are more I/O bound, but when the O.S. needs patches or is misbehaving, then they
"spin" (wait) until the I/O operation to complete. The spinning is a CPU operation. Slowness or Failures in the Async I/O operations show themselves like this. You control the dbwr by setting either the db_writer_processes or dbwr_io_slaves parameter in your parameter file. You should generally leave the db_writer_processes to its default value (CPUs/8 adjusted to a multiple of CPU groups). Setting this parameter lower when you are experiencing CPU spikes may help prevent CPU performance problems from occurring. If setting this parameter lower does help the contention on your processors, but you take an overall performance hit after lowering this parameter, you may need to add additional CPU to your server before increasing this parameter back to the way that you had it. In addition, having async i/o enabled with different combinations of these parameters can also cause performance problems and CPU spikes. See the following note for more information about db_writer_process and dbwr_io_slaves and how they relate to async I/O:

Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

LVL 11

Expert Comment

ID: 26119472
sorry for theses many typos I am using a very tiny keyboard!

Expert Comment

ID: 26122904
The most important part of this is to know your application and application users, and what are their varying requirements?  I might *suppose* that what you have is a mixed transactional and reporting environment, and the transactional is relatively short/low impact transactions, but reporting may be more resource intensive and drive up the CPU utilization, and under such circumstance the transactional rates may be impacted as well.

The simplest tool to investigate is AWR.

sqlplus "/ as sysdba" @?/rdbms/admin/awrrpt

This will give an overview of your SQL workload and what sort of waits are occuring.  

And of course, it is fully functional for demo purposes on XE, but technically you aren't licensed to use it with XE, so consider appropriate due dilligence.

Accepted Solution

tomcatkev earned 1000 total points
ID: 26122907
Oh and what you ARE licensed to use free is Statspack, i.e.

@?/rdbms/admin/spauto      <-- to install/configure it to run hourly
@?/rdbms/admin/spreport    <-- to generate a report

LVL 48

Expert Comment

ID: 26133486
One important reason for this is swaping and paging  the physical RAM.
Possibly too much application are running simultenously or the volume of the RAM is too small or the SGA of Oracle is too big to fit the RAM volume.
Please post the size of the RAM, the size of the Oracle SGA and the RAM used by other applications.

SQL>show sga

Author Comment

ID: 26135820
Here is the results of  free -m

             total       used       free     shared    buffers     cached
Mem:          3930       3767        162          0        247       2687
-/+ buffers/cache:        831       3098
Swap:         2043         35       2008

SQL> show sga

Total System Global Area  805306368 bytes
Fixed Size                1261444 bytes
Variable Size              536871036 bytes
Database Buffers        264241152 bytes
Redo Buffers                2932736 bytes
LVL 48

Assisted Solution

schwertner earned 1000 total points
ID: 26136550
Everything with the RAM seems OK!

Now check the physical reads:

You can query the V$SEGMENT_STATISTICS view for this information. Look for tables that have a high number of physical reads and logical reads. A query similar to the following might provide what you are looking for:

SELECT t.owner,t.table_name,lr.value+pr.value AS total_reads
FROM (SELECT owner,object_name,value FROM v$segment_statistics
WHERE statistic_name='logical reads') lr,
SELECT owner,object_name,value FROM v$segment_statistics
WHERE statistic_name='logical reads') pr,
dba_tables t
WHERE lr.owner=pr.owner AND lr.object_name=pr.object_name
  AND lr.owner=t.owner AND lr.object_name=t.table_name

Featured Post

NEW Veeam Backup for Microsoft Office 365 1.5

With Office 365, it’s your data and your responsibility to protect it. NEW Veeam Backup for Microsoft Office 365 eliminates the risk of losing access to your Office 365 data.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Shell script to create broker configuration file using current broker Configuration, solely for purpose of backup on Linux. Script may need to be modified depending on OS-installation. Please deploy and verify the script in a test environment.
In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.
Suggested Courses

862 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question