oracle 等待,【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 =

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.