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.