Skip to Main Content
  • Questions
  • Wait in DBA_HIST_ACTIVE_SESS_HISTORY

Breadcrumb

May 4th

Question and Answer

Connor McDonald

Thanks for the question, Sujit.

Asked: March 16, 2017 - 7:30 am UTC

Last updated: March 18, 2017 - 5:26 am UTC

Version: 12c

Viewed 1000+ times

You Asked

Hi,

I am trying to get to the wait event from a entry in DBA_HIST_ACTIVE_SESS_HISTORY where event for wait is "enq: TX - index contention". In the row I see P1,P2 and P3. Object id is "-1" . It is a INSERT query. How to get the details of the index wait and the index name from DBA_HIST_ACTIVE_SESS_HISTORY ?

and Connor said...

If the p1,p2,p3 are not available, then I'd proceed as:

1) the SQL_ID gives you the SQL
2) the SQL gives you the table concerned
3) Find the indexes on the table from DBA_INDEXES
4) Examine v$segment_statistics for contention (ITL waits, row lock waits) for those indexes.



Is this answer out of date? If it is, please let us know via a Comment