Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Tips Database Forum

» Database Journal Home
» Database Articles
» Database Tutorials
MS SQL
Oracle
DB2
MS Access
MySQL
» RESOURCES
Database Tools
SQL Scripts & Samples
Tips
» Database Forum
» Slideshows
» Sitemap
Free Newsletters:
DatabaseDaily  
News Via RSS Feed


follow us on Twitter
Database Journal |DBA Support |SQLCourse |SQLCourse2
 

Featured Database Articles

Oracle

Posted December 12, 2016

Is Oracle’s SYS_GUID A Good Choice For A Primary Key?

By David Fitzjarrell

Oracle provides a a function named sys_guid() that generates unique identifiers that can be used for a number of purposes, one of which is as a generated primary key. Occasionally discussions in the Oracle forums discuss the merits and issues of using such global unique identifiers as primary keys; one current discussion asked if using sys_guid() was faster or slower than using a sequence; on a Linux system generating sys_guid() values was illustrated as being faster than using a sequence. The example code, slightly modified, is presented here having been run on Oracle 12.1.0.2 on Windows. Both 12c configurations were used (standard and container) to see if any differences appeared, and since both runs provided similar results the non-container results are provided here. Timing was set on to record and display the elapsed time for each set of serial tests; parallel tests were also completed and log table entries report the elapsed time for those runs. Separate tables were used for each set of tests, the results that were produced are shown below. The test begins with a table and a sequence created with the default settings:


SQL> -- default 20 cache
SQL> create sequence seq1;

Sequence created.

SQL>
SQL> create table t_seq
  2  	 ( id	  number(9) primary key
  3  	 , filler varchar2(1000)
  4  	 );

Table created.

SQL>

The next step inserts 999,999 records into the t_seq table; the execution plan and run statistics are shown below:


SQL> insert into t_seq
  2    select seq1.nextval ,'sdfsf' from dual connect by level < 1000000;

999999 rows created.

Elapsed: 00:00:10.19

Execution Plan
----------------------------------------------------------
Plan hash value: 3365622274

--------------------------------------------------------------------------------
| Id  | Operation                      | Name  | Rows  | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------
|   0 | INSERT STATEMENT               |       |     1 |     2   (0)| 00:00:01 |
|   1 |  LOAD TABLE CONVENTIONAL       | T_SEQ |       |            |          |
|   2 |   SEQUENCE                     | SEQ1  |       |            |          |
|*  3 |    CONNECT BY WITHOUT FILTERING|       |       |            |          |
|   4 |     FAST DUAL                  |       |     1 |     2   (0)| 00:00:01 |
--------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   3 - filter(LEVEL<1000000)


Statistics
----------------------------------------------------------
      50149  recursive calls
     227827  db block gets
      59085  consistent gets
          0  physical reads
  113671864  redo size
        855  bytes sent via SQL*Net to client
        893  bytes received via SQL*Net from client
          3  SQL*Net roundtrips to/from client
          5  sorts (memory)
          0  sorts (disk)
     999999  rows processed

SQL>
SQL> drop table t_seq;

Table dropped.

SQL> drop sequence seq1;

Sequence dropped.

SQL>

The amount of redo generated is large, a result of using the sequence. Another sequence test was executed using a sequence created with a larger cache value. Before each run the user was re-connected to reset the session statistics:


SQL> connect bing/!@#!@#
Connected.
SQL> create sequence seq1 cache 10000;

Sequence created.

SQL>
SQL> create table t_seq
  2  	 ( id	  number(9) primary key
  3  	 , filler varchar2(1000)
  4  	 );

Table created.

SQL>

The same insert statement was executed using the sequence having the larger cache; the execution plan and session statistics are shown below:


SQL> insert into t_seq
  2    select seq1.nextval ,'sdfsf' from dual connect by level < 1000000;

999999 rows created.

Elapsed: 00:00:05.24

Execution Plan
----------------------------------------------------------
Plan hash value: 3365622274

--------------------------------------------------------------------------------
| Id  | Operation                      | Name  | Rows  | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------
|   0 | INSERT STATEMENT               |       |     1 |     2   (0)| 00:00:01 |
|   1 |  LOAD TABLE CONVENTIONAL       | T_SEQ |       |            |          |
|   2 |   SEQUENCE                     | SEQ1  |       |            |          |
|*  3 |    CONNECT BY WITHOUT FILTERING|       |       |            |          |
|   4 |     FAST DUAL                  |       |     1 |     2   (0)| 00:00:01 |
--------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   3 - filter(LEVEL<1000000)


