Caching in – Oracle Sequences and Performance

Several years ago, the BBC found itself a little financially embarrassed. Appropriately enough, given the topic at hand, it was a bit strapped for cache…er…cash.

In an attempt to reduce expenditure, the decision was taken to re-broadcast the entire four series of Blackadder.
Happily, this guaranteed must-see viewing every Friday evening for about six months.

Possibly as a result of this, the dialogue from the show became something of a lingua franca within the IT community, and probably still is for geeks of a certain age.

Just think, how often have you be presented with “a cunning plan” or maybe found yourself in a situation that was “sticker than sticky the stick insect stuck on a sticky bun”.

Oh, just me then.

It is with Wisdom of the Ancients distilled through the dialogue from this classic show that we will now explore the wacky world of Sequence Caching in Oracle.
What’s that ? You don’t see anything wacky about it ? Hmmm, let’s take a closer look then…

Reasons for not caching sequence values

When it comes to sequences, there are a couple of reasons usually advanced for not caching :

  • We don’t want gaps in the values sourced from the sequence
  • We don’t want to “waste” any cache values that may be discarded if not used – what if the sequence runs out of numbers ?

In addition to this, the documentation has this to say on the matter :

” NOTE – Oracle recommends using the CACHE setting to enhance performance if you are using sequences in an Oracle Real Application Clusters environment.”

From this, you may infer that caching isn’t something you really need to consider in a non-RAC environment.

We’ll come back to this shortly.

In the meantime however, a quick demonstration of …

Why values generated from sequences aren’t necessarily gap-free

create table shows
(
    id number primary key,
    year_first_shown number(4),
    title varchar2(100)
)
/

As you can see, this table has a synthetic key. Typically, we’ll use a sequence to generate the values for it.
NOTE – in 12c you can associate the sequence with the column you’re using it to populate.
For now though, we’ll just take the traditional approach…

create sequence shows_id_seq
/

Now let’s add some values to the table…

insert into shows ( id, year_first_shown, title)
values( shows_id_seq.nextval, 1983, 'The Black Adder')
/

commit;

insert into shows ( id, year_first_shown, title)
values( shows_id_seq.nextval, 1986, 'Blackadder II')
/

commit;

insert into shows ( id, year_first_shown, title)
values( shows_id_seq.nextval, 1987, 'Blackadder The Third')
/

rollback;

insert into shows ( id, year_first_shown, title)
values( shows_id_seq.nextval, 1989, 'Blackadder Goes Forth')
/

commit;

insert into shows ( id, year_first_shown, title)
values( shows_id_seq.nextval, 1987, 'Blackadder The Third')
/

commit;

Notice that rollback in the middle there ? The sequence has been invoked and a value obtained but it has not actually been used.
Predictably, once we run this, we have a gap in our ID values…

select *
from shows
order by id
/

	ID YEAR_FIRST_SHOWN TITLE
---------- ---------------- ----------------------------------------------------------------------------------------------------
	 1	       1983 The Black Adder
	 2	       1986 Blackadder II
	 4	       1989 Blackadder Goes Forth
	 5	       1987 Blackadder The Third

As our sequence is specifically for the generation of Primary Key values, it has to be set to NOCYCLE (the default).
If we discard chunks of numbers through caching, doesn’t that increase the chances of the sequence reaching it’s maximum value and ceasing to function ?

How long will my sequence last ?

Let’s take a closer look at the sequence :

 select max_value, cache_size, cycle_flag
  2  from user_sequences
  3  where sequence_name = 'SHOWS_ID_SEQ' 
  4  /


			      MAX_VALUE CACHE_SIZE C
--------------------------------------- ---------- -
	   9999999999999999999999999999 	20 N

SQL> 

That MAX_VALUE is a big number. I wonder how long it would take to reach it if we were to use, say 100 million values per day ?

SQL> select floor( max_value / (100000000 * 365.25)) as years    
  2  from user_sequences
  3  where sequence_name = 'SHOWS_ID_SEQ'
  4  /

				  YEARS
---------------------------------------
		     273785078713210130

SQL> 

Should be alright for a bit then.

Coming back to not worrying about caching in a non-RAC environment, it might be interesting to do a quick test.

Do you need some cache ?

I’ve got a staging table containing one million rows that I need to insert into an application table…

create table maths_stg
(
    black_adder varchar2(100),
    baldrick varchar2(100)
)
/

begin
    for i in 1..1000000 loop
        insert into maths_stg( black_adder, baldrick)
        values
        (
            'I have two beans and I add two more beans. What does that make ?',
            'A very small casserole'
        );
    end loop;
end;
/

commit;

The target table looks like this …

create table maths_questions
(
    id number primary key,
    question varchar2(100),
    answer varchar2(100)
)
/

…and the sequence is created like this ….

create sequence mathq_id_seq
    nocache
/

select cache_size
from user_sequences
where sequence_name = 'MATHQ_ID_SEQ';

CACHE_SIZE
----------
	 0

SQL> 

Despite my earlier statements about sequences, the neat-freak in me has decided to create this one with nocache specified.
After all, I’m not running this on a RAC environment so, it’s no biggie.

