Troubleshooting: High CPU Utilization (文档 ID 164768.1)

In this Document

 Purpose Troubleshooting Steps Prevention is the key Finding High CPU Utilization processes on Windows Finding High CPU Utilization processes on Unix What to look for when one process is using the CPU intensively What process is hogging the CPU? OS Processes and defunct processes What to look for when multiple processes are using the CPU intensively References

---

APPLIES TO:

Oracle Database - Enterprise Edition - Version 7.0.16.0 to 11.2.0.3 [Release 7.0 to 11.2]
Oracle Database - Personal Edition - Version 7.1.4.0 to 11.2.0.3 [Release 7.1.4 to 11.2]
Oracle Database - Standard Edition - Version 7.0.16.0 to 11.2.0.3 [Release 7.0 to 11.2]
Generic Windows
Generic UNIX

PURPOSE

The purpose of this article is to help in diagnosing the reason for Oracle processes consuming high CPU

TROUBLESHOOTING STEPS

Prevention is the key

High CPU utilization may not necessarily point to a problem. It could just mean that the system
is being well utilized. However, if CPU usage is consistently high when the load on the system is low or system performance is poor together with High CPU usage you should most likely investigate the reason for the high CPU usage. Also, if one or more processes are consistently hogging CPU at the expense of other processes, the processes using CPU should be investigated.

Besides collecting diagnostic information in order to solve any problems, there is little or nothing to do to stop processes from using a lot of CPU once they started to do so.On the other hand, a lot can be done to prevent it from happening.

Oracle provides two ways to limit the CPU being used by individual users :

• Profiles 
  
  
  Note:1016552.102 How to use PROFILES to limit user resources
• Resource Manager 
  
  
  Note:106948.1 Oracle8i: Database Resource Manager samples
  Note:471265.1 Example: How to control CPU Resources using the Resource Manager

Finding High CPU Utilization processes on Windows

Following notes may help to collect information on which processes on Windows may be utilizing high CPU.

Note:273646.1 How to diagnose the high CPU utilization of ORACLE.EXE in Windows environment
Note:116236.1 Diagnosing High CPU Utilization on NT
Note:433472.1 OS Watcher For Windows (OSWFW) User Guide

 

Finding High CPU Utilization processes on Unix

On UNIX systems there are two basic tools with the capability of evaluating and estimating the CPU usage on the system. These are vmstat and sar. The following articles explain how to use them and other tolls in diagnosing high CPU on Unix:

Note:148466.1 Monitor Oracle Resource Consumption in UNIX
Note:224176.1 How to use OS commands to diagnose Database Performance issues? 
Note.301137.1 OS Watcher User Guide

What to look for when one process is using the CPU intensively

What process is hogging the CPU?

• Background process
• Oracle (user) process
• OS Process that is not related to Oracle
• Defunc process 
  
  

Background Processes

PMON

The main reasons for PMON high CPU usage are related to specific bugs when cleaning up processes or  registering with the listener.

SMON

SMON is responsible for space consolidation and transaction recovery operations which can cause significant overhead if you are using dictionary managed tablespaces.

SMON 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. 

Excessive space consolidation can consume excessive CPU. See the following note for more information on this and how to troubleshoot. 

Note:61997.1 SMON - Temporary Segment Cleanup and Free Space Coalescing

Recovery operations performed by SMON can also consume high CPU.The following note explains when the SMON is doing recovery and what to do about it: 

Note:414242.1 Database Hangs Because SMON is taking 100% CPU doing transaction recovery 

SMON may do transaction recovery in parallel. This may result in considerable CPU consumption. In such cases you may consider disabling parallel recovery:
 

Note:144332.1 Parallel Rollback may hang database, Parallel query servers get 100% cpu

LGWR & DBWR 

These two processes are usually I/O bound, but when there is a problem on the OS, they  may "spin" (wait) until the I/O operation completes. This spinning is a CPU operation. Slowness or Failures in async I/O operations may also manifest themselves by high CPU consumption. 

If LGWR appears to be intermittently taking up 100% CPU and AIO is setup, then the AIO configuration should be rechecked. As a temporary measure,the following parameter may be set  time to prevent LGWR from spinning

• _lgwr_async_io=false
  This parameter turns of async i/o for lgwr leaving it intact for the rest of the database

 

