Pourquoi ma session Oracle est bloquée? Tableau commençant à 0 - Why is my Oracle session blocked? Table starting at 0

9 février 2017

Pourquoi ma session Oracle est bloquée? Tableau commençant à 0 - Why is my Oracle session blocked? Table starting at 0

Introduction
Oracle met à notre disposition via le dictionnaire de données des milliers de vues. Cette richesse est aussi une faiblesse : où trouver les bonnes informations? Est-ce que je cherche dans les bonnes vues, est-ce qu'il n'y en aurait pas de plus pertinentes que celles que je regarde actuellement?

Ceci est particulièrement vrai dans le cas de sessions bloquées. Identifier qui me bloque, quel objet, sur quelle ligne... il est compliqué de retrouver toutes ces infos car elles sont éparpillées dans N vues : V$SESSION, DBA_WAITERS, V$LOCKED_OBJECT, V$LOCK, V$SESSION_BLOCKERS etc etc.

D'où mon post qui, via un seul ordre SQL, devrait répondre à beaucoup de vos questions :-)

Points d'attention
ATTENTION au fait suivant : le SGBD Oracle a été entièrement réécrit en langage C en 1983 pour sa version 3. Or, en C, les tableaux commencent à 0!

Vous voyez où je veux en venir? Beaucoup de personnes utilisent la pseudo colonne ROWNUM pour identifier l'ordre de création des enregistrements. Le problème est que ROWNUM commence à 1 alors que dans certaines vues du dictionnaire de données Oracle, la numérotation des enregistrements commence à 0! Il y a donc un décalage de 1 ligne et donc une erreur d'interprétation sur l'enregistrement qui est vraiment bloqué! Il faut donc bien connaître cette particularité avant d'utiliser ROWNUM.

Et encore, cette pseudo-colonne ROWNUM n'est pas vraiment pertinente pour identifier l'ordre de création des enregistrements car elle est recalculée à chaque ordre SQL; ROWID est meilleur pour cela, comme nous allons le voir.

Pour prouver ce que je dis, testons avec la vue V$FIXED_TABLE qui liste les structures mémoires X$. La colonne TABLE_NUM listant le numéro des structures commence bien à 0 alors que ROWNUM commence à 1!
          SQL> SELECT V.*, ROWNUM FROM V$FIXED_TABLE V WHERE ROWNUM < 6;
          NAME                  OBJECT_ID           TYPE           TABLE_NUM           ROWNUM
          ------------------------------ ---------- ----- ---------- ------------------------------
          X$KQFTA           4294950912           TABLE           0                               1
          X$KQFVI           4294950913           TABLE           1                               2
          X$KQFVT           4294951149           TABLE           2                               3
          X$KQFDT           4294950914           TABLE           3                               4
          X$KQFCO           4294951036           TABLE           4                               5

Base de tests
Comme base de tests j'utilise celle d'Oracle, à savoir la base avec les tables EMP et DEPT et deux users, HR et HRHR, pour créer un blocage.

Exemple
Le user HRHR veut updater le salaire du premier employé. Avec les pseudo-colonnes ROWID et ROWNUM, nous voyons qu'il s'agit de l'employé d'id 7369. Petit aparté, regardez les dernières colonnes du ROWID, ce sont elles qui donnent l'ordre de création de l'enregistrement : A puis B puis C etc et ROWNUM est visiblement calculé via ces colonnes. L'ordre SQL ne fait contient pas de ORDER BY mais le résultat montre qu'il est trié par ROWID.

          SQL> SELECT ROWID, ROWNUM, E.* FROM EMP E;
          ROWID         ROWNUM EMPNO       ENAME      JOB MGR HIREDATE SAL COMM DEPTNO
          ---------------------------------------- ------------- ---------------- ----------------------
          AAAIB9AAEAAAACTAAA    1 7369 SMITH      CLERK 7902 17/12/80 800 20
          AAAIB9AAEAAAACTAAB    2 7499 ALLEN      SALESMAN 7698 20/02/81 1600 300 30
          AAAIB9AAEAAAACTAAC 3 7521 WARD       SALESMAN 7698 22/02/81 1250 500 30
          AAAIB9AAEAAAACTAAD 4 7566 JONES      MANAGER 7839 02/04/81 2975 20
          AAAIB9AAEAAAACTAAE 5 7654 MARTIN     SALESMAN 7698 28/09/81 1250 1400 30
          AAAIB9AAEAAAACTAAF 6 7698 BLAKE      MANAGER 7839 01/05/81 2850 30
          AAAIB9AAEAAAACTAAG 7 7782 CLARK      MANAGER 7839 09/06/81 2450 10
          AAAIB9AAEAAAACTAAH 8 7788 SCOTT      ANALYST 7566 09/12/82 3000 20
          AAAIB9AAEAAAACTAAI 9 7839 KING PRESIDENT 17/11/81 5000 10
          AAAIB9AAEAAAACTAAJ 10 7844 TURNER     SALESMAN 7698 08/09/81 1500 0 30
          AAAIB9AAEAAAACTAAK 11 7876 ADAMS      CLERK 7788 12/01/83 1100 20
          AAAIB9AAEAAAACTAAL 12 7900 JAMES      CLERK 7698 03/12/81 950 30
          AAAIB9AAEAAAACTAAM 13 7902 FORD       ANALYST 7566 03/12/81 3000 20
          AAAIB9AAEAAAACTAAN 14 7934 MILLER     CLERK 7782 23/01/82 1300 0

