Skip to Content

I’m following up to 2 blogs on index maintenance:

How to take care of index corruptions in primary key indexes.Lars Breddemann

Rebuilding Index online using DB02Siddhesh Ghag
Lars commented “I would definitivly not recommend to rebuild your indexes regularly. Oracle is not THAT bad at maintaining them.”

Siddesh said “rebuilding index is surely not to be done regularly.”

I respectfully disagree; here’s why.

SAP Note 444287 “Checking the index storage quality”, describes 3 methods of verifying index quality.  And by quality, we are not talking “corruption” here.    To me, the term corruption means data loss or damage due to hardware and/or software faults, and needs to be investigated in a different manner.    For BI, indices may be invalidated as a result of data manipulation.  Corruption is a different matter.

For index quality, we should talk about fragmentation, or balancing, which relate to how much extra space is in the index, leading to longer access times, as well as the side effect of pushing good data out of our caches such as the DB_BLOCK_BUFFER (DB_CACHE in recent Oracle versions).

 

Two of the three methods in that note state you could lock the table if you use them.  So, I don’t.  I use the other method.  Our DBAs look at copies of production systems to analyze which tables are in need of index rebuilding, and then run maintenance jobs during quiet periods, usually Saturdays.  However, other table indices could be reorganized more often, using scheduling tools. 

 

Obligatory screen shots:

image

image

image

image

image

 

 

DANGER!

Do not attempt any of the following maneuvers unless you are a well-trained DBA. I am not talking certified, I am talking experience.  Me I was never certified, but I was mentored by the inimitable Jürgen Kirschner.  He loves to ask in his training class “how many DBAs in the room are certified?”  But I digress.  The risks here are great, including halting or damaging your company’s SAP system if you proceed without understanding.

 

Lars says Oracle maintains your indexes.  Here’s a view of a critical SAP table, VBDATA.  Everyone who knows what verbucher means, raise your hand.  I thought so (me neither).

= = = =
Analysis of Index storage quality
Table:                                       VBDATA
Index:                                       VBDATA~0
Owner:                                       SAPR3

Space per block:                   byte                             8,033
Space per index entry:             byte                             45.01
Number of table rows:                                               3,641
Number of blocks (calc.):                                              23

Number of blocks (alloc.):                                            192
Number of blocks in freelists / groups:                            0 /  0
Number of blocks (used):                                              192

Index storage quality:                %                                12
= = = = 

Here, the storage quality is 12%.  Bad.  Very bad.

This index should be reorganized on a regular basis.  We formerly did this daily, but it looks like that maintenance job hasn’t been maintained. 

What is happening here is the index is becoming unbalanced through constant inserts and deletes.  Over time, the space the index occupies will continue to grow though there may be zero rows at the time the system starts and stops.  Each deleted row continues to occupy space in the index segment until a rebuild is done.

Indexes for queuing tables need to be reorganized.  Check out the RFC tables! 

I’m off to the trouble ticket system.  See ya.

 

= = = =

Addendum

You should not think that my saying “floss regularly” means you should rebuild every index in your system on a regular basis.  As Lars points out in commentary, there are downsides to this activity, and you should know what performance improvements are made when doign any such maintenance.  A reference he quotes says ” More efficient index structures can reduce stress on buffer cache. Harder to formulate but requires consideration.”

I’ll toss out a formula:  extra buffer gets per row are bad; many extra are worse.  In the example above I noted we had not rebuilt the index for VBDATA~0 since, oh, about 3 weeks ago when we did our Unicode conversion.  What does the factor for buffer gets per row look like?

image

 

 

I would like to see 4.0 rows per buffer get, if we’re going through the primary index.  If I’m seeing 4.3 buffer gets, that means I’m getting 10% more blocks in my memory cache than necessary.  6.0 buffer gets per row mean I’m wasting 50% of the necessary space.  These values are easy to find via ST04.

We have scripts to rebuild indexes on  TRFCQIN, TRFCQOUT, ARFCSSTATE, and TRFCQSTATE.  We had others that I can’t find now.

Pointers to related SAP notes:

712098 – RSORAISQ: Index Storage Quality Management
830576 – Parameter recommendations for Oracle 10g
869521 – TM enqueues due to REBUILD ONLINE / CREATE ONLINE

To report this post you need to login first.

5 Comments

