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

Oracle Study-- enq:SQ contention wait event

Discover the "enq:SQ contention" wait event through AWR Report:

Application environment:

Transferred from: http://www.xuebuyuan.com/1027129.html

Enq:SQ contention/row cache lock/DFS lock handle (SV) these three wait events are all related to the Sequence of Oracle.

Oracle Sequence Cache parameter description

Http://blog.csdn.net/xujinyang/article/details/6831361

33 common wait events in Oracle

Http://blog.csdn.net/xujinyang/article/details/6882035

Oracle uses the following three locks to manage sequence

(1) row cache lock

In the process of calling sequence.nextval, the data dictionary information is obtained when it is physically modified, which occurs on the sequence of the nocache attribute.

(2) SQ lock-- enq: SQ

Within the scope of the in-memory cache (cache), this lock is owned during the call to sequence.nextval, which occurs on the sequence that gives the cache+noorder attribute.

(3) SV lock-DFS lock handle

When the order between the nodes on the RAC is guaranteed, it is obtained during the call to sequence.nextval and occurs on the sequence with the cache+order attribute.

During the call to nextval by sequence with CACHE attribute, the SQ lock should be acquired in SSX mode. During the contention process for many sessions to acquire SQ lock at the same time, if contention occurs, they wait for enq:SQ-contention.

The P _ 2 value of the enq:SQ-contention event is the object ID of sequence, so if you use the combination of P _ 2 value and DBA_OBJECTS, you can know which Sequence has a waiting object.

The cache value given by the creation Sequence is small, there is a tendency for enq:SQ-contention to wait for an increase, the cache value is small, and the value of the prior CACHE in memory is quickly exhausted. At this time, it is necessary to physically modify the data dictionary information and perform the work of CACHE again. During this period, because you always have to have a SQ lock, the waiting time of the corresponding Enq:SQ-contention event will also be prolonged. Unfortunately, when you create a Sequence The default value of the CACHE value is set to less than 20, so when creating the most used Sequence, the cache value should be higher than 1000.

Occasionally, many sessions are created at the same time, and sometimes enq:SQ-contention wait events occur. The reason is that the V$SESSION.AUDSID (auditing sessionid) column value is created using Sequence. After oracle creates a new session, it uses the nextval named SYS.AUDSESS$ sequence to create the AUDSID value. The default CACHE size of SYS.AUDSESS$ Sequence is set to 20. Many sessions connect at the same time, and the CACHE size of SYS.AUDSESS$ sequence can be expanded to 1000. This can solve the problem of enq:SQ-contention waiting.

When you create a Sequence on RAC, when the CACHE attribute is given:

(1) if the ORDER attribute is not assigned, each node will CACHE a different range of Sequence values to memory. For example, in a RAC environment with two nodes, when creating a sequence with a cache value of 100, 1 node will use 1-100 Magi and 2 nodes will use 101-200. When in use, take the sequence from their respective nodes.

(2) if there is an incremental use of sequence between two nodes, the following ORDER attribute must be assigned.

SQL > Create sequence ordered_sequence cache 100 order

In the case of order, the sequence taken by 2 nodes is incremented. There are examples below to illustrate both cases.

If the sequence that has been assigned the CACHE+ORDER attribute, oracle uses the SV lock for row synchronization, that is, when calling nextval on the sequence assigned the ORDER attribute, you should have the SV lock in SSX mode. In the process of acquiring the SV lock, if a contention occurs, instead of waiting for ROW CACHE or enq:SQ-contention, but for an event named DFS lock handle, which is why there is no such thing as "enq:SV-contention" in the V$EVENT_NAME view.

