Wednesday, March 25, 2015

Oracle APEX - Performance tuning sequences


I've been recently reading about performance issues for sequences with cache values set too low.

  • Daniel Morgan www.morganslibrary.org, covers it in his ACE Director Performance Tuning Bootcamp presentation
  • Tom Kyte says "you would be amazed what setting a sequence cache via alter sequence to 100,000 or more can do during a large load -- amazed."
  • Simon Pane from Pythian goes into great detail in a blog post performance issues with the sequence nextval call. He also show to detect when sequences are impacting performance.

With APEX 5.0 nearing general release, I thought it would be interesting to see what the cache value was set to for WWV_SEQ. In case you don't recognize the sequence, it's used by the APEX engine as part of a calculation to generate globally unique ids for APEX items.

A quick check on apex.oracle.com reveals the APEX team have been thinking about it also:

select sequence_owner, cache_size, last_number
  from all_sequences
 where sequence_name = 'WWV_SEQ';

SEQUENCE_OWNERCACHE_SIZELAST_NUMBER
APEX_0500001006939179
APEX_04020020719290506

Interesting to see the cache size was set to the default value of 20 in APEX 4.2 and is now set to 100 in APEX 5.0. That's the "sweet spot" identified in Simon Panes blog also.

So, if you have an active Oracle APEX 4.2 instance, it may be worthwhile investigating..