Original source:
http://www.eygle.com/statspack/statspack13.htm
Enqueue is a locking mechanism to protect shared resources. This locking mechanism protects shared resources, such as data in the record to avoid two people updating the same data at the same time. Enqueue includes a queuing mechanism, namely FIFO (advanced first out) queuing mechanism.
Enqueue is waiting for common ST, HW, TX, TM, etc.
ST Enqueue, the interval allocation of table space (DMT) managed by spatial management and dictionary, typically for UET $ and FET $ data dictionary tables. For versions that support LMT, you should try to use local management table space. Or consider manually pre-assigned a certain number of zones (Extent), reduce the serious queue competition that occurs during dynamic expansion.
Let's take a look at one instance:
DB Name DB ID Instance Instal Release Ops Host
------------ ------------------------- ---- - - ------------------
DB 40757346 AAA 1 8.1.7.4.0 NO Server
Snap ID Snap Time sessions
--------------------------------
Begin Snap: 2845 31 - October -03 02:10:16 46
END SNAP: 2848 31 - October -03 03:40:05 46
ELAPSED: 89.82 (MINS)
For a Statspack's Report, the sampling time is a very important dimension, and the time to leave time, 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% Total
Event Waits Time (CS) WT TIME
------------------------------------------------------------------------------------------ -----------------------
Enqueue 53, 793 16, 192, 686 67.86
RDBMS IPC Message 19, 999 5, 927, 350 24.84
PMON Timer 1,754 538, 797 2.26
SMON Timer 17 522, 281 2.19
SQL * Net Message from Cliant 94, 525 520, 104 2.18
-------------------------------------------------- ----------- In Statspack Analysis, TOP 5 Waiting for the event is our most concerned part.
In this system, in addition to the enqueue waiting for the event, other four are idle waiting events, no attention. Let's pay attention to Enqueue et al.
Treatment, within 89.82 (mins) sampling interval, accumulated enqueue waits for up to 16, 192, 686CS, 45 hours. This is awesome
Significant, in fact, this system is also therefore encountered huge difficulties. After observing the queue waiting, we should pay attention to the queue waiting waiting for something.
Resource. For the other parts of the StatsPack for quick jump, we see the following details:
ENQUEUE ACTIVITY for DB: DB Instance: AAA Snaps: 2716 -2718
-> Ordered by Waits DESC, Gets DESC
Enqueue Gets Waits
---------- ------------ ----------
ST 1, 554 1,554
-------------------------------------------------- -----------
We see that the main queues are waiting to wait for the ST lock, for DMT, we say this is closely related to FET $, UET $. We are going back
Study the captured SQL statement:
-> End Buffer Gets Threshold: 10000
-> Note That Resources Reported for PL / SQL Includes The Resources Used by
All SQL Statements Called Withnin The PL / SQL Code. AS Individual SQL
Statements Are Also Reported, IT IS Possible and Valid for the Summed
Total% to Exceed 100
Buffer Gets Executions Gets Per EXEC% Total Hash Value
------------------------------------------- ------------
4,800,073 10,268 467.5 51.0 2913840444
SELECT Length from Fet $ Where File # =: 1 and block # =: 2 and ts # =: 3
803, 187 10, 223 78.6 8.5 528349613
Delete from Uet $ Where TS # =: 1 and segfile # =: 2 and segblock # =: 3 a
ND EXT # =: 4
454, 444 10, 300 44.1 4.8 1839874543
Select File #, BLOCK #, Length from Uet $ Where TS # =: 1 and segfile # =:
2 and segblock # =: 3 and extra =: 4
23, 110 10, 230 2.3 0.2 3230982141
INSERT INTO FET $ (File #, Block #, TS #, Length) Values (: 1,: 2,: 3,: 4)
21, 2013 347 61.1 0.2 1705880752select File # from file $ where ts # =: 1
.
9,505 12 792.1 0.1 1714733582
SELECT F.FILE #, F.Block #, F.TS #, F.LENGTH from Fet $ F, TS $ T WHE
RE T.TS # = f.ts # and t.dflextpct! = 0 and T.bitmapped = 0
6,426 235 27.3 0.1 1877781575
Delete from fet $ where file # =: 1 and block # =: 2 and ts # =: 3
We see the database frequent operation UET $, the FET $ system table has become the main bottleneck of the system.
At this point, we can accurately position problems, the corresponding solution is also easy to determine, in 8.1.7, use LMT instead of DMT,
This is the fundamental approach to solve the problem. Of course, it is necessary to implement comprehensive consideration, and the actual situation is much more complicated.
HW enqueue refers to the high water level markings of the segment; manually assigning appropriate districts to avoid this.
TX is the most common enqueue waiting. TX Enqueue Waiting is usually the result of one of the following three issues.
The first question is the repeated index in the unique index, you need to perform submit / rollback operations to release Enqueue.
The second problem is multiple updates to the same bit map index. Because a single bit segment may contain multiple row addresses (RowID), it may be one when multiple users attempt to update the same paragraph
The user will lock the records of other users, at this time, wait. Until the locked user is submitted or rolled back, enqueue is released.
The third issue is also the most likely problem that multiple users update the same block at the same time. If there is not enough ITL slot, block level lock will occur. By increasing initrans and / or
MaxTrans to allow multiple ITL slots (for data sheets that frequently perform DML operations, you should consider setting reasonable values for the corresponding parameters at first, avoid system operation
After the online changes, before 8i, the parameters such as FreeELists cannot be changed online, and the design is particularly important), or increase the PCTFREE value on the table, it can be easily avoided.
This situation is.
The TM ENQUEUE queue lock is obtained before performing DML operations to prevent any DDL operations that are being operated (when the DML operation is operated, its structure cannot be changed).
Author: eygle, Oracle technology followers, Oracle technical forum itpub.www.eygle.com from China is the biggest author's personal site you may contact the author by Guoqiang.Gai@gmail.com welcome to explore technical exchanges and links. exchange.
Original source:
http://www.eygle.com/statspack/statspack13.htm
