Practice: Using the MAX_IDLE_BLOCKER_TIME Parameter

Overview

This practice shows how to terminate a blocking session by using the new MAX_IDLE_BLOCKER_TIME initialization parameter.

Before starting any new practice, refer to the Practices Environment recommendations.

Step 1 : Set up the environment with two sessions

  • Prepare two terminal sessions, one logged in to PDB21 as HR and another one logged in to PDB21 as SYSTEM.

    
    $ sqlplus system@PDB21
    Copyright (c) 1982, 2019, Oracle.  All rights reserved.
    
    Enter password:
    
    Connected to:
    
    SQL> SET SQLPROMPT "SQL system> "
    SQL system>
  • Log in to PDB21 as HR.

    
    $ sqlplus hr@PDB21
    Copyright (c) 1982, 2019, Oracle.  All rights reserved.
    
    Enter password:
    
    Connected to:
    
    SQL> SET SQLPROMPT "SQL hr> "
    SQL hr>
    SQL> 

Step 2 : Set MAX_IDLE_BLOCKER_TIME to two minutes

  • In the SYSTEM session, set the MAX_IDLE_BLOCKER_TIME initialization parameter to two minutes.

    
    SQL system> ALTER SYSTEM SET max_idle_blocker_time=2;
    
    System altered.
    
    SQL system> SHOW PARAMETER max_idle_blocker_time
    
    NAME                            TYPE        VALUE
    ------------------------------- ----------- ------------------------------
    max_idle_blocker_time           integer     2
    SQL system>

Step 3 : Test

  • In the HR session, update the employees' salary.

    
    SQL hr> UPDATE hr.employees SET salary=salary*2;
    
    107 rows updated.
    
    SQL hr>
  • In the SYSTEM session, set all employees' commission percentage to 0. The statement waits for the lock resources held on the row by HR be released.

    
    SQL system> UPDATE hr.employees SET commission_pct=0;

    After two minutes, observe that the statement is executed.

    
    107 rows updated.
    
    SQL system> COMMIT;
    
    Commit completed.
    
    SQL system>
  • Back in the HR session, query the result of the salaries update.

    
    SQL hr> SELECT salary FROM hr.employees;
    SELECT salary FROM hr.employees       *
    ERROR at line 1:
    ORA-03113: end-of-file on communication channel
    Process ID: 32314
    Session ID: 274 Serial number: 8179
    
    SQL hr> EXIT
    $

    The session was automatically terminated because it held a resource for longer than two minutes.

Step 4 : Examine the DIAG trace files

  • View the DIAG trace file.

    
    $ cd /u01/app/oracle/diag/rdbms/cdb21*/CDB21/trace
    $ grep -i 'Session with ID' *
    CDB21_dia0_17197_base_1.trc:HM: Session with ID 197 serial # 34111 (FG) on single instance 1 is hung
    CDB21_dia0_17197_base_1.trc:HM: Session with ID 426 serial # 29909 (FG) on single instance 1 is hung
    CDB21_dia0_17197_base_1.trc:HM: Session with ID 197 with serial number 34111 is no longer hung
    CDB21_dia0_17197_base_1.trc:HM: Session with ID 426 with serial number 29909 is no longer hung
    $ cat CDB21_dia0_17197_base_1.trc
    ...
    HM: Session with ID 274 serial # 8179 (U01I) on single instance 1 is hung
        and is waiting on 'SQL*Net message from client' for 96 seconds.
        Session was previously waiting on 'SQL*Net more data to client'.
        Session ID 274 is blocking 1 session
    ...
    HM: Session with ID 136 serial # 42403 (U011) on single instance 1 is hung
        and is waiting on 'enq: TX - row lock contention' for 96 seconds.
        Session was previously waiting on 'db file sequential read'.
        Final Blocker is Session ID 274 serial# 8179 on instance 1
         which is waiting on 'SQL*Net message from client' for 108 seconds
         p1: 'driver id'=0x54435000, p2: '#bytes'=0x1, p3: ''=0x0
    ...
    *** 2020-03-16T04:31:35.031598+00:00 (CDB$ROOT(1))
    All Current Hang Statistics
    
                          current number of hangs 1
        hangs:current number of impacted sessions 2
                      current number of deadlocks 0
    deadlocks:current number of impacted sessions 0
                     current number of singletons 0
          current number of local active sessions 2
            current number of local hung sessions 1
    
    Suspected Hangs in the System and possibly Rebuilt Hangs
                         Root       Chain Total               Hang
      Hang Hang          Inst Root  #hung #hung  Hang   Hang  Resolution
        ID Type Status   Num  Sess   Sess  Sess  Conf   Span  Action
     ----- ---- -------- ---- ----- ----- ----- ------ ------ -------------------
         1 HANG    VALID    1   274     2     2    LOW  LOCAL Terminate Process
    
      Inst  Sess   Ser             Proc  Wait    Wait
       Num    ID    Num      OSPID  Name Time(s) Event
      ----- ------ ----- --------- ----- ------- -----
            PDBID PDBNm
            ----- ---------------
          1    136 42403     32583  U011      97 enq: TX - row lock contention
                7 PDB20
          1    274  8179     32314  U01I     110 SQL*Net message from client
                7 PDB20
    ;..
    HM: current SQL: UPDATE hr.employees SET commission_pct=0
    
                                                         IO
     Total  Self-         Total  Total  Outlr  Outlr  Outlr
      Hung  Rslvd  Rslvd   Wait WaitTm   Wait WaitTm   Wait
      Sess  Hangs  Hangs  Count   Secs  Count   Secs  Count Wait Event
    ------ ------ ------ ------ ------ ------ ------ ------ -----------
         1      0      0      0      0      0      0      0 enq: TX - row lock contention
    ...
    HM: current SQL: UPDATE employees SET salary=salary*2
    ...
    HM: Session ID 274 serial# 8179 ospid 32314 on instance 1 in Hang ID 1
        was considered hung but is now no longer hung
    
    HM: Session with ID 274 with serial number 8179 is no longer hung
    
    $
    $

    You can also view the PMON trace file.

    
    $ grep -i 'Kill idle blocker' *
    CDB21_pmon_17088.trc:Kill idle blocker, hang detected
    $ cat cat CDB21_pmon_17088.trc
    
    *** 2020-03-16T04:32:04.240685+00:00 ((7))
    Idle session sniped info:
    reason=max_idle_blocker_time parameter sess=0x86a06a20 sid=274 serial=8179 idle=2 limit=2 event=SQL*Net message from client
    client details:
      O/S info: user: oracle, term: pts/0, ospid: 32312
      machine: edcdr8p1 program: sqlplus@edcdr8p1 (TNS V1-V3)
      application name: SQL*Plus, hash value=3669949024
    ...
    $
  • In the SYSTEM session, query the employees' commission percentage.

    
    SQL system> SELECT DISTINCT commission_pct FROM hr.employees;
    
    COMMISSION_PCT
    --------------
                 0
    
    SQL system> EXIT
    $