Oracle diagnosis case-how to diagnose and solve database problems with high CPU consumption (100%)

In many cases, our servers may experience performance problems with CPU consumption of 100%.
Exclude system exceptions. These problems are usually caused by low performance or even incorrect SQL statements in the system, which consume a large amount of CPU.

This article uses a case to show how to capture such SQL statements and provides a general method.

Problem description: high CPU consumption and slow System Operation
OS: Sun Solaris8
Oracle: Oracle9203

1. First, use the Top command to view

$ topload averages: 1.61, 1.28, 1.25 HSWAPJSDB 10:50:44172 processes: 160 sleeping, 1 running, 3 zombie, 6 stopped, 2 on cpuCPU states: % idle, % user, % kernel, % iowait, % swapMemory: 4.0G real, 1.4G free, 1.9G swap in use, 8.9G swap free PID USERNAME THR PR NCE SIZE RES STATE TIME FLTS CPU COMMAND 20521 oracle 1 40 0 1.8G 1.7G run 6:37 0 47.77% oracle 20845 oracle 1 40 0 1.8G 1.7G cpu02 0:41 0 40.98% oracle 20847 oracle 1 58 0 1.8G 1.7G sleep 0:00 0 0.84% oracle 20780 oracle 1 48 0 1.8G 1.7G sleep 0:02 0 0.83% oracle 15828 oracle 1 58 0 1.8G 1.7G sleep 0:58 0 0.53% oracle 20867 root 1 58 0 4384K 2560K sleep 0:00 0 0.29% sshd2 20493 oracle 1 58 0 1.8G 1.7G sleep 0:03 0 0.29% oracle 20887 oracle 1 48 0 1.8G 1.7G sleep 0:00 0 0.13% oracle 20851 oracle 1 58 0 1.8G 1.7G sleep 0:00 0 0.10% oracle 20483 oracle 1 48 0 1.8G 1.7G sleep 0:00 0 0.09% oracle 20875 oracle 1 45 0 1064K 896K sleep 0:00 0 0.07% sh 20794 oracle 1 58 0 1.8G 1.7G sleep 0:00 0 0.06% oracle 20842 jiankong 1 52 2 1224K 896K sleep 0:00 0 0.05% sadc 20888 oracle 1 55 0 1712K 1272K cpu00 0:00 0 0.05% top 19954 oracle 1 58 0 1.8G 1.7G sleep 84:25 0 0.04% oracle

We found that there are two high-CPU-consuming Oracle databases in the city list, consuming 47.77% and 40.98% of CPU resources respectively.

 

2. Find the problematic Process Information

$ ps -ef|grep 20521 oracle 20909 20875 0 10:50:53 pts/10 0:00 grep 20521 oracle 20521 1 47 10:43:59 ? 6:45 oraclejshs (LOCAL=NO)$ ps -ef|grep 20845 oracle 20845 1 44 10:50:00 ? 0:55 oraclejshs (LOCAL=NO) oracle 20918 20875 0 10:50:59 pts/10 0:00 grep 20845

Confirm that the process is two remote connection user processes.

 

3. Familiarize yourself with my getsql. SQL script.

SELECT /*+ ORDERED */ sql_text FROM v$sqltext a WHERE (a.hash_value, a.address) IN ( SELECT DECODE (sql_hash_value, 0, prev_hash_value, sql_hash_value ), DECODE (sql_hash_value, 0, prev_sql_addr, sql_address) FROM v$session b WHERE b.paddr = (SELECT addr FROM v$process c WHERE c.spid = &pid))ORDER BY piece ASC/

Note that three views are involved here and their associations are applied for data acquisition.
First, enter a pid, which is the process id, which we can see in Top or ps.
We can associate the pid with v $ process. spid to obtain information about the Process.
Then, we can associate v $ process. addr with v $ session. paddr to obtain all session-related information.
Combined with v $ sqltext, we can obtain the SQL statement being executed by the current session.

Through the v $ process view, we can associate the operating system with the database.

 

4. Connect to the database and find the problematic SQL and Process

Through the PID we observed in Top, and then apply my getsql script, we get the following output.

$ sqlplus "/ as sysdba"SQL*Plus: Release 9.2.0.3.0 - Production on Mon Dec 29 10:52:14 2003Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.Connected to:Oracle9i Enterprise Edition Release 9.2.0.3.0 - 64bit ProductionWith the Partitioning, OLAP and Oracle Data Mining optionsJServer Release 9.2.0.3.0 - ProductionSQL> @getsqlEnter value for spid: 20521old 10: where c.spid = &pidnew 10: where c.spid = 20521SQL_TEXT----------------------------------------------------------------select * from (select VC2URL,VC2PVDID,VC2MOBILE,VC2ENCRYPTFLAG,SERVICEID,VC2SUB_TYPE,CISORDER,NUMGUID,VC2KEY1, VC2NEEDDISORDER,VC2PACKFLAG,datopertime from hsv_2cpsync where datopertime<=sysdate and numguid>70000000000308 order by NUMGUid) where rownum<=20

So this code is the culprit of the crazy CPU consumption.
The next step is to find out the problem of this code and check whether it can improve its efficiency and reduce resource consumption through optimization.

 

5. Further, we can track the process through the dbms_system package.

SQL> @ getsid Enter value for spid: 20521 Old 3: select addr from v $ process where spid = & spid) New 3: select addr from v $ process where spid = 20521) Sid serial # USERNAME MACHINE ---------------------------------------------------------------- 45 38991 HSUSER_V51 hswapjsptl1.hurray.com.cn SQL> exec dbms_system.set_ SQL _trace_in_session (45,38991, true ); PL/SQL procedure successfully completed. SQL>!

For more information, see:
Http://www.eygle.com/case/ SQL _trace_1.htm

For similar issues on Windows, refer:
Http://www.eygle.com/faq/Use.Nt.tools.manage.Oracle.htm

 

6. One Note

Most of the time, high CPU consumption is caused by problematic SQL statements. Therefore, the problem is often found when you find these SQL statements.
Generally, the problem can be solved.

However, sometimes you may find that the processes that consume the most CPU are background processes, which are generally caused by exceptions, bugs, or exceptions after recovery.
The problem is analyzed in detail.

 

 

 

Author:
Eygle, Oracle technical expert,
Www.eygle.com is the author's personal site. You can contact the author through Guoqiang.Gai@gmail.com. Welcome to technical discussion and link exchange.

Source:

Http://www.eygle.com/case/how.to.getsql.which.cost.most.CPU.htm