Shulou(Shulou.com)06/01 Report--

Queues are used to sequentially lock relational and non-relational Oracle structures. The relational structure may be an Oracle data dictionary table or an application table. For example, when Oracle updates the sys.col$ table or an application updates its employee table, the queue will be called. If a server process is blocked by a locked table, not only will an enqueue wait wait event be post, but the lock information will also be displayed in vSecretenqueuewaiting statistics and other views. Non-relational structures are locked to prevent inappropriate changes such as library cache cursor.

As the name implies, the queue is very orderly and ensures that the structure is changed in a very certain manner. The process's queued request is pushed to the appropriate queue, and when it needs to be processed, its queued entry pops up (also known as dequeue). There's nothing exciting here, but the queue is not to take risks, but to ensure that the Oracle structure is changed in a very orderly, accounting-like way.

Oracle maintains an amazing number of queues. There are 208 queues in Oracle 10gr2 and 247 queues in Oracle 11gr1. But don't panic, because you may only meet a few people in line. In addition, if you are an experienced DBA, you have dealt with row-level and table-level locks that use enqueue.

Diagnostic Enqueue waiting

When solving a queue problem, first determine the type of queue, then determine the SQL involved, and finally develop the solution based on your internal knowledge of the application and related Oracle. Before delving into the most common queuing (transaction (TX) queuing), it is important to know how to determine which queue you are waiting for and related sessions in versions before and after oracle 10g.

Before Oracle 10g, the wait event for all queues was enqueue. This is really unfortunate because it takes a sample from v$lock or v$session_wait to determine the queue name. The following SQL statement is used to confirm the enqueue name from v$session_wait. Session 4388 has locked the table and there is no waiting lock, so it is not displayed. The first session in the queue is 4387, followed by session 4393. The easiest way to determine which SQL is running and the table involved is to query the session's sql_address or sql_hash_values from the v$session. For TM queues, the table can be identified by column p2 (column ID 1). It contains object_id, which you can use to query from dba_objects. This makes it easy to identify contenders.

SQL > col sid format 9999 heading "Sid" SQL > col enq format a4 heading "Enq." SQL > col edes format a30 heading "Enqueue Name" SQL > col md format a10 heading "Lock Mode" truncSQL > col p2 format 9999999 heading "ID 1" SQL > col p3 format 9999999 heading "ID 2" SQL > select sid, 2 chr (bitand (p1,-16777216) / 16777215) | 3 chr (bitand (p1,16711680) / 65535) enq, 4 decode (chr (p1 (p1) -16777216) / 16777215) | | 5 chr (bitand (p1, 16711680) / 65535), 6 'TX', 7' Row related lock (row lock or ITL)', 8 'TM', 9' Table related lock', 10 'TS', 11' Tablespace and Temp Seg related lock' 12 'TT', 13' Temporary Table', 14 'ST', 15' Space Mgt (e.g., uet$, fet$)', 16 'UL', 17' User Defined', 18 chr (bitand (p1,-16777216) / 16777215) | | 19 chr (bitand (p1) 16711680) / 65535) edes, 20 decode (bitand (p1, 65535), 21 1,22 'Null', 23 2, 24' Sub-Share', 25 3, 26 'Sub-Exlusive', 27 4, 28' Share', 29 5 30 'Share/Sub-Exclusive', 31 6, 32' Exclusive', 33 'Other') md, 34 p2, 35 p3 36 from v$session_wait 37 where event =' enqueue' 38 and state = 'WAITING' 39 / SQL > Sid Enq. Enqueue Name Lock Mode ID 1 ID 2-4387 TM Table related lock Exclusive 49911 04393 TM Table related lock Sub-Exlusi 49911 0SQL > @ swswp enq%Database: prod16 31-MAR-10 04:32pmReport: swswp.sql OSM by OraPub Inc. Page 1Session Wait Real Time w/ParametersSessID Wait Event P1 P2 P3-4383 enq: TM-contention 1414332422 49911 04388 enq: TM-contention 1414332422 49911 02 rows selected.SQL > L1 select sid Event,2 p1, p2, p33 from v$session_wait4 where event like'& input%'5 and state = 'WAITING'6* order by event,sid,p1,p2

As with latch wait events, starting with Oracle 10g, each queue has its own wait event. This saves diagnostic steps because we can determine the type of session and queue involved with a simple query. Session 4393 already holds a table lock and does not wait so it is not displayed, sessions 4383 and 4388 are waiting for the lock table so post a TM queue to wait. The called table is obtained by using the P2 column (49911) to associate the query with the object_id of the dba_objects view.

TX Enqueue wait

TX queue waiting is the most common queue waiting event. This is also the most fascinating. You want to delve deeper into this wait event, as it will give you a deeper understanding of how Oracle manages transaction concurrency, which is related to block cloning, undo, read consistency, and related transaction lists.

TX queues are also called row-level lock queues, and there are actually three reasons for TX queues, and only one is actually a row-level lock. Each Oracle block can be abstracted into three regions:

. Row data contains real Oracle row records and is the most important part of each data block.

. Mutable data contains transaction metadata

. The amount of free space can be reduced by row data growth and variable data growth

Related transaction list (ITLs)

The structure of the data area built into each Oracle data block is called the related transaction list (ITLs). These structures are mainly used to be responsible for row-level locking and read consistency of Oracle. From a highly abstract point of view, ITLs can be thought of as checkboxes, with each checkbox associated with a particular transaction. If you want to update a row record, but the locked row is already associated with the ITL of another transaction, you will receive a TX queue waiting, which is indeed a row-level lock.

Each Oracle block creates a specific number of ITLs. The initial value of ITLs is controlled by the initrans space parameter of the table and can be viewed through the ini_trans column of the dba_tables view. Starting with Oracle 9i, the default ini_ transvalue is 1, but through the simple block dump, you can clearly see that two ITL have been created. With two ITLs, a single data block can execute two transactions concurrently.

Suppose the third transaction wants to modify the unlocked rows in the block and only if two ITL exist, the server process of the third transaction will try to create an additional ITL dynamically. However, the server process must first ensure that the maximum number of ITL (max_trans) is not exceeded and that there is free space in the data block. If the server process cannot create an additional ITL, it will issue a TX queue waiting event, and the process will wait patiently. To reduce this situation, the default and maximum values of ITLs for a single block can be set to 255. When this value is not exceeded, you can execute the alter table command to modify it.

Once an ITL is created in the data block, the only way to get space is to recreate the entire table. Modifying the space parameters will not affect the ITL that has been created. This is why the default ITLs is 1 (two ITL are actually created) and the maximum value is set to 255. If there are more concurrent requests for data blocks, ITLs,Oracle would rather consume space than send out TX queues to wait for events and let transactions wait.

At first glance, the maximum number of ITL of255may seem very limited, but consider this: think of the highest and published in the highest concurrency application, in the highest concurrency database. There may be a table with 250 concurrent processes updating, deleting, and inserting records. Now how many processes will update, delete, or insert records concurrently into a single block, rather than the entire table or range? Even with the highest concurrency applications, it is highly impossible to activate more than 255 concurrent transactions in a block. So there is not much limit to the maximum number of ITL of 255. However, if there is a problem, you can reduce the concurrency of the data block by increasing the pct_free parameter of the table or add fixed-length columns in order to reduce the number of row records stored in the block.

Transaction table for Unod segment

Each undo segment contains a structure called a transaction table in its header block. Oracle developers call the rows in the transaction table slots (slots). Each occupied slot is related to a transaction that is or has stored undo information in a undo segment. If a transaction has been committed or rolled back, it is indeed an inactive transaction, otherwise it is an active transaction. In addition to containing the slot number and transaction status, each slot also contains a sequence number. In order to distinguish between different transactions using the same slot and to make slot reuse, sequence numbers can be increased. UBA is the undo block address that provides a direct link to the undo of the transaction. SCN is the system change number of the transaction when the related transaction starts.

