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.