Oracle process raches 100%

Posted on 2009-12-24
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
    LVL 18

    Expert Comment

    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

    LVL 11

    Expert Comment

    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:

    LVL 11

    Expert Comment

    sorry for theses many typos I am using a very tiny keyboard!
    LVL 8

    Expert Comment

    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.
    LVL 8

    Accepted Solution

    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 47

    Expert Comment

    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

    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 47

    Assisted Solution

    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
    ORDER BY 3;

    Featured Post

    IT, Stop Being Called Into Every Meeting

    Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

    Join & Write a Comment

    Working with Network Access Control Lists in Oracle 11g (part 1) Part 2: So, you upgraded to a shiny new 11g database and all of a sudden every program that used UTL_MAIL, UTL_SMTP, UTL_TCP, UTL_HTTP or any oth…
    Join Greg Farro and Ethan Banks from Packet Pushers ( and Greg Ross from Paessler ( for a discussion about smart network …
    Via a live example, show how to take different types of Oracle backups using RMAN.
    This video shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.

    734 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

    Need Help in Real-Time?

    Connect with top rated Experts

    18 Experts available now in Live!

    Get 1:1 Help Now