oracle enqueues 很全面的解釋

. What are Oracle enqueues?
Oracle enqueues are locks at database level that coordinate parallel access to Oracle resources such as objects or data records. For example, enqueues are responsible for several transactions not being able to change the same data record at the same time.Enqueue requests are divided into queues whose requests are then processed in the chronological sequence of their arrival. This is an important difference to Oracle latches (Note 767414), for which there are no queues.Oracle enqueues are often also referred to as exclusive lockwaits.

2. How do Oracle enqueues differ from R/3 enqueues?
R/3 enqueues also coordinate access to the application data in the database. However, they are independent of Oracle enqueues. You can have R/3 enqueues without Oracle enqueues and Oracle enqueues without R/3 enqueues. You can use transaction SM12 to monitor the R/3 enqueues and transaction DB01 to monitor Oracle enqueues.

3. What happens if a requested enqueue has already been allocated?
There are no more timeouts in connection with enqueues. Either an error occurs immediately, or the session waits as long as it has to:

Enqueue requests with NOWAIT
If an enqueue with NOWAIT is requested (for example, as part of a DDL statement without an ONLINE option) and the enqueue is already being held by another session, the following error message is issued:

ORA-00054: resource busy and acquire with NOWAIT specified

Enqueue requests without NOWAIT
If an enqueue without NOWAIT is requested (for example, by SAP transactions), the session waits as long as required if another session is holding the requested enqueue.

4. How can I determine whether I have problems with Oracle enqueues?
Carry out a wait event analysis in accordance with Note 619188 to determine whether the general database performance is impaired by enqueues or whether long-running transactions must wait for enqueues.While there was only one generic wait event "enqueue" up until Oracle 9i, Oracle 10g or higher differentiates between the different enqueue types and causes for the wait event. For example, "enq: TX - allocate ITL entry" denotes an enqueue in the Interested Transaction List in the block header.

5. What are the different types of Oracle enqueues?
Oracle enqueues are always specified in the form. of a 2-digit ID. User enqueues and system enqueues are differentiated as follows:

User enqueues:

TX (transaction enqueue): This enqueue type occurs if you want to change a data record but you cannot do this because a transaction is running in parallel (for example, because the transaction changed the same data record because a unique or primary constraint cannot be guaranteed or because a free ITL slot is no longer available in the block header). The TX enqueue occurs most frequently in the SAP environment. A session only ever holds one TX enqueue - even if several data records of one or several tables are changed.
TM (DML enqueue): This enqueue type occurs if a complete object has to be protected against changes (for example, as part of an index rebuild or a consistency check using VALIDATE STRUCTURE). Whenever a TX enqueue blocks table entries, a TM enqueue is also set so that parallel activities such as index rebuilds or consistency checks are not possible. ONE TM enqueue is set for each transaction and changed object.
UL (user-defined enqueue): A transaction has set an enqueue using DBMS_LOCK.REQUEST (this is not used in the standard SAP system).
System enqueues:

ST (space transaction enqueue): This enqueue is held in dictionary-managed tablespaces within extent allocations and releases.
CI (Cross instance call invocation enqueue)
TT (Temporary table enqueue)
US (Undo segment enqueue)
CF (Control file enqueue)
TC (Thread checkpoint enqueue)
RO (Reuse object enqueue)
...
However, there are numerous other system enqueues which are generally negligible because system enqueues are only held for a very short time.

As of Oracle 10g, the table V$LOCK_TYPE contains an overview of all enqueues that exist.

6. Which Oracle parameters play a role in the enqueue environment?
DML_LOCKS
The DML_LOCKS parameter specifies the maximum number of requests of TM enqueues that may be active simultaneously. If the limit is reached, ORA-00055 occurs (see Note 398927).

ENQUEUE_RESOURCES
The ENQUEUE_RESOURCES parameter specifies the maximum total number of enqueue requests that may be active simultaneously. If the limit is reached, ORA-00052 occurs.
To determine which limits are defined, what the previous highest level was and how many requests are currently active, you can execute the following SELECT on V$RESOURCE_LIMIT:

