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 @jloracle ________________________________ From: Thiago Maciel [thiagomaciel@xxxxxxxxx] 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@xxxxxxxxxxxxxxxxxx<mailto:jonathan@xxxxxxxxxxxxxxxxxx>> 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@xxxxxxxxx<mailto:keyantech@xxxxxxxxx>> To: <Jed_Walker@xxxxxxxxxxxxxxxxx<mailto:Jed_Walker@xxxxxxxxxxxxxxxxx>> Cc: <laimutis.nedzinskas@xxxxxx<mailto:laimutis.nedzinskas@xxxxxx>>; <oracle-l@xxxxxxxxxxxxx<mailto: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<mailto: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> [mailto: | > oracle-l-bounce@xxxxxxxxxxxxx<mailto:oracle-l-bounce@xxxxxxxxxxxxx>] *On Behalf Of *Laimutis.Nedzinskas@xxxxxx<mailto:Laimutis.Nedzinskas@xxxxxx> | > *Sent:* Friday, February 28, 2014 6:33 AM | > *To:* oracle-l@xxxxxxxxxxxxx<mailto: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<mailto:jonathan@xxxxxxxxxxxxxxxxxx>> | > | > To: | > | > | > "Laimutis.Nedzinskas@xxxxxx<mailto:Laimutis.Nedzinskas@xxxxxx>" <Laimutis.Nedzinskas@xxxxxx<mailto:Laimutis.Nedzinskas@xxxxxx>>, " | > oracle-l@xxxxxxxxxxxxx<mailto:oracle-l@xxxxxxxxxxxxx>" <oracle-l@xxxxxxxxxxxxx<mailto:oracle-l@xxxxxxxxxxxxx>> | > | > 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 | > @jloracle | > ------------------------------ | > | > | > *From:* oracle-l-bounce@xxxxxxxxxxxxx<mailto:oracle-l-bounce@xxxxxxxxxxxxx> [oracle-l-bounce@xxxxxxxxxxxxx<mailto:oracle-l-bounce@xxxxxxxxxxxxx>] on | > behalf of Laimutis.Nedzinskas@xxxxxx<mailto:Laimutis.Nedzinskas@xxxxxx> [Laimutis.Nedzinskas@xxxxxx<mailto:Laimutis.Nedzinskas@xxxxxx>] | > * Sent:* 28 February 2014 12:54 | > * To:* oracle-l@xxxxxxxxxxxxx<mailto: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<mailto:keyantech@xxxxxxxxx>> | > | > To: | > | > | > "oracle-l@xxxxxxxxxxxxx<mailto:oracle-l@xxxxxxxxxxxxx>" <oracle-l@xxxxxxxxxxxxx<mailto:oracle-l@xxxxxxxxxxxxx>> | > | > 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 | > | > -- | > //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 -- //www.freelists.org/webpage/oracle-l