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

Oracle uses the following three locks to manage Sequence:

Row cache lock: in the process of calling Sequnece.nextval. It is obtained when the data dictionary information is physically modified. Occurs on the Sequence that gives the NOCACHE attribute.

Enq:SQ-contention (SQ lock): within the scope of cache on memory (cache). Own this lock during a call to Sequence.nextval. Occurs on the Sequence that gives CACHE ambiguity.

DFS lockhandle (SV lock): a quiet situation in which the order between nodes on RAC is guaranteed. Occurs on the Sequence during the call to Sequence.nextval that has been assigned to the CACHE+ORDER.

You can see a description of the lock through v$lock_type:

Select * from v$lock_type a where a.TYPE in ('SQ' 'SV') TYPE: SQNAME: Sequence CacheID1_TAG: object # ID2_TAG: 0IS_USER: NODESCRIPTION: Lock to ensure that only one process can replenish the sequence cache- -- TYPE: SVNAME: Sequence OrderingID1_TAG: object # ID2_TAG: 0IS_USER: NODESCRIPTION: Lock to ensure ordered sequence allocation in RAC mode

During the call to nextval by Sequence with the CACHE attribute, the SQ lock should be acquired in SSX mode. Many sessions hold enq:SQ-contention events when contention occurs to acquire SQ locks at the same time. The P _ 2 value of the enq:SQ-contention event is the OBJECT ID of Sequence. Therefore, if you use the combination of P _ 2 value and DBA_OBJECTS, you can know which Sequence has the waiting phenomenon.

The cache value given by the creation Sequence is small, and there is a tendency to increase the enq:SQ-contention waiting. When the CACHE value is small, the prior CACHE value in memory is quickly exhausted. At this time, you need to physically modify the data dictionary information, and then perform the action of CACHE again. In the meantime, the wait time for related enq:SQ-contention events is also extended because you always have a SQ lock; unfortunately, when you create a Sequence, the default value of CACHE is set to a smaller value of 20. Therefore, when creating a heavily used Sequence, the cache value should be higher than 1000.

Test:

1. Create seq:drop sequence app.info_seq;create sequence app.info_seq start with 1 maxvalue 99999999999999999999999999999999999999 minvalue 1 nocycle order;2. Create the procedure create or replace procedure pseq (a number) asnext_sql number;beginfor x in 1.. a loop execute immediate 'select app.info_seq.nextval from dual' into next_sql;end loop;end;/3. Execute simultaneously in session 1 and sesseion2: exec pseq (400000) 4. Wait event: SQL > select a.EVENT, a.SID, a.P1, a.P2 from v$session a where a.WAIT_CLASS# 6 EVENT SID P1 2 log file parallel write-log file parallel write 11 2 144enq: SQ-contention 36 1397817350 65009enq: SQ-contention 37 1397817350 65009SQL*Net message to client 66 1413697536 1SQL > select chr (bitand (p1 -16777216) / 16777215) | | 2 chr (bitand (p1, 16711680) / 65535) "Lock", 3 bitand (p165535) "Mode" 4 from v$session 5 where event = 'enq: SQ-contention' Lock Mode--SQ 6SQ 6Mode Value Description 1 Null mode 2 Sub-Share 3 Sub-Exclusive 4 Share 5 Share/Sub-Exclusive 6 ExclusiveSQL > select d.ownerjournal d.objectwriter name D.object_type from dba_objects d where object_id in (2 select p2 from v$session a where a.WAIT_CLASS# 6 and event = 'enq: SQ-contention') OWNER OBJECT_NAME OBJECT_TYPE--APP INFO_SEQ SEQUENCE

In addition, the enq:SQ-contention wait event sometimes occurs when many sessions are created at the same time. The reason is that the V$SESSION.AUDSID (auditingsessionid) column values are created using Sequence. After creating a new session, Oracle uses the nextval of the Sequence named SYS.AUDSES$ to create the AUDSID value. The default value for the CACHE size of SYS.AUDSES$Sequence is set to 20. When many sessions connect at the same time, the CACHE size of the SYS.AUDSES$Sequence can be increased to 1000, which solves the enq:SQ-contention waiting problem. The default is 10000 under 10g and 200.11g.

When creating Sequence on RAC, when the CACHE attribute is given, if the ORDER attribute is not assigned, each node will CACHE different ranges of Sequence values to memory. For example, in a two-node RAC environment, when creating a Sequence with a cache value of 100, Node 1 uses Node 1-100.2. If quence is used incrementally between two nodes, the following ORDER attribute must be assigned.

SQL > create sequence ordereQuence cacke 100 order

In the case of a Sequence,Oracle that has been assigned the CACHE+ORDER attribute, a SV lock is used for row synchronization. That is, when you call nextval on a Sequence that is given the ORDER attribute, you should have the SV lock in SSX mode. During the acquisition of SV locks, if contention occurs, instead of waiting for the rowcachelock event or enq:SQ-contention event, wait for the event named DFSlockhandle. For this reason, there are no enq:SQ-contention-like events on the V$EVENT_NAME view. DFSIockhandle events are events waiting in the process of acquiring locks for synchronization of row high-speed buffers or library high-speed buffers in addition to high-speed buffer synchronization in OPS or RAC environments. To preserve the Sequence order between multiple nodes, the lock should be acquired globally, and DFSlockhandle waits occur during this process. In the process of acquiring SV locks, the PI and P _ 2 values of DFSlockhandle waiting events are the same as those of enq:SQ-contertion waiting events, so you can confirm whether it is a SV lock from the Pi value, and which Sequenced has been waiting through the P _ 2 value. When the SV lock contention occurs, the solution is the same as the parents of the SQ lock, that is, to adjust the cache value properly, which is the only way.

In multi-node environment such as RAC, the impact of cache value of Sequence on performance is more serious than that of single node. Therefore, give the CACHE+NOORDER attribute as much as possible and give it a sufficiently large cache value. If order is required, the CACHE+ORDER attribute must be assigned. But at this time, in order to ensure the order, the exchange of data between instances occurs constantly. As a result, the relative performance is slightly poor when the NOORODER attribute is given.

Depending on the attributes assigned when the Sequence is created, the result of the wait event is as follows:

NOCACHE: row cacke lockCACHE+NOORDER: enQ: SQ-contentionCACHE+ORDER (RAC): DTS lock Handle

If the sequence is part of the index, the high concurrency environment will lead to index splitting. The common wait events are: enq: TX-index contention

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.