SELECT * FROM V$RESOURCE_LIMIT
WHERE RESOURCE_NAME IN ('dml_locks', 'enqueue_resources');

7. In which modes can I hold or request an enqueue?

0: Enqueue is not held or requested
1: null (NULL)
2: row-S (SS)
3: row-X (SX)
4: share (S)
5: S/Row-X (SSX)
6: exclusive (X)
8. How do I find out which sessions are currently holding enqueues or waiting for enqueues?
The V$SESSION_WAIT view or the Oracle session overview (transaction ST04 "Detail Analysis Menu -> Oracle Session") displays the wait event "enqueue" (Oracle 9i or lower) or "enq: - " (Oracle 10g or higher) if a session is waiting for an enqueue (see Note 619188).

The V$LOCK Oracle view contains all information about sessions that hold or wait for an enqueue:

SID: SID of the Oracle session that holds the enqueue or waits for it

Associated Type: Enqueue type

ID1, ID2: Enqueue-dependent lock IDs

TM enqueue -> ID1 = Object ID of the locked object

LMODE: Mode of the held enqueues

REQUEST: Mode of the requested enqueues

CTIME: Time in current mode (in seconds), that is, hold or queue time

BLOCK: 0 -> no session waits for the enqueue; 1 -> at least one session waits for the enqueue

For sessions that hold an enqueue, REQUEST is 0 and LMODE is higher than 0. For sessions that wait for an enqueue, REQUEST is greater than 0 and LMODE is 0.  In the Oracle session overview, you can recognize the sessions that wait for an enqueue (REQUEST > 0) by the fact that they wait for an "enqueue" wait event.

 You can identify sessions that want to access the same enqueue and are thus locked by identical entries for TYPE, ID1 and ID2.

 Transaction DB01 displays the current enqueue wait situations including the holding and waiting shadow and client process.

9. Are permanently visible V$LOCK entries with MR, RT, TS and XR enqueue types critical?
These enqueues are held permanently and are not critical. The REQUEST mode is always 0, which means that a session does not have to wait for one of these enqueues.

10. How I can determine the enqueue type for which a session is currently waiting?
As of Oracle 10g, the enqueue type is taken directly from the name of the wait event.Up to Oracle 9i, you can identify the enqueue type using DB01 or V$LOCK.If only the current information from V$SESSION_WAIT is available (for example, in the Oracle session overview in ST04), you can determine the enqueue type using the first P1 wait event parameter:

SELECT CHR(BITAND(, -16777216) / 16777215) ||
       CHR(BITAND(, 16711680) / 65535)
FROM DUAL;

11. How I can determine how long a session has been waiting for certain enqueues since the database was started?
Up to Oracle 8I, there are no enqueue statistics accumulated since the database was started.As of Oracle 9I, this type of evaluation is possible using V$ENQUEUE_STAT:

SELECT EQ_TYPE, CUM_WAIT_TIME
FROM V$ENQUEUE_STAT
WHERE CUM_WAIT_TIME > 0
ORDER BY CUM_WAIT_TIME DESC;

As of Oracle 10g, the information can also be obtained from V$SYSTEM_EVENT due to the differentiated wait event. With the following SELECT, you get the ten enqueue types that are responsible for the highest total queue time:

SELECT * FROM
(SELECT SUBSTR(EVENT, 1, 50) EVENT, TIME_WAITED
FROM V$SYSTEM_EVENT
WHERE EVENT LIKE 'enq: %'
ORDER BY TIME_WAITED DESC)
WHERE ROWNUM <=10;

12. What are typical enqueue wait situations and what are possible solutions?

TYPE is TX, REQUEST is 6

