oracle高CPU问题SQL排障

来源：互联网发布：高仿qq源码编辑：程序博客网时间：2024/04/30 03:59

1. solaris 查找高CPU的进程 top 命令或者prstat -u oracle

load averages: 2.73, 2.96, 3.02 10:23:39
367 processes: 365 sleeping, 1 zombie, 1 on cpu
CPU states: 92.6% idle, 5.4% user, 0.6% kernel, 1.3% iowait, 0.0% swap
Memory: 32G real, 21G free, 6532M swap in use, 22G swap free

PID USERNAME THR PRI NICE SIZE   RES STATE   TIME    CPU COMMAND
10493 oracle     2 59    0    0K    0K sleep 99:59 2.51% oracle
10376 oracle     1 59    0    0K    0K sleep   3:47 1.57% oracle
10429 oracle     2 59    0    0K    0K sleep 55:54 1.06% oracle
10386 oracle     1 59    0    0K    0K sleep   0:14 0.77% oracle
10479 oracle     1 59    0    0K    0K sleep 70:32 0.70% oracle
10401 oracle     1 59    0    0K    0K sleep   3:55 0.70% oracle
10427 oracle     2 59    0    0K    0K sleep 75:36 0.60% oracle
10511 oracle     2 59    0    0K    0K sleep 71:12 0.60% oracle
10485 oracle     2 59    0    0K    0K sleep 97:22 0.37% oracle
10507 oracle     2 59    0    0K    0K sleep 59:55 0.34% oracle
10471 oracle     1 59    0    0K    0K sleep 38:28 0.31% oracle
10393 oracle     1 59    0    0K    0K sleep 26:59 0.25% oracle
10445 oracle     1 59    0    0K    0K sleep 12:37 0.14% oracle
10441 oracle     2 59    0    0K    0K sleep 101:06 0.13% oracle
10489 oracle     2 59    0    0K    0K sleep 97:12 0.11% oracle

# prstat -u oracle
   PID USERNAME SIZE   RSS STATE PRI NICE      TIME CPU PROCESS/NLWP
10455 oracle   5096M 4780M cpu512   1    0   0:23:17 2.0% oracle/1
10493 oracle   5098M 4782M sleep   59    0   1:40:03 1.2% oracle/2
10479 oracle   5099M 4783M cpu16    1    0   1:10:35 0.9% oracle/1
10419 oracle   5095M 4779M sleep   59    0   0:04:56 0.7% oracle/1
10511 oracle   5098M 4782M sleep   59    0   1:11:15 0.6% oracle/2
10423 oracle   5099M 4783M sleep   55    0   1:32:13 0.6% oracle/2
10427 oracle   5099M 4783M sleep   51    0   1:15:38 0.5% oracle/2
10425 oracle   5099M 4783M sleep   38    0   1:20:55 0.5% oracle/2
10407 oracle   5095M 4779M sleep   59    0   0:01:55 0.5% oracle/1
10463 oracle   5098M 4782M sleep   52    0   1:58:24 0.5% oracle/1
10376 oracle   5094M 4778M sleep   59    0   0:03:47 0.5% oracle/1
10417 oracle   5094M 4778M sleep   59    0   0:15:22 0.5% oracle/1
10501 oracle   5099M 4783M sleep   59    0   2:00:34 0.4% oracle/2
10495 oracle   5099M 4783M sleep   59    0   1:38:56 0.4% oracle/1
10395 oracle   5095M 4780M sleep   59    0   0:04:53 0.4% oracle/1
10429 oracle   5099M 4783M sleep   59    0   0:55:55 0.4% oracle/2
10485 oracle   5099M 4783M sleep   59    0   1:37:24 0.4% oracle/2
10431 oracle   5095M 4780M sleep   59    0   0:15:09 0.4% oracle/2
10491 oracle   5095M 4779M sleep   59    0   0:08:12 0.3% oracle/1
10409 oracle   5097M 4781M sleep   59    0   0:31:17 0.3% oracle/2
10465 oracle   5099M 4783M sleep   59    0   1:10:10 0.3% oracle/2
10384 oracle   5094M 4776M sleep   59    0   0:00:27 0.3% oracle/1

2. 使用plsql 连接ORACLE 执行下列查询语句

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

导出查询语句定位分析，通过分析确定系统故障