Oracle capture problem SQL solves CPU overhead

This article attempts to provide a diagnosis method for common CPU consumption problems through a case adjusted in the actual business system.

This article attempts to provide a diagnosis method for common CPU consumption problems through a case adjusted in the actual business system.

The ORACLE tutorial is: Oracle capture problem SQL to solve the excessive CPU consumption. This article attempts to provide a diagnosis method for common CPU consumption problems through a case adjusted in the actual business system. in most cases, system performance problems are caused by poor SQL code. As a DBA, how to discover and solve these SQL problems is particularly important.

In this case, the platform is UNIX, so it is inevitable to apply some commonly used Unix tools, such as vmstat and top.

This article is applicable to medium and high level readers.

System Environment:

OS: Solaris8

Oracle: 8.1.7.4

Problem description:

The developer reports that the system is running slowly, which has affected the normal use of the business system. request assistance in diagnosis.

1. log on to the Database Host

Run the vmstat check and find that the CPU resources are exhausted. A large number of tasks are in the running queue:


2. Use the Top Command

Observe the CPU usage of the process and find that there are no processes with high CPU usage.


3. Check the number of processes


It is found that a large number of Oracle processes exist in the system, which is about 300. A large number of processes consume almost all CPU resources. Normally, the number of Oracle connections is about 100.