Two transactions simultaneously access the same data record in change mode.
If the lock is held for a longer time by the same transaction, you can determine the of the relevant R/3 work process ("Client PID" column) in transaction ST04 by selecting Detail Analysis Menu -> Oracle session". In transaction SM66, the relevant R/3 work process can be determined using ("PID" column). You can now check which action is currently processed by the R/3 work process. If this is a long-running database access, you must analyze and optimize it. If the work process is mainly held in the ABAP, you must review the program logic. If you cannot find a work process with , this is caused by the problem described in Note 20071 and you must terminate the Oracle session.

If enqueue holders are constantly changing, you must check whether the number of accesses can be reduced. It the NRIV table is frequently affected, you can reconsider buffering the number ranges (see Note 678501). If it proves difficult to determine the initiating work process promptly in each case, due to the number of constantly changing enqueue holders, you can use the following statement to determine the relevant processes that block other sessions but do not wait for an enqueue themselves:

SELECT S.SID ORACLE_SID, S.PROCESS CLIENT_PID
FROM V$SESSION S, V$LOCK L1 WHERE
   L1.TYPE = 'TX' AND L1.LMODE = 6 AND
   S.SID = L1.SID AND L1.BLOCK = 1
AND NOT EXISTS
(SELECT * FROM V$LOCK L2 WHERE
   L2.TYPE = 'TX' AND L2.REQUEST > 0 AND
   L1.SID = L2.SID);
 

Enqueues on D020L and D021L and other SAP dictionary tables such as D345T can be triggered on the individual instances by using different kernel patches, because the information saved in the tables depend on the kernel patch, which means that constant adjustments must be carried out. Make sure that the exact identical kernel patch is used on all instances.

                    If enqueues occur on D020L and D020LINF in the batch processing, refer to Notes 596748, 36795, 330165 and 150353.

                    If enqueues occur on DDFTX, refer to Note 395997.

                    Locks on REPOSCR and other tables such as DDFTX may also be associated with the behavior. described in Note 597885.

Enqueues in connection with dynpro loads and CUA loads (for example, on table D342L, DYNPLOAD) are prevented by the functional kernel change described in Note 949737. For more information, see also Note 1028360.

Enqueues on BTCCTL (or TBTCO, TST01, TST03, ...) due to SAP standard transactions may be caused by long-running file system accesses that occur after the data base was changed. For more information, see Note 183648.

Enqueues on the VARINUM table are often triggered by expensive SQL statements on the  TPRI_PAR table. Refer to Note 706478 (15) and make sure that unnecessary entries are periodically deleted from TPRI_PAR and that TPRI_PAR is reorganised if required.

If enqueues still exist after a transaction has been completed, and if secondary database connections are used, the SAP bug described in Note 843042 that results in no COMMIT being sent may be responsible.

If locks such as this occur when SMON processes are accessed in the OBJ$ table in the RAC-environment, see Note 923099 (bugs with Oracle 9.2.0.7 or lower).

If the lock holder is mainly active in SAP report SAPLSENA, SAP enqueue problems have occurred. If this is the casee, check in transcation SM12 if there are old enqueues that have to be deleted. Also check, whether the enqueue logic of the application can be improved.

If MMON-related processes (such as M000 or M001) on Oracle 10.2.0.2 wait for the TX enqueue and if (at worst) a deadlock occurs between these processes that is not automatically resolved by Oracle, this may be due to Oracle bug 6057351. This is fixed with Oracle 10.2.0.4 or higher. As a workaround, you can terminate the M000 or M001 process on 10.2.0.2. After the automatic restart, the problem should no longer exist

As of Oracle 10g, this type of TX enqueue wait is covered by the wait event "enq: TX - row lock contention".

TYPE is TX, REQUEST is 4

Two transactions change data records simultaneously, which are identical in terms of a unique or primary key constraint.
                    For an analysis, proceed as described above under "TYPE = TX, REQUEST = 6".

ORA-00001 errors are an additional index for this error situation ("unique key constraint violation") in relation to the hanging Data Manipulation Language (DML) operations. Therefore, create an SQL trace when required, and check whether the long-running DML operations end with ORA-00001.

