3 Replies Latest reply: Dec 6, 2012 2:02 PM by moreajays RSS

    Insert statements hanaging

    oradba11
      Hi,
      I am working on Oracle 11.1.0.7 on AIX with 2 node rac.
      We are facing some issues that all insert statements are hanging on the database and if we checked there are multipal tables are in locked mode.
      For all tables mode held is Row-X (SX) and mode requested is none.
      If we check blocking others it is showing global...This all happen after deleteing few rows in the tables.
      In awr reports top wait event it is showing as db sequential read..
      Any idea wht is happing on the database and how can i check further to resolve the issue..
        • 1. Re: Insert statements hanaging
          Osama_Mustafa
          did you generate explain Plan to check what happened !! if you yes post it here
          • 2. Re: Insert statements hanaging
            Dom Brooks
            If you want to check database performance look at AWR.

            If you want to look at what individual sessions are doing, what they are waiting on, who they are waiting on, etc, look at v$session for current information and, for recent past actiivity, v$active_session_history/dba_hist_active_sess_history / ash report.

            "Hanging" is a term broadly used. In general your sessions are either waiting or working. Find out which of these is the most significant, as per above, and address.

            In summary, further analysis required.
            • 3. Re: Insert statements hanaging
              moreajays
              Hi ,

              There could be due to missing indexes on FK constraint columns , if found any table in below query relevant to your problematic inserts then plan creating index on the same


              SET pages 56 lines 132 feedback off
              TTITLE ' Foreign Constraints and Columns Without an Index on Child Table'
              SELECT acc.owner
              || '-> '
              || acc.constraint_name
              || '('
              || acc.column_name
              || '['
              || acc.position
              || '])' "Owner, Name, Column, Position",acc.table_name
              FROM all_cons_columns acc, all_constraints ac
              WHERE ac.constraint_name = acc.constraint_name
              AND ac.constraint_type = 'R'
              AND (acc.owner, acc.table_name, acc.column_name, acc.position) IN
              (SELECT acc.owner, acc.table_name, acc.column_name, acc.position
              FROM all_cons_columns acc, all_constraints ac
              WHERE ac.constraint_name = acc.constraint_name
              AND ac.constraint_type = 'R'
              MINUS
              SELECT table_owner, table_name, column_name, column_position
              FROM all_ind_columns)
              ORDER BY ACC.owner, ACC.constraint_name, ACC.column_name, ACC.position;


              Thanks,
              Ajay More
              http://moreajays.blogspot.com