Transaction tables are relevant to performance analysts because they provide transaction numbers. Each transaction has an associated transaction number, and the transaction number is generated based on the transaction table entry of the transaction. The transaction number consists of three sets of numbers. The first part is the transaction table number, the second part is the slot number, and the last is the related sequence number. For example, a transaction number is 00100.000.00007. The association between the ITLs and the transaction table is that each ITL entry is associated with a specific transaction and contains the transaction number in the ITL entry, such as 00100.000.00007.

Learn more about the related transaction list (ITL)

Now that you've seen the ITL and undo segment transaction tables, it's time to group them together as a single unit of work and show how ITLs changes during transaction activity. An in-depth understanding of the relevant transaction list can give you an in-depth understanding of how Oracle manages transaction concurrency, how to create read consistency blocks, and why you should beware of "snapshot too old" errors.

Let's dump the data blocks by executing the command alter system dump datafile 1 block 75847. When the block dump is executed, the block (1Power75847) contains many rows of records and has three active transactions to update four different rows of records. The first and third transactions show that one row of records is being updated, and the second transaction is updating two rows of records.

$cat prod5_ora_21741.trc...Block header dump: 0x00412847Object id on Block? Yseg/obj: 0xff6b csc: 0x00.50fcb6 itc: 3 flg: O typ: 1-DATAfsl: 0 fnx: 0x412848 ver: 0x01Itl Xid Uba Flag Lck Scn/Fsc0x01 0x0003.00d.00000318 0x00c3e3d0.0593.0c-1 fsc 0x0000.000000000x02 0x0008.01b.00000340 0x00c41bce.0481.24-2 fsc 0x0000.000000000x03 0x0001.000.00000320 0x00c45fa0.0599.0b-1 fsc 0x0000.00000000.

The ITL entry contains the following:

Itl: this is the ITL number of the transaction

Xid: this is the transaction ID, which consists of the transaction table ID (0003), the transaction table slot number (00d) and the sequence number (00000318). Transactional ID is important because it is used to ensure that seemingly relevant undo information is truly relevant.

Uba: this is the undo block address. This points directly to the latest change to the transaction, undo, which is necessary to roll back the transaction and read consistency (clone buffer construction).

Flag: the state of the transaction. It can have many values. Here are the common values.

-means that the transaction is active and DML is not committed or rolled back while executing the transaction

-- UMY-means that the transaction has been committed, so any row data can refer to ITL that is not used in the active transaction and they are not locked. The row data for the transaction may not have been merged. For example, if a column is updated, the values before and before the change may remain in the row data.

-- Cmure-- means that the transaction has been committed, the row data has been merged, and the ITL entry in the row data has been deleted. Any block touch may trigger changes to this flag, including select statements. I know it's hard to believe. This seemingly delayed change is often referred to as deferred block cleanup, or simply called block cleanup.

Lck: this is the number of rows that the transaction locks in this block at some point. A value greater than 0 cannot be said to be locked. If this value is 2, just like the second transaction, the transaction is associated with two rows of records. The lock will remain until the flag is changed to Cmurmuri -. This means that after a transaction is committed and is no longer considered active (--Umuri -), the lock value may be greater than 0.

Scn/Fsc:SCN is the system change number and is used to determine when the transaction ends (commit or rollback). In the above example, SCN is not referred to, but after the transaction is committed, SCN is set as follows. SCN is important when creating a read-consistent version of buffer to determine whether the undo needs to be retrieved. FSC references available space credits. It is used for uncommitted transactions when an update or delete operation causes the length of row records to shrink. Oracle will protect this free space from transaction rollback and the need to refill the space. If the free space is used for other purposes, and then the transaction is rolled back, you may need to migrate the row!. In the following dump results, the first two transactions (ITLs x01 and x02) have committed and marked their transactions as inactive. The third transaction, ITL x03, has not yet been committed. After the first two transactions are committed, the same block dump command, alter system dump datafile 1 block 75847. Notice that the flag has changed, and a SCN has been assigned to the transaction.