To correct this problem, ensure that the same primary key is not inserted or changed repeatedly in parallel in a table. (for example, by checking whether the key already exists). If this is not possible, increase the commit-frequency in order to release the enqueues again earlier.

                    As of Oracle 10g, the corresponding wait event is called "enq: TX - row lock contention".

Several DML operations want to access the same index block simultaneously.
If such a 'multiple requested' index block is not in the buffer pool, it must be read by the first transaction from the disk ("db file sequential read"). The other DML operations wait for a TX enqueue during this process.

                    As of Oracle 10g, the corresponding wait event is called "enq: TX - index contention".

Several transactions want to change rows from the same block, but cannot find a free entry in the Interested Transaction list (ITL) in the block header.
If the enqueues occur in a table in connection with massively parallel inserts or updates, this is usually a problem to do with accessing the Interested Transaction List (ITL) in the lock header. The solution for this requires increasing the INITRANS sufficiently, as described in Note 84348.  For BW, also refer to Note 750033 (InfoCubes) and Note 831234 (ODS tables). As of the enhancements from Notes 909484 (Basis 6.20), 935817 (Basis 6.40) or 936253 (Basis 7.00), INITRANS can also be configured by SAP.

                    ITL waits for parallel INSERT operations can be alleviated by increasing the FREELISTs or FREELIST GROUPs, or by switching to ASSM.

                    The relevant 10g Wait Event is called "enq: TX - allocate ITL entry".

Several DML operations want to change data records in the same block of a bitmap index.
Changes in bitmap indexes always lead to the whole area being locked. If several transactions want to change the same area, row lock waits occur in level 4.

                    Drop the bitmap indexes before you begin with parallel loading. See also the relevant section in Note 84348 for further information.

                    These wait situations are logged under 10g as "enq: TX - row lock contention".

When you use Oracle 10g, in individual cases, the "enq: TX - contention" event is also logged.   For example, this is then possible if parallel INSERTs are running and one of these INSERTs triggered an AUTOEXTEND operation and must wait for "Data file init write" (Note 619188). In this case, the other INSERTs wait for "enq: TX - contention".
TYPE = ST

Very large numbers of extents are allocated or deallocated.
You can solve the problem permanently by using LMTS instead of DMTS (refer to  Notes 214995 and 659946). If you use DMTS, you must ensure that the extents of temporary segments, tables and indexes are sufficiently big so that you can avoid large quantities of extents being allocated and deallocated.

 If you use DMTS, check whether PSAPTEMP was created with a sufficient extent size (as described in Note 164925 (OLTP) or 359835 (BW)).

If you use DMTS, check whether there are objects with a large number of extents (more than 500) and a very small extent size and use sufficient extent sizes. This applies in particular to dynamic segments that are deleted from time to time with TRUNCATE/DROP and created again (for example, in the BW environment). Note 666061 describes according to which rules the extent sizes are determined. Note 787533 contains more detailed information about analyzing and correcting errors regarding ST enqueues. Note 78595 contains events that you can use to temporarily deactivate the coalescing of the SMON.

If a TEMPORARY segment that has allocated several extents exists, outside of PSAPTEMP, this may concern a table that has already been dropped. If a DROP terminates, the affected segment is converted into a TEMPORARY segment whose extents are cleaned up in the course of SMON. If all extents are cleaned up, the temporary segment is also deleted. The same applies to index creation: While you create an index, it only exists as a temporary segment. Only after the creation process is complete, it is converted into a permanent segment.

In the BW environment, ST enqueues can also be triggered by the large-scale parallel creation of segments (for example, partitions). Each segment requires at least one extent for each parallel session. As a result, several thousand events can be required within less than an hour, which are serialized on the ST enqueue. To determine whether such a case exists, proceed as follows:

SELECT OBJECT_NAME, CREATED
FROM DBA_OBJECTS
WHERE CREATED > SYSDATE - 1 / 24;

                    This statement returns all segments that were created within the last hour. In such a case, the best solution is to change to LMTS.

