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.
|