Oracle : requêtes de health check (événements d'attente, verrous, espace)
Requêtes SQL*Plus pour diagnostiquer une base Oracle : événements d'attente, verrous, CPU, tablespaces.
Un ensemble de requêtes SQL*Plus pour repérer ce sur quoi une base Oracle est en attente. Les vues gv$ couvrent toutes les instances RAC ; sur une instance unique, utiliser v$.
Top 25 des événements d'attente
set lines 180
set pages 1000
col event format a50
PROMPT --> Top 25 Wait Events
select * from (
select inst_id,event,count(*) E_COUNT from gv$session_wait
where event <> 'SQL*Net message from client'
group by inst_id,event order by 3 desc)
where rownum < 26
order by 3
/
Sessions actives en attente d'un événement donné
SQL*Plus demande le nom de l'événement (un motif LIKE).
col sql format a35
col username format a20
col child format 999
col secs format 9999
col machine format a12
col event format a25
col state format a10
select /*+ rule */ distinct
w.sid,s.username,substr(w.event,1,25) event,substr(s.machine,1,12) machine,substr(w.state,1,10) state,s.SQL_ID,
substr(q.sql_text,1,33) "SQL",round(s.LAST_CALL_ET/60) "MINS", round(s.LAST_CALL_ET) "Sec"
from gv$session_wait w,gv$session s,gv$sql q where w.event like '%&event%'
and w.sid=s.sid
and s.SQL_HASH_VALUE=q.HASH_VALUE(+)
and s.status='ACTIVE'
and s.username is not null
and substr(w.event,1,25) not like 'SQL*Net message from clie%'
order by "MINS"
/
Nombre de sessions foreground
select count(*), status, inst_id from gv$session
where username is not null
group by status, inst_id order by 3;
Verrous bloquants
select l1.inst_id,l1.sid, ' IS BLOCKING ', l2.sid,l1.type,l2.type,l1.lmode,l2.lmode,l2.inst_id
from gv$lock l1, gv$lock l2
where l1.block =1 and l2.request > 0
and l1.id1=l2.id1
and l1.id2=l2.id2;
Requêtes SQL classées par consommation CPU
column sql_text format a40 word_wrapped heading 'SQL|Text'
column cpu_time heading 'CPU|Time'
column elapsed_time heading 'Elapsed|Time'
column disk_reads heading 'Disk|Reads'
column buffer_gets heading 'Buffer|Gets'
column rows_processed heading 'Rows|Processed'
set pages 55 lines 132
select * from
(select sql_text,sql_id,
cpu_time/1000000 cpu_time,
elapsed_time/1000000 elapsed_time,
disk_reads,
buffer_gets,
rows_processed
from v$sqlarea
order by cpu_time desc, disk_reads desc
)
where rownum < 10
/
Ratios de temps CPU et de temps d'attente de la base
SET LINESIZE 1000
SET PAGESIZE 1000
COLUMN metric_name FORMAT a40 HEADING 'METRIC_NAME'
COLUMN metric_unit FORMAT a30 HEADING 'METRIC_UNIT'
COLUMN inst_id FORMAT 99 HEADING 'inst_id' JUSTIFY right
COLUMN end_time FORMAT a20 HEADING 'end_time' TRUNC
select METRIC_NAME,metric_unit,INST_ID,INTSIZE_CSEC,
VALUE,begin_time,end_time
from GV$SYSMETRIC
where METRIC_NAME IN ('Database CPU Time Ratio',
'Database Wait Time Ratio')
and (INTSIZE_CSEC, inst_id) in
(select max(INTSIZE_CSEC), inst_id from GV$SYSMETRIC group by inst_id)
order by 3;
Classes d'attente et temps attendu
Instance 1 uniquement ; retirer le filtre a.inst_id = 1 pour toutes les instances.
SET LINESIZE 145
SET PAGESIZE 9999
select b.wait_class, a.inst_id, a.begin_time,
a.end_time, a.dbtime_in_wait, a.time_waited
from GV$WAITCLASSMETRIC a, GV$SYSTEM_WAIT_CLASS b
where a.wait_class_id = b.wait_class_id
and a.inst_id = 1
and a.inst_id = b.inst_id
-- and b.wait_class='Commit'
order by 5 desc;
Utilisation du tablespace temporaire
SELECT A.tablespace_name tablespace, D.mb_total,
SUM (A.used_blocks * D.block_size) / 1024 / 1024 mb_used,
D.mb_total - SUM (A.used_blocks * D.block_size) / 1024 / 1024 mb_free
FROM v$sort_segment A,
(
SELECT B.name, C.block_size, SUM (C.bytes) / 1024 / 1024 mb_total
FROM v$tablespace B, v$tempfile C
WHERE B.ts#= C.ts#
GROUP BY B.name, C.block_size
) D
WHERE A.tablespace_name = D.name
GROUP BY A.tablespace_name, D.mb_total;
Utilisation des tablespaces
set line 1000;
col TABLESPACE_NAME for a30;
set pagesize 128;
set feedback off;
select a.TABLESPACE_NAME,
a.BYTES/1024/1024/1024 GB_used,
b.BYTES/1024/1024/1024 GB_free,
b.largest,
round(((a.BYTES-b.BYTES)/a.BYTES)*100,2) percent_used
from
(select TABLESPACE_NAME, sum(BYTES) BYTES
from dba_data_files
group by TABLESPACE_NAME) a,
(select TABLESPACE_NAME, sum(BYTES) BYTES, max(BYTES) largest
from dba_free_space
group by TABLESPACE_NAME) b
where a.TABLESPACE_NAME=b.TABLESPACE_NAME
order by ((a.BYTES-b.BYTES)/a.BYTES) desc;
Remarques
GB_usedest la taille allouée des fichiers de données, pas l'espace réellement utilisé ;percent_useddonne le taux d'utilisation réel.- Le hint
/*+ rule */provient de la requête d'origine ; l'optimiseur basé sur les règles n'est plus supporté dans les versions récentes.