Note: 813473.1 LGWR Uses Async I/O Inspite Of Setting _lgwr_async_io=False
Note 7385253.8 Bug 7385253 - Slow Truncate / DBWR uses high CPU / CKPT blocks on RO enqueue

Jobs (CJQ0, Jn, SNPn)  

Job processes run user defined and system defined batch-like tasks. The high CPU usage should be investigated in a similar way as when investigating CPU used by a user process.

Check the Views DBA_JOBS_* , DBA_SCHEDULER_*, DBA_AUTOTASK_* for information of what is being run.

Even on their own these processes may consume a fair amount of CPU as they are in a infinite loop querying the job queue. 

Note: 8531434.8 Bug 8531434 - Solaris: Excessive CPU by MMNL/CJQ0 when running multiple instances and cpus

Advanced Queuing (AQ, QMN)

The AQ processes send and receive messages mostly through tables. Excessive CPU utilization may be because the tables need to be purged or reorganized or other issues related to Advanced Queuing. 

Note 305662.1 Master Note for AQ Queue Monitor Process (QMON) 
Note:271855.1 Procedure to manually Coalesce all the IOTs/indexes Associated with Advanced Queueing tables to maintain Enqueue/Dequeue performance, reduce QMON CPU usage and Redo generation

Parallel Query (Pnn)

Parallel query processes are used specifically in order to do a lot of work and therefore may indeed high CPU.However it  is advised to ensure that the system is set up optimally. The parallel query option is best for data warehouse type environments where only a small number of users will be executing queries at any given time.

Note:203238.1 Using Parallel Execution.

Oracle (User) process

Parsing large queries, procedure compilation or execution, space management and sorting are examples of operations that are CPU intensive.

In order to collect more information on a process using high CPU see the following notes that may be of assistance

Note:352648.1 How to Diagnose High CPU Usage Problems to the Module Level 
Note:452358.1 How to Collect Diagnostics for Database Hanging Issues

An AWR or statspack  report may also be of assistance in diagnosing which activities are using high CPU and what they are doing

Note:748642.1 How to Generate an AWR Report
Note:94224.1 FAQ- Statspack Complete Reference
Note:276103.1 PERFORMANCE TUNING USING 10g ADVISORS AND MANAGEABILITY FEATURES

If the problem is found to be a slow running query, then efforts should be made to tune the query in order that it may avoids consuming high CPU.If it is doing a number of hash joins and full table scans, efforts should be made to add indexes and get the indexes used.

The following notes assist in diagnosing problems with queries and assists in  tuning them.

Note:215187.1 SQLT (SQLTXPLAIN) - Tool that helps to diagnose SQL
Note:199083.1 Master Note: SQL Query Performance Overview

Real-time SQL monitoring is a 11g new feature that enables you to monitor the performance of SQL statements while they are executing. See:

Note.1229904.1 Real-Time SQL Monitoring in 11g

Other tracing techniques might proof useful to decide whether to allow a process to continue
or not and help with analyzing the reason for high cpu usage.

Note:376442.1 How To Collect 10046 Trace (SQL_TRACE) Diagnostics for Performance Issues

 

OS Processes and defunct processes

As OS processes are not related to Oracle we cannot help in diagnosing the cause behind the CPU usage. Please consult with your OS Vendor.

The following notes include information on some known OS issues

Note:419345.1 Linux: Common System Performance Issues
Note:316533.1 AIX: Database performance gets slower the longer the database is running
Note:217990.1 Hpux_sched_noage 178
Note:580273.1 Processes Hang Waiting on 'cursor: pin S wait on X'  (and other Wait Events) on HP-UX 11.23
                                  and 11.31 Itanium Systems
Note:7228334.8 More CPU utilization on IBM Linux on POWER
Note:285026.1  Oracle Threads Not Showing In V$Process and Consuming CPU Background (instance) process

Note: 361670.1 Slow Performance with High CPU Usage on 64-bit Linux with Large SGA

 

What to look for when multiple processes are using the CPU intensively

Sometimes the CPU is fairly distributed across several processes. The only thing to do in 
that case is to try to see if they share a common task, like the execution of a particular package or query. We recommend that you take some AWR or statspack snapshots when the CPU is at it is peak and run reports against the snapshots. From there, you can look at the top 5 wait events in order to determine where the time may be used and look at the top queries that are running that may be causing the CPU to spike.

