【案例52】oracle进程占用CPU100%分析实战

问题现象

Linux环境，数据库CPU一直处于100%。业务系统运行很慢。Top命令结果如下：

问题分析

方法1

根据上图中的oracle进程在操作系统对应的 PID号 :  如  6999,8100 等

通过下面的SQL,查询

select s.SQL_HASH_VALUE, s.SQL_ADDRESSfrom v$session s, v$process pwhere s.PADDR = p.ADDRand p.SPID = '6999' ---换成相关的pid值

然后，将 查询出的 SQL_HASH_VALUE ,SQL_ADDRESS 对应代入下面的sql，或者只用其中一个也可以。

select *from v$sqltext twhere (t.HASH_VALUE = ' s.SQL_HASH_VALUE ' or --自行替换上述查的值t.ADDRESS = ' s.SQL_ADDRESS ') --自行替换上述查的值order by piece

方法2

通过如下SQL查询在数据库中执行缓慢的sql

SELECT /*+rule*/S.SQL_ID,S.SID,s.BLOCKING_SESSION,S.SERIAL#,S.USERNAME,W.EVENT,W.SECONDS_IN_WAIT,W.WAIT_TIME,S.LAST_CALL_ET,S.STATUS,S.CLIENT_INFO,Q.SQL_TEXT,Q.HASH_VALUE,Q.ADDRESS,'alter system kill session ''' || S.SID || ',' || S.SERIAL# ||''' immediate;'FROM V$SESSION_WAIT W, V$SESSION S, V$SQL QWHERE W.SID = S.SIDAND Q.HASH_VALUE = S.SQL_HASH_VALUEAND W.EVENT NOT LIKE '%message from client%'AND S.SID >= 6ORDER BY LAST_CALL_ET DESC;

发现耗时sql如下

--- SQL-1  
--- 这个 SQL 语句用于获取特定用户活动会话相关的执行计划，并以一种特定的格式展示这些信息
select *from (select hash_value || '***' ||rpad('|' || substr(lpad(' ', 1 * (depth - 1)) || operation ||decode(options, null, '', ' ' || options),1,32),33,' ') || '|' ||rpad(decode(id,0,'----- ' || to_char(hash_value) || ' -----',substr(decode(substr(object_name, 1, 7),'SYS_LE_',null,object_name) || ' ',1,20)),21,' ') || '|' ||lpad(decode(cardinality,null,' ',decode(sign(cardinality - 1000),-1,cardinality || ' ',decode(sign(cardinality - 1000000),-1,trunc(cardinality / 1000) || 'K',decode(sign(cardinality - 1000000000),-1,trunc(cardinality / 1000000) || 'M',trunc(cardinality / 1000000000) || 'G')))),7,' ') || '|' ||lpad(decode(bytes,null,' ',decode(sign(bytes - 1024),-1,bytes || ' ',decode(sign(bytes - 1048576),-1,trunc(bytes / 1024) || 'K',decode(sign(bytes - 1073741824),-1,trunc(bytes / 1048576) || 'M',trunc(bytes / 1073741824) || 'G')))),6,' ') || '|' ||lpad(decode(cost,null,' ',decode(sign(cost - 10000000),-1,cost || ' ',decode(sign(cost - 1000000000),-1,trunc(cost / 1000000) || 'M',trunc(cost / 1000000000) || 'G'))),8,' ') || '|' as "Explain plan"from v$sql_planwhere hash_value in (select s.sql_hash_valuefrom v$session swhere s.username = upper('[user]')and s.status = 'ACTIVE'and s.last_call_et > 10))--- SQL-2
--- 监控和分析数据库中活动会话的执行情况
select s.client_identifier,s.sid,s.serial#,sql.sql_fulltext,s.last_call_et,s.event,sql.SQL_ID,child_number,s.sql_hash_valuefrom v$session s, v$sql sqlwhere s.sql_address = sql.ADDRESSand s.username = upper('[user]')and s.status = 'ACTIVE'and s.last_call_et > 10order by sid

方法3

使用AWR查看耗时SQL

Oracle>sqlplus  “/as sysdba”SQL>@?/rdbms/admin/awrrpt.sql

查询SQL ordered by Elapsed Time

查询SQL ordered by CPU Time 

相关SQL

根据awr的报告 ，也可以看出， 耗费CPU的 SQL 时 在awr中 ，可以 通过 SQL ID找到当时执行时对应的执行计划：

select * from table(dbms_xplan.display_awr('6h6zz42n9rmnw'));

解决方案

1、经过沟通，发现相关的SQL是由于NMC调用了Oracle监控脚本导致的。

nmc\server\conf\monitor.properties 文件中配置的监控脚本 。注释掉即可 。

2、停止NMC，杀掉相关数据库会话。