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, 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 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';


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..

1 comment:

Anonymous said...

YouTube -
YouTube - Videoslend, the home of videos on YouTube! It's been a year since I was 유튜브 little. We've been looking for videos to watch online.