Lock Incident Details Job

I've been asked to provide statistics of our Lock Incidents happened last month? I had have only the number of the incidents with no details about who were locked, in which module happened and what was the caused query so I decided to enhance my Lock Job Query to contain more details about each indecent.

we're going to have 2 files;

Lock.sql which contains the following:

spool on
spool /u02/app/oracle/DBScripts/output.log

SELECT COUNT (1) AS "Number of Blocked Sessions"
  FROM gv$lock l1,
       gv$session s1,
       gv$lock l2,
       gv$session s2
 WHERE     s1.sid = l1.sid
       AND s2.sid = l2.sid
       AND l1.BLOCK = 1
       AND l2.request > 0
       AND l1.id1 = l2.id1
       AND l2.id2 = l2.id2
HAVING COUNT (1) > 0;

-- |      Copyright (c) 1998-2012 Jeffrey M. Hunter. All rights reserved.       |
PROMPT
PROMPT +------------------------------------------------------------------------+
PROMPT | Report   : Blocking Locks                                              |
PROMPT | Database: ORCL                                                        |
PROMPT +------------------------------------------------------------------------+
SET ECHO        OFF
SET FEEDBACK    6
SET HEADING     ON
SET LINESIZE    180
SET PAGESIZE    50000
SET TERMOUT     ON
SET TIMING      OFF
SET TRIMOUT     ON
SET TRIMSPOOL   ON
SET VERIFY      OFF
CLEAR COLUMNS
CLEAR BREAKS
CLEAR COMPUTES
PROMPT
PROMPT
PROMPT +------------------------------------------------------------------------+
PROMPT | BLOCKING LOCKS (Summary)                                               |
PROMPT +------------------------------------------------------------------------+
PROMPT
SET serveroutput ON FORMAT WRAPPED
SET feedback OFF

DECLARE
   CURSOR cur_BlockingLocks
   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'        /* Mon Lock equivalent */
                                             ,  1, 'NoLock'            /* N */
                                                           ,  2, 'Row-Share (SS)' /* L */
                                                                                 ,  3, 'Row-Exclusive (SX)' /* R */
                                                                                                           ,  4, 'Share-Table' /* S */
                                                                                                                              ,  5, 'Share-Row-Exclusive (SSX)' /* C */
                                                                                                                                                               ,  6, 'Exclusive' /* X */
                                                                                                                                                                                ,  '[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_BlockingLockRecord IS RECORD
   (
      WaitingInstanceName     VARCHAR2 (16),
      WaitingStatus           VARCHAR2 (8),
      WaitingSid              NUMBER,
      WaitingSerialNum        NUMBER,
      WaitingOracleUsername   VARCHAR2 (30),
      WaitingOSUsername       VARCHAR2 (30),
      WaitingMachine          VARCHAR2 (64),
      WaitingSpid             VARCHAR2 (12),
      WaitingTerminal         VARCHAR2 (30),
      WaitingProgram          VARCHAR2 (48),
      WaitingLockTimeMinute   NUMBER,
      WaiterLockType          VARCHAR2 (30),
      WaiterModeRequest       VARCHAR2 (30),
      LockingInstanceName     VARCHAR2 (16),
      LockingStatus           VARCHAR2 (8),
      LockingSid              NUMBER,
      LockingSerialNum        NUMBER,
      LockingOracleUsername   VARCHAR2 (30),
      LockingOSUsername       VARCHAR2 (30),
      LockingMachine          VARCHAR2 (64),
      LockingSpid             VARCHAR2 (12),
      LockingTerminal         VARCHAR2 (30),
      LockingProgram          VARCHAR2 (48),
      LockingLockTimeMinute   NUMBER,
      SQLText                 VARCHAR2 (1000)
   );

   TYPE t_BlockingLockRecordTable IS TABLE OF t_BlockingLockRecord
                                        INDEX BY BINARY_INTEGER;

   v_BlockingLockArray           t_BlockingLockRecordTable;
   v_BlockingLockRec             cur_BlockingLocks%ROWTYPE;
   v_NumBlockingLocksIncidents   BINARY_INTEGER := 0;