$cat prod5_ora_21741.trc...Block header dump: 0x00412847Object id on Block? Yseg/obj: 0xff6b csc: 0x00.50fcb6 itc: 3 flg: O typ: 1-DATAfsl: 0 fnx: 0x412848 ver: 0x01Itl Xid Uba Flag Lck Scn/Fsc0x01 0x0003.00d.00000318 0x00c3e3d0.0593.0c-- U- 1 fsc 0x0000.0050fd6f0x02 0x0008.01b.00000340 0x00c41bce.0481.24-U2 fsc 0x0000.0050fd6b0x03 0x0001.000.00000320 0x00c45fa0.0599.0b-1 fsc 0x0000.00000000

Two flags-and-- U-- are required because rows involved in active or past active transactions can have valid ITL entries in their row data. Because simply referencing row data and viewing ITL entries do not indicate that the row is currently invoked and locked by the active transaction. To check whether the row is locked, a server process must get an ITL reference from the row data and then check the flag in the variable ITL area of the data block. If flag is--, then the server process knows that the row is indeed called by an active transaction and locked. However, if falg is-- U, the server process knows that the row is not locked.

Part of the work of the block cleanup process deletes the inactive transaction row data ITL entries, modifies the state of their respective ITL entries in the flag of the variable part of the data block to CMurray, and merges the parallel data.

This is a smart strategy because Oracle can quickly record changes in blocks with minimal changes, but still maintain concurrency control at the row level. Final changes to the block are eventually required, but this may occur during periods of low workload, such as after the benchmark is completed.

After executing the query statement to touch block 1,75847, the result of executing the dump command is as follows: the data block (1Power75847), after executing the query statement touch data block, the transaction flags has changed from-- U-to CMB, indicating that block cleanup has occurred.

$cat prod5_ora_21741.trc...Block header dump: 0x00412847Object id on Block? Yseg/obj: 0xff6b csc: 0x00.510047 itc: 3 flg: O typ: 1-DATAfsl: 0 fnx: 0x412848 ver: 0x01Itl Xid Uba Flag Lck Scn/Fsc0x01 0x0003.00d.00000318 0x00c3e3d0.0593.0c Cmurmuri-0 scn 0x0000.0050fd6f0x02 0x0008.01b.00000340 0x00c41bce.0481.24 Cmurmuri-0 scn 0x0000.0050fd6b0x03 0x0001.000.00000320 0x00c45fa0.0599.0b-1 fsc 0x0000.00000000.

Now I realize this is interesting, but I also understand that some readers may think that this block dump and ITL stuff is not so strongly related. But I beg to differ. Not only do you have a more comprehensive understanding of TX queuing, but you also have a clear understanding of how queuing Oracle implements its proprietary row-level locking scheme.

In-depth understanding of Buffer cloning

Block cloning is introduced because it competes with CBC latch. Now take a closer look at how Oracle uses ITLs,undo blocks, SCNs, and other interesting Oracle technologies. When a server process locates a requested buffer and finds that the requested row has changed after the query starts, it must create a mirror of the buffer that goes back in time. This is called the current (CU) buffer consistent read (CR) buffer. Once the buffer is copied and the appropriate undo is applied, the copied buffer is rolled back until the CR buffer is successfully cloned.

