SQL Script

Monitor SQL Area

Updated: 30-Jun-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

REM Copyright (C) Think Forward.com 1998- 2005. All rights reserved. 

spool sqlarea.lst

set pagesize 66 linesize 132
set echo off

column executions      heading "Execs"      format 99999999
column rows_processed  heading "Rows Procd" format 99999999
column loads           heading "Loads"      format 999999.99
column buffer_gets     heading "Buffer Gets"
column disk_reads     heading "Disk Reads"
column sql_text        heading "SQL Text"   format a60 wrap
column avg_cost        heading "Avg Cost"   format 99999999
column gets_per_exec   heading "Gets Per Exec"   format 99999999
column reads_per_exec   heading "Read Per Exec"   format 99999999
column rows_per_exec   heading "Rows Per Exec"   format 99999999

break on report
compute sum      of rows_processed     on report
compute sum      of executions         on report
compute avg      of avg_cost           on report
compute avg      of gets_per_exec      on report
compute avg      of reads_per_exec      on report
compute avg      of row_per_exec      on report


PROMPT 
PROMPT Top most expensive SQL (Buffer Gets by Executions)...
PROMPT

select buffer_gets, 
       executions,
       buffer_gets/ decode(executions,0,1, executions) gets_per_exec,
       hash_value,
      sql_text
from  v$sqlarea
where buffer_gets > 50000
order by buffer_gets desc
/


PROMPT Top most expensive SQL (Physical Reads by Executions)...
PROMPT

select disk_reads, 
       executions,
       disk_reads / decode(executions,0,1, executions) reads_per_exec,
       hash_value,
      sql_text
   from  v$sqlarea
   where disk_reads > 10000
order by disk_reads desc
/

PROMPT Top most expensive SQL (Rows Processed by Executions)...
PROMPT

select rows_processed, 
       executions,
       rows_processed / decode(executions,0,1, executions) rows_per_exec,
       hash_value,
      sql_text
from  v$sqlarea
where rows_processed > 10000
order by rows_processed desc
/

PROMPT 
PROMPT Top most expensive SQL (Buffer Gets vs Rows Processed)...
PROMPT

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 decode(rows_processed,0,1,1) * buffer_gets/ decode(rows_processed,0,1,rows_processed) > 10000
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 
rem
rem This May be a candidate application for using the init.ora parameter
rem CURSOR_SHARING = FORCE|SIMILAR


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

prompt Output spooled to sqlarea.lst

spool off

Return to Index of SQL Scripts

Site Map

SQL Script

Monitor SQL Area

Updated: 30-Jun-1999Version: 1.1

Description

Parameters

SQL Source

Site Map

Updated: 30-Jun-1999
Version: 1.1