Statistics
----------------------------------------------------------
        249  recursive calls
      77911  db block gets
       9188  consistent gets
          1  physical reads
   79744836  redo size
        854  bytes sent via SQL*Net to client
        893  bytes received via SQL*Net from client
          3  SQL*Net roundtrips to/from client
          5  sorts (memory)
          0  sorts (disk)
     999999  rows processed

SQL>

Using the larger sequence cache reduced the redo size by 33927028, which cut the execution time roughly in half. On to the sys_guid() part of the serial testing, with a new table created and a new connection established:


SQL> connect bing/!@#!@#
Connected.
SQL> create table t_raw
  2  	 ( id	  raw(16) primary key
  3  	 , filler varchar2(1000)
  4  	 );

Table created.

SQL>
SQL> insert into t_raw
  2    select sys_guid(),'sdfsf' from dual connect by level < 1000000;

999999 rows created.

Elapsed: 00:00:54.15

Execution Plan
----------------------------------------------------------
Plan hash value: 1236776825

-------------------------------------------------------------------------------
| Id  | Operation                     | Name  | Rows  | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------
|   0 | INSERT STATEMENT              |       |     1 |     2   (0)| 00:00:01 |
|   1 |  LOAD TABLE CONVENTIONAL      | T_RAW |       |            |          |
|*  2 |   CONNECT BY WITHOUT FILTERING|       |       |            |          |
|   3 |    FAST DUAL                  |       |     1 |     2   (0)| 00:00:01 |
-------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter(LEVEL<1000000)


Statistics
----------------------------------------------------------
       1442  recursive calls
    2956342  db block gets
      23736  consistent gets
         13  physical reads
  375573628  redo size
        854  bytes sent via SQL*Net to client
        890  bytes received via SQL*Net from client
          3  SQL*Net roundtrips to/from client
         34  sorts (memory)
          0  sorts (disk)
     999999  rows processed

SQL>

A benefit of using sys_guid() is the redo size is less than half that of using the sequence with the larger cache and almost a fourth that of using a sequence with the standard cache size. Unfortunately, on Windows, the execution took almost 11 times longer than using a sequence with a large cache value. Returning to the default cache size for the sequence, a PL/SQL loop is used to return the generated values of the sequence; since serveroutput is not turned on, the time to return the values isn't included in the execution time (and as a result the values aren't displayed). The elapsed time to run the block is found at the end of the execution as well as the redo statistics for the session:


SQL> connect bing/!@#!@#
Connected.
SQL> create sequence seq1 ;

Sequence created.

SQL>
SQL> set timing on
SQL> declare
  2    x number(38);
  3    function sf return number is
  4    begin
  5  	 return seq1.nextval;
  6    end;
  7  begin
  8    for i in 1..100000 loop
  9  	 x := sf;
 10    end loop;
 11  end;
 12  /

PL/SQL procedure successfully completed.

Elapsed: 00:00:03.23
SQL>
SQL> select m.statistic#, n.name, m.value
  2  from v$mystat m, v$statname n
  3  where m.statistic# = n.statistic#
  4  and n.name like '%redo%'
  5  and m.value > 0;

STATISTIC# NAME                                                                  VALUE
---------- ---------------------------------------------------------------- ----------
       257 redo entries                                                          10014
       258 redo size                                                           3620676
       302 redo subscn max counts                                                    1
       307 redo synch time (usec)                                                  251
       308 redo synch time overhead (usec)                                         553
       309 redo synch time overhead count (  2ms)                                    2
       314 redo synch writes                                                         3
       323 redo write info find                                                      2

8 rows selected.

Elapsed: 00:00:00.00

SQL>

The reported redo size is smaller, but there were no inserts into a table performed in this test. A similar test was run using a sequence with a cache value of 10000:


SQL> drop sequence seq1;

Sequence dropped.

SQL> connect bing/!@#!@#
Connected.
SQL> create sequence seq1 cache 10000;

Sequence created.

SQL>
SQL> set timing on
SQL> declare
  2    x number(38);
  3    function sf return number is
  4    begin
  5  	 return seq1.nextval;
  6    end;
  7  begin
  8    for i in 1..100000 loop
  9  	 x := sf;
 10    end loop;
 11  end;
 12  /

PL/SQL procedure successfully completed.

