Re: Index Contention / Sequence Caching
- From: Riyaj Shamsudeen <riyaj.shamsudeen@xxxxxxxxx>
- To: SUzzell@xxxxxxxxxx
- Date: Mon, 17 Mar 2014 08:38:40 -0700
Hello Stephen,
If you had sequence caching issues, then the object number for the gc
buffer busy event would be set to the object number of seq$ table.
This is a classic right-hand-growth-index-leaf-block contention. I have a
blog entry:
http://orainternals.wordpress.com/2010/09/27/gc-buffer-busy-waits/
Partitioning the index is probably the optimal solution. Of course, for
heavily used sequences, you should increase the cache to much higher value
even in a single instance.
Only if I don't have partitioning license, I would use reverse key indexes.
Even then, reverse key indexes can induce few other problems, simply, avoid
if possible.
Cheers
Riyaj Shamsudeen
Principal DBA,
Ora!nternals - http://www.orainternals.com - Specialists in Performance,
RAC and EBS
Blog: http://orainternals.wordpress.com/
Oracle ACE Director and OakTable member <http://www.oaktable.com/>
Co-author of the books: Expert Oracle
Practices<http://tinyurl.com/book-expert-oracle-practices/>
, Pro Oracle SQL, <http://tinyurl.com/ahpvms8>
<http://tinyurl.com/ahpvms8>Expert
RAC Practices 12c. <http://tinyurl.com/expert-rac-12c> Expert PL/SQL
practices <http://tinyurl.com/book-expert-plsql-practices>
<http://tinyurl.com/book-expert-plsql-practices>
Other related posts:
