LLI Wiki — My Digital Knowledge Base

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

Mastodon LLIMastodon SocialFosstodon