Locks blocking

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

Locks blocking identifie les verrous bloquants dans la base (compatible RAC via les vues gv$) et affiche, pour chaque incident, la session bloquée et la session bloquante côte à côte, sous forme d'un rapport texte structuré généré en PL/SQL. Le script se termine par une liste complémentaire de tous les objets actuellement verrouillés.

Cette page est la variante « rapport structuré » de la détection de verrous bloquants : le script construit une collection PL/SQL puis imprime un bloc détaillé par incident via DBMS_OUTPUT. Pour une variante plus légère, en SQL*Plus pur avec colonnes tabulaires (sans PL/SQL), voir Locks blocking2.

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 (détail par incident)                    |
PROMPT | Instance  : &current_instance                                         |
PROMPT +------------------------------------------------------------------------+

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

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

SET SERVEROUTPUT ON FORMAT WRAPPED
SET FEEDBACK OFF

DECLARE

    CURSOR cur_blocking_locks IS
        SELECT
            iw.instance_name AS waiting_instance
          , sw.status        AS waiting_status
          , lw.sid           AS waiting_sid
          , sw.serial#       AS waiting_serial_num
          , sw.username      AS waiting_oracle_username
          , sw.osuser        AS waiting_os_username
          , sw.machine       AS waiting_machine
          , pw.spid          AS waiting_spid
          , SUBSTR(sw.terminal, 0, 39) AS waiting_terminal
          , SUBSTR(sw.program, 0, 39)  AS waiting_program
          , ROUND(lw.ctime / 60) AS 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-') AS waiter_lock_type
          , 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]') AS waiter_mode_request
          , ih.instance_name AS locking_instance
          , sh.status        AS locking_status
          , lh.sid           AS locking_sid
          , sh.serial#       AS locking_serial_num
          , sh.username      AS locking_oracle_username
          , sh.osuser        AS locking_os_username
          , sh.machine       AS locking_machine
          , ph.spid          AS locking_spid
          , SUBSTR(sh.terminal, 0, 39) AS locking_terminal
          , SUBSTR(sh.program, 0, 39)  AS locking_program
          , ROUND(lh.ctime / 60) AS locking_lock_time_min
          , aw.sql_text      AS waiting_sql_text
        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
          , 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 pw.inst_id = lw.inst_id
          AND ph.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.paddr       = pw.addr (+)
          AND sh.paddr       = ph.addr (+)
          AND sw.sql_address = aw.address
        ORDER BY
            iw.instance_name
          , lw.sid;

    TYPE t_blocking_lock_record IS RECORD (
          waiting_instance_name    VARCHAR2(16)
        , waiting_status           VARCHAR2(8)
        , waiting_sid              NUMBER
        , waiting_serial_num       NUMBER
        , waiting_oracle_username  VARCHAR2(30)
        , waiting_os_username      VARCHAR2(30)
        , waiting_machine          VARCHAR2(64)
        , waiting_spid             VARCHAR2(12)
        , waiting_terminal         VARCHAR2(30)
        , waiting_program          VARCHAR2(48)
        , waiting_lock_time_minute NUMBER
        , waiter_lock_type         VARCHAR2(30)
        , waiter_mode_request      VARCHAR2(30)
        , locking_instance_name    VARCHAR2(16)
        , locking_status           VARCHAR2(8)
        , locking_sid              NUMBER
        , locking_serial_num       NUMBER
        , locking_oracle_username  VARCHAR2(30)
        , locking_os_username      VARCHAR2(30)
        , locking_machine          VARCHAR2(64)
        , locking_spid             VARCHAR2(12)
        , locking_terminal         VARCHAR2(30)
        , locking_program          VARCHAR2(48)
        , locking_lock_time_minute NUMBER
        , sql_text                 VARCHAR2(1000)
    );

    TYPE t_blocking_lock_table IS TABLE OF t_blocking_lock_record INDEX BY BINARY_INTEGER;

    v_blocking_lock_array         t_blocking_lock_table;
    v_blocking_lock_rec           cur_blocking_locks%ROWTYPE;
    v_num_blocking_lock_incidents BINARY_INTEGER := 0;

