Specific steps to solve CPU 100% caused by Oracle Process

1: Check the system

Sar-U 5 5

　　2: check who is using the CPU

Topas

PS-Ef | grep ora # Check the size of column 4 C (unit, 100 per CPU)

　3: Check the number of CPUs

/Usr/sbin/bindprocessor-Q

Lsattr El proc0

　　4: two possibilities:

1: A Background (Instance) Process

2: An Oracle (User) process # This is the largest possible.

　　5: for user processes: the main reasons for high CPU usage are:

Large queries, procedure compilation or execution,

Space management and sorting

5.1 view the CPU usage of each session:

Select ss. Sid, se. Command, ss. Value CPU, se. username, se. Program

From v $ sesstat SS, V $ session se

Where ss. Statistic # In

(Select statistic #

From v $ statname

Where name = 'cpu used by this session ')

And se. Sid = ss. Sid

And ss. Sid> 6

Order by SS. Sid

5.2: Compare the above sessions

Compare which session has the most CPU usage time, and then view the specific situation of the session:

Select S. Sid, event, wait_time, W. seq #, Q. SQL _text

From v $ session_wait W, V $ session S, V $ PROCESS p, V $ sqlarea Q

Where S. paddr = P. ADDR and

S. Sid = & P and

S. SQL _address = Q. address;

5.3: View

After obtaining the preceding information, check whether the corresponding operation has hash joins and full table scans. If hash joins and full table scans exist, you must create the corresponding index or check whether the index is valid.

In addition, you must check whether there are parallel queries and multiple users are executing the same SQL statement at the same time. If there is a need to disable parallel queries and any type of parallel prompts (hints ); if the query uses intermedia data, you must restrict the use of the worldlist of intermedia to reduce the total index size. (Try restricting the wordlist that intermedia uses to help reduce the total indexsize ).

　　6. Notes

The above scheme can only be detected based on the Operations completed, and the long-time operations being performed can only be detected after the operation is completed. Therefore, we can use another good tool to detect long-running operation statements. V $ session_longops. This view displays the operations that are running or completed. After each process is complete, the information of this view is refreshed.

　　7: how to find a process that uses CPU in a centralized manner:

Many times, N processes share the CPU utilization on average. The only possibility is that these processes execute the same package or query.

In this case, it is recommended to run several snapshots when the CPU usage is high through statspack, and then check the statspack report based on these snapshots to check the top query in the report. Use the SQL _trace and tkprof tool to trace the problem.

Check whether the buffer cache hit rate is heavy by 95%.

In the report, you also need to check the table scans (long tables) to see if a full table scan exists during report generation.

　　8: Parameters

In addition, some are not particularly important, but they must also be concerned that the checked parameters may consume CPU.

Parallel query parallel query:

Parallel queries are best used in a data warehouse environment. In that case, only a few users can use them at the same time. In an online transaction processing environment, when many users query a huge table in a database in parallel, the CPU is full. So it is best to disable parallel queries at the database level: set the parameters as follows:

Parallel_min_server = 0 parallel_max_server = 0

Parallel_automatic_tuning = false;

After configuring the preceding parameters, if parallel prompts are used in SQL statements, parallel queries may still occur. Therefore, you must continue to monitor related SQL statements, if any, remove the prompt directly.