Locks blocking2

De wiki.nexiat.fr
Aller à la navigation Aller à la recherche
Fiche express
Type Script SQL*Plus de diagnostic
Rôle Rapport tabulaire des verrous bloquants (sessions bloquante/bloquée)
Voir aussi Locks blocking

Locks blocking2 identifie les verrous bloquants dans la base (compatible RAC via les vues gv$) et les restitue sous forme de quatre requêtes SQL*Plus tabulaires successives, mises en forme par des COLUMN ... FORMAT ... HEADING classiques : un résumé des couples bloquant/bloqué, le détail des sessions (machine, PID OS, utilisateur), le texte SQL en attente, puis la liste des objets verrouillés.

Cette page est la variante « requêtes SQL*Plus pures » de la détection de verrous bloquants, sans PL/SQL : plus simple à adapter (il suffit de modifier les COLUMN ou d'ajouter un filtre WHERE) mais moins lisible en une seule fois qu'un rapport structuré. Pour un rendu condensé par incident, voir Locks blocking.

Script

SET TERMOUT OFF
COLUMN current_instance NEW_VALUE current_instance NOPRINT
SELECT RPAD(instance_name, 17) current_instance FROM v$instance;
SET TERMOUT ON

PROMPT
PROMPT +------------------------------------------------------------------------+
PROMPT | Rapport   : Verrous bloquants (requêtes tabulaires)                    |
PROMPT | Instance  : &current_instance                                         |
PROMPT +------------------------------------------------------------------------+

SET ECHO OFF FEEDBACK 6 HEADING ON LINESIZE 256 PAGESIZE 50000 TIMING OFF VERIFY OFF
CLEAR COLUMNS BREAKS COMPUTES

COLUMN waiting_instance_sid_serial FORMAT a24  HEADING '[ATTENTE]|Instance - SID / Serial#'
COLUMN waiting_oracle_username     FORMAT a20  HEADING '[ATTENTE]|Utilisateur Oracle'
COLUMN waiting_pid                 FORMAT a11  HEADING '[ATTENTE]|PID'
COLUMN waiting_machine             FORMAT a15  HEADING '[ATTENTE]|Machine' TRUNC
COLUMN waiting_os_username         FORMAT a15  HEADING '[ATTENTE]|Utilisateur OS'
COLUMN waiter_lock_type_mode_req   FORMAT a35  HEADING 'Type de verrou / Mode demandé'
COLUMN waiting_lock_time_min       FORMAT a10  HEADING '[ATTENTE]|Durée'
COLUMN waiting_sql_text            FORMAT a105 HEADING '[ATTENTE]|SQL' WRAP

COLUMN locking_instance_sid_serial FORMAT a24  HEADING '[VERROUILLE]|Instance - SID / Serial#'
COLUMN locking_oracle_username     FORMAT a20  HEADING '[VERROUILLE]|Utilisateur Oracle'
COLUMN locking_pid                 FORMAT a11  HEADING '[VERROUILLE]|PID'
COLUMN locking_machine             FORMAT a15  HEADING '[VERROUILLE]|Machine' TRUNC
COLUMN locking_os_username         FORMAT a15  HEADING '[VERROUILLE]|Utilisateur OS'
COLUMN locking_lock_time_min       FORMAT a10  HEADING '[VERROUILLE]|Durée'

COLUMN instance_name    FORMAT a8  HEADING 'Instance'
COLUMN sid              FORMAT 999999 HEADING 'SID'
COLUMN session_status   FORMAT a9  HEADING 'Statut'
COLUMN object_owner     FORMAT a15 HEADING 'Propriétaire'
COLUMN object_name      FORMAT a25 HEADING 'Objet'
COLUMN object_type      FORMAT a15 HEADING 'Type'
COLUMN locked_mode                 HEADING 'Mode'

PROMPT
PROMPT +------------------------------------------------------------------------+
PROMPT | VERROUS BLOQUANTS (Résumé)                                             |
PROMPT +------------------------------------------------------------------------+

SELECT
    iw.instance_name || ' - ' || lw.sid || ' / ' || sw.serial#  waiting_instance_sid_serial
  , sw.username                                                 waiting_oracle_username
  , ROUND(lw.ctime / 60) || ' min.'                              waiting_lock_time_min
  , DECODE(lh.type
          , 'CF', 'Control File'
          , 'DX', 'Distributed Transaction'
          , 'FS', 'File Set'
          , 'IR', 'Instance Recovery'
          , 'IS', 'Instance State'
          , 'IV', 'Libcache Invalidation'
          , 'LS', 'Log Start or Log Switch'
          , 'MR', 'Media Recovery'
          , 'RT', 'Redo Thread'
          , 'RW', 'Row Wait'
          , 'SQ', 'Sequence Number'
          , 'ST', 'Diskspace Transaction'
          , 'TE', 'Extend Table'
          , 'TT', 'Temp Table'
          , 'TX', 'Transaction'
          , 'TM', 'DML'
          , 'UL', 'PLSQL User_lock'
          , 'UN', 'User Name'
          , 'Nothing-') || ' / ' ||
    DECODE(lw.request
          , 0, 'None'
          , 1, 'NoLock'
          , 2, 'Row-Share (SS)'
          , 3, 'Row-Exclusive (SX)'
          , 4, 'Share-Table'
          , 5, 'Share-Row-Exclusive (SSX)'
          , 6, 'Exclusive'
          , '[Nothing]')                                              waiter_lock_type_mode_req
  , ih.instance_name || ' - ' || lh.sid || ' / ' || sh.serial#        locking_instance_sid_serial
  , sh.username                                                       locking_oracle_username
  , ROUND(lh.ctime / 60) || ' min.'                                   locking_lock_time_min
FROM
    gv$lock lw, gv$lock lh, gv$instance iw, gv$instance ih, gv$session sw, gv$session sh
WHERE
      iw.inst_id = lw.inst_id AND ih.inst_id = lh.inst_id
  AND sw.inst_id = lw.inst_id AND sh.inst_id = lh.inst_id
  AND sw.sid = lw.sid AND sh.sid = lh.sid
  AND lh.id1 = lw.id1 AND lh.id2 = lw.id2
  AND lh.request = 0 AND lw.lmode = 0
  AND (lh.id1, lh.id2) IN (
        SELECT id1, id2 FROM gv$lock WHERE request = 0
        INTERSECT
        SELECT id1, id2 FROM gv$lock WHERE lmode = 0
      )
ORDER BY
    iw.instance_name, lw.sid;


PROMPT
PROMPT +------------------------------------------------------------------------+
PROMPT | VERROUS BLOQUANTS (Détail des sessions)                                |
PROMPT +------------------------------------------------------------------------+

SELECT
    iw.instance_name || ' - ' || lw.sid || ' / ' || sw.serial#  waiting_instance_sid_serial
  , sw.username                                                 waiting_oracle_username
  , sw.osuser                                                   waiting_os_username
  , sw.machine                                                  waiting_machine
  , pw.spid                                                     waiting_pid
  , ih.instance_name || ' - ' || lh.sid || ' / ' || sh.serial#  locking_instance_sid_serial
  , sh.username                                                 locking_oracle_username
  , sh.osuser                                                   locking_os_username
  , sh.machine                                                  locking_machine
  , ph.spid                                                     locking_pid
FROM
    gv$lock lw, gv$lock lh, gv$instance iw, gv$instance ih
  , gv$session sw, gv$session sh, gv$process pw, gv$process ph
WHERE
      iw.inst_id = lw.inst_id AND ih.inst_id = lh.inst_id
  AND sw.inst_id = lw.inst_id AND sh.inst_id = lh.inst_id
  AND pw.inst_id = lw.inst_id AND ph.inst_id = lh.inst_id
  AND sw.sid = lw.sid AND sh.sid = lh.sid
  AND lh.id1 = lw.id1 AND lh.id2 = lw.id2
  AND lh.request = 0 AND lw.lmode = 0
  AND (lh.id1, lh.id2) IN (
        SELECT id1, id2 FROM gv$lock WHERE request = 0
        INTERSECT
        SELECT id1, id2 FROM gv$lock WHERE lmode = 0
      )
  AND sw.paddr = pw.addr (+)
  AND sh.paddr = ph.addr (+)
ORDER BY
    iw.instance_name, lw.sid;


PROMPT
PROMPT +------------------------------------------------------------------------+
PROMPT | VERROUS BLOQUANTS (SQL en attente)                                     |
PROMPT +------------------------------------------------------------------------+

SELECT
    iw.instance_name || ' - ' || lw.sid || ' / ' || sw.serial#  waiting_instance_sid_serial
  , aw.sql_text                                                 waiting_sql_text
FROM
    gv$lock lw, gv$lock lh, gv$instance iw, gv$instance ih
  , gv$session sw, gv$session sh, gv$sqlarea aw
WHERE
      iw.inst_id = lw.inst_id AND ih.inst_id = lh.inst_id
  AND sw.inst_id = lw.inst_id AND sh.inst_id = lh.inst_id
  AND aw.inst_id = lw.inst_id
  AND sw.sid = lw.sid AND sh.sid = lh.sid
  AND lh.id1 = lw.id1 AND lh.id2 = lw.id2
  AND lh.request = 0 AND lw.lmode = 0
  AND (lh.id1, lh.id2) IN (
        SELECT id1, id2 FROM gv$lock WHERE request = 0
        INTERSECT
        SELECT id1, id2 FROM gv$lock WHERE lmode = 0
      )
  AND sw.sql_address = aw.address
ORDER BY
    iw.instance_name, lw.sid;


PROMPT
PROMPT +------------------------------------------------------------------------+
PROMPT | OBJETS VERROUILLÉS                                                     |
PROMPT +------------------------------------------------------------------------+

SELECT
    i.instance_name   instance_name
  , l.session_id       sid
  , s.status            session_status
  , l.oracle_username   locking_oracle_user
  , s.osuser            locking_os_user
  , s.machine           locking_machine
  , p.spid              locking_os_pid
  , o.owner             object_owner
  , o.object_name       object_name
  , o.object_type       object_type
  , DECODE(l.locked_mode
          , 0, 'None'
          , 1, 'NoLock'
          , 2, 'Row-Share (SS)'
          , 3, 'Row-Exclusive (SX)'
          , 4, 'Share-Table'
          , 5, 'Share-Row-Exclusive (SSX)'
          , 6, 'Exclusive'
          , '[Nothing]') locked_mode
FROM
    dba_objects o, gv$session s, gv$process p, gv$locked_object l, gv$instance i
WHERE
      i.inst_id   = l.inst_id
  AND s.inst_id   = l.inst_id
  AND s.inst_id   = p.inst_id
  AND s.sid       = l.session_id
  AND o.object_id = l.object_id
  AND s.paddr     = p.addr
ORDER BY
    i.instance_name, l.session_id;

Les quatre requêtes reposent sur la même logique de jointure que Locks blocking : apparier, sur un même identifiant de ressource (id1/id2 de gv$lock), une session en attente (lmode = 0) à la session qui détient effectivement le verrou (request = 0), la sous-requête INTERSECT ne conservant que les ressources où les deux situations coexistent. Découper le rapport en requêtes séparées permet de ne lancer que la partie utile (par exemple uniquement le résumé, ou uniquement les objets verrouillés) sans exécuter le bloc PL/SQL complet.

Voir aussi

  • Locks blocking — même diagnostic sous forme de rapport PL/SQL condensé par incident
Récupérée de "https://wiki.nexiat.fr/index.php?title=Locks_blocking2&oldid=2247"