This discussion is archived
12 Replies Latest reply: Jan 30, 2013 1:17 PM by jgarry RSS

dumb question:   back out plan for rebuilding indexes...

user12159859 Newbie
Currently Being Moderated
I know this is going to sound like a dumb question, and perhaps I should consider changing my alias to dumb-dba but I think that name is already taken.... Ha, ha... :-)

I'm at a site that has very strict change management rules, and even running a job to rebuild indexes requires a Change Request, and a backout plan.

I'm running Oracle 11g EE (not that it matters).

Any idea what to put down for "Backout Plan"?

As far as I know, the only backout would be to enter Ctrl-C while rebuilding the index and Oracle would automatically revert back to using the original index.
Or, if something crazy happened and the index hit corrupt blocks or something, just rebuild it again.

Any other ideas?
  • 1. Re: dumb question:   back out plan for rebuilding indexes...
    sb92075 Guru
    Currently Being Moderated
    why are you rebuilding index at all?

    post SQL & results that show index rebuild is appropriate action.
  • 2. Re: dumb question:   back out plan for rebuilding indexes...
    user12159859 Newbie
    Currently Being Moderated
    Depending on who you listen to: Don B., Jonathan Lewis, Richard Foote, etc., I realize this is a controversial subject.
    But the truth is that I've proven it enough times to myself that rebuilding indexes gives me back unused space, as well as improves performance (especially on a highly active OLTP database).
    Bitmap indexes is a totally different subject, but I'm only talking about b-tree indexes for now.

    Proof is in the pudding, and I've tested the pudding enough times to know the benefit is there.
    I have scripts that show leaf levels at 3, but besides that, it still gives faster performance after rebuilding.

    Deeper subjects include whether it is worth putting in large block sizes and such, but as Jonathan and Richard have convinced me for now, I'm not going to do that until I can test it and prove it to myself.

    Keeping indexes in a separate tablespace on a separate LUN is another arguable benefit, but I'm still holding out on that one while using properly configured LUNs on a SAN.

    Anyway, I actually wasn't interested to get into discussion of whether or not they are worthwhile to rebuild, verses coalesce, or whatever, but rather a simple (dumb) answer to a (dumb) question on what the backout plan might be for such a subject. My answer would be to rebuilt it again, or Control-C to roll it back.

    Do you know what I mean? :-)

    Edited by: user12159859 on Jan 30, 2013 8:14 AM
  • 3. Re: dumb question:   back out plan for rebuilding indexes...
    Rob_J Journeyer
    Currently Being Moderated
    Are you rebuilding them online? I would say that the backout plan would be to re-create the index. Spool out a list of the DDL first and that's your script to re-create them all.
  • 4. Re: dumb question:   back out plan for rebuilding indexes...
    user12159859 Newbie
    Currently Being Moderated
    yeah,that's about all I can do. That works for me. Mainly needing a sanity check.
    Thanks.
  • 5. Re: dumb question:   back out plan for rebuilding indexes...
    jgarry Guru
    Currently Being Moderated
    >
    Keeping indexes in a separate tablespace on a separate LUN is another arguable benefit, but I'm still holding out on that one while using properly configured LUNs on a SAN.
    Hmmmm. Need a sanity check there. What are the arguments? In general, any performance benefit has been in the simple spreading of I/O across devices, a side effect of such a separation from olden times which has evolved into a myth. Within a SAN, it is difficult to improve things with such a separation, over simply assigning more spindles to one LUN. Even if you can separate out LUN access through separate controllers, you are better off looking at tablespace access and isolating the most used, which on an OLTP system, will often be undo (and redo/archiving have completely different characteristics). It's a problem of optimally allocating fewer bottlenecked data access paths than you have data.

    I suggest you find the classic SAME paper.

    I also can't help wondering how long it takes for your performance to degrade after rebuilding the indices, and by how much. The space I'm sure you can easily quantify.
  • 6. Re: dumb question:   back out plan for rebuilding indexes...
    user12159859 Newbie
    Currently Being Moderated
    Good questions jqarry.

    First, I tend to agree that simply adding more disks to a LUN is the best way to improve performance; hence, wide stripes of 200+ spindles. My last job, we routinely used 400+ physical drives to stripe across and I still got the argument that logical and physical pathing still made a difference in performance, therefore putting the indexes in their own tablespace on a separate LUN (wide striped as well) gave better performance. (This was IBM P6 and P7 servers connected via SVC to their SAN.)

    I'm not sure I understand how a 'logical' LUN can make any better difference in performance, but after much arguing with our sys admins and having IBM attest to the same, I finally capitulated and set up separate index tablespaces on a separate LUN. Did I notice any improvement: No. :-) But who am I to argue.

    I checked the top events of AWR reports and db_file_sequential_read was not in the top 2 or 3 at least. But then again, it wasn't to begin with either, so how could we tell if we had improvement. We were not I/O bound by the fact that the db_file_sequential_read was not in the top wait events, and we did use a lot of indexes. Almost all queries used indexes.

    As I said, there is a lot of controversial discussions on this subject, and to be honest, I really wish I could understand it better. But I think the only real way to know if through true scientific testing.
    Theory is theory. Only true way to know is by true testing and comparing apples with apples. Perhaps using RAT could work by taking an RMAN copy of the database replay and applying it in a test environment and doing true testing, but we both know that would be an enormous amount of work for a sole DBA who also has to take care of real time production databases and issues.

    Next, regarding rebuilding of indexes, it is hard to say, except that each database is different based on amount of OLTP activity (updates, deletes, inserts, etc.). For our PeopleSoft databases, we found that rebuilding the indexes every three months was appropriate in a moderate organization size of 10,000 employees who used the PeopleSoft database for all of the HR related activities.

    PS: Yes, I'm a firm believe in SAME. RAID 10 all the way, or even RAID 50. (Raid 5 plus 1 + 0 on a well balanced SAN - RAID 5 parity checking has no noticeable impact what-so-ever, especially with large caches available on the SVC controllers.)

    Edited by: user12159859 on Jan 30, 2013 10:07 AM
  • 7. Re: dumb question:   back out plan for rebuilding indexes...
    6363 Guru
    Currently Being Moderated
    user12159859 wrote:
    Depending on who you listen to: Don B., Jonathan Lewis, Richard Foote, etc., I realize this is a controversial subject.
    Well only if you are actually crazy enough to believe the first of those sources, everyone else seems to agree that it is in the vast majority of cases it is a useless exercise.
    But the truth is that I've proven it enough times to myself that rebuilding indexes gives me back unused space, as well as improves performance (especially on a highly active OLTP database).
    Proof is in the pudding, and I've tested the pudding enough times to know the benefit is there.
    I have scripts that show leaf levels at 3, but besides that, it still gives faster performance after rebuilding.
    Can you post the proof that it improves performance? I ask because no matter how many times I hear this, no one has yet posted such proof and that makes me somewhat suspicious. Do you test performance of all the queries in the OLTP database? That sounds like it might be a lot, especially on a highly active OLTP database. If you do not test the performance of all the queries and DML how do you know that some do not also slow down?

    Also are these improvements temporary or permanent? I assume temporary since you refer to this as if it is an ongoing exercise. If temporary how long do the performance improvements last and how much does performance degrade when it starts to degrade, because knowing index structure it would seem the degradation could be quite severe so that performance at times is actually worse than before the rebuild.
    Anyway, I actually wasn't interested to get into discussion of whether or not they are worthwhile to rebuild, verses coalesce, or whatever, but rather a simple (dumb) answer to a (dumb) question on what the backout plan might be for such a subject.
    I think the simple dumb answer would depend on whether the operation you want to plan for backing out should be done in the first place.
  • 8. Re: dumb question:   back out plan for rebuilding indexes...
    user12159859 Newbie
    Currently Being Moderated
    Well only if you are actually crazy enough to believe the first of those sources, everyone else seems to agree that it is in the vast majority of cases it is a useless exercise.
    LOL... so noted!
    Can you post the proof that it improves performance?
    Unfortunately, that was at my previous job. I'm at a new job now with indexes that have never been rebuilt, so I will test and repost once I have the results.
     Do you test performance of all the queries in the OLTP database? That sounds like it might be a lot, especially on a highly active OLTP database. If you do not test the performance of all the queries and DML how do you know that some do not also slow down?
    Well, to some degree, you are right. But, if you've done your due testing to determine the best settings for optimizer_index_cost_adj and optimizer_index_caching, then you've set the use of indexes to be most beneficial for your longer running queries. You can't seem to benefit both ends of the spectrum at the same time, at least not without using profiles. But just by using standard queries and indexes, you have to pick a happy medium. At any event, good statistics and indexes are going to be better than bad statistics and old indexes that are down at 4 or 5 leaf levels. Now, whether to coalesce indexes or rebuild would be another topic I have to test myself rather than trusting other people's testing because their environments may be so radically different, especially if it is a pristine test environment.
    Also are these improvements temporary or permanent? 
    yes, temporary... usually after rebuilding every three months or so, great performance gains are realized on large jobs that reduce from 1-1/2 hours down to 45 minutes or less.
  • 9. Re: dumb question:   back out plan for rebuilding indexes...
    Jonathan Lewis Oracle ACE Director
    Currently Being Moderated
    user12159859 wrote:

    Any idea what to put down for "Backout Plan"?
    I think it depends what it is you're supposed to be backing out from. I can think of two options:
    1) The rebuild it taking far too long and is impacting performance
    2) The rebuild is complete, and seems to have caused a performance problem.

    In the first case, killing the session (if that's possible on your platform) seems appropriate.

    In the second case, you're a bit stuck - however there are two possibilities to consider: save the index stats before the rebuild and write them back after the rebuild in case it's the change in stats that has introduced changes in plans; alternatively, make sure you know how much free space there was on average in each index leaf block before the rebuild and rebuild it again with pctfree set to allow that much free space after the rebuild - if it was that change in block usage that caused a concurrency problem that might help - you might want to work out a suitable initrans at the same time.

    Regards
    Jonathan Lewis
  • 10. Re: dumb question:   back out plan for rebuilding indexes...
    user12159859 Newbie
    Currently Being Moderated
    Thank you Jonathan. I think you have the correct answer here and that's what i should do.
    Thanks for your suggestion.

    I've looked for ways to save index stats, and referencing your web page on saving table stats seems to be similar enough to do the same with index stats.
    But how would I restore them (all) to all indexes, or would I need to do that?

    I looked at the dbms_stats procedure, and the only things I could find that are even close are:
    PROCEDURE RESTORE_FIXED_OBJECTS_STATS
     Argument Name                  Type                    In/Out Default?
     ------------------------------ ----------------------- ------ --------
     AS_OF_TIMESTAMP                TIMESTAMP WITH TIME ZONE IN
     FORCE                          BOOLEAN                 IN     DEFAULT
     NO_INVALIDATE                  BOOLEAN                 IN     DEFAULT
    
    PROCEDURE RESTORE_SCHEMA_STATS
     Argument Name                  Type                    In/Out Default?
     ------------------------------ ----------------------- ------ --------
     OWNNAME                        VARCHAR2                IN
     AS_OF_TIMESTAMP                TIMESTAMP WITH TIME ZONE IN
     FORCE                          BOOLEAN                 IN     DEFAULT
     NO_INVALIDATE                  BOOLEAN                 IN     DEFAULT
    
    PROCEDURE RESTORE_TABLE_STATS
     Argument Name                  Type                    In/Out Default?
     ------------------------------ ----------------------- ------ --------
     OWNNAME                        VARCHAR2                IN
     TABNAME                        VARCHAR2                IN
     AS_OF_TIMESTAMP                TIMESTAMP WITH TIME ZONE IN
     RESTORE_CLUSTER_INDEX          BOOLEAN                 IN     DEFAULT
     FORCE                          BOOLEAN                 IN     DEFAULT
     NO_INVALIDATE                  BOOLEAN                 IN     DEFAULT
    but no RESTORE_INDEX_STATS (in v.11.2.0.2)

    Edited by: user12159859 on Jan 30, 2013 12:39 PM
  • 11. Re: dumb question:   back out plan for rebuilding indexes...
    6363 Guru
    Currently Being Moderated
    user12159859 wrote:
    3360 wrote:

    Can you post the proof that it improves performance?
    Unfortunately, that was at my previous job. I'm at a new job now with indexes that have never been rebuilt, so I will test and repost once I have the results.
    Thanks, so far this proof has been elusive.

    >>
    Do you test performance of all the queries in the OLTP database? That sounds like it might be a lot, especially on a highly active OLTP database. If you do not test the performance of all the queries and DML how do you know that some do not also slow down?
    Well, to some degree, you are right. But, if you've done your due testing to determine the best settings for optimizer_index_cost_adj and optimizer_index_caching, then you've set the use of indexes to be most beneficial for your longer running queries.
    Indexes are typically more effective for shorter running queries, since indexes are designed to access small amounts of data I would be quiestioning why these processes run so long. It seems you may be tuning your system for queries that make inappropriate use of indexes at the expense of queries that make best use of indexes.
    But just by using standard queries and indexes, you have to pick a happy medium. At any event, good statistics and indexes are going to be better than bad statistics and old indexes that are down at 4 or 5 leaf levels.
    I agree with the statistics part, I have seen no evidence that the age of indexes has anything to do with performance, or that it will reduce the number of leaf levels

    {message:id=1915395}
    yes, temporary... usually after rebuilding every three months or so, great performance gains are realized on large jobs that reduce from 1-1/2 hours down to 45 minutes or less.
    A job that makes intensive use of indexes for 45 minutes would appear to be either badly designed or going specatacularly wrong at some point and it would probaly be better to look into that than spend time and effort rebuilding indexes.
  • 12. Re: dumb question:   back out plan for rebuilding indexes...
    jgarry Guru
    Currently Being Moderated
    3360 wrote:
    >
    >
    {message:id=1915395}
    yes, temporary... usually after rebuilding every three months or so, great performance gains are realized on large jobs that reduce from 1-1/2 hours down to 45 minutes or less.
    This doesn't seem (given the small amount of info) an unreasonable situation for index rebuilding - sometimes the strange old app code does demand it.

    >
    A job that makes intensive use of indexes for 45 minutes would appear to be either badly designed or going specatacularly wrong at some point and it would probaly be better to look into that than spend time and effort rebuilding indexes.
    Yes, especially if the optimizer_index* parameters are pushing batch things into inappropriate nested loops or whatever. See http://richardfoote.wordpress.com/2009/09/01/optimizer_index_caching-parameter/ (among many others)

Legend

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