Oracle Performance: DFS Lock Handle and Oracle Sequence
By admin on Aug 28, 2009 in open source
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
Sorry, comments for this entry are closed at this time.