BEGIN
   DBMS_OUTPUT.ENABLE (1000000);

   OPEN cur_BlockingLocks;

   LOOP
      FETCH cur_BlockingLocks INTO v_BlockingLockRec;

      EXIT WHEN cur_BlockingLocks%NOTFOUND;

      v_NumBlockingLocksIncidents := v_NumBlockingLocksIncidents + 1;

      v_BlockingLockArray (v_NumBlockingLocksIncidents).WaitingInstanceName :=
         v_BlockingLockRec.waiting_instance;
      v_BlockingLockArray (v_NumBlockingLocksIncidents).WaitingStatus :=
         v_BlockingLockRec.waiting_status;
      v_BlockingLockArray (v_NumBlockingLocksIncidents).WaitingSid :=
         v_BlockingLockRec.waiting_sid;
      v_BlockingLockArray (v_NumBlockingLocksIncidents).WaitingSerialNum :=
         v_BlockingLockRec.waiting_serial_num;
      v_BlockingLockArray (v_NumBlockingLocksIncidents).WaitingOracleUsername :=
         v_BlockingLockRec.waiting_oracle_username;
      v_BlockingLockArray (v_NumBlockingLocksIncidents).WaitingOSUsername :=
         v_BlockingLockRec.waiting_os_username;
      v_BlockingLockArray (v_NumBlockingLocksIncidents).WaitingMachine :=
         v_BlockingLockRec.waiting_machine;
      v_BlockingLockArray (v_NumBlockingLocksIncidents).WaitingSpid :=
         v_BlockingLockRec.waiting_spid;
      v_BlockingLockArray (v_NumBlockingLocksIncidents).WaitingTerminal :=
         v_BlockingLockRec.waiting_terminal;
      v_BlockingLockArray (v_NumBlockingLocksIncidents).WaitingProgram :=
         v_BlockingLockRec.waiting_program;
      v_BlockingLockArray (v_NumBlockingLocksIncidents).WaitingLockTimeMinute :=
         v_BlockingLockRec.waiting_lock_time_min;
      v_BlockingLockArray (v_NumBlockingLocksIncidents).WaiterLockType :=
         v_BlockingLockRec.waiter_lock_type;
      v_BlockingLockArray (v_NumBlockingLocksIncidents).WaiterModeRequest :=
         v_BlockingLockRec.waiter_mode_request;
      v_BlockingLockArray (v_NumBlockingLocksIncidents).LockingInstanceName :=
         v_BlockingLockRec.locking_instance;
      v_BlockingLockArray (v_NumBlockingLocksIncidents).LockingStatus :=
         v_BlockingLockRec.locking_status;
      v_BlockingLockArray (v_NumBlockingLocksIncidents).LockingSid :=
         v_BlockingLockRec.locking_sid;
      v_BlockingLockArray (v_NumBlockingLocksIncidents).LockingSerialNum :=
         v_BlockingLockRec.locking_serial_num;
      v_BlockingLockArray (v_NumBlockingLocksIncidents).LockingOracleUsername :=
         v_BlockingLockRec.locking_oracle_username;
      v_BlockingLockArray (v_NumBlockingLocksIncidents).LockingOSUsername :=
         v_BlockingLockRec.locking_os_username;
      v_BlockingLockArray (v_NumBlockingLocksIncidents).LockingMachine :=
         v_BlockingLockRec.locking_machine;
      v_BlockingLockArray (v_NumBlockingLocksIncidents).LockingSpid :=
         v_BlockingLockRec.locking_spid;
      v_BlockingLockArray (v_NumBlockingLocksIncidents).LockingTerminal :=
         v_BlockingLockRec.locking_terminal;
      v_BlockingLockArray (v_NumBlockingLocksIncidents).LockingProgram :=
         v_BlockingLockRec.locking_program;
      v_BlockingLockArray (v_NumBlockingLocksIncidents).LockingLockTimeMinute :=
         v_BlockingLockRec.locking_lock_time_min;
      v_BlockingLockArray (v_NumBlockingLocksIncidents).SQLText :=
         v_BlockingLockRec.waiting_sql_text;
   END LOOP;

   CLOSE cur_BlockingLocks;

   DBMS_OUTPUT.
    PUT_LINE (
      'Number of blocking lock incidents: ' || v_BlockingLockArray.COUNT);
   DBMS_OUTPUT.PUT (CHR (10));

   FOR RowIndex IN 1 .. v_BlockingLockArray.COUNT
   LOOP
      DBMS_OUTPUT.PUT_LINE ('Incident ' || RowIndex);
      DBMS_OUTPUT.
       PUT_LINE (
         '---------------------------------------------------------------------------------------------------------');
      DBMS_OUTPUT.
       PUT_LINE (
         '                        WAITING                                  BLOCKING');
      DBMS_OUTPUT.
       PUT_LINE (
         '                        ---------------------------------------- ----------------------------------------');
      DBMS_OUTPUT.
       PUT_LINE (
            'Instance Name         : '
         || RPAD (v_BlockingLockArray (RowIndex).WaitingInstanceName, 41)
         || v_BlockingLockArray (RowIndex).LockingInstanceName);
      DBMS_OUTPUT.
       PUT_LINE (
            'Oracle SID            : '
         || RPAD (v_BlockingLockArray (RowIndex).WaitingSid, 41)
         || v_BlockingLockArray (RowIndex).LockingSid);
      DBMS_OUTPUT.
       PUT_LINE (
            'Serial#               : '
         || RPAD (v_BlockingLockArray (RowIndex).WaitingSerialNum, 41)
         || v_BlockingLockArray (RowIndex).LockingSerialNum);
      DBMS_OUTPUT.
       PUT_LINE (
            'Oracle User           : '
         || RPAD (v_BlockingLockArray (RowIndex).WaitingOracleUsername, 41)
         || v_BlockingLockArray (RowIndex).LockingOracleUsername);
      DBMS_OUTPUT.
       PUT_LINE (
            'O/S User              : '
         || RPAD (v_BlockingLockArray (RowIndex).WaitingOSUsername, 41)
         || v_BlockingLockArray (RowIndex).LockingOSUsername);
      DBMS_OUTPUT.
       PUT_LINE (
            'Machine               : '
         || RPAD (v_BlockingLockArray (RowIndex).WaitingMachine, 41)
         || v_BlockingLockArray (RowIndex).LockingMachine);
      DBMS_OUTPUT.
       PUT_LINE (
            'O/S PID               : '
         || RPAD (v_BlockingLockArray (RowIndex).WaitingSpid, 41)
         || v_BlockingLockArray (RowIndex).LockingSpid);
      DBMS_OUTPUT.
       PUT_LINE (
            'Terminal              : '
         || RPAD (v_BlockingLockArray (RowIndex).WaitingTerminal, 41)
         || v_BlockingLockArray (RowIndex).LockingTerminal);
      DBMS_OUTPUT.
       PUT_LINE (
         'Lock Time             : '
         || RPAD (
               v_BlockingLockArray (RowIndex).WaitingLockTimeMinute
               || ' minutes',
               41)
         || v_BlockingLockArray (RowIndex).LockingLockTimeMinute
         || ' minutes');
      DBMS_OUTPUT.
       PUT_LINE (
            'Status                : '
         || RPAD (v_BlockingLockArray (RowIndex).WaitingStatus, 41)
         || v_BlockingLockArray (RowIndex).LockingStatus);
      DBMS_OUTPUT.
       PUT_LINE (
            'Program               : '
         || RPAD (v_BlockingLockArray (RowIndex).WaitingProgram, 41)
         || v_BlockingLockArray (RowIndex).LockingProgram);
      DBMS_OUTPUT.
       PUT_LINE (
         'Waiter Lock Type      : '
         || v_BlockingLockArray (RowIndex).WaiterLockType);
      DBMS_OUTPUT.
       PUT_LINE (
         'Waiter Mode Request   : '
         || v_BlockingLockArray (RowIndex).WaiterModeRequest);
      DBMS_OUTPUT.
       PUT_LINE (
         'Waiting SQL           : ' || v_BlockingLockArray (RowIndex).SQLText);
      DBMS_OUTPUT.PUT (CHR (10));
   END LOOP;
