Re: Index Contention / Sequence Caching

From: Mohamed Houri <mohamed.houri_at_gmail.com>
Date: Mon, 17 Mar 2014 17:18:00 +0100
Message-ID: <CAJu8R6ibFYNQs61VWikiCNUSHMLPBukqOoE8Nxw9qfwPYSwVFA_at_mail.gmail.com>

Reverse key indexes on remote database are not visible to the optimizer

http://jonathanlewis.wordpress.com/?s=reverse+key+

And the effect it coud have on the clustering factor of the reversed index can damage its desirability by the CBO

http://jonathanlewis.wordpress.com/2009/09/15/index-explosion-4/

Best regards
Mohamed Houri
www.hourim.wordpress.com

2014-03-17 17:03 GMT+01:00 Uzzell, Stephan <SUzzell_at_micros.com>:

> Hi Riyaj,
>
>
>
> 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 - 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>
>
>
>
>
>

-- 
Bien Respectueusement
Mohamed Houri

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

Received on Mon Mar 17 2014 - 17:18:00 CET