Suppose our query execution time is SCN 12330, and the query ends up with the buffer 7678 to be accessed. However, we notice that there are some ITL active transactions 7.3.8 that are currently active and that buffer may have changed after our query started. Transaction 5.2.6 is inactive (flag is C, SCN is assigned, and Lck is 0), but the changed commit time is after our query starts and affects the current (CU) buffer. These block changes mean that CU buffer has changed since our query started at SCN 12330 and cannot be used for our query. We need a consistent read copy that can go back in time to the SCN 12330 point in time. Therefore, CU buffer 7678 must be cloned and undo applied to create a SCN12330 point-in-time CR buffer.

Before performing a buffer clone, you must find an infrequently accessed free buffer and replace it with a 7678 CU buffer. The server process will get the LRU chain latch and the associated LRU chain, and then scan from the LRU side of the LRU chain to find the free buffer that is not frequently accessed. Eventually you will find an infrequently accessed free buffer and replace it with a copy of CU buffer 7678. Of course, the CBC structure will also be updated to map the location of the clone buffer in the buffer cache.

Start with the active transaction 7.3.8 associated with the first ITL. The server process needs to retrieve all undo records generated after our query start time scn 12330. The recently generated undo for transaction 7.3.8 can be found in the undo block 2 undo 45 linked to the undo block address (UBA) of its ITL. The server process must then access the undo block 2meme 45. This requires a request for the CBC activity and may also request the LRU activity to perform the IO call. Once we have accessed undo buffer 2, we will check by comparing the transaction numbers to make sure that we are working with the correct transaction. The data block and the undo block transaction number need to match (7.3.8), because the transaction is active, so the undo information should not be covered.

The SCN of undo block 2 undo 45 is 12348, which means that the block change represented by the undo block occurs after our query start time scn 12330, so we need to apply undo data to the cloned CR buffer and make it go back a little bit.

Undo block 2Jing 45 is also linked to another undo block 2Jing 90. This is a undo chain and may last for a period of time, consuming a lot of computing resources. The server process must now access the undo block 2150 90 (requesting CBC activities and possibly LRU activities to perform IO calls) and compare the transaction numbers again to make sure they match. They match, now check the SCN. The SCN of undo block 2Jing 90 is 12320, which is before our query start time SCN 12330, so we don't need to apply undo. Without undo, our CR buffer would represent the point-in-time version of block 7678 at SCN 12320, which is earlier than the time we want to query SCN 12330.

Now take a look at the second ITL, which is associated with transaction 5core 2.6. This transaction was committed at the SCN 12350 point in time, after the start time of our query, so we need to apply its undo. From the ITL entry, we will get the ndo block address 2ju 70 and access the undo block. Now compare the transaction number, because the transaction has been committed, the undo information will no longer be protected. Increasing the undo retention period allows udno information to remain longer, but not protected.

Suppose another server process overrides the related transaction undo information in the undo block 2J 70. If this happens, the transaction number of the server process will be recorded and will be recorded as 5.2.6 here. Through the transaction number comparison, we noticed the difference and immediately knew that the undo in undo block 2, CR buffer 70 could not be applied to our CR buffer. At this point, the server process will issue an error message that the snapshot is too old and stop our query. Obviously, the undo block snapshot is too old because it is overwritten by other processes.

Fortunately, the transaction numbers match. The undo in undo block 2 CR buffer 70 was changed at a point in time in SCN 12340, after our query started, so we applied this undo to our CR buffer. The next undo link is empty, so there is no other undo to apply.

Now back to the ITL entry, there is no more ITL to consider, so we have completed the cloning of the data block. Any server process can now access CR buffer 7678, which contains what SCN 12330 time represents.

It is now clear why ITLs is so important, and Oracle's read consistency model, while powerful, necessary, and efficient, is still relatively expensive because it can consume large amounts of CPU and IO, thus slowing down the application's response time. Oracle is well aware of this and uses memory-optimized structures to temporarily store undo information since Oracle 10gr2. These objects are not segment types and are not affected by segment-related CBC and LRU chain activities. In memory, undo is stored in shared pool.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.