END;
/
spool off
exit


The second file is Lock.sh which contains

ORACLE_HOME=/u02/app/oracle/product/11.2.0/dbhome_1; export ORACLE_HOME
ORACLE_UNQNAME=imcpr; export ORACLE_UNQNAME
ORACLE_SID=orcl2; export ORACLE_SID
$ORACLE_HOME/bin/sqlplus / as sysdba @/u02/app/oracle/DBScripts/Lock.sql
FILE=/u02/app/oracle/DBScripts/ouput.log

grep -w "Number of Blocked Sessions" $FILE >/dev/null

if [ $? -eq 0 ]
        then

                   cat /u02/app/oracle/DBScripts/AlterSys.sql | mail -v hmhamed@
feco.com

        else
                    echo null
fi

Now let's schedule this executable file in the crontab to be executed every 5 minutes

$ crontab -e

0,5,10,15,20,25,30,35,40,45,50,55 * * * * /u02/app/oracle/DBScripts/Lock.sh -cron -silent start

The output form will be as below:

Number of Blocked Sessions                                                     

--------------------------                                                     

                         2                                                     

+------------------------------------------------------------------------+

| Report   : Blocking Locks                                              |

| Database: ORCL                                                             |

+------------------------------------------------------------------------+

+------------------------------------------------------------------------+

