LLI Wiki — My Digital Knowledge Base

Top des requêtes SQL depuis V$SQLAREA (SQL*Plus)

Script SQL*Plus qui liste les requêtes les plus coûteuses de V$SQLAREA (temps écoulé, CPU, buffer gets, lectures physiques, lignes) et celles qui ne diffèrent que par leurs littéraux.

Le script ci-dessous s'exécute dans SQL*Plus et écrit sa sortie dans sqlarea.lst. Il classe les instructions du shared pool de V$SQLAREA selon le temps écoulé, le temps CPU, les buffer gets, les lectures physiques et les lignes traitées, avec dix lignes par section. La dernière section liste les instructions qui partagent les mêmes 65 premiers caractères : ce sont des candidates à l'usage de variables de liaison ou de 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

Remarques

Mastodon LLIMastodon SocialFosstodon