本文共 1308 字，大约阅读时间需要 4 分钟。

高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

转载地址：http://qitai.baihongyu.com/