Elapsed: 00:00:02.19
SQL>
SQL> select m.statistic#, n.name, m.value
  2  from v$mystat m, v$statname n
  3  where m.statistic# = n.statistic#
  4  and n.name like '%redo%'
  5  and m.value > 0;

STATISTIC# NAME                                                                  VALUE
---------- ---------------------------------------------------------------- ----------
       257 redo entries                                                             34
       258 redo size                                                             11940
       307 redo synch time (usec)                                                  110
       308 redo synch time overhead (usec)                                      303802
       309 redo synch time overhead count (  2ms)                                    1
       313 redo synch time overhead count (inf)                                      1
       314 redo synch writes                                                         3
       323 redo write info find                                                      2

8 rows selected.

Elapsed: 00:00:00.00
SQL>

As shown in a prior test the redo statistics show a smaller redo size for the larger cache. On to the sys_guid() test:


SQL> connect bing/!@#!@#
Connected.
SQL> declare
  2    x raw(16);
  3    function sf return varchar2 is
  4    begin
  5  	 return sys_guid();
  6    end;
  7  begin
  8    for i in 1..100000 loop
  9  	 x := sf;
 10    end loop;
 11  end;
 12  /

PL/SQL procedure successfully completed.

Elapsed: 00:00:04.70
SQL>
SQL> select m.statistic#, n.name, m.value
  2  from v$mystat m, v$statname n
  3  where m.statistic# = n.statistic#
  4  and n.name like '%redo%'
  5  and m.value > 0;

STATISTIC# NAME                                                                  VALUE
---------- ---------------------------------------------------------------- ----------
       257 redo entries                                                              6
       258 redo size                                                              1476
       302 redo subscn max counts                                                    1
       307 redo synch time (usec)                                                    1
       308 redo synch time overhead (usec)                                         575
       309 redo synch time overhead count (  2ms)                                    1
       314 redo synch writes                                                         1
       323 redo write info find                                                      1

8 rows selected.

Elapsed: 00:00:00.00
SQL>

Again, as previously illustrated, the redo generation is much less. The execution time, however, is at least twice as long as it was for the large-cache sequence. So on a Windows-based Oracle installation using a sequence takes much less time than using the sys_guid() calls. Parallel execution may be faster and the results may be reversed so further testing is necessary. These tests begin with the sequence created with the default cache value but starting at 100000000 (to more accurately reflect real production conditions) and multiple tables created with the CACHE option, which places the blocks in the most-recently used area of the cache to speed access and delay aging:


SQL> -- SEQUENCE MULTI SESSION TEST
SQL> drop table t_seq;

Table dropped.

Elapsed: 00:00:00.02
SQL> drop table tmp1;

Table dropped.

Elapsed: 00:00:00.01
SQL> drop table tmp2;

Table dropped.

Elapsed: 00:00:00.01
SQL> drop table tmp3;

Table dropped.

Elapsed: 00:00:00.01
SQL> drop table tmp4;

Table dropped.

Elapsed: 00:00:00.01
SQL>
SQL> create table tmp1 cache as select 1 dummy from dual connect by level < 1000000;

Table created.

Elapsed: 00:00:01.32
SQL> create table tmp2 cache as select 1 dummy from tmp1;

Table created.

Elapsed: 00:00:00.54
SQL> create table tmp3 cache as select 1 dummy from tmp1;

Table created.

Elapsed: 00:00:00.61
SQL> create table tmp4 cache as select 1 dummy from tmp1;

Table created.

Elapsed: 00:00:00.49
SQL>
SQL> drop sequence seq1 ;

Sequence dropped.

Elapsed: 00:00:00.00
SQL> create sequence seq1 start with 100000000 ;

Sequence created.

Elapsed: 00:00:00.00
SQL>
SQL> create table t_seq
  2  	 ( id	  number(9) primary key
  3  	 , filler varchar2(1000)
  4  	 );

Table created.

Elapsed: 00:00:00.01
SQL>
SQL> alter system switch logfile;

System altered.

Elapsed: 00:00:01.29
SQL> alter system checkpoint;

System altered.

Elapsed: 00:00:00.20
SQL> alter system flush buffer_cache;

System altered.

Elapsed: 00:00:00.13
SQL>
SQL> select /*+ full(tmp1) */ count(*) from tmp1;

  COUNT(*)
----------
    999999

Elapsed: 00:00:00.05
SQL> select /*+ full(tmp2) */ count(*) from tmp2;

  COUNT(*)
