RE: Index Contention / Sequence Caching

I had actually seen your blog entry - that's what got me as far down the path as I made it.

I'm wondering though - you've made it pretty clear you don't think highly of reverse key indices. I understand that they can't be range scanned - but are there other minuses to them?

In this particular case, the PK isn't generally used by any queries against the table. Since there are no range scans involved, I'm wondering if that eliminates some (all?) of the negatives of a reverse key index?

Thanks!

Stephan Uzzell

From: Riyaj Shamsudeen [mailto:riyaj.shamsudeen_at_gmail.com] Sent: Monday, 17 March, 2014 11:39
To: Uzzell, Stephan
Cc: oracle-l_at_freelists.org
Subject: Re: Index Contention / Sequence Caching

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<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> Expert RAC Practices 12c.<http://tinyurl.com/expert-rac-12c> Expert PL/SQL practices<http://tinyurl.com/book-expert-plsql-practices>

--
http://www.freelists.org/webpage/oracle-l