SQL Script

Monitor SQL Area

Updated : 30-June-1999
Version : 1.1

Description

This script queries the SQL area ordered by the the average cost of the statement. The "Avg Cost" row is basically the No. of Buffer Gets per Rows processed. Where no rows are processed, all Buffer Gets are reported for the statement. The statements with the greatest cost are at the top of the report.

The script also lists any potential candidates for a converting to stored procedures by running a case insensitive query.

Parameters

None

SQL Source

--
-- Query the v$sqlarea view for expensive
-- statements
--
-- The "Avg Cost" row is basically the No. of Buffer Gets per Rows processed.
-- Where no rows are processed, all Buffer Gets are reported for the statement.
--
-- The statements with the greatest cost are at the top of the report.
--
spool sqlarea.lst
set pagesize 66 linesize 132
set echo on

column executions      heading "Execs"      format 99999999
column rows_processed  heading "Rows Procd" format a20
column loads           heading "Loads"      format 999999.99
column buffer_gets     heading "Buffer Gets"
column sql_text        heading "SQL Text"   format a60 wrap
column avg_cost        heading "Avg Cost"   format 99999999
break on report

compute sum      of rows_processed     on report
compute sum      of executions         on report
compute sum  avg of loads              on report
compute avg      of avg_cost           on report

select buffer_gets, lpad(rows_processed ||
       decode(users_opening + users_executing, 0, ' ','*'),20) "rows_processed",
       executions, loads,
       (decode(rows_processed,0,1,1)) *
            buffer_gets/ decode(rows_processed,0,1,
                                  rows_processed) avg_cost,
      sql_text
from  v$sqlarea
where hash_value = hash_value
order by 5 desc
/

rem Check to see if there are any candidates for procedures or 
rem for using bind variables. Check this by comparing UPPER

select 	upper(substr(sql_text, 1, 65)) sqltext, count(*)
from 	v$sqlarea   
group by upper(substr(sql_text, 1, 65))
having count(*) > 1
/

prompt Output spooled to sqlarea.lst
spool off