Guid vs Sequences – Keep Learning Keep Living

Hi Everyone,

I haven’t written any posts lately because there is so much going on in my life and biggest change is I am moving to the Netherlands. it is a big change for me and my family but I hope everything will be fine (if we can find a house to rent because it is really a problem 🙂 ).

Before the end of 2023, I would like to write about something small. Sequences vs Guid on database. I discussed this topic on oracle community many years ago and saw it lately and wanted to point a few things.

SYS_GUID() function generates guid on Oracle database and it might be quite fast, even faster than sequences since sequences run internal commits to provide consistency but it has some disadvantages.

1- Space usage

Guids are 16 bytes data. smallest number to use 16 bytes should be 10000000000000000000000000001. you can use vsize to check the sizes:

select vsize(sys_guid()) , vsize(10000000000000000000000000001)from dual ;
VSIZE(SYS_GUID()) VSIZE(99999999999999999999999999999)
----------------- ------------------------------------
               16                                   16

select vsize(sys_guid()) , vsize(10000000000000000000000000001)from dual ;

VSIZE(SYS_GUID()) VSIZE(99999999999999999999999999999)

----------------- ------------------------------------

16 16

this is a huge number, probably there won’t be much system which use so many numbers as keys. that means if you start from 1 on your sequence, for billion of rows, it will require relatively small amount of space to store numbers but guids will require much more:

create table tmp_guid as select sys_guid() id from dual connect by level <= 1000000;

create sequence seq cache 100000;
create table tmp_seq as select seq.nextval id from dual connect by level <= 1000000;

select segment_name, round(bytes/1024/1024) size_in_KB from user_Segments where segment_name in ('TMP_SEQ', 'TMP_GUID');
SEGMENT_NAME  SIZE_IN_KB
------------- ----------
TMP_GUID      25
TMP_SEQ       13

create table tmp_guid as select sys_guid() id from dual connect by level <= 1000000;

create sequence seq cache 100000;

create table tmp_seq as select seq.nextval id from dual connect by level <= 1000000;

select segment_name, round(bytes/1024/1024) size_in_KB from user_Segments where segment_name in ('TMP_SEQ', 'TMP_GUID');

SEGMENT_NAME SIZE_IN_KB

------------- ----------

TMP_GUID 25

TMP_SEQ 13

it almost double the size.

2- Performance

This one is tricky. Let’s do a test:

drop sequence seq; 
create sequence seq cache 100000; 

set timing on

declare
  rr raw(32767);
begin
  for i in 1..100000 loop
   rr := sys_guid();
  end loop;
end;
/

Elapsed: 00:00:00.125

declare
  rr simple_integer := 0;
begin
  for i in 1..100000 loop
   rr := seq.nextval;
  end loop;
end;
/

Elapsed: 00:00:00.567

drop sequence seq;

create sequence seq cache 100000;

set timing on

declare

rr raw(32767);

begin

for i in 1..100000 loop

rr := sys_guid();

end loop;

end;

Elapsed: 00:00:00.125

declare

rr simple_integer := 0;

begin

for i in 1..100000 loop

rr := seq.nextval;

end loop;

end;

Elapsed: 00:00:00.567

for 100.000 generation, guid takes 0.1 second and sequence takes 0.5 second. ratio is big but time difference is not much significant. still guid is 5 times faster or it seems so 🙂 Guid generation in oracle relies on operating system. sys_guid() function is actually calling OS guid generation function. the example above is from a database which resides on Linux host. here is the results from a db which resides on Windows 11 host:

set timing on

declare
  rr raw(32767);
begin
  for i in 1..100000 loop
   rr := sys_guid();
  end loop;
end;
/

Elapsed: 00:00:07.778

declare
  rr simple_integer := 0;
begin
  for i in 1..100000 loop
   rr := seq.nextval;
  end loop;
end;
/
Elapsed: 00:00:01.000

set timing on

declare

rr raw(32767);

begin

for i in 1..100000 loop

rr := sys_guid();

end loop;

end;

Elapsed: 00:00:07.778

declare

rr simple_integer := 0;

begin

for i in 1..100000 loop

rr := seq.nextval;

end loop;

end;

Elapsed: 00:00:01.000

suddenly, guid took 7.7 seconds. almost 80 times slower on Windows OS. the reason of this can be seen by querying data:

select sys_guid() from dual connect by level <=5;

Output On Linux:
0DA41BD1A0D<strong>3</strong>621DE0634406C40A46BD
0DA41BD1A0D<strong>4</strong>621DE0634406C40A46BD
0DA41BD1A0D<strong>5</strong>621DE0634406C40A46BD
0DA41BD1A0D<strong>6</strong>621DE0634406C40A46BD
0DA41BD1A0D<strong>7</strong>621DE0634406C40A46BD

