RE: Rebuild Index?

From: Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk>
Date: Thu, 6 Mar 2014 22:02:57 +0000
Message-ID: <CE70217733273F49A8A162EE074F64D901DE1897_at_exmbx05.thus.corp>


That bug relates to the first small insert after a very large batch insert, so it's a bit of a special case.

There are a couple variations on the same theme (one, or a few, small-scale DML being undualy expensive after a large-scale DML) for both ASSM and free-list management when it comes to indexes, and Oracle Corp. have made various changes to try and address them over the years.

Regards
Jonathan Lewis
http://jonathanlewis.wordpress.com
_at_jloracle



From: Thiago Maciel [thiagomaciel_at_gmail.com] Sent: 06 March 2014 18:46
To: Jonathan Lewis
Cc: Oracle-L List
Subject: Re: Rebuild Index?

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<mailto: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<mailto:keyantech_at_gmail.com>> To: <Jed_Walker_at_cable.comcast.com<mailto:Jed_Walker_at_cable.comcast.com>> Cc: <laimutis.nedzinskas_at_seb.lt<mailto:laimutis.nedzinskas_at_seb.lt>>; <oracle-l_at_freelists.org<mailto: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<mailto: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> [mailto:
| > oracle-l-bounce_at_freelists.org<mailto:oracle-l-bounce_at_freelists.org>] *On Behalf Of
*Laimutis.Nedzinskas_at_seb.lt<mailto:Laimutis.Nedzinskas_at_seb.lt>
| > *Sent:* Friday, February 28, 2014 6:33 AM
| > *To:* oracle-l_at_freelists.org<mailto: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<mailto:jonathan_at_jlcomp.demon.co.uk>>
| >
| > To:
| >
| >
| > "Laimutis.Nedzinskas_at_seb.lt<mailto:Laimutis.Nedzinskas_at_seb.lt>" <Laimutis.Nedzinskas_at_seb.lt<mailto:Laimutis.Nedzinskas_at_seb.lt>>, "
| > oracle-l_at_freelists.org<mailto:oracle-l_at_freelists.org>" <oracle-l_at_freelists.org<mailto:oracle-l_at_freelists.org>>
| >
| > Date:
| >
| >
| > 2014.02.28 15<tel:2014.02.28%2015>: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<mailto:oracle-l-bounce_at_freelists.org> [oracle-l-bounce_at_freelists.org<mailto:oracle-l-bounce_at_freelists.org>]
on
| > behalf of Laimutis.Nedzinskas_at_seb.lt<mailto:Laimutis.Nedzinskas_at_seb.lt> [Laimutis.Nedzinskas_at_seb.lt<mailto:Laimutis.Nedzinskas_at_seb.lt>]
| > * Sent:* 28 February 2014 12:54
| > * To:* oracle-l_at_freelists.org<mailto: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<mailto:keyantech_at_gmail.com>>
| >
| > To:
| >
| >
| > "oracle-l_at_freelists.org<mailto:oracle-l_at_freelists.org>" <oracle-l_at_freelists.org<mailto:oracle-l_at_freelists.org>>
| >
| > Date:
| >
| >
| > 2014.02.28 14<tel:2014.02.28%2014>: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<http://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-l
Received on Thu Mar 06 2014 - 23:02:57 CET

Original text of this message