| BLOCKING LOCKS (Summary)                                               |

+------------------------------------------------------------------------+

Number of blocking lock incidents: 1

Incident 1

---------------------------------------------------------------------------------------------------------

                        WAITING                                  BLOCKING

                        ---------------------------------------- ----------------------------------------

Instance Name     : orcl1                                   orcl1

Oracle SID            : 584                                      693

Serial#                   : 25655                                  8223

Oracle User           : APPS                                 APPS

O/S User               : hmhamed                           hmhamed

Machine               : FECO-PC                            FECO-PC

O/S PID                : 725012                                 442788

Terminal               : FECO-PC                            FECO-PC

Lock Time            : 4 minutes                            4 minutes

Status                   : ACTIVE                              INACTIVE

Program               : Toad.exe                              Toad.exe

Waiter Lock Type          : Transaction

Waiter Mode Request   : Exclusive

Waiting SQL                   : update reg_type set required='N'

How to Query Oracle Hidden Parameters

The hidden parameters cannot queried from v$parameter or "show parameter" unless they are changed explicitly by "alter system" command or in init.ora. However having SYSDBA role make you able to query those parameters alongside their values and descriptions as follow:

  SELECT a.ksppinm Param,
         b.ksppstvl SessionVal,
         c.ksppstvl InstanceVal,
         a.ksppdesc Descr
    FROM x$ksppi a, x$ksppcv b, x$ksppsv c
   WHERE     a.indx = b.indx
         AND a.indx = c.indx
         AND a.ksppinm LIKE '/_%' ESCAPE '/'
ORDER BY 1

Caution:

Hidden and undocumented parameters are very critical and only be useful to Sr. DBA who clearly understand how this parameter would change the internal behaviour for Oracle. So don't change any of them unless suggested by Oracle Support for specific case.