SQL Script
Monitor SQL Area
|
SQL ScriptMonitor SQL Area |
|
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.
None
spool sqlarea.lst
set pagesize 66 linesize 132
set echo off
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
PROMPT
PROMPT Top 10 most expensive SQL ...
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 hash_value = hash_value
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 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
|
Home
|
Company Profile
|
Services
|
Contact Us
|
SQL scripts and tips
|
Quiz
|
 |