Enqueue Wait Events - TM

From: <oraklee_at_gmail.com>
Date: 25 Jan 2006 18:26:24 -0800
Message-ID: <1138242384.441004.81110@f14g2000cwb.googlegroups.com>

Good day,

We have 3-node Oracle 9.2.0.5 RAC database on AIX 5.2.

We're seeing enqueue wait events in our database. Here is a Statspack output (elapsed time of about 5 minutes):

Top 5 Timed Events

% Total

Event                                Waits    Time (s) Ela Time
----------------------------- ------------ ----------- --------
enqueue                              9,523       4,097    42.48
ksxr poll remote instances          23,000       3,512    36.41
db file sequential read            270,913       1,315    13.63
CPU time                                           527     5.47
global cache cr request            144,482          65      .67
          -------------------------------------------------------------

Enqueue activity for DB:
-> Enqueue stats gathered prior to 9i should not be compared with 9i
data
-> ordered by Wait Time desc, Waits desc

                                                  Avg Wt         Wait
Eq  Requests  Succ Gets Failed Gets       Waits   Time (ms)   Time (s)
-- --------- ---------- ----------- ----------- ----------- ----------
TM    77,597     77,579           0       1,146    3,681.83      4,219
TX    16,795     16,793           0          31        8.32          0
CU    10,130     10,129           0          10       18.00          0
**************************************************************************

Furthermore, we have captured the running statement and its waiting state by querying V$SESSION and V$SESSION_WAIT (and v$sql + dba_objects):
Lo Mode OBJECT_NAME (WAIT_SECS) SQL_TEXT

-- ----- -------------- ----------------------------------------
TM 5     BWPHO_PK       (3) DELETE BORROWER WHERE BORROWERKEY = :B1
TM 5     BWPHO_BORRW_FK (2) DELETE BORROWER WHERE BORROWERKEY = :B1

Lo == Lock Type
Mode == Requested Lock Mode (5 == SSX or Sub Shared Exclusive) OJBECT_NAME == ROW_WAIT_OBJ# (obj associated with locked resource) WAIT_SECS == seconds in wait state
SQL_TEXT == sql statement

The above output implies that the DELETE BORROWER statement is waiting on child records. We have confirmed that the referenced foreign key / primary key indexes do exist and valid.

Can anyone comment or answer on the following questions:

1) What is the difference between TM and TX lock type?
2) When would we see TM / Sub Shared Exclusive (lock mode 5)?
3) According to the Statspack output, this wait event seems significant

(42% of all wait time). Is this a real wait event (one that would impact response time significantly)?

We're trying to debug this condition, and we just cannot seem to replicate it in a controlled environment.

Thanks in advance for any feedback on this.

Regards,
Kevin Lee
Optizon, Inc. Received on Wed Jan 25 2006 - 20:26:24 CST

Enqueue Wait Events - TM - SSX