SEQUENCE 问题

SEQUENCE解决的是并发问题，应该对DML速度有没有帮助 [@more@]

SEQUENCE解决的是并发问题，应该对DML速度有没有帮助

1. 尽量用sequence，不要利用程序解决，数据库自身的东西效率要高的很。

2. 设置SEQUENCE_CACHE_ENTRIES
When an application accesses a sequence in the sequence cache, the sequence numbers are read quickly. However, if an application accesses a sequence that is not in the cache, then the sequence must be read from disk to the cache before the sequence numbers are used.
If your applications use many sequences concurrently, then your sequence cache might not be large enough to hold all the sequences. In this case, access to sequence numbers might often require disk reads. For fast access to all sequences, be sure your cache has enough entries to hold all the sequences used concurrently by your applications.
The number of entries in the sequence cache is determined by the initialization parameter SEQUENCE_CACHE_ENTRIES. The default value for this parameter is 10 entries. Oracle creates and uses sequences internally for auditing, grants of system privileges, grants of object privileges, profiles, debugging stored procedures, and labels. Be sure your sequence cache has enough entries to hold these sequences as well as sequences used by your applications.
If the value for your SEQUENCE_CACHE_ENTRIES parameter is too low, then it is possible to skip sequence values. For example, assume that this parameter is set to 4, and that you currently have four cached sequences. If you create a fifth sequence, then it will replace the least recently used sequence in the cache. All of the remaining values in this displaced sequence are lost. In other words, if the displaced sequence originally held 10 cached sequence values, and only one had been used, then nine would be lost when the sequence was displaced.

3. sequence的间断问题
TRX的rollback, instance的crash都会导致已cache的sequence丢失。

4.对OPS而言，特别是OLTP系统，为防止出现”DFS LOCK HANDLE” 事件，（中国网通VOIP数据库出现过）最好设置大的cache值。

注意：cache和order选项同时使用时，cache无效。

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/28429/viewspace-843128/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/28429/viewspace-843128/