----------
    999999

Elapsed: 00:00:00.04
SQL> select /*+ full(tmp3) */ count(*) from tmp3;

  COUNT(*)
----------
    999999

Elapsed: 00:00:00.04
SQL> select /*+ full(tmp4) */ count(*) from tmp4;

  COUNT(*)
----------
    999999

Elapsed: 00:00:00.04
SQL>
SQL> drop table tmp_log;

Table dropped.

Elapsed: 00:00:00.04
SQL> create table tmp_log(mydata varchar2(4000), optime timestamp);

Table created.

Elapsed: 00:00:00.01
SQL>
SQL> create or replace PROCEDURE    sp_log(p varchar2) as
  2    PRAGMA AUTONOMOUS_TRANSACTION;
  3  begin
  4    insert into tmp_log values (p , systimestamp);
  5    commit;
  6  end;
  7  /

Procedure created.

Elapsed: 00:00:00.03
SQL>
SQL> show errors
No errors.
SQL>
SQL> create or replace procedure sp_test_seq(p number) as
  2  begin
  3    sp_log('JOB ' || p || ' BEGIN');
  4
  5    if p = 1 then
  6  	 insert  into t_seq
  7  	   select seq1.nextval ,'sdfsf' from tmp1;
  8    elsif p = 2 then
  9  	 insert into t_seq
 10  	   select seq1.nextval ,'sdfsf' from tmp2;
 11    elsif p = 3 then
 12  	 insert  into t_seq
 13  	   select seq1.nextval ,'sdfsf' from tmp3;
 14    elsif p = 4 then
 15  	 insert into t_seq
 16  	   select seq1.nextval ,'sdfsf' from tmp4;
 17    end if;
 18    commit;
 19
 20    sp_log('JOB ' || p || ' END');
 21  end;
 22  /

Procedure created.

Elapsed: 00:00:00.02
SQL>
SQL> show errors
No errors.
SQL>
SQL> declare
  2    x_time date := sysdate + 1/1440;
  3  begin
  4
  5
  6    dbms_scheduler.create_job(job_name => 'TEST_SEQ1',
  7  				 job_type => 'PLSQL_BLOCK',
  8  				 job_action => 'begin sp_test_seq(1); end;',
  9  				 enabled=> true,
 10  				 start_date=> x_time
 11  			       );
 12    dbms_scheduler.create_job(job_name => 'TEST_SEQ2',
 13  				 job_type => 'PLSQL_BLOCK',
 14  				 job_action => 'begin sp_test_seq(2); end;',
 15  				 enabled=> true,
 16  				 start_date=> x_time
 17  			       );
 18    dbms_scheduler.create_job(job_name => 'TEST_SEQ3',
 19  				 job_type => 'PLSQL_BLOCK',
 20  				 job_action => 'begin sp_test_seq(3); end;',
 21  				 enabled=> true,
 22  				 start_date=> x_time
 23  			       );
 24    dbms_scheduler.create_job(job_name => 'TEST_SEQ4',
 25  				 job_type => 'PLSQL_BLOCK',
 26  				 job_action => 'begin sp_test_seq(4); end;',
 27  				 enabled=> true,
 28  				 start_date=> x_time
 29  			       );
 30  end;
 31  /

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.02
SQL>
SQL> select job_name, start_date from user_scheduler_jobs where job_name like 'TEST%';

JOB_NAME     START_DATE
------------ ---------------------------------------------------------------------------
TEST_SEQ1    27-NOV-16 01.46.47.000000 PM -07:00
TEST_SEQ2    27-NOV-16 01.46.47.000000 PM -07:00
TEST_SEQ3    27-NOV-16 01.46.47.000000 PM -07:00
TEST_SEQ4    27-NOV-16 01.46.47.000000 PM -07:00

Elapsed: 00:00:00.00
SQL>
SQL> exec dbms_lock.sleep(120)

PL/SQL procedure successfully completed.

Elapsed: 00:02:00.00
SQL>
SQL> select * from
  2  (select mydata, optime, lead(optime) over (order by mydata) optime_end, lead(optime) over (order by mydata) - optime elapsed
  3  from tmp_log)
  4  where mydata like '%BEGIN%'
  5  /

