top five recent SQL statements that performed the highest disk I/O

SELECT sql_text, disk_reads FROM (SELECT sql_text, buffer_gets, disk_reads, sorts,

cpu_time/1000000 cpu, rows_processed, elapsed_time FROM v$sqlstats ORDER BY disk_reads DESC)

WHERE rownum <= 5;

If you join V$SQLSTATS to V$SQL, you can see information for particular users. See the following example:

SELECT schema, sql_text, disk_reads, round(cpu,2)

FROM (SELECT s.parsing_schema_name schema, t.sql_id, t.sql_text, t.disk_reads,

t.sorts, t.cpu_time/1000000 cpu, t.rows_processed, t.elapsed_time

FROM v$sqlstats t join v$sql s on(t.sql_id = s.sql_id)

WHERE parsing_schema_name = 'SCOTT' ORDER BY disk_reads DESC)

WHERE rownum <= 5;

The V$SQL_MONITOR view contains currently running SQL statements, as well as recently run SQL statements.

If you wanted to see the top five most CPU-consuming queries in your database, you could issue the following query:

SELECT * FROM ( SELECT sid, buffer_gets, disk_reads, round(cpu_time/1000000,1) cpu_seconds F

ROM v$sql_monitor ORDER BY cpu_time desc)

WHERE rownum <= 5;