Output On Windows:
22C5DAC9C6D646CF938C3669429D338A
A9287E15E31F42308823656212D0E49B
154F601EFD244B3886D07CF5F95DA799
97C2D3861FA7493A8CAEEF530D17DC2F
8234A4410800413A9C9C90AC2CF201E7

select sys_guid() from dual connect by level <=5;

Output On Linux:

0DA41BD1A0D3621DE0634406C40A46BD

0DA41BD1A0D4621DE0634406C40A46BD

0DA41BD1A0D5621DE0634406C40A46BD

0DA41BD1A0D6621DE0634406C40A46BD

0DA41BD1A0D7621DE0634406C40A46BD

Output On Windows:

22C5DAC9C6D646CF938C3669429D338A

A9287E15E31F42308823656212D0E49B

154F601EFD244B3886D07CF5F95DA799

97C2D3861FA7493A8CAEEF530D17DC2F

8234A4410800413A9C9C90AC2CF201E7

Linux generates guid in an order (which is better by the way, you can use sorting in sql) but windows guid is more randomize. because of this randomization, windows guid generation takes much more time. So be careful on this one.

Guid is really a good option and much faster then sequences but will require more disk space. Also, it is not good looking to human eye but of course this shouldn’t be a reason.

Also, sys_guid() funciton almost has no concurrency problems. if you start to call a sequence from different sessions so many times (even the cache size is big) there will be some library cache: mutex X Concurrency. This will not happen on guids. here is an example:

truncate table tmp_guid;

begin
  for i in 1..5 loop
    dbms_scheduler.create_job('aj_test'||i, job_type=>'PLSQL_BLOCK', job_action=> 'begin insert into tmp_guid select sys_guid() from dual connect by level < 10000000; end;', start_date=>sysdate, enabled=> true);
  end loop;
end;
/

select run_duration 
from user_scheduler_job_run_Details 
where job_name like 'AJ_TEST%' 
order by log_Date desc;
---------------------------------------------------------
+00 00:00:07.000000
+00 00:00:07.000000
+00 00:00:07.000000
+00 00:00:06.000000
+00 00:00:06.000000

/*********************************************************/

drop sequence seq;
create sequence seq cache 1000000;

truncate table tmp_seq;

begin
  for i in 1..5 loop
    dbms_scheduler.create_job('aj_test_seq'||i, job_type=>'PLSQL_BLOCK', job_action=> 'begin insert into tmp_seq select seq.nextval from dual connect by level < 10000000; end;', start_date=>sysdate, enabled=> true);
  end loop;
end;
/

select run_duration 
from user_scheduler_job_run_Details 
where job_name like 'AJ_TEST_SEQ%' 
order by log_Date desc;
---------------------------------------------------------

+00 00:01:21.000000
+00 00:01:21.000000
+00 00:01:20.000000
+00 00:01:20.000000
+00 00:01:18.000000

truncate table tmp_guid;

begin

for i in 1..5 loop

dbms_scheduler.create_job('aj_test'||i, job_type=>'PLSQL_BLOCK', job_action=> 'begin insert into tmp_guid select sys_guid() from dual connect by level < 10000000; end;', start_date=>sysdate, enabled=> true);

end loop;

end;

select run_duration

from user_scheduler_job_run_Details

where job_name like 'AJ_TEST%'

order by log_Date desc;

---------------------------------------------------------

+00 00:00:07.000000

+00 00:00:06.000000

/*********************************************************/

drop sequence seq;

create sequence seq cache 1000000;

truncate table tmp_seq;

begin

for i in 1..5 loop

dbms_scheduler.create_job('aj_test_seq'||i, job_type=>'PLSQL_BLOCK', job_action=> 'begin insert into tmp_seq select seq.nextval from dual connect by level < 10000000; end;', start_date=>sysdate, enabled=> true);

end loop;

end;

select run_duration

from user_scheduler_job_run_Details

where job_name like 'AJ_TEST_SEQ%'

order by log_Date desc;

---------------------------------------------------------

+00 00:01:21.000000

+00 00:01:20.000000

+00 00:01:18.000000

as you see 5 concurrent sessions took at most 7 seconds to complete 10 million row insertion but for sequences it takes over 80 seconds. Also, as a reminder, I used 1 million cache size on sequence to increase the speed.

That is all for now.

I wish you all a happy new year. I hope the world found peace this year. Of course this is what everyone wishes every year and wishes are ideal but history is full of violence (this was from a movie if I am not wrong). Still, let’s hope the best.

1- Space usage

2- Performance

Leave a Reply Cancel reply