CPU爆满问题

查询慢sql

SELECT 
    sql_id,
    executions,
    round(elapsed_time/1000000) total_sec,
    round(elapsed_time/1000000/decode(executions,0,1,executions),4) avg_sec,
    round(cpu_time/1000000) cpu_sec,
    buffer_gets,
    disk_reads,
    parsing_schema_name,
    sql_text
FROM 
    v$sql
WHERE 
    cpu_time/1000000 > 10  -- CPU时间大于5秒
    AND elapsed_time/1000000 > 20  -- 总执行时间大于10秒
ORDER BY 
    cpu_time DESC;

查找执行该SQL的会话

SELECT sid, serial#, username, status, machine, program
FROM v$session
WHERE sql_id = ‘xxxxxxxxxxxx’ AND status = ‘ACTIVE’;

打印停止语句

SELECT
    'ALTER SYSTEM KILL SESSION ''' ||
    s.sid || ',' || s.serial# ||
    ''' IMMEDIATE;' AS kill_sql
FROM v$session s
WHERE s.sql_id = 'xxxxxxxxxxxx'
  AND s.username IS NOT NULL
  AND s.sid <> SYS_CONTEXT('USERENV','SID');

执行输出的语句

登录到操作系统，进入sqlplus执行

查询完整的sql语句

上面的查询输出的sql语句不完整，可以用这个查询完整语句，最好拼接上SQL_ID

SELECT 
    sql_id, 
    executions, 
    round(elapsed_time/1000000) total_sec, 
    round(elapsed_time/1000000/decode(executions,0,1,executions),4) avg_sec, 
    round(cpu_time/1000000) cpu_sec, 
    buffer_gets, 
    disk_reads, 
    parsing_schema_name, 
    dbms_lob.substr(sql_fulltext, 4000, 1) as full_sql_text
FROM 
    v$sql
WHERE 
    cpu_time/1000000 > 10
    AND elapsed_time/1000000 > 20
    AND SQL_ID = 'xxxxxxxxxxxx'
ORDER BY 
    cpu_time DESC;