【Oracle等待事件】Transaction等待

【Oracle等待事件】Transaction等待

 

注意如果 不当设置了例如10513 level 2的等待事件,那么可能造成该Transaction等待,原因是一些实例CRASH所产生的死事务,将不会被SMON主动回滚,这可能造成部分行数据始终被锁定 而不释放。

 

Definition:     Wait for a blocking transaction to be rolled back. Continue
waiting until the transaction has been rolled back.

Wait Time:      Wait for up to 1 second for a blocking transaction to be
rolled back. Continue waiting until transaction has been
rolled out.

Parameters:
P1 – Undo Segment No | Slot No within Undo header.
P2 – Wrap Sequence [ max value is UB4MAXVAL ].
P3 – Count

undo seg#
SELECT * FROM v$rollstat WHERE usn = <undo seg#>

slot#   This is the slot# within the transaction table that is being
used to store a transaction.

wrap#   Wrap or sequence number of the slot. For each new transaction
this number increases.

count   Number of times that Oracle7 has waited on this transaction.

Commentary:     Used within kctwit() [ General TX wait routine . ]

Once we have acquired the TX enqueue of the transaction we
are waiting for , we must continue waiting until the blocking
transaction has been committed/rolled back. If the transaction
has been aborted, then we are waiting for PMON or SMON
to complete the rollback (We may be waiting for SMON if a
rollback segment needs recovery).

This wait is interleaved with “undo segment recovery”, because
on each ‘loop’ we call ktucun().
Whether we see this event depends on the availability of the
failed transactions enqueue. This wait may be more prevalent in
a 2pc environment.

Dependency:     We are dependent on the transaction being rolled out, but we
could be waiting for either the local PMON, or a PMON from
another instance [parallel server].

The Undo Segment No will tell us which instance we are waiting
for , but at present this will remain an eyeball operation
rather than automating it within ph2.

Validity:    N/A

Advise:         The waiting basically depends on the size of that transaction
that is being rolled back. You may want to check if PMON / SMON
is doing the rollback for a killed session and if so check
Parameter:CLEANUP_ROLLBACK_ENTRIES.

From 7.3 onwards View:X$KTUXE can be used to see the
state of a transaction – it shows the transaction table
entries in the rollback segment header.

Related:        Lock:TX
Note:34540.1 Enqueue Metrics.
Bug:960962   Oracle8 OPS issue which can cause OPS
deferred transaction recovery to take a long
time, hence causing ‘transaction’ waits.

 

关注dbDao.com的新浪微博

扫码关注dbDao.com 微信公众号:

Comments

  1. uiui says:

    的确如此。我今天就碰到了。

Speak Your Mind

TEL/電話+86 13764045638
Email service@parnassusdata.com
QQ 47079569