Anyway, let’s see just how long this load will take.
NOTE – The steps followed for this and each subsequent test are :

  1. Drop the sequence (if it exists)
  2. Drop the MATHS_QUESTIONS table (if it exists) and purge from the Recycle Bin
  3. Create the sequence with the appropriate CACHE setting
  4. Create the MATHS_QUESTIONS table
  5. Bounce the database
  6. Issue an ALTER SYSTEM SWITCH LOGFILE to minimize the effect of checkpointing
  7. Run the load

So, for a sequence with a cache size of 0 (i.e. nocache) :

set timing on
insert into maths_questions( id, question, answer)
    select mathq_id_seq.nextval, black_adder, baldrick
    from maths_stg
/

1000000 rows created.

Elapsed: 00:03:15.97

Now, just for fun, let’s try that again with the sequence cache set to the default (i.e. 20).

set timing on
insert into maths_questions( id, question, answer)
    select mathq_id_seq.nextval, black_adder, baldrick
    from maths_stg
/

1000000 rows created.

Elapsed: 00:00:31.04

Interesting. The load time has gone down from 195 seconds to 31.

Increasing the cache size to 100 the results are even faster :

set timing on
insert into maths_questions( id, question, answer)
    select mathq_id_seq.nextval, black_adder, baldrick
    from maths_stg
/

1000000 rows created.

Elapsed: 00:00:17.26

Now I could go on. Indeed, I did, I tested with cache sizes of up to 1 million.
Unfortunately, the results weren’t consistent. In other words a cache size of 1000 was often faster than for 1 million, but not always.
This is despite the database re-start between runs and switching the logfiles.
This may well have something to do with the limited resources on my test database. It’s Express Edition, remember, which means it will access at most, 1GB RAM and a single CPU core.

That said, there remains the question of exactly why increasing the sequence cache size has such a dramatic effect on performance.
This is where things get a bit…strange…

Curious results and how not to make sense of them

The next logical step in finding out what’s happening would be to do a bit of tracing. So, before executing the insert statement, I ran :

select value
from v$diag_info
where name = 'Default Trace File'
/

…to establish the name and location of the trace file of the current session. This was followed by…

exec dbms_monitor.session_trace_enable( null, null, true, true)

…to start tracing in the current session.

Finally, after the insert, I stopped tracing with :

exec dbms_monitor.session_trace_disable

I then generated tkprof output from the resulting tracefiles using the following command on the OS :

tkprof tracefile_name.trc tkp_output.txt explain=uid/mypwd@db

…where tracefile_name.trc is the name of the tracefile and tkp_output.txt is the name of the file into which the tkprof output is saved.

Even before I looked at the tkprof output, it became apparent that tracing has rather a dramatic effect on runtime as the below table shows :

Cache Size No Trace Time Trace Time
NOCACHE 03:15.97 18:01.22
20 00:31.04 01:24.28
100 00:17.26 00:31.01

Perhaps the most illuminating information to be gleaned from the tkprof output is this recursive statement, which is listed immediately after our insert statement :

SQL ID: 4m7m0t6fjcs5x Plan Hash: 1935744642

update seq$ set increment$=:2,minvalue=:3,maxvalue=:4,cycle#=:5,order$=:6,
  cache=:7,highwater=:8,audit$=:9,flags=:10 
where
 obj#=:1

Looking at the tkprof output for this statement is somewhat revealing.

Where the sequence is set to NOCACHE :

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute 1000000    909.24     913.93          2    1000464    2034042     1000000
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total   1000001    909.24     913.93          2    1000464    2034042     1000000

With a cache size of 20 :

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute  50000     58.14      58.66          0      50000     100016       50000
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total    50001     58.14      58.66          0      50000     100016       50000

Finally, for a cache size of 100 :

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute  10000     12.17      12.37          0      10107      21362       10000
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total    10001     12.17      12.37          0      10107      21362       10000

Given the massive distortion in the results caused by the simple act of tracing the session, these results are unlikely to be truly representative of exactly what goes on in a normal ( i.e. non-traced) session. However, the number of executions of this statement does provide an indication of just why the increase in cache size reduces runtime in this example.

I think that last statement should be qualified – an increase in sequence cache size will reduce the runtime…up to a point.
Beyond a certain cache size, you will start to encounter diminishing returns and possibly even performance degradation.
The optimum setting for the sequence cache will depend on the resources (particularly CPU) available in your target environment.

So, what, if anything can we conclude from all this ?

Conclusions

If you have a data load that’s taking longer than expected and you can see that a statement with an SQL ID of 4m7m0t6fjcs5x is particularly active, then you may well want to look at the relevant sequence and review it’s cache setting.

Additionally, you may well consider whether tracing in an environment other than production would be prudent if you’re trying to track down a problem in a load job which has this characteristic.

On the plus side, unless you’re doing something particularly unusual, any NOCYCLE sequences with a default MAX_VALUE are unlikely ever to run out on you.

Advertisements

One thought on “Caching in – Oracle Sequences and Performance

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s