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

Source: Internet
Author: User

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

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.