Oracle CPU打高问题排查思路

通过系统进程排查

1.使用top命令确定进程pid

可以看到有较多会话进程消耗CPU达到100%，然后找到这些存在问题的进程查看具体信息

[oracle@ajdbosread 12.1.2]$ ps -ef | grep 432705
oracle   432705      1 87 09:28 ?        02:13:13 oraclequerydb (LOCAL=NO)

确认进程为远程连接后，使用如下SQL得到执行SQL的具体信息

2.获取执行SQL

SELECT   /*+ ORDERED */
         sql_id,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 = '&spid'))
ORDER BY piece ASC;


SQL> SELECT   /*+ ORDERED */
  2           sql_id,sql_text
  3      FROM v$sqltext a
  4     WHERE (a.hash_value, a.address) IN (
  5              SELECT DECODE (sql_hash_value,
  6                             0, prev_hash_value,
  7                             sql_hash_value
  8                            ),
  9                     DECODE (sql_hash_value, 0, prev_sql_addr, sql_address)
 10                FROM v$session b
 11               WHERE b.paddr = (SELECT addr
 12                                  FROM v$process c
 13                                 WHERE c.spid = '&spid'))
 14  ORDER BY piece ASC;
Enter value for spid: 432737
old  13:                                WHERE c.spid = '&spid'))
new  13:                                WHERE c.spid = '432737'))

SQL_ID        SQL_TEXT
------------- ----------------------------------------------------------------
b070y4k8mnpb4 UPDATE RP_RETAILANALYSIS T SET T.WEEK4QTY = (SELECT SUM(NVL(B.QT
b070y4k8mnpb4 Y, 0)) FROM M_RETAIL A, M_RETAILITEM B WHERE A.ID = B.M_RETAIL_I
b070y4k8mnpb4 D AND A.C_STORE_ID = T.C_STORE_ID AND B.M_PRODUCT_ID = T.M_PRODU
b070y4k8mnpb4 CT_ID AND B.M_ATTRIBUTESETINSTANCE_ID = T.M_ATTRIBUTESETINSTANCE
b070y4k8mnpb4 _ID AND A.STATUS = 2 AND A.BILLDATE BETWEEN TO_CHAR(TRUNC(SYSDAT
b070y4k8mnpb4 E - 28, 'd'), 'yyyymmdd') AND TO_CHAR((TRUNC(SYSDATE - 28, 'd')
b070y4k8mnpb4 + 6), 'yyyymmdd') GROUP BY A.C_STORE_ID, B.M_PRODUCT_ID, B.M_ATT
b070y4k8mnpb4 RIBUTESETINSTANCE_ID)

通过活跃会话查询

SQL Plus格式化
set linesize 2000 

1.定位活跃会话sid,sql_id

select /* osql */ /*+ rule */username,sid,serial#,spid,sql_id from (
            select
                a.username username,a.sid sid,a.serial# serial#,b.spid spid,a.sql_id sql_id,a.sql_child_number,a.program program,last_call_et, a.machine machine, a.event event
           from v$session a,v$process b
            where a.status = 'ACTIVE'
              and a.paddr = b.addr
              and rawtohex(sql_address) <> '00'
              and a.username is not null
              and a.type<>'BACKGROUND'
              and sid <> (select sid from v$mystat where rownum = 1)
              order by last_call_et desc) where rownum <= 10

2.定位主要等待事件及主机

select /* osql */ /*+ rule */username,sid,serial#,sql_id,event,machine from (
            select
                a.username username,a.sid sid,a.serial# serial#,b.spid spid,a.sql_id sql_id,a.sql_child_number,a.program program,last_call_et, a.machine machine, a.event event
           from v$session a,v$process b
            where a.status = 'ACTIVE'
              and a.paddr = b.addr
              and rawtohex(sql_address) <> '00'
              and a.username is not null
              and a.type<>'BACKGROUND'
              and sid <> (select sid from v$mystat where rownum = 1)
              order by last_call_et desc) where rownum <= 10

3.根据sql_id找到执行SQL和执行计划

#获取执行SQL
 select SQL_ID,SQL_FULLTEXT
 from  v$sql where sql_id='&sql_id'
#获取执行计划
 select * from table(dbms_xplan.display_awr('&sql_id'))