This content has been marked as final. Show 4 replies
Maybe there is a problem with the cache_size (in a RAC) environment. We are seeing locks on sequence IDGEN1$ which running SYNC_INDEX. Most of the time we have 10K items put in the pending table (dr$pending) before we do the sync index. When we have a larger volume to process we have 100K before the sync index is invoked. Would a cache_size of 20 not be an issue? Is not the recommended low value 1000 in a RAC environment?
In the database there are 171 sequences with 20 (most of them) and a few with zero cache_size value.
IDGEN1$ is not only used for indexing, but for more general cases in Oracle. There is bug 7694580, which is solved in 11.2. Simply stated, if the sequence cache size is 20, then you may increase the value to 1000. This is not only applicable for RAC, but for all large systems. So indeed it is allowed to increase the value (as user SYS):
Herald ten Dam
alter sequence sys.IDGEN1$ cache 1000;