SQL Script Monitor SQL Area

Updated: 24-Mar-2005
Version: 6.0

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 180 trimspool on
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 elapsed_time   heading "Elasped Time" format 999999999
column cpu_time       heading "CPU Time" format 999999999
column sql_text        heading "SQL Text"   format a60 wrap
column avg_cost        heading "Avg Cost"   format 99999999
column etime_per_exec   heading "ETime Per Exec"   format 999999999
column ctime_per_exec   heading "CPU Time Per Exec"   format 999999999
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 etime_per_exec      on report
compute avg      of ctime_per_exec      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 10 most expensive SQL (Elapsed Time (secs) )...
PROMPT

select rownum as rank, a.*
from (
   select elapsed_Time/1000000 elapsed_time, 
       executions,
       buffer_gets,
       disk_reads,
       cpu_time
       hash_value,
      sql_text
   from  v$sqlarea
   where elapsed_time/1000000 > 5
   order by elapsed_time desc) a
where rownum < 11
/

PROMPT 
PROMPT Top 10 most expensive SQL (Elapsed Time (secs) Per Exec )...
PROMPT

select rownum as rank, a.*
from (
   select elapsed_Time/1000000 elapsed_time, 
       executions,
       elapsed_Time / (1000000 * decode(executions,0,1, executions) ) etime_per_exec,
       buffer_gets,
       disk_reads,
       cpu_time
       hash_value,
      sql_text
   from  v$sqlarea
   where elapsed_time/1000000 > 5
   order by etime_per_exec desc) a
where rownum < 11
/

PROMPT 
PROMPT Top 10 most expensive SQL (CPU Time (secs) )...
PROMPT

select rownum as rank, a.*
from (
   select cpu_time/1000000 cpu_time, 
       executions,
       buffer_gets,
       disk_reads,
       cpu_time
       hash_value,
      sql_text
   from  v$sqlarea
   where cpu_time/1000000 > 5
   order by cpu_time desc) a
where rownum < 11
/

PROMPT 
PROMPT Top 10 most expensive SQL (CPU Time (secs) per Exec)...
PROMPT

select rownum as rank, a.*
from (
   select cpu_time/1000000 cpu_time, 
       executions,
       cpu_time / (1000000 * decode(executions,0,1, executions)) ctime_per_exec,
       buffer_gets,
       disk_reads,
       cpu_time
       hash_value,
      sql_text
   from  v$sqlarea
   where cpu_time/1000000 > 5
   order by ctime_per_exec desc) a
where rownum < 11
/
PROMPT 
PROMPT Top 10 most expensive SQL (Buffer Gets)...
PROMPT

select rownum as rank, a.*
from (
   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) a
where rownum < 11
/

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

select rownum as rank, a.*
from (
   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 gets_per_exec desc) a
where rownum < 11
/

PROMPT Top 10 most expensive SQL (Physical Reads)...
PROMPT

select rownum as rank, a.*
from (
   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) a
where rownum < 11
/

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

select rownum as rank, a.*
from (
   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 reads_per_exec desc) a
where rownum < 11
/

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

select rownum as rank, a.*
from (
   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_per_exec desc) a
where rownum < 11
/

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

select rownum as rank, a.*
from (
   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) a
where rownum < 11
/

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 rownum as rank, a.*
from (
   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) a
where rownum < 11
/

prompt Output spooled to sqlarea.lst

spool off                                                                   

Related Links

Oracle 8i: (SQL Script) Monitor SQL Area

Return to Index of SQL Scripts

Site Map

General Information : Home | Company Profile | Contact Us | Legal
Database Support : Proactive Support | Pay Per Incident Support
Expert Services : High Availability | Creative Solutions | Training
Build Services : Oracle Installations | Revive your Oracle Setup | Low Cost Development
Free Oracle Resources: DBA Scripts | Oracle Links