The DFS lock handle event is a waiting event in the process of acquiring a lock synchronously in a row high-speed buffer or a library high-speed buffer in addition to high-speed buffer synchronization in OPS or RAC environments. If the global lock is guaranteed, DFS look handle waiting will occur in this process. The P1 and P2 values of the SV waiting event occurred in the process of acquiring the DFS lock handle lock are the same as those of the enq:SQ-contention waiting event (p1 waiting modesigned namespace, p2 waiting object #). Therefore, the value of P 1 can confirm whether it is a SV lock, and P 2 can confirm which Sequence has been waiting.

The solution to the SV lock contention problem is the same as in the case of SQ locks, except that the cache value is adjusted appropriately, which is the only way.

Test the Sequence of 1:NOORDER

Node1:

SQL > create sequence seq_noorder start with 1 increment by 1 cache 20 NOORDER

Sequence created.

SQL > select seq_noorder.nextval from dual

NEXTVAL

-

one

SQL > /

NEXTVAL

-

two

SQL > /

NEXTVAL

-

three

Node2:

SQL > select seq_noorder.nextval from dual

NEXTVAL

-

twenty-one

SQL > /

NEXTVAL

-

twenty-two

SQL > /

NEXTVAL

-

twenty-three

Node2 doesn't start with 4, it starts with 21, because node1 has already cache 20.

Test 2: Sequence for ORDER

Node1:

SQL > create sequence seq_order start with 1 increment by 1 cache 20 ORDER

Sequence created.

SQL > select seq_order.nextval from dual

NEXTVAL

-

one

SQL > /

NEXTVAL

-

two

SQL > /

NEXTVAL

-

three

Node2:

SQL > select seq_order.nextval from dual

NEXTVAL

-

four

SQL > /

NEXTVAL

-

five

SQL > /

NEXTVAL

-

six

After the Order is specified, the sequence is taken sequentially.

Sequence synchronization between RAC is discussed in the following link:

Sequences in Oracle 10g RAC

Http://www.pythian.com/news/383/sequences-in-oracle-10g-rac/

How does RAC synchronize sequences?

In Oracle 10g RAC, if you specify the "ordered" clause for a sequence, then a global lock is allocated by the node when you access the sequence.

This lock acquisition happens only at the first sequence access for the node (A), and subsequent uses of the sequence do not wait on this lock. If another node (B) selects from that sequence, it requests the same global lock and once acquired it returns

The sequence's next value.

The wait event associated with this activity is recorded as "events in waitclass Other" when looked in gv$system_event. So much for event groups, it couldn't be more obscure. That view shows overall statistics for the session.

However if you look in the gv$session_wait_history it shows as "DFS lock handle" with the "p1" parameter been the object_id of the sequence. This second view has a sample of the last 10 wait events for a session.

In a SQL_TRACE with waitevents (10046 trace) it will be a "DFS lock handle" but in AWR or statspack reports it will be "events in waitclass Other" So much for consistency.

Summary:

When the CACHE attribute is not assigned, regardless of whether the ORDER property or the RAC environment has been waiting for the ROW CACHE event, whether ROW CACHE LOCK can be used globally, single-instance environment or multi-instance environment can occur at the same time.

Oracle Sequence defaults to NOORDER. If ORDER; is set to have no effect in a single instance environment, in the RAC environment, multiple instances actually cache the same sequence. In this case, when multiple instances send the sequence simultaneously, there will be a brief competition for resources to synchronize multiple instances. Due to the poor performance compared to noorder, do not use ORDER unless necessary in the RAC environment, especially to avoid NOCACHE ORDER combinations.

In the multi-node environment such as RAC, the cache value of sequence has a more serious impact on the performance than the single-node environment, so we should give the CACHE+NOORDER attribute as much as possible and give it a large enough value.

But if Cache is used, if DB crashes at this time, sequence will start over after cache, and sequence that is not used in cache will be skipped. That is, sequence is discontinuous. So only when the multi-node peak concurrency is very large and the continuity requirement is not high, use: noorder + cache

According to the attributes assigned when the Sequence is created, the result of the wait event is as follows:

NOCACHE:-- > row cache lock

CAHCE+NOORDER-- > enq: SQ-contention (SQ lock)

CACHE+ORDER (RAC):-> DFS look handle (SV lock)

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.