Expensive SQL

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.