This discussion is archived
1 5 6 7 8 9 11 Previous Next 163 Replies Latest reply: Oct 22, 2009 3:23 PM by Hoek Go to original post RSS
  • 90. Re: Index rebuild
    635471 Expert
    Currently Being Moderated
    burleson wrote:
    Maybe you should credit Jonathan with correcting the mistakes in your article
    Because that would be a lie.

    There were NO mistakes in the article, NONE!

    His suggestions were all trivial and nitpicky, nothing much at all . . .
    Confusing DBA_SEGMENTS and DBA_TABLES for example ...
    Lewis seems to have trouble foxusing, his postats are all over the place.
    Postats are often the first casualty of having trouble with foxusing.
    Are you still a data warehouse engineer?
    Are you still a database architect?
  • 91. Re: Index rebuild
    108476 Journeyer
    Currently Being Moderated
    Confusing DBA_SEGMENTS and DBA_TABLES for example ...
    There was no confusion.

    I stated dba_segments because dba_tables does not include index segments.

    Lewis asked me to change it to dba_tables, and I refused, check it out for yourself . . .
    Postats are often the first casualty of having trouble with foxusing.
    More attempted subtrefuge . . . Look, DKB made a typo . . .

    BFD
    Are you still a database architect?
    There is a difference.

    McGraw-Hill gave me that title, it was NOT self-appointed, like your title!

    It's the same as being an Oracle "expert". It's OK when the courts call you an expert, but it's dishonest to self-appoint yourself as an expert, especially when it's done without any evidence nor proof of extertise . . .
    database architect?
    And, by the way, I complained about that job title, asking that they change it to something more appropriate like "Grand genuis DBA lauriate of the world", but they would not do it . . .
  • 92. Re: Index rebuild
    635471 Expert
    Currently Being Moderated
    burleson wrote:
    *- People without any credientials [sic] in enginering call themselves software engineers.*
    I know a fellow who calls himself an "Oracle engineer" and he barely gradiated [sic] from high school.
    Disgraceful. As a Masters of Aeronautical Engineering I'm absolutely livid with indifference.
  • 93. Re: Index rebuild
    635471 Expert
    Currently Being Moderated
    burleson wrote:
    Lewis posts in this thread are all over the place, way too much subtrefuge [[sic]] for my taste, too little on-point communication . . .

    Even when asked direct questions about index rebuilding, he misses them, and instead responds with something out of left field, like his critique of an unrelated article . . .
    Ah, thanks for the reminder ... did you ever provide a response on that question about the SAP table that your clients rebuild every day on your advice? Experts seemed to think that they could help you help your clients avoid having to do that. Now where was that thread ... Re: corrupted indexes ... I think we can call Richard Foote an expert on indexes.
  • 94. Re: Index rebuild
    635471 Expert
    Currently Being Moderated
    burleson wrote:
    More attempted subtrefuge (sic) . . . Look, DKB made a typo . . .
    I do not think that word means what you think it means.
  • 95. Re: Index rebuild
    Jonathan Lewis Oracle ACE Director
    Currently Being Moderated
    burleson wrote:
    Confusing DBA_SEGMENTS and DBA_TABLES for example ...
    There was no confusion.

    I stated dba_segments because dba_tables does not include index segments.

    Lewis asked me to change it to dba_tables, and I refused, check it out for yourself . . .
    I think you must be looking at this comment:
    3) ".. a table that continues to extend, even though the dba_segments view shows lot's of free blocks."
    I think you meant the dba_tables view, not the dba_segments view.
    I raised that point because the number of free blocks is an attribute of dba_tables, not dba_segments. You can't see "free blocks" in dba_segments.

    I hesitate to complicate matters, by the way, but if if you pre-allocate an extent to an object and associate it with an instance, then all the blocks of that extent have to be formatted and put into the freelist group associated with that extent. I can't remember off-hand the exact timing for the formatting (and haven't checked it since I had to sort out some problems many years ago on a large OPS system on 7.3) - it may be at the moment you allocate the extent, it may be postponed until you allocate an extent to another instance that forces the high water mark to jump past the end of the current extent. As a consequence, it would be perfectly valid for some freelist groups to have far more blocks on their freelists than others - and an attempt to rebalance the freelists could actually introduce a RAC contention problem.

    So you might want to add a brief comment that if a site deliberately uses the "allocate extent" mechanism, then the output of the script need to be reviewed with some caution.


    Regards
    Jonathan Lewis
    http://jonathanlewis.wordpress.com
    http://www.jlcomp.demon.co.uk

    To post code, statspack/AWR report, execution plans or trace files, start and end the section with the tag {noformat}
    {noformat} (lowercase, curly brackets, no spaces) so that the text appears in 
    fixed format
    .
    
    "Science is more than a body of knowledge; it is a way of thinking" 
    Carl Sagan                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                        
  • 96. Re: Index rebuild
    Jonathan Lewis Oracle ACE Director
    Currently Being Moderated
    Jonathan Lewis wrote:
    burleson wrote:
    Hi Jonathan,


    If what you say is true, then simply get Oracle to issue a bug number.  I'm sure that you can convince them with your "proof".
    It's not a bug, it's simply the expected behaviour - as documented on Metalink and demonstrable with some simple programming.

    I have made an error in the statement though - the requirement for co-prime values applies only in single instance systems (as per the exchange of letters you had with Rich Niemiec). In RAC systems the freelist group block selected is dependent on the Instance id.
    If you want some Oracle documentation about this, then here's an extract from Metalink note: 1029850.6

    <ul>
    In a non OPS environment or OPS and Single-Instance environment:
    Free list group is: (P % NFB) + 1
    </ul>

    Personally, though, I wouldn't take the note on trust 100% because (a) metalink isn't always right (b) the note references 7.3 as the only version, and (c) although the modified date on the note is 2008, the note itself is clearly much older. (Obviously it's easy to check that the formula is still relevant, though.)

    Since we have
        free list group chosen = mod(process_id, freelist groups) + 1
    and, as you know,
        (process) freelist chosen = mod(process_id, freelists) + 1
    the conclusion follows that if you are going to use multiple freelists groups in single instance Oracle then the number of freelists and freelist groups should not have any common factors.

    Regards
    Jonathan Lewis
    http://jonathanlewis.wordpress.com
    http://www.jlcomp.demon.co.uk

    To post code, statspack/AWR report, execution plans or trace files, start and end the section with the tag {noformat}
    {noformat} (lowercase, curly brackets, no spaces) so that the text appears in 
    fixed format
    .
    
    
    "Science is more than a body of knowledge; it is a way of thinking" 
    Carl Sagan                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                            
  • 97. Re: Index rebuild
    Jonathan Lewis Oracle ACE Director
    Currently Being Moderated
    Don Lewis wrote:

    In this house, we OBEY the laws of thermodynamics!
    Homer Simpson
    Possibly the funniest line that they've ever written.

    Regards
    Jonathan Lewis
    http://jonathanlewis.wordpress.com
    http://www.jlcomp.demon.co.uk

    To post code, statspack/AWR report, execution plans or trace files, start and end the section with the tag {noformat}
    {noformat} (lowercase, curly brackets, no spaces) so that the text appears in 
    fixed format
    .
    
    "Science is more than a body of knowledge; it is a way of thinking" 
    Carl Sagan                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                            
  • 98. Re: Index rebuild
    Jonathan Lewis Oracle ACE Director
    Currently Being Moderated
    When all the sound and fury has been eliminated, the bottom line on indexes is this:
    <ul>
    The baseline for B-tree indexes in Oracle is that there is usually no significant benefit in rebuilding (or even coalescing) them.

    There are a few special cases where indexes will benefit from regular house-keeping (which is more likely to be a coalesce than a rebuild)

    There are a few special cases where rebuilding an index will cause subsequent performance problems - and even a coalesce could cause problems.
    </ul>

    There are therefore two "simple" strategies to adopt for indexes:

    <ul>
    Make it your default action to rebuild every+ index on a regular basis and then work out, over a period of time, which indexes (if any) should definitely be removed from the rebuild schedule. (A key, though far from conclusive, warning symptom to watch for - certain transactions or reports take longer to run immediately after the rebuild)

    Make it your default action not_ to rebuild any_ the indexes on a regular basis and then work out, over a period of time, which indexes (if any) should be rebuilt (or coalesced) on a regular basis. (A key, though far from conclusive, warning symptom to watch for - certain transactions or reports take longer and longer to run as time passes)
    </ul>


    Supplementary comments:
    1) If your system is completely idle over the evening or weekends and it is feasible to rebuild every index in the free time, then the first option may appear to be the easier option. (Finding indexes that should not be rebuilt is usually a little easier than finding indexes that should be rebuilt.)

    2) Even if you decide to rebuild every index every weekend, or even every evening, you may still find that there are indexes that could benefit from an even more frequent coalesce. (On one occasion I even advised a client to coalesce a critical index once per hour to deal with a very specific activity).

    3) When you rebuild an index you automatically recompute the stats (from 10g onwards), and in earlier versions you may have a routine that gathers stats after a rebuild or coalesce anyway. Sometimes a change in performance may not be because the index is "healthier" or "cleaner" - it may be because the statistics have caused Oracle to change the execution plan. (This comment is relevant whether the performance has improved or got worse). So when you have a candidate for the non-default treatment (whatever your choice of default), remember to check execution paths before and after rebuilding.

    Regards
    Jonathan Lewis
    http://jonathanlewis.wordpress.com
    http://www.jlcomp.demon.co.uk

    To post code, statspack/AWR report, execution plans or trace files, start and end the section with the tag {noformat}
    {noformat} (lowercase, curly brackets, no spaces) so that the text appears in 
    fixed format
    .
    
    "Science is more than a body of knowledge; it is a way of thinking" 
    Carl Sagan                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                
  • 99. Re: Index rebuild
    ji li Pro
    Currently Being Moderated
    Wow... this sure is a long thread.

    So what is the final concensus:

    It seems the majority agree that B-Tree indexes don't need to be rebuilt (or coalesced)
    and bitmap indexes need rebuilding frequently (on tables with moderate data changes)

    Richard Foote says: ?
    Tom Kyte's page says B-tree indexes dont' need rebuilding
    Don Burleson's opinion appears to suggest frequent rebuilding of b-tree indexes (on moderate data changes)
    Jonathan Lewis: ?

    Edited by: ji li on Oct 14, 2009 1:03 PM

    Corrected name.

    Edited by: ji li on Oct 14, 2009 1:07 PM
  • 100. Re: Index rebuild
    311441 Employee ACE
    Currently Being Moderated
    ji li wrote:

    So what is the final concensus:

    It seems the majority agree that B-Tree indexes don't need to be rebuilt (or coalesced)
    and bitmap indexes need rebuilding frequently (on tables with moderate data changes)

    Richard Foote says: ?
    Tom Kyte's page says B-tree indexes dont' need rebuilding
    Don Burleson's opinion appears to suggest frequent rebuilding of b-tree indexes (on moderate data changes)
    Jonathan Lewis: ?
    Hi Ji

    Well Richard Foote agrees with Jonathan's summary above.

    The main point I would add to Jonathan's comments though is that for more and more environments, with larger and larger databases going into the many terabytes and with ever increasing availability requirements, option 1) is simply not a viable option. This becomes even less so when one needs to also consider the potential impact on features such as dataguard, flashback archive and flashback recovery, etc. when one attempts rebuilding massive volumes of indexes.

    Especially, when for the vast majority of such indexes, the rebuild was a pointless exercise anyways.

    I can't even begin to think how anyone can consider such an operation as low risk and low impact !!

    In these environments, one needs to go down the option 2 track and be selective in what one rebuilds and do so only when it actually makes a difference.

    One correction to your comment. I wouldn't necessarily consider rebuilding bitmap indexes frequently just becuase of moderate (or even high) changes since 10g. Oracle is much much smarter in how it deals with subsequent inserts and changes to bitmap indexes by cleverly adjusting rowid ranges where possible so they don't degenerate anywhere near as frequently or as bad as they did prior to 10g.

    In summary:

    1) By default, don't just rebuild indexes.

    2) In special cases, considering coalescing before rebuilding problematic indexes but understand the differences and when one is better than the other.

    3) In such cases, consider why the index is being problematic and see if actions to prevent the issue in the first can be implemented.

    However, one really does need to have at least a basic understanding of how Oracle indexes function before one can maintain them appropriately. May I suggest that both Jonathan Lewis and Tom Kyte that you've quoted above obviously both have an excellent understanding of indexes and so can make appropriate recommendations.

    Cheers

    Richard Foote
    http://richardfoote.wordpress.com/
  • 101. Re: Index rebuild
    BillyVerreynne Oracle ACE
    Currently Being Moderated
    burleson wrote:

    I've been a full-time, working DBA for over 25 years now, and I've never needed to write an artificial test case.  Not once.

    It baffles me why any practicng DBA would want to write a test case, when they have a real-world test database, full of real data and waiting to be used . . .
    As it baffles me that you have for the last 25 years ignored one of the very basic and most fundamental concepts in engineering.
    Simplification of a complex problem in order to isolate and identify the root cause.

    And this hold true from dealing with problems in aircraft design, to problems with software systems.


    Do I need to a real world database to show the issues caused by not using bind variables? No.

    Do I need a real world database to show the issues caused by fetching across commits? No.

    Do I need a real world database to show the issues caused updating columns that are bitmap indexed? No.


    These and most other Oracle issues can be investigated and analysed using test cases. And there is nothing so-called artificial about these test cases either. As these are run in the exact same Oracle environment as so-called real-world databases.

    Or are you claiming that I/O and wait states and event and so on are a royal blue colour on real-world databases and a measly yellow on Oracle databases that are not so-called real-world production databases?
  • 102. Re: Index rebuild
    635471 Expert
    Currently Being Moderated
    Billy  Verreynne  wrote:
    As it baffles me that you have for the last 25 years ignored one of the very basic and most fundamental concepts in engineering.
    Simplification of a complex problem in order to isolate and identify the root cause.

    And this hold true from dealing with problems in aircraft design, to problems with software systems.
    Yes, that's very true. Aircraft are a good analogy because they are so complex that anything other than an incremental design change can easily exceed our abilities to model with confidence. However, that doesn't stop us from testing the basics, like sticking a completely flat plate in a wind tunnel or bending a square cross-section beam to see what happens and compare the results with theory and simulation. You may be dealing with an upgraded material or surface finish or some other potentially unpredictable change, but you do exactly the same tests in the new case as in the old ones in order to understand the behavioural changes. Only a fool would do otherwise.

    The parallels ought to be obvious.
  • 103. Re: Index rebuild
    706417 Explorer
    Currently Being Moderated
    Hi Don,

    My criteria for index rebuilding are:
    1. Is any index of a silly size? e.g. way out of proportion to the table? Rebuild if getting out of hand and get code fixed to prevent a repeat.
    2. Are queries slowing down? If slowing down, then is an index a likely culprit? If so, does it look to be skewed? Rebuild if getting out of hand and get code fixed to prevent a repeat.
    3. The related table had to be moved - if < 10g
    4. The index is corrupt (rare in modern Oracle).

    3 & 4 = always. 1 & 2 = maybes.

    I haven't rebuilt an index for reasons 1 or 2 for years.


    Regards - Don Lewis
  • 104. Re: Index rebuild
    Randolf Geist Oracle ACE Director
    Currently Being Moderated
    burleson wrote:
    I've been a full-time, working DBA for over 25 years now, and I've never needed to write an artificial test case.  Not once.

    It baffles me why any practicng DBA would want to write a test case, when they have a real-world test database, full of real data and waiting to be used . . .
    And yet Oracle decided to add the [SQL Test Case Builder (TCB)|http://download.oracle.com/docs/cd/E11882_01/server.112/e10821/sql_overview.htm#PFGRF16001] to 11g and even found it so important to backport it to 10.2.0.4 so that it can be used to easily automatically generate reproducible test cases that optionally can be uploaded to Oracle Support.

    Regards,
    Randolf

    Oracle related stuff blog:
    http://oracle-randolf.blogspot.com/

    SQLTools++ for Oracle (Open source Oracle GUI for Windows):
    http://www.sqltools-plusplus.org:7676/
    http://sourceforge.net/projects/sqlt-pp/
1 5 6 7 8 9 11 Previous Next

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points