Statspack 13-enqueue

Original source:

Http://www.eygle.com/statspack/statspack13.htm



Enqueue is a locking mechanism for protecting shared resources. This locking mechanism protects shared resources, such as data in records, to prevent two of people from updating the same data at the same time. Enqueue
Includes a queuing mechanism, i.e. FIFO (first-in first out) queuing mechanism.

Enqueue waiting for the common have St, HW, TX, TM, etc.

ST Enqueue, for Space management and dictionary management of the Table space (DMT) of the interval allocation, in the DMT is typically for uet$ and fet$ data dictionary table contention. For those who support LMT
version, you should use local management table space as much as possible. Or consider manually allocating a certain number of extents (Extent) to reduce dynamic expansion
A serious queue competition occurs when.

Let's take a look at the following example:





 db Name DB Id Instance Inst Num release OPS Host-------------------------------------------------------------- -------------DB 40757346 AAA 1 8.1.7.4.0 NO Server Snap Id snap time Sessions---------------------------------Begin Sn ap:2845 3 January-October -03 02:10:16 snap:2848 3 January-October -03 03:40:05 elapsed:89.82 (mins) for a statspack, the sampling time is a very important dimension , leaving time for reference, any waiting is not enough to explain the problem. Cache sizes~~~~~~~~~~~ db_block_buffers:51200 log_buffer:2097152 db_block_size:16384 shared_pool_size:209715200 ..... . Top 5 Wait events~~~~~~~~~~~~~~~~~ wait% totalevent Waits time (CS) Wt time--------------------------------------------- ------------------------------enqueue 53,793 16,192,686 67.86rdbms IPC message 19,999 5,927,350 24.84pmon timer 1,754 53 8,797 2.26smon timer 522,281 2.19sql*net message from client 94,525 520,104 2.18-------------------------------------- -----------------------in Statspack analysis, the top 5 wait event is our most concerned part. In this system, in addition to enqueue wait for events, the other 4 are idle waiting events, do not need attention. Let's take a look at the Enqueue wait event, at 89.82 (mins) In the sampling interval, the cumulative enqueue wait for up to 16,192,686cs, that is, about 45 hours. This wait has been too obvious, in fact, the system is also encountering great difficulties, after watching the queue waiting, we should focus on waiting for what resources queue. Quick jump to the other parts of the statspack, we see the following details: Enqueue activity for DB:DB instance:aaa snaps:2716-2718-> ordered by waits s descenqueue Gets Waits--------------------------------ST 1,554 1,554--------------------------------------------- ----------------we see the main queue waiting for St locking, for DMT, we say this wait is closely related to fet$,uet$ contention. We're looking back at the captured SQL statement:-> end Buffer Gets threshold:10000-> Note This reported for Pl/sql includes the RESOURC Es used by all SQL statements called within the Pl/sql code. As individual SQL statements are also reported, it is possible and valid for the summed total% to exceed MB Gets Executions Gets per Exec% Total Hash Value------------------------------------------------------------4,800,073 10,2 467.5 51.0 2913840444select length from fet$ where File#=:1 and Block#=:2 and Ts#=:3 803,187 10,223 78.6 8.5 528349613d Elete from uet$ where Ts#=:1 and Segfile#=:2 and Segblock#=:3 and Ext#=:4 454,444 10,300 44.1 4.8 1839874543select file#,block#,length from uet$ where Ts#=:1 and Segfile#=:2 and Segblock#=:3 and Ext#=:4 23,110 10,230 2.3 0.2 3230982141insert into fet$ (file#,block#,ts#,length) values (: 1,:2,:3,:4) 21,201 347 61.1 0.2 1705880752select file# from file$ where ts#=:1 .... 9,505 792.1 0.1 1714733582select f.file#, f.block#, f.ts#, f.length from fet$ F, ts$ t where t.ts#=f.ts# and T.DFLEXTPC T!=0 and T.bitmapped=0 6,426 235 27.3 0.1 1877781575delete from fet$ where File#=:1 and Block#=:2 and ts#=:3 we see database operations frequently uet The $,fet$ system table has become the main bottleneck of the system. At this point, we can accurately for the positioning of the system, the corresponding solution is also easy to determine, in the 8.1.7, the use of LMT instead of DMT, which is the fundamental solution to the problem, of course, the implementation of the comprehensive consideration, the actual situation is much more complicated.


The HW enqueue refers to the high water mark of the segment and waits, and allocating the appropriate area manually can avoid this wait.

TX is the most common enqueue waiting. TX enqueue Wait is usually the result of one of the following three problems.
The first problem is a duplicate index in a unique index, and you need to perform a commit/rollback (rollback) operation to release the Enqueue.
The second issue is the multiple updates to the same-figure index segment. Because a single bitmap segment may contain multiple row addresses (ROWID), when multiple users try to update the same paragraph, a
The user locks the record requested by another user and waits for it to appear. Enqueue is released until a locked user commits or rolls back.
The third and most likely problem is that multiple users update the same block at the same time. Block-level locking occurs if there are not enough ITL slots. By increasing initrans and/or
Maxtrans to allow the use of multiple ITL slots (for data tables that frequently perform DML operations at the beginning of the table, consideration should be given to setting reasonable values for the corresponding parameters to avoid system operation
Later online changes, before 8i, freelists and other parameters can not be changed online, design-time considerations are particularly important), or increase the Pctfree value on the table, you can easily avoid
This situation.
The TM enqueue queue Lock is obtained prior to the DML operation to prevent any DDL operations on the data table being manipulated (its structure cannot be changed when DML operates a data table).




The author of this article:
Eygle,oracle technology concern, from China's largest Oracle technology forum Itpub.
Www.eygle.com is the author's personal site. You can contact the author by Guoqiang.Gai@gmail.com. Welcome technical discussions and exchange of links.

Original source:

Http://www.eygle.com/statspack/statspack13.htm