L'ordre SQL de mise à jour est le suivant; attention, on ne fait pas de COMMIT pour justement laisser le lock sur cet enregistrement.
          SQL>UPDATE HR.EMP SET SAL = 2000 WHERE EMPNO = 7369;
          1 row updated.

Maintenant, le user HR veut augmenter de 10% tous les employés. L'ordre SQL de mise à jour est le suivant :
          SQL> UPDATE EMP SET SAL = SAL*1.1;
HR est maintenant bloqué sous SQL*Plus, il n'a plus la main car un verrou est posé sur le premier enregistrement à cause de l'update de HRHR. HR est donc en attente du COMMIT ou du ROLLBACK de HRHR et cela peut durer indéfiniment. Au bout d'un moment HR en a assez d'attendre et donc va voir son DBA et lui assène "C'est lent, je suis bloqué, qu'est-ce qui se passe?"

L'ordre SQL que je vous propose va récupérer un max d'infos sur ce blocage. Il se base sur V$SESSION pour identifier la session bloquée et la session bloquante, V$SQL pour l'ordre SQL qui bloque et DBA_OBJECTS pour les infos sur l'objet bloqué.
J'utilise le terme anglais BLOCKED plutôt que BLOQUE car, écrit en majuscule, BLOQUE peut signifier "bloqué" mais aussi "qui bloque" alors que c'est BLOQUANT que j'utilise pour identifier les objets "qui bloquent".
          SELECT
                    SBLOCKED.BLOCKING_SESSION AS "SESSION BLOQUANTE",
                    SBLOCKED.SID AS "SESSION BLOCKED",
                    SBLOCKING.OSUSER AS "OSUSER BLOQUANT",
                    SBLOCKED.OSUSER AS "OSUSER BLOCKED",
                    SBLOCKING.USERNAME AS "USERNAME BLOQUANT",
                    SBLOCKED.USERNAME AS "USERNAME BLOCKED",
                    SBLOCKING.PROGRAM AS "PROGRAMME BLOQUANT",
                    SBLOCKED.PROGRAM AS "PROGRAMME BLOCKED",
                    SBLOCKED.STATE AS "ETAT BLOCKED",
                    SBLOCKED.WAIT_TIME_MICRO / (1000000 *60) AS "BLOCAGE MINUTES",
                    SBLOCKED.ROW_WAIT_OBJ#,
                    OB.OBJECT_NAME AS "OBJET BLOCKED",
                    SBLOCKED.ROW_WAIT_FILE#,
                    SBLOCKED.ROW_WAIT_BLOCK#,
                    SBLOCKED.ROW_WAIT_ROW# AS "NUMERO ENREGISTREMENT BLOCKED",
                    SBLOCKED.SQL_ID AS "SQL ID BLOCKED",
                    SQL_BLOQUE.sql_text AS "ORDRE SQL BLOCKED"
          FROM
                    V$SESSION SBLOCKED,
                    V$SESSION SBLOCKING,
                    V$SQL SQL_BLOQUE,
                    DBA_OBJECTS OB
          WHERE
                    SBLOCKED.BLOCKING_SESSION = SBLOCKING.SID AND
                    SBLOCKED.SQL_ID = SQL_BLOQUE.SQL_ID AND
                    SBLOCKED.ROW_WAIT_OBJ# = OB.OBJECT_ID AND
                    SBLOCKED.STATUS = 'ACTIVE' AND
                    SBLOCKED.BLOCKING_SESSION IS NOT NULL
          ORDER BY
                    SBLOCKED.BLOCKING_SESSION;

Voici le résultat avec les champs disposés verticalement pour que ce soit plus lisible. Vous remarquerez que le champ ROW_WAIT_ROW# ("Current row being locked" selon la doc Oracle) dans V$SESSION vaut bien 0 alors que HRHR a updaté l'enregistrement de rownum 1 et donc un décalage de 1 ligne comme dit précédemment.
          SESSION BLOQUANTE : 13
          SESSION BLOCKED : 140

          OSUSER BLOQUANT : Tom\Suki
          OSUSER BLOCKED : Tom\Suki

          USERNAME BLOQUANT : HRHR
          USERNAME BLOCKED : HR

          PROGRAMME BLOQUANT : sqlplus.exe
          PROGRAMME BLOCKED : sqlplus.exe

          ETAT BLOQUE : WAITING
          BLOCAGE MINUTES : 5,77041632

          ROW_WAIT_OBJ# : 32893
          OBJET BLOCKED : EMP

          ROW_WAIT_FILE# : 4
          ROW_WAIT_BLOCK# : 147
          NUMERO ENREGISTREMENT BLOCKED : 0

          SQL ID BLOCKED : 2jdmxyxvnxumt
          ORDRE SQL BLOCKED : UPDATE EMP SET SAL = SAL*1.1

Voilà, avec cet ordre SQL vous pourrez rapidement identifier le problème de blocage et contacter la bonne personne pour quelle termine sa transaction.