Capture problems in Oracle SQL to solve the excessive CPU consumption (1)

This article attempts to provide a common diagnosis method for CPU consumption problems through a case adjusted in the actual business system. In most cases, system performance problems are caused by poor SQL code. As a DBA, how to discover and solve these SQL problems is particularly important.

In this case, the platform is UNIX, so it is inevitable to apply some commonly used Unix tools, such as vmstat and top.

This article is applicable to medium and high level readers.

System Environment: OS: Solaris8 Oracle: 8.1.7.4

Problem description: The developer reports that the system is running slowly, which has affected the normal use of the business system. Ask for assistance in diagnosis.

1. log on to the Database Host

Run the vmstat check and find that the CPU resources are exhausted. A large number of tasks are in the running queue:

bash-2.03$ vmstat 3 procs memory page disk faults cpu r b w swap free re mf pi po fr de sr s6 s9 s1 sd in sy cs us sy id 0 0 0 5504232 1464112 0 0 0 0 0 0 0 0 1 1 0 4294967196 0 0 -84 -5 -145 131 0 0 5368072 1518360 56 691 0 2 2 0 0 0 1 0 0 3011 7918 2795 97 3 0 131 0 0 5377328 1522464 81 719 0 2 2 0 0 0 1 0 0 2766 8019 2577 96 4 0 130 0 0 5382400 1524776 67 682 0 0 0 0 0 0 0 0 0 3570 8534 3316 97 3 0 134 0 0 5373616 1520512 127 1078 0 2 2 0 0 0 1 0 0 3838 9584 3623 96 4 0 136 0 0 5369392 1518496 107 924 0 5 5 0 0 0 0 0 0 2920 8573 2639 97 3 0 132 0 0 5364912 1516224 63 578 0 0 0 0 0 0 0 0 0 3358 7944 3119 97 3 0 129 0 0 5358648 1511712 189 1236 0 0 0 0 0 0 0 0 0 3366 10365 3135 95 5 0 129 0 0 5354528 1511304 120 1194 0 0 0 0 0 0 0 4 0 3235 8864 2911 96 4 0 128 0 0 5346848 1507704 99 823 0 0 0 0 0 0 0 3 0 3189 9048 3074 96 4 0 125 0 0 5341248 1504704 80 843 0 2 2 0 0 0 6 1 0 3563 9514 3314 95 5 0 133 0 0 5332744 1501112 79 798 0 0 0 0 0 0 0 1 0 3218 8805 2902 97 3 0 129 0 0 5325384 1497368 107 643 0 2 2 0 0 0 1 4 0 3184 8297 2879 96 4 0 126 0 0 5363144 1514320 81 753 0 0 0 0 0 0 0 0 0 2533 7409 2164 97 3 0 136 0 0 5355624 1510512 169 566 786 0 0 0 0 0 0 1 0 3002 8600 2810 96 4 0 130 1 0 5351448 1502936 267 580 1821 0 0 0 0 0 0 0 0 3126 7812 2900 96 4 0 129 0 0 5347256 1499568 155 913 2 2 2 0 0 0 0 1 0 2225 8076 1941 98 2 0 116 0 0 5338192 1495400 177 1162 0 0 0 0 0 0 0 1 0 1947 7781 1639 97 3 0

2. Use the Top Command

Observe the CPU usage of the process and find that there are no processes with high CPU usage.

$ top last pid: 28313; load averages: 99.90, 117.54, 125.71 23:28:38 296 processes: 186 sleeping, 99 running, 2 zombie, 9 on cpu CPU states: 0.0% idle, 96.5% user, 3.5% kernel, 0.0% iowait, 0.0% swap Memory: 4096M real, 1404M free, 2185M swap in use, 5114M swap free PID USERNAME THR PRI NICE SIZE RES STATE TIME CPU COMMAND 27082 oracle8i 1 33 0 1328M 1309M run 0:17 1.29% oracle 26719 oracle8i 1 55 0 1327M 1306M sleep 0:29 1.11% oracle 28103 oracle8i 1 35 0 1327M 1304M run 0:06 1.10% oracle 28161 oracle8i 1 25 0 1327M 1305M run 0:04 1.10% oracle 26199 oracle8i 1 45 0 1328M 1309M run 0:42 1.10% oracle 26892 oracle8i 1 33 0 1328M 1310M run 0:24 1.09% oracle 27805 oracle8i 1 45 0 1327M 1306M cpu/1 0:10 1.04% oracle 23800 oracle8i 1 23 0 1327M 1306M run 1:28 1.03% oracle 25197 oracle8i 1 34 0 1328M 1309M run 0:57 1.03% oracle 21593 oracle8i 1 33 0 1327M 1306M run 2:12 1.01% oracle 27616 oracle8i 1 45 0 1329M 1311M run 0:14 1.01% oracle 27821 oracle8i 1 43 0 1327M 1306M run 0:10 1.00% oracle 26517 oracle8i 1 33 0 1328M 1309M run 0:33 0.97% oracle 25785 oracle8i 1 44 0 1328M 1309M run 0:46 0.96% oracle 26241 oracle8i 1 45 0 1327M 1306M run 0:42 0.96% oracle

3. Check the number of processes

bash-2.03$ ps -ef|grep ora|wc -l 258 bash-2.03$ ps -ef|grep ora|wc -l 275 bash-2.03$ ps -ef|grep ora|wc -l 274 bash-2.03$ ps -ef|grep ora|wc -l 278 bash-2.03$ ps -ef|grep ora|wc -l 277 bash-2.03$ ps -ef|grep ora|wc -l 366

It is found that a large number of Oracle processes exist in the system, which is about 300. A large number of processes consume almost all CPU resources. Normally, the number of Oracle connections is about 100.