高cpu占用sql的诊断流程

高cpu占用sql的诊断流程


top 得到高cpu的进程id

通过进程id(System Process ID)得到sql
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
/

为进程开启trace
exec dbms_system.set_sql_trace_in_session(&sid,&serial,true);

等待sql运行一段时间后关闭trace
exec dbms_system.set_sql_trace_in_session(&sid,&serial,false);

得到trace文件
select
      d.value||'/'||lower(rtrim(i.instance, chr(0)))||'_ora_'||p.spid||'.trc' trace_file_name
    from
      ( select p.spid
        from sys.v$mystat m,sys.v$session s,sys.v$process p
        where m.statistic# = 1 and s.sid = m.sid and p.addr = s.paddr) p,
      ( select t.instance from sys.v$thread  t,sys.v$parameter  v
        where v.name = 'thread' and (v.value = 0 or t.thread# = to_number(v.value))) i,
      ( select value from sys.v$parameter where name = 'user_dump_dest') d
/

分析trace文件与解决问题
tkprof rawtrace.trc prftrace.txt explain=apps/apps sys=no


1. [精彩] Oracle诊断案例-如何诊断和解决CPU高度消耗(100%)的数据库问题
http://www.chinaunix.net/old_jh/19/419925.html

2. Oracle诊断案例-Sql_trace之一
http://www.eygle.com/case/sql_trace_1.htm

3. 使用SQL_TRACE进行数据库诊断
http://www.eygle.com/case/Use.sql_trace.to.Diagnose.database.htm