This discussion is archived
1 2 3 4 5 6 7 Previous Next 92 Replies Latest reply: Nov 27, 2009 12:11 AM by Hemant K Chitale Go to original post RSS
  • 45. Re: corrupted indexes
    Hemant K Chitale Oracle ACE
    Currently Being Moderated
    Scenario 2
  • 46. Re: corrupted indexes
    Jonathan Lewis Oracle ACE Director
    Currently Being Moderated
    Hemant K Chitale wrote:
    Scenario 2
    Hemant,

    Does this mean that your think I've probably managed to explain why your index is getting much larger than you were expecting it to be ? If so you may want to run a coalesce command against the index very frequently - perhaps as often as every hour or two (start cautiously, and then reduce the time slowly if the coalesce doesn't seem to cause any undesirable side effects).


    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                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                            
  • 47. Re: corrupted indexes
    515958 Pro
    Currently Being Moderated
    (start cautiously, and then reduce the time slowly if the coalesce doesn't seem to cause any undesirable side effects).
    Sir,

    What may be the side effect?
    Are you referring to the resource utilization during coalesce process?
    Is shrink an option?
    What are the trade-offs of using coalesce and shrink?

    Regards,
    S.K.

    Edited by: Santosh Kumar on Sep 26, 2009 11:47 AM(Added two more questions)
  • 48. Re: corrupted indexes
    Jonathan Lewis Oracle ACE Director
    Currently Being Moderated
    Santosh Kumar wrote:
    (start cautiously, and then reduce the time slowly if the coalesce doesn't seem to cause any undesirable side effects).
    Sir,

    What may be the side effect?
    Are you referring to the resource utilization during coalesce process?
    That's one thing to consider (reads, writes, and redo generation in particular) - the other main contender is the side effect of trying to do the coalesce and having other processes inserting data at the same time. The potential for buffer busy waits and latch contention climbs. If there's a recognisable pattern of usage, it may be best to do this just a couple of times each day shortly after the table has been busy and has just become less busy.

    Is shrink an option?
    Could be
    What are the trade-offs of using coalesce and shrink?
    I haven't investigated the shrink closely enough to make a sensible comment on the trade-offs.
    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                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                    
  • 49. Re: corrupted indexes
    Hemant K Chitale Oracle ACE
    Currently Being Moderated
    The table would be busy during the day as well, outside of the batch cycle, running (smaller) user jobs.

    I've scheduled a weekly rebuild.



    UPDATE BY HEMANT K CHITALE (the same person !) : 27-Nov-09

    I understand that Burleson has quoted this posting of mine, quite out of context at in his article on [Online Index Rebuilding.|http://www.dba-oracle.com/t_scheduling_oracle_index_rebuilding.htm]


    NOTE : I do not recommend rebuilding of ALL indexes weekly. This was a specific table (and I have identified only three such likely tables in a database of thousands of tables) that may require : Preferably a COALECE (as adviced by Jonathan Lewis) -- if I can get the code changed !! -- or, as the second best option, a regular REBUILD.

    The behaviour described in Oracle Bug#6447841 matches what I have seen. This is [clear later in this same thread|http://forums.oracle.com/forums/thread.jspa?messageID=3792540#3792540]


    Here are two postings on my blog where I discuss Rebuilding Indexes -- in general, not relating to the behaviour described in Bug 6447841.

    [1. Rebuilding Indexes|http://hemantoracledba.blogspot.com/2008/03/rebuilding-indexes.html]
    [2. Rebuilding Indexes - When and Why ?|http://hemantoracledba.blogspot.com/2008/03/rebuild-indexes-when-and-why.html]


    Hemant K Chitale
    http://hemantoracledba.blogpsot.com

    Edited by: Hemant K Chitale on Nov 27, 2009 4:12 PM
  • 50. Re: corrupted indexes
    108476 Journeyer
    Currently Being Moderated
    Hi Hemant,
    I've scheduled a weekly rebuild.
    Just curious, please:

    - Is your shop concerned about "justifying" index maintenance?

    - Will you be measuring the before-and-after effects?

    - Why a rebuild over a coalesce?

    Please advise. Thanks!
  • 51. Re: corrupted indexes
    Jonathan Lewis Oracle ACE Director
    Currently Being Moderated
    Hemant,

    So far we've only been looking for reasons why the index should have become larger than originally expected and then come up with a strategy for reducing the impact of the growth. The index grows to hold the volume of data that gets inserted at peak periods - and the index can't shrink after the data has been deleted.

    BUT - as I pointed out [+*in an earlier posting*+|http://forums.oracle.com/forums/message.jspa?messageID=3756591#3756591] this piece of code was clear written by someone who didn't understand Oracle's read-consistency of locking models. So it's worth thinking about how to solve the problem, rather than restricting ourselves to damage limitation.

    From your description of how this job works (and particularly the indication that as soon as one process commits, the data that it had inserted will immediately be deleted by the next process in the queue), it looks like a case where replacing the orginal table with a global temporary table (GTT) would be perfectly safe and very sensible.

    If you can use a GTT at this point, with the default "on commit delete rows" option - the INSERT will generate less redo, the data will "disappear" the moment the process commits, and there will never be any data for the DELETE statement to delete.

    You may have to watch out for odd execution plans if you join the GTT to other tables - but your current code presumably survives with some incorrect statistics; and there are various ways to create suitable statisitcs on a GTT if necessary.

    Obviously you will need to check with Peoplesoft support to see if they will allow you to make this change - but if they say it can't be done they ought to be able to explain why it won't work. You might also like to browse through [+*David Kurtz blog - The Peoplesoft DBA Blog*+|http://blog.psftdba.com/] to see what he has to say about using GTTs under Peoplesoft processes - it's a topic I've discussed with him a couple of times and I think he has something about it on his blog.

    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
    .
    
    
    "There are three classes of people. Those who see; those who see when they are shown; those who do not see."
    Leonardo da Vinci                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                
  • 52. Re: corrupted indexes
    Hemant K Chitale Oracle ACE
    Currently Being Moderated
    Yes, I have been following David Kurtz's postings. A number of ideas on GTTs, Dynamic Sampling, Partitioning etc are available.
    Implementing them in Peoplesoft
    a. Isn't straightforward when you have to go through tight change controls
    b. Isnt' easy when there is concern about "breaking" a vendor's supplied schema and whether subsequent patches/upgrades from the vendor will or will not break

    This isn't a home-grown application.
  • 53. Re: corrupted indexes
    Hemant K Chitale Oracle ACE
    Currently Being Moderated
    Don,

    It's not a question of "justifying" this or that. (I really don't favour religious wars). It's about putting in DDL in a running system. Any such commands must be outside of batch and normal user hours. A weekend is the best time and since the index segment grows significantly during the week (yes, I know that some people don't believe that such things happen), I might as well do a REBUILD.
    If I could put in the script to run 4 times a day, I'd use COALESCE.
  • 54. Re: corrupted indexes
    Jonathan Lewis Oracle ACE Director
    Currently Being Moderated
    Hemant K Chitale wrote:
    and since the index segment grows significantly during the week (yes, I know that some people don't believe that such things happen)
    I don't think anyone*** believes that such things don't happen - but some people are aware of the reasons why index segments can grow significantly, and know what should be done to stop them growing if it's really necessary; and some people know how to identify in advance the indexes that are likely to grow unreasonably and take defensive action to minimise risk and damage.


    *** Although I think I've seen Sybrand Bakker expliode with "You don't need to rebuild your indexes" occasionally. But he does let his exasperation burst into hyperbole from time to time.


    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                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                
  • 55. Re: corrupted indexes
    Randolf Geist Oracle ACE Director
    Currently Being Moderated
    Hemant K Chitale wrote:
    The table would be busy during the day as well, outside of the batch cycle, running (smaller) user jobs.

    I've scheduled a weekly rebuild.
    Hemant,

    now I'm a bit puzzled. What is the effect of rebuilding the index once a week, i.e. what is the damage limitation achieved?

    Do you mean to say that you see the index grow further during the next weeks if you don't shrink it in some way? According to Jonathan's "Scenario 2" the index grows due to concurrent inserts running, so the maximum size of the index should be determined by the highest concurrency that you encounter, and therefore the index shouldn't grow indefinitely.

    Regarding the "damage": Reading the "AskTom" thread I understand that you've demonstrated that the optimizer chooses an Index Fast Full Scan which is less efficient than a corresponding Full Table Scan due to the index size. You also seem to indicate that this is caused by locked statistics on that table when it was 500 rows in size, therefore the optimizer believes the index is small. Similar issues may affect the DELETE statement.

    What if you tell the optimizer the real size of the index? Obviously many operations would choose a full table scan instead, so this potential issue could possibly be solved by using more accurate statistics for the index.

    So far I've only read that the DELETE seems to suffer from this issue, and I suspect that using more appropriate statistics for the index could solve this issue. What else seems to be affected by the index size? Do you see range scans that have to visit a lot more leaf blocks than necessary? And if yes, does it really cause performance issues?

    Have you encountered that with more accurate statistics for the index other execution plans that are more efficient when using the index switch to a less efficient Full Table Scans instead?

    What I want to say here is: Why artificially shrink an index that is due to its usage pattern supposed to be "large"? What is affected by the index being large and how can this be addressed? If this can be solved using appropriate statistics, why bother with the index rebuild, only to have it grow again during the week, leading to more work during the processing due to necessary block splits and increased undo/redo usage?

    I wonder if the weekly rebuild really solves the issue - what about the index growth during the week, shouldn't your DELETEs already be negatively impacted by the index growth during the week?

    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/
  • 56. Re: corrupted indexes
    108476 Journeyer
    Currently Being Moderated
    Hi Randloph,
    Regarding the "damage": Reading the "AskTom" thread I understand that you've demonstrated that the optimizer chooses an Index Fast Full Scan which is less efficient than a corresponding Full Table Scan due to the index size.
    Remember, an index rebuild creates a "pristine" index, clean and fresh, and in accordance with Oracle's idea of what an optimal index structure should look like . . .

    Are you suggesting that the pristine state of a brand-new index is somehow sub-optimal?

    Please advise. Thanks!
  • 57. Re: corrupted indexes
    108476 Journeyer
    Currently Being Moderated
    Hi Hemant,
    It's not a question of "justifying" this or that. (I really don't favour religious wars).
    Me neither, just asking . . .

    If you have a downtime window for database maintenance, there is nothing to justify really, since there is no real cost . . .

    ***************************************************************
    A weekend is the best time and since the index segment grows significantly during the week (yes, I know that some people don't believe that such things happen),
    Yeah. Some people also say that that have "proven" that the moon landing was staged . . .

    What are you gonna do?

    ***************************************************************
    I might as well do a REBUILD.
    You are preaching to the Choir . . .

    People need to understand that a server depreciated rapidly, regardless of how much it is used . . .

    Hence, the cost of rebuilding objects into their pristine state is exactly zero!
  • 58. Re: corrupted indexes
    Randolf Geist Oracle ACE Director
    Currently Being Moderated
    burleson wrote:
    Remember, an index rebuild creates a "pristine" index, clean and fresh, and in accordance with Oracle's idea of what an optimal index structure should look like . . .

    Are you suggesting that the pristine state of a brand-new index is somehow sub-optimal?

    Please advise. Thanks!
    Don,

    have you read my complete post? The cost of rebuilding the index is not zero as you suggest in your other post. As already mentioned in my previous post, while growing the index again during subsequent operations the database has to work harder bringing the index back to its previous state, generating more undo and redo and potentially increasing the possibility for contention.

    And then the question, how often do you have to rebuild in order to really achieve a damage limitation? Is once a week sufficient, or do the DELETEs already suffer before the next rebuild is scheduled to run? Why not run a coalesce more often, since the rebuild is by default an offline operation requiring an exclusive lock, or needs to run as an online operation, increasing the complexity and the chances that things might go wrong (and even the online rebuild needs a short period of time with an exclusive lock which might a problem on a busy table).

    Why not address this particular issue using appropriate statistics (if there are no other side effects that I've asked for) and leave the index in its "natural" state?

    There are certainly cases where a regular coalesce or even a rebuild might be in order, but I'm just questioning here is a weekly rebuild really a reasonable solution to this particular problem.

    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/
  • 59. Re: corrupted indexes
    108476 Journeyer
    Currently Being Moderated
    Hi Randolf,

    Sorry, you did not answer my question, I'll repeat it:

    How is pristine state of a freshly rebuilt index sub-optimal?

    The cost of rebuilding the index is not zero as you suggest in your other post.
    Done properly during a scheduled downtime window, an index rebuild is 100% safe and risk free.

    Remember, hardware depreciates regardless of use, so the cost of the computing resources is exactly zero.

    There are certainly cases where a regular coalesce or even a rebuild might be in order
    Sure there are, and this may be one of them . . .

    I'm just questioning here is a weekly rebuild really a reasonable solution to this particular problem.
    Yup, that's exactly what you are doing.

    I don't know you, but you must be quite the guru to openly question Hemant's judgement  . . .

Legend

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