- 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_OWNER | CACHE_SIZE | LAST_NUMBER |
|---|---|---|
| APEX_050000 | 100 | 6939179 |
| APEX_040200 | 20 | 719290506 |
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..
