I'm following up to 2 blogs on index maintenance:
How to take care of index corruptions in primary key indexes. - lars.breddemann/blog
Rebuilding Index online using DB02 - siddhesh.ghag2
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:
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?
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
5 Comments