MYDATA          OPTIME                       OPTIME_END                   ELAPSED
--------------- ---------------------------- ---------------------------- ----------------------------
JOB 1 BEGIN     27-NOV-16 01.46.50.233000 PM 27-NOV-16 01.47.28.113000 PM +000000000 00:00:37.880000
JOB 2 BEGIN     27-NOV-16 01.46.50.234000 PM 27-NOV-16 01.47.27.904000 PM +000000000 00:00:37.670000
JOB 3 BEGIN     27-NOV-16 01.46.50.235000 PM 27-NOV-16 01.47.28.169000 PM +000000000 00:00:37.934000
JOB 4 BEGIN     27-NOV-16 01.46.50.244000 PM 27-NOV-16 01.47.28.121000 PM +000000000 00:00:37.877000

Elapsed: 00:00:00.00
SQL>

Parallel takes longer on Windows, possibly because of the underlying architecture, but each of the four concurrent inserts consumed about 39 seconds. To see if concurrent processes using the sys_guid() call may end up faster we set up the test again, this time using the sys_guid() call:


SQL> -- SYS_GUID MULTI SESSION TEST
SQL> drop table t_raw;

Table dropped.

Elapsed: 00:00:00.01
SQL> drop table tmp1;

Table dropped.

Elapsed: 00:00:00.01
SQL> drop table tmp2;

Table dropped.

Elapsed: 00:00:00.01
SQL> drop table tmp3;

Table dropped.

Elapsed: 00:00:00.01
SQL> drop table tmp4;

Table dropped.

Elapsed: 00:00:00.01
SQL>
SQL> create table tmp1 cache as select 1 dummy from dual connect by level < 1000000;

Table created.

Elapsed: 00:00:00.85
SQL> create table tmp2 cache as select 1 dummy from tmp1;

Table created.

Elapsed: 00:00:00.62
SQL> create table tmp3 cache as select 1 dummy from tmp1;

Table created.

Elapsed: 00:00:00.57
SQL> create table tmp4 cache as select 1 dummy from tmp1;

Table created.

Elapsed: 00:00:00.48
SQL>
SQL> create table t_raw
  2  	 ( id	  raw(16) primary key
  3  	 , filler varchar2(1000)
  4  	 );

Table created.

Elapsed: 00:00:00.01
SQL>
SQL> alter system switch logfile;

System altered.

Elapsed: 00:00:03.02
SQL> alter system checkpoint;

System altered.

Elapsed: 00:00:00.17
SQL> alter system flush buffer_cache;

System altered.

Elapsed: 00:00:00.34
SQL>
SQL> select /*+ full(tmp1) */ count(*) from tmp1; -- to make sure table is in buffer_cache
  2  select /*+ full(tmp2) */ count(*) from tmp2; -- to make sure table is in buffer_cache
  3  select /*+ full(tmp3) */ count(*) from tmp3; -- to make sure table is in buffer_cache
  4  select /*+ full(tmp4) */ count(*) from tmp4; -- to make sure table is in buffer_cache
  5
SQL> drop table tmp_log;

Table dropped.

Elapsed: 00:00:00.03
SQL> create table tmp_log(mydata varchar2(4000), optime timestamp);

Table created.

Elapsed: 00:00:00.01
SQL>
SQL> create or replace PROCEDURE    sp_log(p varchar2) as
  2    PRAGMA AUTONOMOUS_TRANSACTION;
  3  begin
  4    insert into tmp_log values (p , systimestamp);
  5    commit;
  6  end;
  7  /

Procedure created.

Elapsed: 00:00:00.03
SQL>
SQL> show errors
No errors.
SQL>
SQL> create or replace procedure sp_test_guid(p number) as
  2  begin
  3    sp_log('JOB ' || p || ' BEGIN');
  4
  5    if p = 1 then
  6  	 insert  into t_raw
  7  	   select sys_guid() ,'sdfsf' from tmp1;
  8    elsif p = 2 then
  9  	 insert  into t_raw
 10  	   select sys_guid() ,'sdfsf' from tmp2;
 11    elsif p = 3 then
 12  	 insert  into t_raw
 13  	   select sys_guid() ,'sdfsf' from tmp3;
 14    elsif p = 4 then
 15  	 insert into t_raw
 16  	   select sys_guid() ,'sdfsf' from tmp4;
 17    end if;
 18    commit;
 19
 20    sp_log('JOB ' || p || ' END');
 21  end;
 22  /

Procedure created.

