oracle 序列缓存和性能
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/24298868/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Sequence cache and performance
提问by being_uncertain
I could see the DBA team advises to set the sequence cache to a higher value at the time of performance optimization. To increase the value from 20 to 1000 or 5000.The oracle docs, says the the cache value,
我可以看到 DBA 团队建议在性能优化时将序列缓存设置为更高的值。要将值从 20 增加到 1000 或 5000。oracle 文档说缓存值，
Specify how many values of the sequence the database preallocates and keeps in memory for faster access.
Specify how many values of the sequence the database preallocates and keeps in memory for faster access.
Somewhere in the AWR report I can see,
在 AWR 报告的某处我可以看到，
select SEQ_MY_SEQU_EMP_ID.nextval from dual
select SEQ_MY_SEQU_EMP_ID.nextval from dual
Can any performance improvement be seen if I increase the cache value of SEQ_MY_SEQU_EMP_ID.
如果我增加SEQ_MY_SEQU_EMP_ID.
My question is:
我的问题是：
Is the sequence cache perform any significant role in performance? If so how to know what is the sufficient cache value required for a sequence.
序列缓存对性能有什么重要作用吗？如果是这样，如何知道序列所需的足够缓存值是多少。
采纳答案by VikiYang
We can get the sequence values from oracle cache before them used out. When all of them were used, oracle will allocate a new batch of values and update oracle data dictionary. If you have 100000 records need to insert and set the cache size is 20, oracle will update data dictionary 5000 times, but only 20 times if you set 5000 as cache size.
我们可以在序列值用完之前从 oracle 缓存中获取它们。当所有这些都被使用时，oracle 将分配新的一批值并更新 oracle 数据字典。如果你有100000条记录需要插入并且设置缓存大小为20，oracle会更新数据字典5000次，但是如果你设置5000作为缓存大小只有20次。
More information maybe help you: http://support.esri.com/en/knowledgebase/techarticles/detail/20498
更多信息可能对您有所帮助：http: //support.esri.com/en/knowledgebase/techarticles/detail/20498
回答by SriniV
If you omit both CACHE and NOCACHE, then the database caches 20 sequence numbers by default. Oracle recommends using the CACHE setting to enhance performance if you are using sequences in an Oracle Real Application Clusters environment.
如果同时省略 CACHE 和 NOCACHE，则数据库默认缓存 20 个序列号。如果您在 Oracle Real Application Clusters 环境中使用序列，Oracle 建议使用 CACHE 设置来提高性能。
Using the CACHE and NOORDER options together results in the best performance for a sequence. CACHE option is used without the ORDER option, each instance caches a separate range of numbers and sequence numbers may be assigned out of order by the different instances. So more the value of CACHE less writes into dictionary but more sequence numbers might be lost. But there is no point in worrying about losing the numbers, since rollback, shutdown will definitely "lose" a number.
一起使用 CACHE 和 NOORDER 选项会导致序列的最佳性能。CACHE 选项在没有 ORDER 选项的情况下使用，每个实例缓存一个单独的数字范围，序列号可能由不同的实例乱序分配。因此，缓存的值越多，写入字典的次数就越少，但可能会丢失更多的序列号。但是担心丢失数字是没有意义的，因为回滚，关机肯定会“丢失”一个数字。
CACHE option causes each instance to cache its own range of numbers, thus reducing I/O to the Oracle Data Dictionary, and the NOORDER option eliminates message traffic over the interconnect to coordinate the sequential allocation of numbers across all instances of the database. NOCACHE will be SLOW...
CACHE 选项使每个实例缓存其自己的数字范围，从而减少对 Oracle 数据字典的 I/O，并且 NOORDER 选项消除了互连上的消息流量，以协调数据库所有实例之间的数字顺序分配。NOCACHE 会很慢...
Read this
读这个
回答by mic.sca
Besides spending more time updating oracle data dictionary having small sequence caches can have other negative effects if you work with a Clustered Oracle installation.
如果您使用集群 Oracle 安装，除了花费更多时间更新具有小序列缓存的 oracle 数据字典之外，还会产生其他负面影响。
In Oracle 10g RAC Grid, Services and Clustering 1st Edition by Murali Vallath it is stated that if you happen to have
Murali Vallath 在 Oracle 10g RAC Grid, Services and Clustering 1st Edition 中指出，如果您碰巧有
- an Oracle Cluster (RAC)
- a non-partitioned index on a column populated with an increasing sequence value
- concurrent multi instance inserts
- Oracle 集群 (RAC)
- 填充有递增序列值的列上的非分区索引
- 并发多实例插入
you can incur in high contention on the rightmost index block and experience a lot of Cluster Waits (up to 90% of total insert time). If you increase the size of the relevant sequence cache you can reduce the impact of Cluster Waits on your index.
您可能会在最右侧的索引块上发生高争用并经历大量集群等待（高达总插入时间的 90%）。如果增加相关序列缓存的大小，则可以减少 Cluster Waits 对索引的影响。
回答by being_uncertain
Have done some research and found some relevant information in this regard:
做了一些研究，找到了这方面的一些相关资料：
- We need to check the database for sequences which are high-usage but defined with the default cache size of 20 - the performance benefits of altering the cache size of such a sequence can be noticeable.
- Increasing the cache size of a sequence does not waste space, the cache is still defined by just two numbers, the last used and the high water mark; it is just that the high water mark is jumped by a much larger value every time it is reached.
- A cached sequence will return values exactly the same as a non-cached one. However, a sequence cache is kept in the shared pool just as other cached information is. This means it can age out of the shared pool in the same way as a procedure if it is not accessed frequently enough. Everything is the cache is also lost when the instance is shut down.
- 我们需要检查数据库中是否存在高使用率但定义为默认缓存大小 20 的序列 - 更改此类序列的缓存大小的性能优势可能会很明显。
- 增加一个序列的缓存大小不会浪费空间，缓存仍然只由两个数字定义，最后使用的和高水位标记；只是每次到达高水位线时都会跳一个更大的值。
- 缓存序列将返回与非缓存序列完全相同的值。但是，序列缓存与其他缓存信息一样保存在共享池中。这意味着如果访问频率不够高，它可能会以与过程相同的方式从共享池中老化。一切都是当实例关闭时缓存也会丢失。
回答by Ankit Bajpai
By default in ORACLE cache in sequence contains 20 values. We can redefine it by given cache clause in sequence definition. Giving cache caluse in sequence benefitted into that when we want generate big integers then it takes lesser time than normal, otherwise there are no such drastic performance increment by declaring cache clause in sequence definition.
默认在 ORACLE 缓存中按顺序包含 20 个值。我们可以通过序列定义中给定的缓存子句重新定义它。按顺序提供缓存调用的好处是，当我们想要生成大整数时，它需要比正常情况下更少的时间，否则通过在序列定义中声明缓存子句不会有如此剧烈的性能提升。
