An
interesting question came up this morning with a fellow college about the
performance concern on using One Oracle sequence to generate primary key values
across many tables.
There is
only one sequence cache/latch per instance. So regardless if you have one or
thousand sequences this won’t change. The hot spot would be sys.seq$ - since
every now and then when we exhaust the value from cache, we update the value in
the dictionary (recursive sql) and that could produce contention in multi user
scenario.
I ran this
benchmark and the setup:
create sequence
seq1;
create sequence
seq2;
create sequence
seq3;
create sequence
seq4;
create sequence
seq5;
create sequence
seq6;
create sequence
seq7;
create sequence
seq8;
create sequence
seq9;
create sequence
seq10;
demo@ORA12C>
create or replace procedure p(x int default NULL)
2 as
3
l_value int;
4
l_str long;
5
begin
6
if x is null then
7
l_str :=' select seq1.nextval from dual ';
8
else
9
l_str :=' select seq'||x||'.nextval from dual ';
10
end if;
11
12
for i in 1..100000
13
loop
14
execute immediate l_str into l_value;
15 end loop;
16 end;
17 /
Procedure
created.
Ran two invocations of the procedure,
first with ten concurrent sessions each with same sequence, then next with ten
concurrent sessions each with its own/different sequences.
demo@ORA12C>
variable x number
demo@ORA12C>
exec :x := dbms_workload_repository.create_snapshot;
PL/SQL
procedure successfully completed.
demo@ORA12C>
print x
X
----------
3760
demo@ORA12C>
declare
2
l_job int ;
3
begin
4
for i in 1..10
5
loop
6
dbms_job.submit( l_job, ' begin p; end; ');
7
end loop;
8
commit;
9 end;
10 /
PL/SQL
procedure successfully completed.
demo@ORA12C>
exec :x := dbms_workload_repository.create_snapshot;
PL/SQL
procedure successfully completed.
demo@ORA12C>
print x
X
----------
3761
demo@ORA12C>
declare
2
l_job int ;
3
begin
4
for i in 1..10
5
loop
6
dbms_job.submit( l_job, ' begin p('||i||'); end; ');
7
end loop;
8
commit;
9 end;
10 /
PL/SQL
procedure successfully completed.
demo@ORA12C>
exec :x := dbms_workload_repository.create_snapshot;
PL/SQL
procedure successfully completed.
demo@ORA12C>
print x
X
----------
3762
demo@ORA12C>
The AWR report for the first execution
(one sequence for all) report this:
Top 10
Foreground Events by Total Wait Time
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Total Wait Avg % DB Wait
Event Waits Time (sec) Wait
time Class
------------------------------
----------- ---------- --------- ------ --------
enq: SQ - contention 175,240 144
821.53us 43.7 Configur
DB CPU 51 15.5
library cache: mutex X 4,874 19
3.89ms 5.8 Concurre
db file
sequential read 509 3.6
7.08ms 1.1 User I/O
log file
sync 12 .7
59.11ms .2 Commit
cursor: pin
S 621 .6
895.18us .2 Concurre
latch free 1,009 .2
157.53us .0 Other
control file
sequential read 198 .1
582.89us .0 System I
Disk file
operations I/O 19 .1
4.48ms .0 User I/O
kksfbc child
completion 1 0
49.52ms .0 Other
The AWR report for the second execution
(different sequence for each) report this:
Top 10
Foreground Events by Total Wait Time
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Total Wait Avg % DB Wait
Event Waits Time
(sec) Wait time Class
------------------------------
----------- ---------- --------- ------ --------
buffer busy
waits 6,031 66.3
11.00ms 31.8 Concurre
DB CPU
50.9 24.4
latch free 1,074 4.3
4.05ms 2.1 Other
latch: shared
pool 548 2.4
4.40ms 1.2 Concurre
db file
sequential read 215 1.1
5.34ms .5 User I/O
latch: cache
buffers chains 66 .3
4.12ms .1 Concurre
control file
sequential read 198 .1
530.98us .1 System I
db file
scattered read 14 .1
5.92ms .0 User I/O
library cache
load lock 8 .1
8.12ms .0 Concurre
log file sync 12 0
4.07ms .0 Commit
So yes, there could be some contention
introduced by having a single sequence object for everything.
