How to troubleshoot and troubleshoot a database problem with CPU height consumption (100%) __ Database

Many times our servers may experience a 100% performance problem with CPU consumption. Troubleshoot system exceptions, which are often caused by a low performance or even faulty SQL statement in the system that consumes a large amount of CPU. This article provides a common way to capture such SQL in one case. Problem Description: System CPU height consumption, system running slowly Os:sun Solaris8 oracle:oracle9203 1. First view through the top command

$ top Load averages:1.61, 1.28, 1.25 hswapjsdb 10:50:44 172 processes:160 sleeping, 1 running, 3 Zombie, 6 stopped, 2 on CPU CPU states:% Idle,% user,% kernel,% iowait,% swap Memory : 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 0 1.8G 1.7G run 6:37 0 47.77% Oracle 20845 Oracle 1 0 1.8G 1.7G cpu0 2 0:41 0 40.98% Oracle 20847 Oracle 1 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% 0 1.8G 1.7G sleep 0:58 0 0.53% Oracle 20 867 Root 1 0 4384K 2560K sleep 0:00 0 0.29% sshd2 20493 Oracle 1-0 1.8G 1.7G sleep 0:03 0 0.29% Oracle 20887 Oracle 1 0 1.8G 1.7G sleep 0:00 0 0.13% Oracle 20851 Oracle 1 0 1.8G 1 .7G Sleep 0:00 0 010% Oracle 20483 Oracle 1 0 1.8G 1.7G sleep 0:00 0 0.09% Oracle 20875 Oracle 1 0 1064K 896K Sleep 0:00 0 0.07% sh 20794 Oracle 1 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 0 1712K 1272K cpu00 0:00 0 0.05% top 19954 o Racle 1 0 1.8G 1.7G sleep 84:25 0 0.04% Oracle

We found two highly CPU-intensive Oracle cities in the city list, consuming 47.77% and 40.98% of CPU resources respectively. 2. Find the process information in question

$ ps-ef|grep 20521 Oracle 20909 20875 0 10:50:53 pts/10 0:00 grep 20521 Oracle 20521 1 47 10:43: The other? 6:45 Oraclejshs (local=no) $ ps-ef|grep 20845 Oracle 20845 1 10:50:00? 0:55 Oraclejshs (local=no) Oracle 20918 20875 0 10:50:59 pts/10 0:00 grep 20845

Verify that this is a user process of two remote connections. 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 = ' ;p ID ") Order by piece ASC /

Note that here we have 3 views and apply their associations for data acquisition. First you need to enter a PID, this PID is the process ID, that is, in top or PS we see the PID. With the PID and v$process.spid associated we can get information about the process and then through V$PROCESS.ADDR and v$session.paddr, we can get all the information related to the session. Combined with V$sqltext, we can get the SQL statement that the current session is executing. With the v$process view, we were able to associate the operating system with the database. 4. Connect the database, locate the problem SQL and processWith the PID we observed in top, and then the application of my getsql script, we get the following result output.

$ sqlplus "/as SYSDBA" Sql*plus:release 9.2.0.3.0-production on Mon Dec 10:52:14 2003 Copyright (c) 1982, 2002, Oracle corporation. all rights reserved. Connected to: oracle9i Enterprise Edition release 9.2.0.3.0-64bit Production with the partitioning, OLAP and Ora CLE Data Mining Options jserver release 9.2.0.3.0-production sql> @getsql Enter value for Spid:20521 old 10:where c.spid = ' &pid ' new 10:where c.spid = ' 20521 ' sql_text ---------------- ------------------------------------------------ SELECT * FROM (select Vc2url,vc2pvdid,vc2mobile, Vc2encryptflag,s erviceid,vc2sub_type,cisorder,numguid,vc2key1, vc2needdisorder,v C2PACKFLAG, Datopertime from Hsv_2cpsync where Datopertime<=sysda te and numguid>70000000000308 the order by Numguid) where RO Wnum<=20

So this code is the culprit that is currently consuming CPU furiously. The next thing you need to do is find out what this code is all about and see if it can be optimized to improve efficiency and reduce resource consumption. 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 F Rom v$process where spid = 20521) SID serial# USERNAME MACHINE--------------------------------------------------------- -------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>!

This section can refer to: http://www.eygle.com/case/sql_trace_1.htm for similar problems on Windows, you can refer to: http://www.eygle.com/faq/ Use.Nt.tools.manage.Oracle.htm 6. A little noteMost of the time, high CPU consumption is caused by problem SQL, so finding these SQL often leads to problems, and tuning is usually the solution to the problem. But sometimes you may find that the most CPU-consuming process is the background process, which is usually caused by exceptions, bugs, or recovered exceptions, which require specific analysis of the problem. -the end-