Many times our server may experience the performance problem of CPU consumption. Exclude the system's exception, this type of problem is usually because there is a low-performance SQL statement in the system, which consumes a large number of CPUs.
This article gives a general method for how to capture such SQL through a case.
Problem Description: The system CPU is highly consumed, the system is running slowly OS: Sun Solaris8Oracle: Oracle9203
1. First view 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 Flt 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 have found that there are two high CPUs consumed in the city, which consume 47.77% and 40.98% of CPU resources, respectively.
2. Find the process information of the problem
$ PS-EF | GREP 20521
Oracle 20909 20875 0 10:50:53 PTS / 10 0:00 Grep 20521racle 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 this is two remotely connected user processes.
3. Familiar 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 we involve three views and applies their associations. First, you need to enter a PID. This PID is the Process ID, that is, the PID we can see in TOP or PS. PID. PID and V $ Process.spid Related links We can get relevant information about Process to associate through V $ process.addr and V $ session.paddr, we can get all information related to session. Combine V $ SQLText, we can get the current session is executing SQL statement.
With the V $ Process view, we have to associate the operating system and database.
4. Connect the database, find the problem SQL and processes
Through the PID we observed in TOP, apply my getSQL script, we get the following results.
$ SQLPLUS "/ as sysdba"
SQL * Plus: Release 9.2.0.3.0 - Production on Mon Dec 29 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 ORACLE DATA MINING OPTIONS
JServer Release 9.2.0.3.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, VC2NEDDISORDER, V
C2PACKFLAG, Datopertime from HSV_2cpsync Where Datopertime <= sysdate and numguid> 70000000000308 Order by NumGuid) Where rownum <= 20
Then this code is currently being crazy to consume the CPU's culprit. The job that needs to be done is to find out the problem of this code, see if it can improve its efficiency, reduce resource consumption.
5. Further we can track the process through the dbms_system package
SQL> @getsidenter value for SPID: 20521OLD 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>!
This part can be referring to: http://www.eygle.com/case/SQL_TRACE_1.HTM
For similar issues on Windows, you can refer to: http://www.eygle.com/faq/use.t.tools.manage.Oracle.htm
6. A little description
Many times, high CPU consumption is caused by problem SQL, so finding these SQL usually finds the problem, and can solve the problem by optimizing adjustments.
But sometimes you may find that these most CPU processes are background processes, which is generally due to abnormalities, bugs or recovery abnormalities, requiring specific problems to analyze.
