enq: SQ – contention

Here I will show how to deal with enq:SQ contention wait event. When we select from sequence using NEXTVAL is generate from seq$ table if it is not cached therefore it is costly when there is many concurrent request for it which cause enq SQ-contetion. If the sequence is cached then it will be available in a memory structure and no need to generate the value so by using cache in highly used sequence we can avoid enq:SQ contention wait event.

Here I have shown an example how using cache in sequence can reduce enq:SQ contention wait event.

-- As Oracle User

Create a script name test.ksh

test.ksh

#!/bin/ksh

ORACLE_BASE=/opt/oracle

export ORACLE_BASE

ORACLE_HOME=$ORACLE_BASE/product/11.2.0.3

export ORACLE_HOME

TNS_ADMIN=$ORACLE_HOME/network/admin

export TNS_ADMIN

NLS_LANG=american_america.UTF8

export NLS_LANG

NLS_DATE_FORMAT='YYYY-MM-DD HH24:MI:SS'

export NLS_DATE_FORMAT

PATH=$ORACLE_HOME/bin:$ORACLE_HOME/OPatch:/sbin:$PATH

export PATH

umask 022

export ORACLE_SID=teston

sqlplus scott/tiger @test 0 &

sqlplus scott/tiger @test 1 &

sqlplus scott/tiger @test 2 &

sqlplus scott/tiger @test 3 &

sqlplus scott/tiger @test 4 &

sqlplus scott/tiger @test 5 &

sqlplus scott/tiger @test 6 &

sqlplus scott/tiger @test 7 &

sqlplus scott/tiger @test 8 &

sqlplus scott/tiger @test 9 &

sqlplus scott/tiger @test 10 &

sqlplus scott/tiger @test 11 &

sqlplus scott/tiger @test 12 &

sqlplus scott/tiger @test 13 &

sqlplus scott/tiger @test 14 &

sqlplus scott/tiger @test 15 &

sqlplus scott/tiger @test 16 &

sqlplus scott/tiger @test 17 &

sqlplus scott/tiger @test 18 &

sqlplus scott/tiger @test 19 &

test.sql

spool test&&1

set echo on

declare

num1 number;

begin

for a in 1..10000 loop

select seq1.nextval into num1 from dual;

end loop;

end;

/

exit

spool off

-- At 16:00

-- As scott user

SQL> create sequence seq1;

Sequence created.

Execute test.ksh

[oracle@stagedb ~]$ ./test.ksh

-- As sys user

SQL> select count(*),sum(time_waited)

from v$active_session_history

where event='enq: SQ - contention' and

to_char(SAMPLE_TIME,'DDMMYYYY HH24:MI')>='12012017 16:00'

and to_char(SAMPLE_TIME,'DDMMYYYY HH24:MI')<'12012017 16:10';

COUNT(*) SUM(TIME_WAITED)

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

74 119150

-- At 16:11

-- As scott user

SQL> drop sequence seq1;

Sequence dropped.

SQL> create sequence seq1 cache 100;

Sequence created.

Execute test.ksh

[oracle@stagedb ~]$ ./test.ksh

SQL> select count(*),sum(time_waited)

from v$active_session_history

where event='enq: SQ - contention' and

to_char(SAMPLE_TIME,'DDMMYYYY HH24:MI')>='12012017 16:10'

and to_char(SAMPLE_TIME,'DDMMYYYY HH24:MI')<'12012017 16:20';

COUNT(*) SUM(TIME_WAITED)

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

5 181

-- At 16:21

-- As scott user

SQL> drop sequence seq1;

Sequence dropped.

SQL> create sequence seq1 cache 200;

Sequence created.

SQL> select count(*),sum(time_waited)

from v$active_session_history

where event='enq: SQ - contention' and

to_char(SAMPLE_TIME,'DDMMYYYY HH24:MI')>='12012017 16:20'

and to_char(SAMPLE_TIME,'DDMMYYYY HH24:MI')<'12012017 16:30';

COUNT(*) SUM(TIME_WAITED)

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

0

So you can see here after using the cache value 200 there is no enq:SQ contention wait time.