OCP knowledge point explanation queue, resources and locks

I. queue and shared resourcesShared resources can be accessed by multiple sessions and processes at the same time. Therefore, its access needs to be protected. In Oracle, everything except PGA includes memory, disk, CPU, table, index, and transaction. There are too many types, which are represented by the word "everything. 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 IDTaking 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 StructureContinue 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 and RS is short for Resource Structure ). Each resource structure maintains a list of owners, waiting persons, and conversion persons. For example: 650) this. width = 650; "src =" http://www.bkjia.com/uploads/allimg/131229/1944342604-0.jpg "border =" 0 "alt =" "/> the <TM-432-0> before the resource structure is the queue identifier, it can also be used as a resource structure identifier ). Each owner, the waiting person, and the converter has 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, this topic describes the mode in which a session obtains the resource structure. Details: 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 for" list 3) if the lock has been obtained, but the session is waiting for it to be converted to a different mode, the lock structure will form a resource table on all the resource structures in the list of convertors, the locks in the resource table and resource structure are allocated in the 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 has been created --------------- hour ------------- enqueue_resources 32 32 968 UNLIMITED. 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 tableTo 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. 650) this. width = 650; "src =" http://www.bkjia.com/uploads/allimg/131229/19443415Z-1.jpg "border =" 0 "alt =" "/> is the diagram of the HASH Bucket and the 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 HASH tables, HASH chains, and hashes. The HASH table does not need to be protected 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. 650) this. width = 650; "src =" http://www.bkjia.com/uploads/allimg/131229/19443432K-2.jpg "border =" 0 "alt =" "/> relationships between queues, resource structures, and lock structures, as shown in. Is the result of the following statement published in two sessions: In session 10: sid = 10 pid = 11> insert into a1 values (, 1); 1 row has been created. In session 12: sid = 12 pid = 12> insert into a1 values (, 2); 1 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 can be seen, there is a TM queue. The address is 7B6D5C40 and the Object ID is 6657. This is exactly the A1 table. View V $ LOCK view: sid = 13 pid = 13> select * from v $ lock where sid> = 8; addr kaddr sid ty ID1 ID2 lmode request ctime block -------- ---------- -- ---------- average limit 10 TX 458765 2212 6 0 452 07AF3A074 limit 10 TM 6657 0 3 0 452 07AF837AC 12 TX 327686 2355 6 0 84 07AF3A17C 7AF3A190 12 TM 6657 0 3 0 84 0 the above results show, the TM-6657-0 has two locks, The addresses are 7AF3A088 and 7AF3A190 respectively. Note: The information in is partly from the DUMP of the queue structure. The statement is as follows. If you are interested, try it on your own. Sid = 10 pid = 11> alter session set events 'immediate trace name enqueues level 4'; the length of the hash table is set by _ enqueue_hash, the number of resource structures, also known as the length of the resource table), is set by ENQUEUE_RESOURCES. The number of Lock structures can also be controlled by DBA. The implicit parameter _ enqueue_locks is the queue Lock (also known as Enqueue Lock) that sets the number of Lock structures. The default value is 2230. It can be seen that in X $ KSQEQ, the data being used is displayed in V $ ENQUEUE_LOCK. However, the locks related to the TM and TX queues are different from those in other cases. They are not displayed in the X $ KSQEQ structure unless they have an Enqueue wait, therefore, the V $ ENQUEUE_LOCK on X $ KSQEQ does not have the TX and TM queue locks in use. Oracle uses different structures to manage TX and TM exclusion. TX: X $ KTCXB, kernel transaction control transaction object, basic view of V $ TRANSACTION_ENQUEUE. Its size is controlled by the transactions initialization parameter. We will talk about TX here. Let's look at TM: X $ KTADM, which defines the DML lock for Kernel transaction access. The size of X $ KTADM is defined by DML_LOCKS, that is, the number of tmlocks. X $ KTADM is of little significance to DBA. It can provide us with information such as the LOCK mode, LOCK time, request mode, and session SID. The amount of information is not more than V $ LOCK. The best way to observe the TM and TX locks in use through the above two X $ views is: set linesize 800col KTCXBNAM for a20select * from x $ ktcxb where ktcxblkp in (select kaddr from v $ lock where type = 'tx '); select * from x $ ktadm where ksqlkadr in (select kaddr from v $ lock where type = 'Tm '); you can also see the queue lock usage in V $ RESOURCE_LIMIT: sid = 9 pid = 10> select * from v $ resource_limit where resource_name like 'enqueue % '; RESOURCE_NAME CURRENT_UTILIZATION MAX_UTILIZATION I NITIAL_ALLOCATION LIMIT_VALUE limit --------------- limit ----------- limit 26 30 2230 limit 26 26 1200 UNLIMITED note the relationship between enqueue_locks and transactions and DML_LOCKS. enqueue_locks indicates the total number of queue locks, transactions defines the number of TX locks in the total queue lock. DML_LOCKS is the number of tmlocks in the total queue lock. That is to say, transactions and DML_LOCKS are part of enqueue_locks.

<->

This article from "ye shaochen" blog, please be sure to keep this source http://yeshaochen.blog.51cto.com/3155801/947045