Thursday, September 12, 2013

"enq: HW - contention", small tool to find involved segment

Analyzing ASH, every time I find "enq: HW - contention" wait, the most important answer is-which segment is in case. Look in example:

Top User Events

    Event Event Class % Event Avg Active Sessions
    CPU + Wait for CPU CPU 62.89 20.15
    enq: HW - contention Configuration 23.74 7.61
    db file sequential read User I/O 3.18 1.02
    log file sync Commit 1.84 0.59
    log buffer space Configuration 1.32 0.42

    which is follow by (in same ASH report):

    Top Event P1/P2/P3 Values

      Event % Event P1 Value, P2 Value, P3 Value % Activity Parameter 1 Parameter 2 Parameter 3
      enq: HW - contention 23.74 "1213661190","21","201377283" 23.70 name|mode table space # block
      db file sequential read 3.24 "48","101101","1" 0.01 file# block# blocks
      log file sync 1.84 "520","2876838558","0" 0.01 buffer# sync scn NOT DEFINED
      log file parallel write 1.73 "1","468","2" 0.01 files blocks requests
      direct path read 1.28 "26","1334118","1" 0.01 file number first dba block cnt
      For all cases with big "WF - contention" wait I use this small but useful procedure. Here is the code:
      CREATE OR REPLACE PROCEDURE hw_wait (p_p3 IN PLS_INTEGER
                                          )
      AUTHID CURRENT_USER
      IS
      /* ---------------------------------------------------------------------------
      
       Filename: hw_wait.prc
       CR/TR#  :
       Purpose : Find object that cause enq: HW - contention
      
       Date    : 12.09.2013.
       Author  : Damir Vadas, damir@vadas.hr
      
       Remarks : Tested on 11g only (should work on lower versions)
                 grant select on sys.dba_extents to ;
                 grant select on sys.dba_lobs to ;
                 grant select on sys.dba_tables to ;
      
       Changes (DD.MM.YYYY, Name, CR/TR#):
      --------------------------------------------------------------------------- */
        l_block# PLS_INTEGER;
        l_file#  PLS_INTEGER;
        l_owner  VARCHAR2(128 CHAR); 
        l_segment_type VARCHAR2(128 CHAR);
        l_segment_name VARCHAR2(128 CHAR);
        l_table_name   VARCHAR2(128 CHAR);
        l_column_name  VARCHAR2(128 CHAR); 
      BEGIN
        select DBMS_UTILITY.DATA_BLOCK_ADDRESS_FILE(p_p3) FILE#,
               DBMS_UTILITY.DATA_BLOCK_ADDRESS_BLOCK(p_p3) BLOCK#
          into l_file#,
               l_block#
          from dual
        ;
        select owner,    segment_type,  segment_name
          into l_owner,l_segment_type,l_segment_name
          from sys.dba_extents
         where file_id = l_file#
           and l_block# between block_id and block_id + blocks - 1
        ;
        CASE 
          WHEN l_segment_type= 'LOBSEGMENT' THEN  
            SELECT table_name  , column_name
              INTO l_table_name, l_column_name
              FROM sys.dba_lobs
             WHERE segment_name = (SELECT object_name
                                     FROM dba_objects
                                    WHERE owner = l_owner AND object_name = l_segment_name
                                 )
            ;
            dbms_output.put_line(l_owner || '.' || l_table_name || '-' || l_column_name);
          WHEN l_segment_type IN ('TABLE','INDEX') THEN
            dbms_output.put_line(l_owner || '.' || l_segment_name); 
          ELSE
            dbms_output.put_line('analyze for : '||l_owner||'.'||l_segment_type||'-'||l_segment_type||' not implemented.');
        END CASE;    
      EXCEPTION
        WHEN OTHERS THEN
          dbms_output.put_line(SQLERRM);
      END HW_wait;
      /
      
      Result is:
      SQL> set serveroutput on size 1000000;
      SQL> exec HW_WAIT(201377283);  -- parameter is P3 value from ASH second table!
      MATS.DF00_MESSAGE_QUEUE_TBL-"USER_DATA"."TEXT_LOB"
      
      PL/SQL procedure successfully completed.
      
      SQL>
      

      The End

      Please be aware that only some segment types are covered so if you involve with some new you might have to add your code for that.

      No comments :

      Post a Comment

      Zagreb u srcu!

      Copyright © 2009-2014 Damir Vadas

      All rights reserved.


      Sign by Danasoft - Get Your Sign