“Don't you know anything at all about numbers?"
"Well, I don't think they're very important," snapped Milo, too embarrassed to admit the truth.
"NOT IMPORTANT!" roared the Dodecahedron, turning red with fury. "Could you have tea for two
without the two — or three blind mice without the three? Would there be four corners of the earth
if there weren't a four? And how would you sail the seven seas without a seven?"
"All I meant was—" began Milo, but the Dodecahedron, overcome with emotion and shouting furiously,
carried right on.
"If you had high hopes, how would you know how high they were? And did you know that narrow escapes
come in all different widths? Would you travel the whole wide world without ever knowing how wide
it was? And how could you do anything at long last," he concluded, waving his arms over his head,
"without knowing how long the last was? Why, numbers are the most beautiful and valuable things in
the world. Just follow me and I'll show you." He turned on his heel and stalked off into the cave.
-- Norton Juster, The Phantom Tollbooth
How best to generate a primary key is a discussion that seems to never get an answer suitable to everyone. Sequences are a popular choice, as are other forms of generated or “artificial” keys. In addition to that Oracle provides a function named sys_guid() that generates unique identifiers that can be used for a number of purposes, one of which is, as one might expect, 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 the sequence with the larger cache size is the redo size is less than half that of using the sys_guid() call. Additionally, 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>
Now, absent the insert, 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 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 aboutn 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 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 and insert statements was consistent regardless of the operating system used (Windows or Linux) and was larger than that when using a sequence and insert statements; absent any DML the sys_guid() call generated far less redo. 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.
It would seem that 42 isn’t the only important number.
Oracle Tips and Tricks -- David Fitzjarrell 