The ST enqueue problems that occur when you reorganise or allocate a large number of extents are mainly caused by poor indexdesign in the Oracle dictionary tables FET$ and UET$. However, despite the severe problems, Oracle does not find it necessary to improve the design.

                    In 10g, the corresponding Wait Event is called "enq: ST - contention".

TYPE = TM, REQUEST = 3

Object lock during operations such as REBUILD INDEX or VALIDATE STRUCTURE or due to command "LOCK TABLE ...  IN EXCLUSIVE MODE [NOWAIT]"
                    Determine the session that holds the lock and determine its LMODE.

If LMODE = 4, the locking session currently executes an operation such as an index rebuild or a consistency check using VALIDATE STRUCTURE without an ONLINE flag. Terminate this operation, so  other transactions can access the object again.

If LMODE = 3, the session that holds the lock is not the main problem, since several sessions can use a TM lock with mode 3 at the same time and in the same table (for instance, if two transactions change different records in the same table at the same time). Instead, you must analyze whether there is another waiter that requires a TM enqueue with REQUEST = 4 (for example, REBUILD INDEX ONLINE, see below) and which therefore blocks all requests in the queue lined up behind it.

                    In 10g, the wait event is called "enq: TM - contention".

TYPE = TM, REQUEST = 4

REBUILD INDEX ONLINE or SHRINK waits due to an active TX enqueue on the corresponding table.
As described in Note 682926, a REBUILD INDEX ONLINE is only online-enabled to a limited extent. You must therefore make sure that an online rebuild is only started if no long-running transactions are working in the relevant table. See Note 910389 for information about Segment Shrinking.

                    In 10g, the wait event is called "enq: TM - contention".

TYPE = US

US-enqueues occur in relation to the automatic online or offline setting of undo segments by the SMON-process or by other undo segment activities..
To reduce US enqueues, you can set the following event. Among other things, this event suppresses the automatic offline setting of undo segments that are no longer required.

event="10511 trace name context forever, level 2"

If US enqueues are responsible for performance problems in the Real Application Cluster, import the latest Oracle patch set. If this does not solve the problem, create an SAP customer message to initiate further analysis.

                    In 10g, the wait event is called "enq: US - contention".

TYPE = CI

The CI enqueue is allocated when a session executes a cross instance call, in other words, when a background process is triggered in the Oracle instance to carry out a certain task. When a session has allocated the CI enqueue successfully, it waits for the Wait Event "rdbms ipc reply" until the background process issues a response. Other sessions that also require the CI enqueue must wait for it.this means that the optimization of CI enqueues corresponds to the optimization of "rdbms ipc reply" waits. Therefore, refer to the "rdbms ipc reply" section in Note 619188.
                    In 10g, the wait event is called "enq: US - contention".

TYPE = RO

The reuse object enqueue is used in exactly the same way as the CI enqueue in TRUNCATEs and DROPs. For this reason, refer to the details described under "TYPE = CI".
                    In 10g, the wait event is called "enq: RO - fast object reuse".

TYPE = TT

The TT enqueue is used to avoid deadlocks in parallel tablespace operations (deleting and creating tablespaces, creating data files, tablespace point-in-time recovery, creating undo segments in the tablespace, ALTER TABLESPACE, extent allocation and deallocation in the tablespace, ...).
If an ALTER DATABASE TEMPFILE DROP hangs due to a TT enqueue that is retained by the SMON process in Oracle 9.2.0.6 or lower, Oracle bug 3833893 is responsible. This problem has been solved in Oracle 9.2.0.7. For more information, see Note 867681.

Longer waits for the TT enqueue are generally caused by Oracle bugs. If you experience problems with this enqueue type, you should therefore first import the latest Oracle patchset.

                    If the problem persists, open an SAP message.

                    In 10g, the wait event is called "enq: TT - contention".

TYPE = HW

