Oracle个人技巧 -- top cpu

字数 2570阅读 701评论 0赞 3

top   
  
SELECT /*+ ordered */ p.spid, s.sid, s.serial#, s.username,   
TO_CHAR(s.logon_time, 'mm-dd-yyyy hh24:mi') logon_time, s.last_call_et, st.value,   
s.sql_hash_value, s.sql_address, sq.sql_text   
FROM v$statname sn, v$sesstat st, v$process p, v$session s, v$sql sq   
WHERE s.paddr=p.addr   
AND s.sql_hash_value = sq.hash_value and s.sql_Address = sq.address   
AND s.sid = st.sid   
AND st.STATISTIC# = sn.statistic#   
AND sn.NAME = 'CPU used by this session'   
AND p.spid = &osPID -- parameter to restrict for a specific PID   
AND s.status = ‘ACTIVE’   
ORDER BY st.value desc   
  
  
SELECT p.spid, s.sid, s.serial#   
FROM v$session s, v$process p   
WHERE s.paddr = p.addr AND p.spid = 24078   
  
  
SPID SID SERIAL#   
------------ ---------- ----------   
24078 18 5   
  
  
SQL> begin dbms_system.set_ev(18,5, 10046,12,''); end; -- trace on   
  
-- collect trace information for approximately 15 minutes during the problem   
  
SQL> begin dbms_system.set_ev(18, 5, 10046,0,''); end; -- trace off   
  
  
tkprof ora_24078.trc 24078.prf sort=fchela   
  
call count cpu elapsed disk query current rows   
------- ------ -------- ---------- ---------- ---------- ---------- ----------   
Parse 555 0.09 0.83 0 0 0 0   
Execute 555 0.42 0.78 0 0 0 0   
Fetch 555 114.04 385.03 513 1448514 0 11724   
------- ------ -------- ---------- ---------- ---------- ---------- ----------   
total 1665 114.55 386.65 513 1448514 0 11724   
  
  
Elapsed times include waiting on following events:   
  
Event waited on Times Max. Wait Total Waited   
---------------------------------------- Waited ---------- ------------   
SQL*Net message from client 556 1.94 134.68   
SQL*Net message to client 555 0.00 0.00   
db file sequential read 513 0.65 6.68   
SQL*Net more data to client 169 0.00 0.02   
latch free 323 1.03 59.85   
  
  
  
The top SQL statement found in the TKProf report (sorted by fetch elapsed time) was the   
same one we saw frequently while querying V$SESSION (hash value of 1656209319).   
  
Let’s analyze the TKProf to see how the time was spent for this SQL statement:   
  
1.) An average of 11,724 rows / 555 fetch calls = 21 rows/call is returned   
2.) An average of 1448514 buffer gets /555 executions = 2,609 buffer gets/execution   
3.) The elapsed time for this statement was 386.65 seconds   
4.) The CPU time for this statement was 114.55 seconds   
5.) The wait time should be:   
wait time = elapsed time – CPU time   
wait time = 386.65 – 114.55 = 272.10 seconds

Oracle

著作权归作者所有

如果觉得我的文章对您有用，请点赞。您的支持将鼓励我继续创作！

添加新评论0 条评论

Ctrl+Enter 发表

匿名评论

Oracle个人技巧 -- top cpu

添加新评论0 条评论

作者其他文章

相关文章

相关问题

相关资料