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
- Plusieurs listes de colonnes n'ont pas de virgule après
cpu_time(sections du temps écoulé, du temps écoulé par exécution, du temps CPU et du temps CPU par exécution).hash_valuedevient alors un alias decpu_time, et la valeur de hachage n'est pas renvoyée. Le script est repris tel quel de la source : ajoutez la virgule avant de l'exécuter. - La moyenne
compute avg of row_per_execne correspond à aucun alias de colonne, la liste de sélection utilisantrows_per_exec. - Les seuils (par exemple
> 5secondes,> 50000buffer gets) sont les valeurs de la source. Adaptez-les à votre charge. - Le fichier source était rangé avec les notes de maintenance MSSQL, mais son contenu concerne Oracle.