A HW enqueue occurs if several processes are simultaneously trying to move the high water mark of a table. In particular, this situation can occur when there are parallel INSERT operations. In such a case, the high water mark is moved by five blocks (in the case of several FREELISTs: five blocks for each FREELIST) by default. If there is a large number of parallel INSERTs, this may be insufficient. The following optimizations are possible:
                    Increase the number of FREELIST

Increase the _BUMP_HIGHWATER_MARK_COUNT parameter to a value greater than 5, which increases the high water mark by more than five blocks and means that the HWM does not have to be changed so often. Caution: Due to possible side effects (such as an increase in the space required), this underscore parameter should only be adjusted after consultation with SAP.

HW enqueues may also occur when you create objects with large initial values, since in this case, the high water mark has to be increased up to the INITIAL value.

                    In 10g, the wait event is called "enq: HW - contention".

TYPE = HV

The HV enqueue is similar to the HW enqueue; the only difference is that it occurs with parallel direct path INSERTs. This may be the case if BRSPACE online reorganizations are made using active DML parallel processing.
                    In 10g, the wait event is called "enq: HV - contention".

TYPE = TC

TC enqueue waits may occur in connection with ALTER TABLESPACE BEGIN BACKUP and when other tablespace commands such as OFFLINE or READ ONLY are used. A checkpoint is executed for these operations. While the DBWR writes the dirty blocks from the buffer pool to the hard disk, the system waits for a TC enqueue. Long runtimes may be caused by a large buffer pool or poor DBWR performance.
If massive TC enqueue waits occur, the runtime of the backup may increase significantly as a result. However, there are no side effects on the system that is running in parallel.

                    If the backup takes too long because of TC enqueues, open a message for further analysis with SAP.

A TC enqueue is also requested if, in the context of parallel query accesses, the data on the disk is accessed directly without using the buffer pool. To ensure data consistency, you must ensure that all changes to the affected segment are written from the buffer pool to the disk. This occurs in the context of a segment checkpoint, where the TC enqueue is held.

                    In 10g, the wait event is called "enq: TC - contention".

TYPE = CF

CF enqueues are control file enqueues, which occur during parallel access to the control file. Actions that make it necessary to access the control file can be for example, BEGIN BACKUP, redolog archiving by an ARCH process, or a logfile switch by the LGWR process. If a CF enqueue is requested within a period of 15 minutes without success, ORA-00600 [2103] occurs, and the Oracle instance may terminate (see Note 658744)..
In "checkpoint not complete" situations (Note 79341), the LGWR process may wait for a long time for the CF enqueue. This is a follow-on problem. The "checkpoint not complete" situations that cause this problem must be corrected (Note 793113).

                    See Note 658744, which contains possible causes and solutions for CF enqueues and the ORA-00600 [2103] that are triggered by it.

CF enqueue waits may also occur during an Archiver stuck (Note 391) since in such cases the ARCH process may hold the CF enqueue for a long time. In this case, CF enqueue are only a follow-on problem; after you solve the Archiver stuck situation, the enqueue waits will also disappear.

                    In 10g, the wait event is called "enq: CF - contention".

TYPE = SS

SS enqueues are sort segment enqueues. A session that must carry out a sorting process on the disk must first request a corresponding Sort Segment. Until this request is met, the session waits on the Wait Event "sort segment request". If several sessions request a sort segment during this time, these sessions wait on an SS enqueue.
The SS enqueue occurs in particular if the SMON process is very busy and cannot respond to the incoming requests promptly. This may happen, for example, when it carries out large rollback activities.

If you encounter performance problems due to SS enqueues, you first have to import the most recent Oracle Patchset, since older patchsets may be partially responsible for problems with SS enqueues. If the problem still occurs with a current patchset, open an SAP message.

                    In 10g, the wait event is called "enq: SS - contention".

TYPE = FB

The FB enqueue (format block enqueue) is retained if blocks are formatted in ASSM tablespaces (for example, in the context of INSERT operations). Significant waiting times for this enqueue type are generally a result of other problems, such as"log buffer space" waits. Therefore, check whether there are also noticeable Wait Events during times of increased FB Enqueue Waits.
                    In 10g, the wait event is called "enq: FB - contention".

