This discussion is archived
3 Replies Latest reply: Dec 6, 2012 12:02 PM by moreajays RSS

Insert statements hanaging

oradba11 Newbie
Currently Being Moderated
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 Oracle ACE
    Currently Being Moderated
    did you generate explain Plan to check what happened !! if you yes post it here
  • 2. Re: Insert statements hanaging
    Dom Brooks Guru
    Currently Being Moderated
    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 Pro
    Currently Being Moderated
    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

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points