This example shows how to deal with the enq: SQ – contention wait event. It was tested on Oracle 11.2. First I created a sequence and queried the amount of time already spent on this wait event:
SQL> create sequence seq1
2 /
Sequence created.
SQL> select time_waited/100
2 from v$system_event
3 where event = 'enq: SQ - contention'
4 /
no rows selected
SQL>
Then I created the following UNIX shell script to run test.sql 20 times simultaneously:
Oracle 11.2: cat test.ksh
#!/bin/ksh
export ORAENV_ASK=NO
export ORACLE_SID=ORCL
. oraenv
sqlplus / @test 0 &
sqlplus / @test 1 &
sqlplus / @test 2 &
sqlplus / @test 3 &
sqlplus / @test 4 &
sqlplus / @test 5 &
sqlplus / @test 6 &
sqlplus / @test 7 &
sqlplus / @test 8 &
sqlplus / @test 9 &
sqlplus / @test 10 &
sqlplus / @test 11 &
sqlplus / @test 12 &
sqlplus / @test 13 &
sqlplus / @test 14 &
sqlplus / @test 15 &
sqlplus / @test 16 &
sqlplus / @test 17 &
sqlplus / @test 18 &
sqlplus / @test 19 &
Oracle 11.2:
This is test.sql. It accepts a parameter and uses it to generate a SPOOL file name. Then it selects 10,000 values from the sequence created earlier:
Oracle 11.2: cat 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
Oracle 11.2:
I ran test.ksh and waited until all the SPOOL files had been created.
(I first created this post in 2014. Then I checked it for relevance in 2017. At this point it was not obvious to me what the start_test1.sql, start_test2.sql, end_test1.sql and end_test2.sql files were for. I'm guessing they were used to create and drop the sequences before and after each test):
Oracle 11.2: ls -ltr
total 36
-rwxr--r-- 1 oracle dba 487 Apr 11 13:48 test.ksh
-rw-r--r-- 1 oracle dba 147 Apr 11 13:55 end_test1.sql
-rw-r--r-- 1 oracle dba 147 Apr 11 13:56 end_test2.sql
-rw-r--r-- 1 oracle dba 150 Apr 11 13:56 start_test1.sql
-rw-r--r-- 1 oracle dba 161 Apr 11 13:56 start_test2.sql
-rwxr--r-- 1 oracle dba 154 Apr 11 13:57 test.sql
-rw-r--r-- 1 oracle dba 184 Apr 11 14:43 start_test1.lst
-rw-r--r-- 1 oracle dba 206 Apr 11 16:12 test16.lst
-rw-r--r-- 1 oracle dba 206 Apr 11 16:12 test1.lst
-rw-r--r-- 1 oracle dba 206 Apr 11 16:12 test4.lst
-rw-r--r-- 1 oracle dba 206 Apr 11 16:12 test6.lst
-rw-r--r-- 1 oracle dba 206 Apr 11 16:12 test12.lst
-rw-r--r-- 1 oracle dba 206 Apr 11 16:12 test9.lst
-rw-r--r-- 1 oracle dba 206 Apr 11 16:12 test13.lst
-rw-r--r-- 1 oracle dba 206 Apr 11 16:12 test19.lst
-rw-r--r-- 1 oracle dba 206 Apr 11 16:12 test3.lst
-rw-r--r-- 1 oracle dba 206 Apr 11 16:12 test2.lst
-rw-r--r-- 1 oracle dba 206 Apr 11 16:12 test15.lst
-rw-r--r-- 1 oracle dba 206 Apr 11 16:12 test17.lst
-rw-r--r-- 1 oracle dba 206 Apr 11 16:12 test11.lst
-rw-r--r-- 1 oracle dba 206 Apr 11 16:12 test0.lst
-rw-r--r-- 1 oracle dba 206 Apr 11 16:12 test10.lst
-rw-r--r-- 1 oracle dba 206 Apr 11 16:12 test14.lst
-rw-r--r-- 1 oracle dba 206 Apr 11 16:12 test5.lst
-rw-r--r-- 1 oracle dba 206 Apr 11 16:12 test8.lst
-rw-r--r-- 1 oracle dba 206 Apr 11 16:12 test7.lst
-rw-r--r-- 1 oracle dba 206 Apr 11 16:12 test18.lst
Oracle 11.2:
Then I looked in one of them to see that it had worked OK:
Oracle 11.2: cat test18.lst
SQL> declare
2 num1 number;
3 begin
4 for a in 1..10000 loop
5 select seq1.nextval into num1 from dual;
6 end loop;
7 end;
8 /
PL/SQL procedure successfully completed.
SQL> exit
Oracle 11.2:
I rechecked the amount of time spent on the wait event. This is the time Oracle spends waiting for the next value to come back from the sequence. It had gone from zero to 436.8 seconds as 20 sessions had been requesting values at the same time:
SQL> select time_waited/100
2 from v$system_event
3 where event = 'enq: SQ - contention'
4 /
TIME_WAITED/100
---------------
436.8
SQL> drop sequence seq1
2 /
Sequence dropped.
SQL>
I recreated the sequence but this time I told Oracle to cache 10,000 values in memory:
SQL> create sequence seq1 cache 10000
2 /
Sequence created.
SQL> select time_waited/100
2 from v$system_event
3 where event = 'enq: SQ - contention'
4 /
TIME_WAITED/100
---------------
436.8
SQL>
I reran test.ksh then rechecked the time spent on the wait event. This time it had increased by less than 20 seconds:
SQL> select time_waited/100
2 from v$system_event
3 where event = 'enq: SQ - contention'
4 /
TIME_WAITED/100
---------------
455.15
SQL> drop sequence seq1
2 /
Sequence dropped.
SQL>
I recreated the sequence but this time I told Oracle to cache 200,000 values in memory:
SQL> create sequence seq1 cache 200000
2 /
Sequence created.
SQL> select time_waited/100
2 from v$system_event
3 where event = 'enq: SQ - contention'
4 /
TIME_WAITED/100
---------------
455.15
SQL>
I reran test.ksh then rechecked the time spent on the wait event. This time it had not increased at all:
SQL> select time_waited/100
2 from v$system_event
3 where event = 'enq: SQ - contention'
4 /
TIME_WAITED/100
---------------
455.15
SQL> drop sequence seq1
2 /
Sequence dropped.
SQL>
So, when you create a sequence, if you set the cache value appropriately, you can reduce the time spent on this wait event or maybe even eliminate it altogether.
No comments:
Post a Comment