OCP knowledge point explanation queue, resources and locks

1. Shared resources of a queue can be accessed by multiple sessions and processes at the same time. Therefore, access to these resources must be protected. In Oracle, apart from PGA, everything (including memory, disk, CPU, table, index, transaction, and so on,

1. Shared resources of a queue can be accessed by multiple sessions and processes at the same time. Therefore, access to these resources must be protected. In Oracle, apart from PGA, everything (including memory, disk, CPU, table, index, transaction, and so on,

I. queue and shared resources

Shared resources can be accessed by multiple sessions and processes at the same time. Therefore, its access needs to be protected. In Oracle, apart from PGA, all things (including memory, disk, CPU, table, index, transaction, etc., there are too many types, website space, all represented by the word "things) all are shared resources. When multiple processes or sessions operate on shared resources, a queue is required. The queue is the queue ). Different teams are required to access different shared resources. It can be said that there are as many queues as there are shared resources to be protected. The queue name is generally composed of two bytes, such as TM, TX, JQ ,....... For more information about the types and names of all queues, see the appendix in the V $ LOCK view.

Ii. Queue ID

Taking TM as an example, it is a DML queue lock. When you perform DML operations on a table, you need to queue here first. The formal point is that you need to obtain the TM queue lock first. TM is also called a table lock, because it is obtained mainly by performing operations on the table. If there are 1000 tables in the database, there is not only one queue for these one thousand tables. If so, it is not reasonable. 1000 tables should have 1000 TM queues. If one thousand queues are called TM, it is difficult to distinguish them in future operations. Therefore, the names of these one thousand TM queues must be named respectively. This name is also known as the queue identifier. The name format of the TM queue is: TM-OID-0. The OID is the Object ID, that is, the Object ID. The last part is generally 0. If the OID of the AA table is 6636, Its Queue ID is the TM-6636-0.

The naming formats of each queue are different. In general, "queue name-ID1-ID2", ID1 and ID2 are two parameters respectively. For TM queue, ID1 is OID and ID2 is 0.

Iii. Resource Structure

Continue with our above assumptions. For example, if the database has one thousand tables, this will correspond to one thousand TM queues. However, only when a table is operated can related queue information be created for it. For example, if a session publishes an update operation on the AA table, you need to create the TM queue information for AA in the SGA. These TM Information related to AA is also known as "Resource Structure" (KSQRS, KSQ is short for Kernel service queues, RS is short for Resource Structure, that is, Resource Structure ). Each resource structure maintains a list of owners, waiting persons, and conversion persons. For example:

Before the resource structure The queue ID can also be used as the resource structure ID (resource ID ).

Each owner, the waiting person, and the converter have a lock structure (Ksqlk). Simply put, each owner, the waiting person, and the converter have a linked list, which consists of sessions, lock modes, and other information, website space, which specifically describes what session gets the resource structure in what mode. The details are as follows:

1) if the session gets the lock, the lock structure will be on the owner list

2) If the session is waiting for the lock to be obtained, the lock structure will be on the waiting list.

3) if the lock has been acquired, but the session is waiting for it to be converted to a different mode, the lock structure will be on the list of Transcoder

All resource structures constitute a resource table, and the locks in the resource table and resource structure are allocated in SGA. The total number of rows in the resource table is determined by the initialization parameter ENQUEUE_RESOURCES, And the rows in the resource table can be seen in X $ KSQRS. In use, it will be displayed in V $ RESOURCE. You can also view the limit and usage of the resource structure quantity in v $ resource_limit:

Sid = 9 pid = 10> select * from v $ resource_limit where resource_name = 'enqueue _ resources ';

RESOURCE_NAME CURRENT_UTILIZATION MAX_UTILIZATION INITIAL_ALLOCATION LIMIT_VALUE

-------------------------------------------------------------------------------------------------

Enqueue_resources 32 32 968 UNLIMITED