If the problem only occurs when a particular application is active, check to make sure that the application is not overloading the database with more connections than  it is setup to handle. 
Application architecture that does not use persistent connections (connection pooling, shared servers) and connects and disconnects using dedicated servers consume large amounts of CPU and are unstable in nature and may be susceptible to "login storms" .

REFERENCES

NOTE:361670.1 - Slow Performance with High CPU Usage on 64-bit Linux with Large SGA
NOTE:271855.1 - Procedure to manually Coalesce all the IOTs/indexes Associated with Advanced Queueing tables to maintain Enqueue/Dequeue performance, reduce QMON CPU usage and Redo generation
NOTE:273646.1 - How to Diagnose High CPU Utilization of ORACLE.EXE in Windows Environment
NOTE:276103.1 - Performance Tuning Using Advisors and Manageability Features: AWR, ASH, ADDM and Sql Tuning Advisor
NOTE:285026.1 - Oracle Threads Not Showing In V$Process and Consuming CPU
NOTE:301137.1 - OSWatcher Black Box (Includes: [Video])
NOTE:305662.1 - Master Note for AQ Queue Monitor Process (QMON)
NOTE:316533.1 - AIX: Database performance gets slower the longer the database is running
NOTE:352648.1 - How to Diagnose High CPU Usage Problems to the Module Level
NOTE:369412.1 - Case Study:Resolving High CPU Usage on Oracle Servers
NOTE:813473.1 - LGWR consumes 100% CPU
NOTE:8531434.8 - Bug 8531434 - Solaris: Excessive CPU by MMNL/CJQ0 when running multiple instances and cpus
NOTE:94224.1 - FAQ- Statspack Complete Reference
NOTE:376442.1 - * How To Collect 10046 Trace (SQL_TRACE) Diagnostics for Performance Issues
NOTE:414242.1 - Database Hangs Because SMON Is Taking 100% CPU Doing Transaction Recovery
NOTE:419345.1 - Linux: Common System Performance Issues
NOTE:433472.1 - OS Watcher For Windows (OSWFW) User Guide
NOTE:452358.1 - How to Collect Diagnostics for Database Hanging Issues
NOTE:471265.1 - Example: How to control CPU Resources using the Resource Manager
NOTE:580273.1 - Processes Hang Waiting on 'cursor: pin S wait on X' (and other Wait Events) on HP-UX 11.23 and 11.31 Itanium Systems
NOTE:61997.1 - SMON - Temporary Segment Cleanup and Free Space Coalescing
NOTE:7228334.8 - Bug 7228334 - IBM-Linux-Power: More CPU utilization on IBM Linux on POWER
NOTE:7385253.8 - Bug 7385253 - Slow Truncate / DBWR uses high CPU / CKPT blocks on RO enqueue
NOTE:748642.1 - How to Generate an AWR Report and Create Baselines
BUG:7385253 - DBWR IS CONSUMING HIGH CPU

NOTE:1016552.102 - How To Use PROFILES To Limit User Resources
NOTE:106948.1 - Database Resource Manager samples
NOTE:116236.1 - Diagnosing High CPU Utilization on NT
NOTE:1229904.1 - Real-Time SQL Monitoring in 11g
NOTE:144332.1 - Parallel Rollback may hang database, Parallel query servers get 100% cpu
NOTE:148466.1 - Monitor Oracle Resource Consumption in UNIX
NOTE:199083.1 - * Master Note: SQL Query Performance Overview
NOTE:203238.1 - Using Parallel Execution
NOTE:215187.1 - SQLT (SQLTXPLAIN) - Tool that helps to diagnose a SQL statement performing poorly or one that produces wrong results
NOTE:217990.1 - Init.ora Parameter "HPUX_SCHED_NOAGE" Reference
NOTE:224176.1 - How to use OS commands to diagnose Database Performance issues?

  

相关内容

   

 

产品

   

• Oracle Database Products > Oracle Database Suite > Oracle Database > Oracle Database - Enterprise Edition > RDBMS
• Oracle Database Products > Oracle Database Suite > Oracle Database > Oracle Database - Personal Edition
• Oracle Database Products > Oracle Database Suite > Oracle Database > Oracle Database - Standard Edition

 

关键字

   

CONSUME CPU;CONSUMPTION;CPU;CPU CONSUMPTION;CPU USAGE;EXCESSIVE CPU USAGE;HIGH CPU USAGE;PERFORMANCE;TROUBLESHOOT

 

错误

   

ORA-1575