Top SQL statements from V$SQLAREA (SQL*Plus)
SQL*Plus script that lists the most expensive statements in V$SQLAREA by elapsed time, CPU, buffer gets, physical reads and rows, and the statements that share the same text prefix.
The script below runs in SQL*Plus and spools its output to sqlarea.lst. It ranks the statements of the shared pool in V$SQLAREA by elapsed time, CPU time, buffer gets, physical reads and rows processed, with ten rows per section. The last section lists the statements that share the same first 65 characters, which are candidates for bind variables or for CURSOR_SHARING.
spool sqlarea.lst
set pagesize 100 linesize 500 trimspool on
SET LONG 2000000
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, dbms_lob.getlength(sql_text) , 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
col SQL_TEXT for a99
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
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
Notes
- Several select lists have no comma after
cpu_time(in the elapsed time, elapsed time per execution, CPU time and CPU time per execution sections).hash_valuethen becomes an alias ofcpu_time, and the hash value is not returned. The script is kept as in the source: add the comma before running it. - The average in
compute avg of row_per_execdoes not match any column alias, since the select list usesrows_per_exec. - The thresholds (for example
> 5seconds,> 50000buffer gets) are the source's values. Adjust them to the workload. - The source file was stored with the MSSQL maintenance notes, but its content is Oracle.