We can see that currently 32 Resource structures are used, and a maximum of 32 Resource structures are used. The initialization parameters are allocated with 968 resource structures. The maximum usage is UNLIMITED. Because of the algorithm used by Oracle 9i, the total number of rows we see in X $ KSQRS is not 968, but 992. To ensure reuse, unused Resource structures in the Resource table are placed in a connection List called Resource Free List. We can release some update statements without submitting them, so that the occupied TM and TX resource structures will never be released. Observe that the resource structure of X $ KSQRS increases, but the total number of rows in the view remains unchanged, or 992. The new TM and TX resource structures occupy other released resource structures.

Iv. Resource Structure hash table

To quickly find a resource structure in the resource table, Oracle still needs to use the HASH algorithm. Oracle calculates the HASH value based on the resource ID. Of course, like the Library cache, the HASH values of the occupied resource structure in the resource table constitute a HASH Buckut.

It is the diagram of the HASH Bucket and resource structure. It can be seen that it is very similar to the Library cache. HASH Algorithms? All HASH algorithms have a lot in common. We can see that to access the Hash Bucket, the Enqueue hash chain is required. It needs to protect the HASH table and the Hash chain after each Hash Bucket. Its quantity is controlled by the implicit parameter _ enqueue_hash_chain_latches. The Enqueue hash chain latches are similar to the Buffer cache chain latches. All of them are one-stick to manage multiple hash buckets, but the Enqueue hash chain is more special. In a single CPU environment, there is only one, but all hash buckets need to be managed. This is because of the contention of the queue, bid is much smaller than Buffer.

All HASH Algorithms in Oracle must have hashed tables, hashes, and hashes. The website space and HASH tables do not need to be protected, this is because the HASH table is an array of fixed sizes. Each array element is a Hash Bucket. The hash value of each hash bucket is also set in advance. Therefore, there are no changes to the hash bucket, such as inserting a hash bucket, modifying the hash value of a hash bucket, or deleting an unused hash bucket. These operations do not exist. Therefore, since there is no modification operation, the hash table does not need to be protected. What needs to be protected is the chain after each hash bucket. Multiple objects can be attached to the chain after each bucket. We may add or delete objects to or from the chain. Since there are modifications, we need to protect them.

The length of the queue resource Hash table is controlled by _ enqueue_hash. Its initial value is the SESSION parameter, and the initial value is 375. There are a total of 992 rows in the resource table, and there are only 375 hash buckets. Therefore, multiple resource structures must be placed behind a hash bucket. If the value of ENQUEUE_RESOURCES has been added, that is, the resource structure is more than the default value, but the value of the _ enqueue_hash parameter that controls the hash table length does not increase. This means that more resource structures need to be attached to each hash bucket, which may be caused by competition of the Enqueue hash chain latches. How to observe the use of latches is described in the next chapter. The initial value of _ enqueue_hash is determined by the Sessions parameter. The formula is as follows: (sessions-10) * 2) + 55. The default value is (170-10) * 2) + 55, it is equal to 375 and does not increase with the increase of ENQUEUE_RESOURCES.

The relationship between the queue, resource structure, and lock structure is as follows. Is the result of the following statement published in two sessions:

In session 10: sid = 10 pid = 11> insert into a1 values (, 1 );

One row has been created.

In session 12: sid = 12 pid = 12> insert into a1 values (2, 2 );

One row has been created.

View the hash table View:

Sid = 9 pid = 10> select * from v $ resource where type = 'TT ';

Addr ty ID1 ID2

------------------------------

7B6D5C40 TM 6657 0

We can see that there is a TM queue with the address 7B6D5C40 and the Object ID 6657. This is exactly the A1 table.

View the V $ LOCK View:

Sid = 13 pid = 13> select * from v $ lock where sid> = 8;

Addr kaddr sid ty ID1 ID2 LMODE REQUEST CTIME BLOCK

----------------------------------------------------------------------------------------