BEGIN

    DBMS_OUTPUT.ENABLE(1000000);

    OPEN cur_blocking_locks;
    LOOP
        FETCH cur_blocking_locks INTO v_blocking_lock_rec;
        EXIT WHEN cur_blocking_locks%NOTFOUND;

        v_num_blocking_lock_incidents := v_num_blocking_lock_incidents + 1;

        v_blocking_lock_array(v_num_blocking_lock_incidents).waiting_instance_name    := v_blocking_lock_rec.waiting_instance;
        v_blocking_lock_array(v_num_blocking_lock_incidents).waiting_status           := v_blocking_lock_rec.waiting_status;
        v_blocking_lock_array(v_num_blocking_lock_incidents).waiting_sid              := v_blocking_lock_rec.waiting_sid;
        v_blocking_lock_array(v_num_blocking_lock_incidents).waiting_serial_num       := v_blocking_lock_rec.waiting_serial_num;
        v_blocking_lock_array(v_num_blocking_lock_incidents).waiting_oracle_username  := v_blocking_lock_rec.waiting_oracle_username;
        v_blocking_lock_array(v_num_blocking_lock_incidents).waiting_os_username      := v_blocking_lock_rec.waiting_os_username;
        v_blocking_lock_array(v_num_blocking_lock_incidents).waiting_machine          := v_blocking_lock_rec.waiting_machine;
        v_blocking_lock_array(v_num_blocking_lock_incidents).waiting_spid             := v_blocking_lock_rec.waiting_spid;
        v_blocking_lock_array(v_num_blocking_lock_incidents).waiting_terminal         := v_blocking_lock_rec.waiting_terminal;
        v_blocking_lock_array(v_num_blocking_lock_incidents).waiting_program          := v_blocking_lock_rec.waiting_program;
        v_blocking_lock_array(v_num_blocking_lock_incidents).waiting_lock_time_minute := v_blocking_lock_rec.waiting_lock_time_min;
        v_blocking_lock_array(v_num_blocking_lock_incidents).waiter_lock_type         := v_blocking_lock_rec.waiter_lock_type;
        v_blocking_lock_array(v_num_blocking_lock_incidents).waiter_mode_request      := v_blocking_lock_rec.waiter_mode_request;
        v_blocking_lock_array(v_num_blocking_lock_incidents).locking_instance_name    := v_blocking_lock_rec.locking_instance;
        v_blocking_lock_array(v_num_blocking_lock_incidents).locking_status           := v_blocking_lock_rec.locking_status;
        v_blocking_lock_array(v_num_blocking_lock_incidents).locking_sid              := v_blocking_lock_rec.locking_sid;
        v_blocking_lock_array(v_num_blocking_lock_incidents).locking_serial_num       := v_blocking_lock_rec.locking_serial_num;
        v_blocking_lock_array(v_num_blocking_lock_incidents).locking_oracle_username  := v_blocking_lock_rec.locking_oracle_username;
        v_blocking_lock_array(v_num_blocking_lock_incidents).locking_os_username      := v_blocking_lock_rec.locking_os_username;
        v_blocking_lock_array(v_num_blocking_lock_incidents).locking_machine          := v_blocking_lock_rec.locking_machine;
        v_blocking_lock_array(v_num_blocking_lock_incidents).locking_spid             := v_blocking_lock_rec.locking_spid;
        v_blocking_lock_array(v_num_blocking_lock_incidents).locking_terminal         := v_blocking_lock_rec.locking_terminal;
        v_blocking_lock_array(v_num_blocking_lock_incidents).locking_program          := v_blocking_lock_rec.locking_program;
        v_blocking_lock_array(v_num_blocking_lock_incidents).locking_lock_time_minute := v_blocking_lock_rec.locking_lock_time_min;
        v_blocking_lock_array(v_num_blocking_lock_incidents).sql_text                 := v_blocking_lock_rec.waiting_sql_text;
    END LOOP;
    CLOSE cur_blocking_locks;

    DBMS_OUTPUT.PUT_LINE('Nombre d''incidents de verrou bloquant : ' || v_blocking_lock_array.COUNT);
    DBMS_OUTPUT.PUT(CHR(10));

    FOR row_index IN 1 .. v_blocking_lock_array.COUNT LOOP
        DBMS_OUTPUT.PUT_LINE('Incident ' || row_index);
        DBMS_OUTPUT.PUT_LINE('---------------------------------------------------------------------------------------------------------');
        DBMS_OUTPUT.PUT_LINE('                        BLOQUÉE                                  BLOQUANTE');
        DBMS_OUTPUT.PUT_LINE('                        ---------------------------------------- ----------------------------------------');
        DBMS_OUTPUT.PUT_LINE('Instance              : ' || RPAD(v_blocking_lock_array(row_index).waiting_instance_name, 41)   || v_blocking_lock_array(row_index).locking_instance_name);
        DBMS_OUTPUT.PUT_LINE('SID Oracle            : ' || RPAD(v_blocking_lock_array(row_index).waiting_sid, 41)             || v_blocking_lock_array(row_index).locking_sid);
        DBMS_OUTPUT.PUT_LINE('Serial#               : ' || RPAD(v_blocking_lock_array(row_index).waiting_serial_num, 41)      || v_blocking_lock_array(row_index).locking_serial_num);
        DBMS_OUTPUT.PUT_LINE('Utilisateur Oracle    : ' || RPAD(v_blocking_lock_array(row_index).waiting_oracle_username, 41) || v_blocking_lock_array(row_index).locking_oracle_username);
        DBMS_OUTPUT.PUT_LINE('Utilisateur OS        : ' || RPAD(v_blocking_lock_array(row_index).waiting_os_username, 41)     || v_blocking_lock_array(row_index).locking_os_username);
        DBMS_OUTPUT.PUT_LINE('Machine               : ' || RPAD(v_blocking_lock_array(row_index).waiting_machine, 41)         || v_blocking_lock_array(row_index).locking_machine);
        DBMS_OUTPUT.PUT_LINE('PID OS                : ' || RPAD(v_blocking_lock_array(row_index).waiting_spid, 41)            || v_blocking_lock_array(row_index).locking_spid);
        DBMS_OUTPUT.PUT_LINE('Terminal              : ' || RPAD(v_blocking_lock_array(row_index).waiting_terminal, 41)        || v_blocking_lock_array(row_index).locking_terminal);
        DBMS_OUTPUT.PUT_LINE('Durée du verrou       : ' || RPAD(v_blocking_lock_array(row_index).waiting_lock_time_minute || ' min', 41) || v_blocking_lock_array(row_index).locking_lock_time_minute || ' min');
        DBMS_OUTPUT.PUT_LINE('Statut                : ' || RPAD(v_blocking_lock_array(row_index).waiting_status, 41)          || v_blocking_lock_array(row_index).locking_status);
        DBMS_OUTPUT.PUT_LINE('Programme             : ' || RPAD(v_blocking_lock_array(row_index).waiting_program, 41)         || v_blocking_lock_array(row_index).locking_program);
        DBMS_OUTPUT.PUT_LINE('Type de verrou attendu: ' || v_blocking_lock_array(row_index).waiter_lock_type);
        DBMS_OUTPUT.PUT_LINE('Mode demandé          : ' || v_blocking_lock_array(row_index).waiter_mode_request);
        DBMS_OUTPUT.PUT_LINE('SQL en attente        : ' || v_blocking_lock_array(row_index).sql_text);
        DBMS_OUTPUT.PUT(CHR(10));
    END LOOP;

