Re: Rebuild Index?

From: Karth Panchan <keyantech_at_gmail.com>
Date: Fri, 28 Feb 2014 22:48:43 -0500
Message-ID: <CAFj-6g-0hiqBHnWvRp18dFN6_Np7KDUJ_YsUFqFuEGo+C85mww_at_mail.gmail.com>



Lyall, Jonathan, Mark,Laimutis,Dam and Jed

Thanks so much for your valuable comments and suggestions.

We only have B-Tree indexes since it is high OLTP with transactions expected to complete in less than minute environment. There are no batch job. Couple of reports with DML operations.

My teammate showed me metrics, before and after index rebuild how the performance changed in non-production instance. This is production issue. We see this anomaly to only Single table.

Now, we have to find why only this table have this issue. Points noted what and where to look.

Thanks so much for everyone to answer this question.

Karth

On Fri, Feb 28, 2014 at 11:37 AM, Walker, Jed S < Jed_Walker_at_cable.comcast.com> wrote:

> Truly one of those cases where I like forgot about theory and statistics
> and just see if it works. I've dealt with many systems where rebuilding the
> indexes has saved the system and keeps it performing well (in the majority
> of cases I wish they'd just partition it). You certainly don't need to
> waste resources if it isn't necessary but sometimes it is. If it works, do
> it, and then try to find a better way to organize the table so you don't
> have to.
>
>
>
> Shameless plug:
> http://tinky2jed.wordpress.com/technical-stuff/oracle-stuff/breaking-two-myths-about-rebuilding-indexes-in-the-oracle-database/
>
>
>
> *From:* oracle-l-bounce_at_freelists.org [mailto:
> oracle-l-bounce_at_freelists.org] *On Behalf Of *Laimutis.Nedzinskas_at_seb.lt
> *Sent:* Friday, February 28, 2014 6:33 AM
> *To:* oracle-l_at_freelists.org
> *Subject:* RE: Rebuild Index?
>
>
>
> Actually, we did have this problem. Indexes grew, performance degraded.
>
> We knew it's fifo queue by design we are dealing with. But the
> counter-myth that indexes do not need rebuild is well alive and stalling
> people's minds.
> The metalink 271855.1 helps to convince.
>
>
>
> ---------------------------------------------------------------------------------
> Please consider the environment before printing this e-mail
>
> [image: Inactive hide details for Jonathan Lewis ---2014.02.28
> 15:05:50--->> "I know this effect was discarded as a myth but then why t]Jonathan
> Lewis ---2014.02.28 15:05:50--->> "I know this effect was discarded as a
> myth but then why this:" No, that case was repeatedly iden
>
> From:
>
>
> Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk>
>
> To:
>
>
> "Laimutis.Nedzinskas_at_seb.lt" <Laimutis.Nedzinskas_at_seb.lt>, "
> oracle-l_at_freelists.org" <oracle-l_at_freelists.org>
>
> Date:
>
>
> 2014.02.28 15:05
>
> Subject:
>
>
> RE: Rebuild Index?
> ------------------------------
>
>
>
>
>
>
> >> "I know this effect was discarded as a myth but then why this:"
>
> No, that case was repeatedly identified as one of the special cases that
> needed to be recognised and understood.
>
> You'll also notice that the word "coalesce" appears in that document
> title, not "rebuild" - that's because the people who understood how indexes
> worked also understood why the FIFO was a special case and how best to
> address it ... and said so, many times. (Actually, "shrink space compact"
> seems to be more efficient than "coalesce" in recent versions - but the
> whole AQ thing introduces some funny effects around the edges anyway.)
>
>
>
> Regards
> Jonathan Lewis
> http://jonathanlewis.wordpress.com
> _at_jloracle
> ------------------------------
>
>
> *From:* oracle-l-bounce_at_freelists.org [oracle-l-bounce_at_freelists.org] on
> behalf of Laimutis.Nedzinskas_at_seb.lt [Laimutis.Nedzinskas_at_seb.lt]
> * Sent:* 28 February 2014 12:54
> * To:* oracle-l_at_freelists.org
> * Subject:* Re: Rebuild Index?
>
> index rebuild is part of oracle queues houskeeping.
>
> under some circumstances (example, table as FIFO queue, ever growing
> ID/timestamp, inserts of new records, delete of old records) indexes tend
> to expand to unrealistic sizes.
> I know this effect was discarded as a myth but then why this:
>
> "Procedure to manually Coalesce all the IOTs/indexes Associated with
> Advanced Queueing tables to maintain Enqueue/Dequeue performance, reduce
> QMON CPU usage and Redo generation (Doc ID 271855.1)"
>
> Bottom line:
> a particular b-tree index implementation might not be intended for queues.
> If you take time to read cs articles about b-tree implementations youl'll
> see there is nothing trivial about b-tree performance in concurrent
> environments.
> Oracle definetely made some practical assumptions: who deletes data in the
> database ? It's a very rear condition that data deletions are allowed.
> On the other hand, index update is almost delete and then insert. But
> update is rather random, normally. You might expect (half)empty index
> blocks will be reused.
> FIFO insert/delete is another beast.
>
>
> Brgds, Laimis N
>
>
> ---------------------------------------------------------------------------------
> Please consider the environment before printing this e-mail
>
> [image: Inactive hide details for Karth Panchan ---2014.02.28
> 14:16:12---Everyone We have table with lot of insert/update/delete every]Karth
> Panchan ---2014.02.28 14:16:12---Everyone We have table with lot of
> insert/update/delete every day. High OLTP application in 11g R2
>
> From:
>
>
> Karth Panchan <keyantech_at_gmail.com>
>
> To:
>
>
> "oracle-l_at_freelists.org" <oracle-l_at_freelists.org>
>
> Date:
>
>
> 2014.02.28 14:16
>
> Subject:
>
>
> Rebuild Index?
> ------------------------------
>
>
>
>
> Everyone
>
> We have table with lot of insert/update/delete every day. High OLTP
> application in 11g R2 on Linux.
>
> To gain performance my teammate recommend Rebuild index.
>
> His assumption Index size should be less than table size. Other wise we
> need to rebuild index.
>
> Is that correct? Want get your thoughts.
>
> I am skeptic after Reading Asktom and Jonathan Lewis blog. Both of them
> claim need good metrics and it is last option.
>
> Thanks
> Karth
>
> --
> http://www.freelists.org/webpage/oracle-l
>
>
>



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


image003.png image001.gif image004.png
Received on Sat Mar 01 2014 - 04:48:43 CET

Original text of this message