TYPE = PE

The PE enqueue (parameter enqueue) occurs if a parameter change using "ALTER SYSTEM SET ..." or "ALTER SESSION SET ..." is active and other sessions are waiting for the completion of this change. Significant waiting times on this enqueue are generally connected to runtime problems during the parameter change. You must primarily check whether there are "ALTER ... SET" commands with longer runtime and what is triggering this increased runtime.
                    In 10g, the wait event is called "enq: PE - contention".

TYPE = TQ

The TQ enqueue (queue table access enqueue) may occur in connection with Data Pump activities. For more information, see Note 1013049.
                    In Oracle 10g, the Wait Event is called "enq: TQ - DDL contention".

TYPE = PS

The PS enqueue (Parallel Slave Synchronization Enqueue) occurs for parallel query coordinators if problems occur when setting up slaves or parsing the statement to be executed. Therefore, the PS enqueue is usually a follow-up problem and you must investigate why the start of the slave has been delayed (for example, by analyzing a wait event). For more information about the parallel query, see Note 651060.
 On Oracle 10g (10.2.0.3 or lower), bug 5908030 for statements such as INSERT ... SELECT or MERGE and the use of parallel execution may cause a "cursor: pin X" and "enq: PS - contention" deadlock. As a workaround, you can use _KKS_USE_MUTEX_PIN = FALSE to deactivate the use of mutexes (see Note 964344).

                    In Oracle 10g, the Wait Event is called "enq: PS - contention".

TYPE = UL

The UL enqueue is set by executing DBMS_LOCK.REQUEST. This module is not used in the standard SAP system, but it may occur in connection with the Oracle data pump (Note 1013049).
                    In Oracle 10g, the Wait Event is called "enq: UL - contention".

TYPE = SQ

The sequence cache enqueue is used to synchronize access to Oracle sequences. Values may be increased in connection with parallelized online reorganizations, which does not usually require any major changes. If there is an RAC environment problem with enqueues, check if the problem is solved by importing the current patch set. If not, open an SAP customer message for a more thorough analysis.
                    In Oracle 10g, the Wait Event is called "enq: SQ - contention".

TYPE = KO

KO enqueues coordinate the checkpoints of several segments, for example when you create temporary Oracle tables in STAR transformations. Increased wait times are usually a result of poor DBWR performance. Therefore, check the Oracle I/O behavior. according to Note 793113.
                    In Oracle 10g, the Wait Event is called "enq: KO - fast object checkpoint".

TYPE = SS
TYPE = TS

If segments are allocated in the temporary tablespace, the sort segment enqueue and the temporary segment enqueue are required.  There should be no significant wait times for these enqueues. Due to Oracle bug 6083815, the SMON process may allocate the TS enqueue at level 3 although the M001 process requires this enqueue at level 6, but must wait for SMON. At the same time, M001 has already exclusively allocated the SS enqueue, and therefore, all further PSAPTEMP requirements must permanently wait for the SS enqueue.
If the SS enqueue times or the TS enqueue times are long, open a message with SAP.
                    In Oracle 10g, the wait events are called "enq:SS - contention" and "enq: TS - contention".

13. How I can determine which tables are most responsible for TX enqueue waits?
As of Oracle 9I, V$SEGMENT_STATISTICS contains information about TX enqueues in the application ("row lock waits") and ITL TX enqueues ("ITL waits"). You can determine the top ten of the segments in terms of the following two categories as follows:

SELECT * FROM
   (SELECT OBJECT_NAME, SUBSTR(STATISTIC_NAME, 1, 30), VALUE
   FROM V$SEGMENT_STATISTICS
   WHERE STATISTIC_NAME = 'ITL waits' OR
         STATISTIC_NAME = 'row lock waits'
   ORDER BY VALUE DESC )
WHERE ROWNUM <=10;

Note that only the NUMBER, but not the DURATION of enqueue wait situations is recorded. Therefore, the returned objects may not actually be responsible for the longest enqueue wait situations.

As of Oracle 9i, a further indication of enqueue problems is the time it takes to execute an SQL statement. In newer SAP releases, this can be determined in the shared cursor cache analysis in transactions ST04 or ST04N (see Note 766349). Alternatively, you can use a database query such as the following one to determine the SQL statements that take the longest to be executed (times are in microseconds):

column SQLTEXT word_wrap format A43
column READS wrapped format A8
column ELAPEREXEC wrapped format A8
column ELATIME wrapped format A8
column CPUTIME wrapped format A8

SELECT * FROM
(SELECT TO_CHAR(ELAPSED_TIME) ELATIME,
         TO_CHAR(DECODE(EXECUTIONS, 0, 0,
           TRUNC(ELAPSED_TIME / EXECUTIONS))) ELAPEREXEC,
         TO_CHAR(CPU_TIME) CPUTIME,
         TO_CHAR(DISK_READS) READS,
         MODULE || SQL_TEXT SQLTEXT
FROM V$SQL
ORDER BY DECODE(EXECUTIONS, 0, 0,
   TRUNC(ELAPSED_TIME / EXECUTIONS)) DESC)
WHERE ROWNUM <=10;

As of Oracle 10g, it is possible to determine which objects are mainly responsible for enqueue waits by using the V$ACTIVE_SESSION_HISTORY view for the recent past. See Note 619188 for more information.

Furthermore, as of Oracle 10g, you can determine those SQL statements that waited the most for wait events of type "Application". In many cases, this corresponds to the TX enqueue waits:

SELECT * FROM
(SELECT
     ROUND(APPLICATION_WAIT_TIME / 1000000)
       "APPLICATION WAIT TIME (S)",
     SQL_ID,
     SQL_TEXT
   FROM
     V$SQLSTATS
   ORDER BY APPLICATION_WAIT_TIME DESC )
WHERE ROWNUM <=10;

14. How do I find the table and the data record that is responsible for a TX enqueue?
If you identified a session that holds a TX enqueue over a long period of time, you can use the ROW_WAIT_OBJ#, ROW_WAIT_FILE#, ROW_WAIT_BLOCK# and ROW_WAIT_ROW# columns of V$SESSION to determine the relevant object and the relevant entry.

The following statement generates an additional SQL statement that returns the data record on which the session must wait due to a TX enqueue:

SELECT 'SELECT * FROM "' || O.OWNER || '"."' || O.OBJECT_NAME || '"
WHERE ROWID = DBMS_ROWID.ROWID_CREATE(1, ' || S.ROW_WAIT_OBJ# || ', ' ||
   S.ROW_WAIT_FILE# || ', ' || ROW_WAIT_BLOCK# || ', ' ||
   ROW_WAIT_ROW# || ');'
FROM DBA_OBJECTS O, V$SESSION S
WHERE S.ROW_WAIT_OBJ# = O.OBJECT_ID AND S.SID = ;

              The name of the relevant table can be taken from the statement itself.

15. How can I trace the enqueue requests of a session?
In individual cases it may make sense to analyze which enqueues are requested by a certain activity in the process. You can obtain this information (for example, during the reproduction on SQLPLUS level) by activating the Oracle event 10704:

ALTER SESSION SET EVENTS = '10704 trace name context forever, level 10';

16. Where can I find more information about Oracle enqueues?
Oracle online documentation:

Oracle9i Database Reference
-> 3 Dynamic Performance (V$) Views
-> V$LOCK

Oracle9i Database Performance Tuning Guide and Reference
-> 22 Instance Tuning
-> Wait Events
-> Enqueue

Oracle10g Database Reference
-> 4 Dynamic Performance (V$) Views
-> V$LOCK

Oracle10g Database Performance Tuning Guide
-> 10 Instance Tuning Using Performance Views
-> Wait Event Statistics
-> Enqueue Waits

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/12020513/viewspace-671722/，如需轉載，請註明出處，否則將追究法律責任。