You must be Logged on to comment or reply to a post.

  1. Siddhesh Ghag
    Jim,

    Point noted, agreed that queueing table indexes etc are to be rebuilt every now and then. However lars and me were speaking about large indexes, or atleast that was my intention.

    I have seen some indexes that take hours to rebuild, which is not an option in production datbases, especially if the index was primary.

    Also, blog which lars wrote describes an alternative to the option of rebuilding indexes by creating an additional index and dropping the original one.

    Nice blog anyways.

    Regards,
    Siddhesh

    (0) 
    1. Jim Spath Post author
      Siddesh – for large tables, we regularly run index rebuilds after large archiving jobs complete.

      Check out related SAP notes:

      712098 – RSORAISQ: Index Storage Quality Management
      869521 – TM enqueues due to REBUILD ONLINE / CREATE ONLINE
      830576 – Parameter recommendations for Oracle 10g
      and _events_:
      10626
      10629

      Jim

      (0) 
  2. Lars Breddemann
    Hi Jim,

    still I don’t agree.
    Oracle does reuse allocated space. For a detailed discussion of this topic there are lots of good sources in the Oracle internet community available (e.g. asktom.oracle.com, or the blog of Richard Foote http://richardfoote.wordpress.com/2007/12/11/index-internals-rebuilding-the-truth/)

    Anyhow, the case for RFC-Tables and indexes is a *very* special one. Due to the extreme monotonic insert (right) delete (left) scheme, the index maintenance is not as effective as is could be.

    So there index rebuild can (not have to!) be a good decision. Keep in mind that the released blocks will be reallocated later on, when the index grows again. That takes time (=> performance drops). It generates more REDO to grow the index again. So it’s quite possible that you “fix” one performance issue and introduce a different one.

    Most often a COALESCE will be completely enough to  take care of the problem.

    Please note: I don’t say, that nobody should never rebuild their indexes.
    I just say: don’t do it regularly without a precise reasoning for it.
    The absolute performance gain has to be measured and it has to be clarified why the index rebuild is necessary there.

    Personally I can count the number of times I’ve seen that the index rebuild really solved the problem on one hand.
    But I’ve seen MANY cases where people kept rebuilding indexes without seeing what really caused the problems.

    Hmmm… actually I don’t wanted to start a ‘crusade’ here – so I hope nobody feels offended by my comments.

    Best regards,
    Lars

    (0) 
    1. Jim Spath Post author
      Lars – no offense taken, none intended.  I don’t think you meant “never” rebuild indexes any more than I meant “always” rebuild.  I just thought I’d give my opinion as you both had statements that could be interpreted as “never”.  We run a pretty big SAP shop, _archiving_ more than 1TB per year, so we tune constantly.
      I’ve added a screen shot from ST04 showing an effective way of finding indexes that could perform better, whether through rebuild, coalesce, or other means.  Jim
      (0) 
  3. David Hull
    As Tom Kyte said, once upon a time at http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:3035290021641
    [quote]My opinion — 99.9% of all reorgs, rebuilds, etc are a total and utter waste of time and energy.[/quote]

    Another quote from Tom Kyte at http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:2913600659112:

    [quote]
    I do not believe in a scheduled rebuild of indexes.  I do believe in
    a) determining the root cause of a performance related issue
    b) correcting it
    and if part of b) is rebuilding an index, so be it.  but to rebuild on a schedule?  No.
    [/quote]

    And, as Richard Foote put it so elegantly at http://richardfoote.wordpress.com/2008/07/03/3-steps-to-performance-tuning-working-class-hero/,

    [quote]Basically my response to the question of what basic steps one should follow when performing performance tuning was:
    1) Identify an actual problem that needs addressing, one that’s problematic to the business, not one that only exists in some statistic or in one’s imagination
    2) Determine what’s actually causing the problem as identified in Step 1.
    3) Address the specific issue as identified in Step 2.[/quote]

    You have not provided any evidence that your index 1) is causing a performance problem, much less that 2) a reorg of the index addresses this problem.  Buffer gets per row… Index storage quality… these are trivial statistics that do not tell the story of impact to a system.  How long are the queries running?  How many I/O operations are the index accesses performing?  These are true indicators of performance.

    [quote]For index quality, we should talk about fragmentation, or balancing, which relate to how much extra space is in the index, leading to longer access times, as well as the side effect of pushing good data out of our caches such as the DB_BLOCK_BUFFER (DB_CACHE in recent Oracle versions).[/quote]

    First, fragmentation and balancing don’t really have anything to do with each other.  Balancing refers to the number of levels in a B*Tree index.  And Oracle indexes are never unbalanced.  (see Richard Foote’s Index Internals paper, referenced by Lars, for evidence of this.)  Fragmentation, referring to empty entries in index leaf blocks, which is what you’re referring to, does not necessarily lead to longer access times.  It *could* conceivably happen in some situations, but you’ve shown no evidence to support that it *is* happening.

    [quote]What is happening here is the index is becoming unbalanced through constant inserts and deletes. Over time, the space the index occupies will continue to grow though there may be zero rows at the time the system starts and stops. Each deleted row continues to occupy space in the index segment until a rebuild is done.[/quote]

    No, that is not at all what is happening.  I quote Tom Kyte again: “Indexes do not become unbalanced – it is physically IMPOSSIBLE.”

    What is happening here is perfectly normal.  Blocks are allocated to the index and entries are inserted and deleted frequently.  The space that is freed due to deletes does get reused, though.  When an index entry is deleted, that empty space can be reused by the next entry that meets the same leaf criteria.  If all of the entries in a block get deleted, that block will be placed back on the freelist and reused somewhere else in that index.

    So, when the application is shut down and all entries are removed from the VBDATA table, all the index blocks associated with that index will be empty and will be put back on the freelist, to be used again.

    [quote]Do not attempt any of the following maneuvers unless you are a well-trained DBA. I am not talking certified, I am talking experience.  Me I was never certified, but I was mentored by the inimitable Jürgen Kirschner.  He loves to ask in his training class “how many DBAs in the room are certified?”  But I digress.  The risks here are great, including halting or damaging your company’s SAP system if you proceed without understanding.[/quote]

    You are right here, lack of understanding can certainly damage your system.  I would highly recommend sending your article to Jürgen to get his opinion on it.

    (0) 

Leave a Reply