Elapsed: 00:00:00.02
SQL>
SQL> show errors
No errors.
SQL>
SQL> declare
  2    x_time date := sysdate + 1/1440;
  3  begin
  4
  5    dbms_scheduler.create_job(job_name => 'TEST_GUID1',
  6  				 job_type => 'PLSQL_BLOCK',
  7  				 job_action => 'begin sp_test_guid(1); end;',
  8  				 enabled=> true,
  9  				 start_date=> x_time
 10  			       );
 11    dbms_scheduler.create_job(job_name => 'TEST_GUID2',
 12  				 job_type => 'PLSQL_BLOCK',
 13  				 job_action => 'begin sp_test_guid(2); end;',
 14  				 enabled=> true,
 15  				 start_date=> x_time
 16  			       );
 17    dbms_scheduler.create_job(job_name => 'TEST_GUID3',
 18  				 job_type => 'PLSQL_BLOCK',
 19  				 job_action => 'begin sp_test_guid(3); end;',
 20  				 enabled=> true,
 21  				 start_date=> x_time
 22  			       );
 23    dbms_scheduler.create_job(job_name => 'TEST_GUID4',
 24  				 job_type => 'PLSQL_BLOCK',
 25  				 job_action => 'begin sp_test_guid(4); end;',
 26  				 enabled=> true,
 27  				 start_date=> x_time
 28  			       );
 29  end;
 30  /

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.04
SQL>
SQL> select job_name, start_date from user_scheduler_jobs where job_name like 'TEST%';

JOB_NAME     START_DATE
------------ ---------------------------------------------------------------------------
TEST_GUID1   27-NOV-16 01.48.53.000000 PM -07:00
TEST_GUID2   27-NOV-16 01.48.53.000000 PM -07:00
TEST_GUID3   27-NOV-16 01.48.53.000000 PM -07:00
TEST_GUID4   27-NOV-16 01.48.53.000000 PM -07:00

Elapsed: 00:00:00.00
SQL>
SQL> exec dbms_lock.sleep(180);

PL/SQL procedure successfully completed.

Elapsed: 00:03:00.00
SQL>
SQL> select * from
  2  (select mydata, optime, lead(optime) over (order by mydata) optime_end, lead(optime) over (order by mydata) - optime elapsed
  3  from tmp_log)
  4  where mydata like '%BEGIN%'
  5  /

MYDATA          OPTIME                       OPTIME_END                   ELAPSED
--------------- ---------------------------- ---------------------------- ----------------------------
JOB 1 BEGIN     27-NOV-16 01.48.54.228000 PM 27-NOV-16 01.50.49.312000 PM +000000000 00:01:55.084000
JOB 2 BEGIN     27-NOV-16 01.48.54.236000 PM 27-NOV-16 01.50.46.200000 PM +000000000 00:01:51.964000
JOB 3 BEGIN     27-NOV-16 01.48.54.245000 PM 27-NOV-16 01.50.47.742000 PM +000000000 00:01:53.497000
JOB 4 BEGIN     27-NOV-16 01.48.54.267000 PM 27-NOV-16 01.50.48.966000 PM +000000000 00:01:54.699000

Elapsed: 00:00:00.00
SQL>
SQL> set timing off echo off linesize 80 trimspool off

Table dropped.


Table dropped.


Sequence dropped.

Generating sys_guid() values is not faster when run in parallel in a Windows environment; each process ran almost two minutes before completing, roughly four times longer than the parallel sequence executions and twice as long as the serial sys_guid() runs.

The redo size using the sys_guid() call was consistent regardless of the operating system used (Windows or Linux) and was smaller than that when using a sequence. Sequence cache size can affect the redo generation as a larger cache generates smaller amounts of redo, and the redo generation affects the execution time with a sequence. Without fail on Windows using sys_guid() takes longer. This is one area where testing on the operating system you are using is a must since Oracle on Linux can, and does, perform much differently than Oracle on Windows. Before you decide to change your primary key strategy to using sys_guid() test to see how it performs; you may be surprised at the results.

See all articles by David Fitzjarrell



Oracle Archives

Comment and Contribute

 


(Maximum characters: 1200). You have characters left.

 

 




Latest Forum Threads
Oracle Forum
Topic By Replies Updated
Oracle Data Mining: Classification jan.hasller 0 July 5th, 07:19 AM
Find duplicates - Unique IDs Lava 5 July 2nd, 08:30 AM
no matching unique or primary key rcanter 1 April 25th, 12:32 PM
Update values of one table based on condition of values in other table using Trigger Gladiator 3 February 29th, 06:01 PM