Knowledge Base : Show TOP SQL

10 Juni 2, 2022

Show top 25 SQL Statements, sorted per CPU usage:

SELECT * FROM 
(SELECT s.username,
s.machine,
s.terminal,
a.sql_id,
a.cpu_time/1000000 cpu_time, 
a.elapsed_time/1000000 elapsed_time, 
a.disk_reads, 
a.buffer_gets, 
a.rows_processed ,
a.sql_text
FROM v$sqlarea a,
v$session s
WHERE a.sql_id = s.sql_id
ORDER BY a.cpu_time DESC
) 
WHERE rownum <=25;

Perhaps MOATS is more efficient.

Mumbai is also a nice performance monitoring toolset.