Re: Rebuild Index?
- From: "Jonathan Lewis" <jonathan@xxxxxxxxxxxxxxxxxx>
- To: <oracle-l@xxxxxxxxxxxxx>
- Date: Sun, 2 Mar 2014 19:26:55 -0000
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@xxxxxxxxx>
To: <Jed_Walker@xxxxxxxxxxxxxxxxx>
Cc: <laimutis.nedzinskas@xxxxxx>; <oracle-l@xxxxxxxxxxxxx>
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@xxxxxxxxxxxxxxxxx> 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@xxxxxxxxxxxxx [mailto:
| > oracle-l-bounce@xxxxxxxxxxxxx] *On Behalf Of
*Laimutis.Nedzinskas@xxxxxx
| > *Sent:* Friday, February 28, 2014 6:33 AM
| > *To:* oracle-l@xxxxxxxxxxxxx
| > *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@xxxxxxxxxxxxxxxxxx>
| >
| > To:
| >
| >
| > "Laimutis.Nedzinskas@xxxxxx" <Laimutis.Nedzinskas@xxxxxx>, "
| > oracle-l@xxxxxxxxxxxxx" <oracle-l@xxxxxxxxxxxxx>
| >
| > 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
| > @jloracle
| > ------------------------------
| >
| >
| > *From:* oracle-l-bounce@xxxxxxxxxxxxx [oracle-l-bounce@xxxxxxxxxxxxx]
on
| > behalf of Laimutis.Nedzinskas@xxxxxx [Laimutis.Nedzinskas@xxxxxx]
| > * Sent:* 28 February 2014 12:54
| > * To:* oracle-l@xxxxxxxxxxxxx
| > * 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@xxxxxxxxx>
| >
| > To:
| >
| >
| > "oracle-l@xxxxxxxxxxxxx" <oracle-l@xxxxxxxxxxxxx>
| >
| > 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
| >
| > --
| > //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
--
//www.freelists.org/webpage/oracle-l
Other related posts:
