Re: Rebuild Index?
Date: Thu, 6 Mar 2014 15:46:03 -0300
Message-ID: <CAAHb+wNzkWd1drHtbUHGKnKeZLCtRP+kPJY0g1XjMh5x5bkuog_at_mail.gmail.com>
Also please be advised the default behavior in ASSM:
- INSERT slow on ASSM (Doc ID 1263496.1)
I once had a issue in a large batch INSERT where most of wait events was in "db file sequential read". When tracing the ASSM with the following events:
ALTER SESSION SET EVENTS '10320 trace name context forever , level 3'; ALTER SESSION SET EVENTS '10612 trace name context forever , level 1';
It helped me to conclude this behavior, and my solution was just partition the index.
Regards.
On Sun, Mar 2, 2014 at 4:26 PM, Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk>wrote:
>
> Important point to remember - if you think you've found a special case make
> sure you log what happens when you rebuild: at a minimum, time to rebuild,
> and index size before and after rebuild.
>
> Regards
>
> Jonathan Lewis
> http://jonathanlewis.wordpress.com/all-postings
>
> Author: Oracle Core (Apress 2011)
> http://www.apress.com/9781430239543
>
> ----- Original Message -----
> From: "Karth Panchan" <keyantech_at_gmail.com>
> To: <Jed_Walker_at_cable.comcast.com>
> Cc: <laimutis.nedzinskas_at_seb.lt>; <oracle-l_at_freelists.org>
> Sent: Saturday, March 01, 2014 3:48 AM
> Subject: Re: Rebuild Index?
>
>
> | 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
> | >
> | >
> | >
> |
> |
> |
> | -----
> | No virus found in this message.
> | Checked by AVG - www.avg.com
> | Version: 2014.0.4335 / Virus Database: 3705/7134 - Release Date: 02/28/14
>
> --
> http://www.freelists.org/webpage/oracle-l
>
>
>
-- http://www.freelists.org/webpage/oracle-lReceived on Thu Mar 06 2014 - 19:46:03 CET