SET ECHO OFF
SET FEEDBACK OFF
SET PAGES 0
SET LINES 80

REM
REM || Title : show_sqlarea.sql
REM ||
REM || Purpose : This script will Display poorly performing SQL
REM ||
REM ||
REM || Release No : 1
REM ||
REM || Variables : None
REM ||
REM || Schema : sys
REM ||
REM ||
REM ||

SELECT '---------------------------------------------------------------------------'
nl,
a.sql_text nl,
' '
nl,
'Executions = '|| a.executions nl,
'Rows Processed per Exec = '|| ROUND (a.rows_processed / a.executions, 2) nl,
'Buffer Gets per Exec = '|| ROUND (buffer_gets / a.executions, 2) nl,
'Total Rows Processed = '|| rows_processed nl,
'Disk Reads = '|| disk_reads nl,
'Buffer gets = '|| buffer_gets nl,
'Hit Ratio = '|| ROUND (( buffer_gets - disk_reads) / buffer_gets, 2)
FROM v$sqlarea a,
v$sqltext_with_newlines b
WHERE a.address = b.address
AND a.hash_value = b.hash_value
AND executions > 0
AND buffer_gets > 0
ORDER BY buffer_gets,
executions DESC;

SPOOL show_sql_bfg_exec.lis
/
SPOOL off

SET FEEDBACK ON
SET PAGES 80