11/7/2015 4:39:46 PM
When an indexed field in a table is changed repeatedly, the data is deleted from the index and re-inserted at a new block in the index. This result in decrease of index storage quality. Thus index building will improve the index storage quality.
The memory quality of an index indicates how much memory space would be required for the index if an optimal index was to be created. In the case of a determined memory quality of 20%, approximately 80% of the allocated space is currently not used (or no longer used). Note that it is not usually possible to achieve full utilization (100%) of the allocated space.
You can use report RSORAISQ to examine the indexes in the system and save a history of the analyses performed. You can use the data saved in this way to develop a strategy for your system and develop a plan as to when certain indexes should be rebuilt (either on a regular basis or after certain administrative tasks have been performed).
Possible reasons for the rebuild
You want to release the unused space again.
Accesses to the object are slow or consume an unnecessary amount of database resources (high number of get buffers).
Report RSORAISQ provides the following functions:
Analysis of the memory quality of one or more indexes
Display of indexes that have already been analyzed
Rebuild of one or more indexes
No analysis and no rebuild for bitmap indexes or partitioned objects
No selection of several tablespaces in a run
No restart option for the Analyze function
NOTE: Lock problems:
DB01 does not display the lock caused by the rebuild. Instead, it displays the sessions waiting for this lock.
The online rebuild requires a table lock twice. If, at this time, a transaction executes changing operations on the same object, Oracle will wait on the lock until a commit or rollback is performed to exit the changing transaction. Unfortunately, the table lock that was requested results in all other new actions that only want to change individual rows also having to wait for this lock. The table lock itself is only required for a short period of time.
If you are considering avoiding these lock problems as much as possible, you can do so as of Oracle 184.108.40.206 by setting events (see Note 869521). The option described under point (2) of the note probably offers the best additional security:
The lock is requested several times. If the lock cannot be successfully requested, the object is not locked for all other accesses. This process can also be limited in terms of time.
However, this behavior probably applies only to the first lock request, and is therefore not one hundred percent certain.
If you set the event as follows, Oracle attempts to request the lock 32 times in four minutes, and if this fails, the rebuild for this object is terminated with an error, and you can try again later. event = '10629 trace name context forever, level 32'
Above all, you should only execute rebuilds for a large number of objects or for individual central objects (Mara, update tables, spool tables and so on) in parallel to your production operations if you are certain that there are no long-running changing operations with infrequent commits, and if you are sure that there is minimal work load for the update process and the spool. If you do not ensure that this is the case, situations that resemble downtime periods can occur. If you cannot ensure that this is the case, execute a rebuild for these objects only during downtime.
If you like this blog, please share (Facebook/LinkedIn/Google+) to click below links so it will reach to others.