END;
/

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

COLUMN instance_name       FORMAT a9  HEADING 'Instance'
COLUMN sid_serial          FORMAT a15 HEADING 'SID / Serial#'
COLUMN session_status      FORMAT a9  HEADING 'Status'
COLUMN locking_oracle_user FORMAT a20 HEADING 'Locking Oracle User'
COLUMN object_owner        FORMAT a15 HEADING 'Object Owner'
COLUMN object_name         FORMAT a25 HEADING 'Object Name'
COLUMN object_type         FORMAT a15 HEADING 'Object Type'
COLUMN locked_mode                    HEADING 'Locked Mode'

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

SELECT
    i.instance_name                       instance_name
  , l.session_id || ' / ' || s.serial#    sid_serial
  , s.status                              session_status
  , l.oracle_username                     locking_oracle_user
  , 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$locked_object  l
  , gv$instance       i
WHERE
      i.inst_id   = l.inst_id
  AND s.inst_id   = l.inst_id
  AND s.sid       = l.session_id
  AND o.object_id = l.object_id
ORDER BY
    i.instance_name
  , l.session_id;

Le rapport PL/SQL exploite les jointures classiques sur gv$lock pour apparier une session bloquée (lw.lmode = 0, en attente) à sa session bloquante (lh.request = 0, qui détient le verrou), sur le même identifiant de ressource (id1/id2). La sous-requête INTERSECT restreint le résultat aux seules ressources où coexistent une demande en attente et une détention effective. Le tableau DECODE traduit le code de type de verrou (lh.type) et le mode demandé (lw.request) en libellés lisibles.

Voir aussi

  • Locks blocking2 — même diagnostic sous forme de requêtes SQL*Plus tabulaires, sans bloc PL/SQL
Récupérée de "https://wiki.nexiat.fr/index.php?title=Locks_blocking&oldid=2246"