Poking nose to SQL workarea of your oracle DB

– This script in detail the SQL_WorkArea.
– Giving list of 500 work area with largest optimal size.
– Helps also to track the SQL query along with its cost, disk reads, rows processed.

set lines 290
set pages 501

Column workarea_address heading wk_add format A20
Column module format a50
Column optimizer_cost format 999999999999
Column sql_id format A13
column disk_read_per_row format 9999999999
Column operation_type format A20
Column policy format A10
Column estimated_optimal_size heading est_opt_size format 999999999999999999
Column optimal_executions heading opt_ex_cnt format 9999999999
Column onepass_executions heading onep_ex_cnt format 9999999999
Column multipass_executions heading mltp_ex_cnt format 9999999999
column max_tempseg_size format 9999999999999999
column last_tempseg_size format 9999999999999999

spool workarea.lst
Prompt "New Script"
Select * from (
SELECT	swa.workarea_address, s.module,s.sql_id,s.optimizer_cost,s.disk_reads,s.rows_processed,
(s.disk_reads/s.rows_processed) disk_read_per_row,
swa.operation_type, swa.policy, swa.estimated_optimal_size,swa.OPTIMAL_EXECUTIONS,
swa.ONEPASS_EXECUTIONS,swa.MULTIPASSES_EXECUTIONS,
nvl(swa.MAX_TEMPSEG_SIZE,0) max_tempseg_size, nvl(swa.LAST_TEMPSEG_SIZE,0) last_tempseg_size
FROM	sys.v$sql_workarea swa, sys.v$sql s
where	s.address = swa.address
and	s.hash_value = swa.hash_value
and	s.child_number = swa.child_number
and	s.rows_processed > 0
ORDER BY	s.disk_reads desc,s.optimizer_cost desc
)
where rownum < 500;
-- check the above value as per your requirement.
spool off