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.