RSS Feed for This PostCurrent Article

Oracle Performance: DFS Lock Handle and Oracle Sequence

If you are using Oracle sequences very frequently in a RAC environment, most often you will see that one of the most timed events is “DFS Lock Handle”. Commonly you should set the sequence to have a larger cache size and with the NOORDER option, which is the default, for best performance.

Performance of combined options worst to best:

  • NOCACHE with ORDER
  • NOCACHE with NOORDER
  • CACHE with ORDER
  • CACHE with NOORDER

Few more things to note

  • High volume insert intensive applications using Sequence generated keys add extra overhead
    NOCACHE is the worst for performance
  • CACHE NOORDER is the best for performance
  • Services may help improve performance even for CACHE NOORDER sequences if the insert application uses a service with only one Preferred Instance.
  • This eliminates the Interconnect traffic and synchronisation overhead required to coordinate the next value from amongst all the instances when using CACHE ORDER

Most information here

http://dbatrain.files.wordpress.com/2009/01/rac_sequences.pdf

http://www.pythian.com/news/383/sequences-in-oracle-10g-rac


Trackback URL


Sorry, comments for this entry are closed at this time.