Find Top 10 Expensive SQL Statements
V$SQLAREA lists statistics on shared SQL area and contains one row per SQL string. It provides statistics on SQL statements that are in memory, parsed, and ready for execution.
select * from (select disk_reads , executions , sql_text from v$sqlarea order by 1 desc, 2 desc) where rownum <=10;
How to Create and View Explain Plans
Explain plan can be used to identify expensive queries that will consume excessive resources. You create a plan by typing "explain plan for" (without the quotes) followed by the sql statement you want to analyze. Then type SELECT * FROM table(DBMS_XPLAN.DISPLAY); to display the result.