Oracle个人技巧 -- top cpu

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