Index Rebuild Part II

I am not getting into the moot of when to rebuild index or not, since these things already discussed by many experts at their sites. I am just providing the internal machanism, pros & cons of rebuilding index.

Oracle has 4 main features with regard to its internal maintenance of
indexes that makes index rebuilds such a rare requirement.

1) 50-50 block split and self-balancing mechanism
2) 90-10 block split mechanism for monotonically increasing values
3) Reusability of deleted row space within an index node
4) Reusability of emptied nodes for subsequent index splits.

These 4 features combined almost eliminates the need for index rebuilds.

In fact most indexes that have index entries randomly distributed throughout
the index tree structure remain both balanced (as all Oracle indexes must
be) and fragmentation free *because* leaf row entries are reused.
Inserts/Updates and Deletes result in "holes" being created but importantly
"refilled" typically at even rates. The issue of wasted deleted space is
therefore typically a non-issue.

Rebuilding such indexes can actually be detrimental to overall performance
for a number of reasons. Firstly, it requires a significant amount of
resources and can conflict with the general running of the database. But
perhaps more importantly, it can actually be self-defeating in what rebuilds
are supposed to achieve. That's because after an index rebuild, the index is
more tightly packed with less overall free space (else why rebuild). This
means however that index splits are more likely to now occur which directly
impacts performance due to the additional I/O and CPU this entails. And
after the block split, we now have two blocks each with 50% free space.
After a period of time, the index potentially has "issues" due to
insufficient used space and the vicious rebuild cycle continues. The better
course of action is to do nothing and let the index evolve to it's natural
"equilibrium".

When should one perform a rebuild?

Firstly, if the index value were to have monotonically increasing values
then any deleted space could be a problem as this space may not be reused
(making feature 3 above redundant). However, if sufficient entries are
deleted resulting in index nodes being fully emptied (say via a bulk delete)
then feature 4 would kick in and the deleted space could be reused. The
question now becomes one of *when* would the equivalent amount of index
entries be reinserted from the time of the deletions, as index scans (in all
it's manifestations) would be impacted during this interim period. So
monotonically increasing values *and* sparse deletions would present one
case for an index rebuild. These types of indexes can be identified as
having predominately 90-10 splits rather than the usual 50-50 split.

Another case would be an index that has deletions without subsequent inserts
or inserts within an acceptable period of time. Such a case would result in
wasted space that can't be effectively reused as there's not the sufficient
insert activity to reclaim the space. However, in this scenario, it's really
the *table* itself rather than the indexes directly that should be rebuilt.
Because such "shrinkage" results in both the table and associated indexes
being fragmented with HWMs that need resetting (to prevent performance
issues with Full Table Scans and all types of Index Scans). Yes the index
needs rebuilding but only as a result of the dependent table being rebuilt
as well.


The information has taken from multiple Metalink Notes.

Index Rebuild Part I

Couple of days ago, as per devt. team request, I had split a partition of one of the partitioned tables, which was having local indexes, fortunately, the partition which I split was belong to current month partition and all the indexes status became unusable and I had to rebuild them.
I was surprised to see that while index rebuilding, it has taken double space than the original one and released 50% occupaid space after the index rebuild. I was suprised because, I was an impression that only index rebuild online would take double space because it create a jounral table and put the data there untile index rebuild finishes.

--- The reason for taking double space while rebuilding is :

"For a rebuild, the index has to be scanned, the results sorted, and new extents built
to hold the newly constructed index. These extents must co-exist with the original index until the process is complete and the original can be dropped.Thus, the rebuild requires a sort and sufficient free space to hold two versions of the index temporarily."
-------------

I have found few good notes on rebuild index offline/online, its pros and cons of rebuild indexes. Thought, it would be helpful to others.

An index rebuilt either Online or Offline.

Online Index Rebuild Features:
+ ALTER INDEX REBUILD ONLINE;
+ DMLs are allowed on the base table
+ It is comparatively Slow
+ Base table is referred for the new index
+ Base table is locked in shared mode and DDLs are not possible
+ Intermediate table stores the data changes in the base table, during the index rebuild to update the new index later

Offline Index Rebuild Features:
+ ALTER INDEX REBUILD; (Default)
+ Does not refer the base table and the base table is exclusively locked
+ New index is created from the old index
+ No DML and DDL possible on the base table
+ Comparatively faster

So, the base table is not referred for data when the index is rebuilt offline.

When an index is rebuilt offline there is no FTS on the base table. When index is rebuilt online all the blocks from the base table are accessed.

These conclusions are when we donot make an scenario when the index is unusable and then there
is data load to the base table, and finally the index is rebuilt.

As documentation says, while REBUILDing an index OFFLINE, the base table is not referred. But there are situations where the base table is accessed similar to an index create, they are:

+ index is made "unusable"
+ data is loaded with sql loader with skip_unusable_indexes = TRUE
+ index is REBUILD OFFLINE

Basically, when an index is made "unusable", it is normally meant that it will not be used at all (that is drop later) or it has to be drop/create. Here the index being unusable takes more priority than the Offline rebuild of it. An unusable index has to refer the base table while rebuilding.

Oracle10g AS Forms and Reports diployment.

Wow, here comes my first assignment in the production after attending Oracle 10g AS courses.
The requirement is to upgrade Oracle 9i AS Forms & Reports to Oracle 10g Forms & Reports.
The good thing about 10g compare to 9i is that you will have a start and stop GUI option in the Oracle folder (windows), which eventually doing OPMNCTL STOPALL.
Procedure that I have done:

1. Ensure that the server ipaddress, DNS, and localhost name (server name) is registered in the windows
hosts file.
2. Start installation of Oracle 10g from the CD. (Middle-tier only, since Forms and Reports comes as
separate CD in 10g Rel.1, I have no other options to select for middle-tier).
3. Specify iasadmin username and password, this password is used later to connect as ias_admin to enter
in the emctl tool. Enterprise Manager Console.
4. Once the installation successfull, check the install log for port and other details of http server.
5. Application Diployment. (forms and reports).
copied the source in d:\App1
specify the location in the httdp.conf file, so that AS can recognize the location of source.
edit \oracleASHome\appache\appache\conf\httpd.conf file
replace value of *DocumentRoot (*DocumentRoot "d:\App1")
replace directory (
and save the file.
6. Configure tnsname.ora file to access to the target database.
7. Stop and start all the process.
8. From the browser, run like , http://hostname

Wow, it was so easy to do and I was happy that in first attempt I was succeeded.

Oracle Application Server 10g Training

Our training on 'Oracle10g Rel.2 Application Server' has started 3 days ago. Its a 5 day training course which is combined Oracle10g AS fund I & II into a single course. We have skipped the chapters which are not useful in our day to day work and concentrated on only topics which is useful, of course, due to the time contraint to finish the course in 5 day time.

First Day of the course:

During the first hour itself I felt that the instuctor has good knowledge about Application Server and the stuff he is teaching. Well, he was from Oracle DUBAI.

Our entire first session gone in explaining and discussing about our organization setup, what is our setup, and what are the features that we are using in 10g AS. It was a very nice session and the instructor was very much happy to receive our questions.
Post lunch session started with Application Server installation on Linux plat form. We have all installed and configured Application Server on our labs PC in the following sequence:

1. Installed Infrastructure
2. Installed Middle-tier (J2EE/OC4J instance)
3. reviwed the important logs and xml files.
4. Starting all the process using , opmnctl, dcmctl and emctl.
5. Stoping starting instances (middle-tier).

Day two:

Today also a lot of question and answering and discussed about our problems which we are facing in the organization using Application Server.

Today we have learned how to create multiple OC4J instances and application diployment.

Day Three:

Today we have learned about Application Security, Oracle Wallet Manager and infrastructure backup and recovery.

Well, I felt confident that I can install and configure the Oracle 10g AS and diploy the java applications. By the way, we are using J2EE (OC4J instance) not web cache, portal or any other thing from Oracle 10g AS bundle.

Once I finish the course, I will also partitipcate Oracle Application Server Forum like who I am doing for database forums.

Day 4 and 5 will more instresting as we are going to talk about backu pand recovery, cluster and performance tuning. I like performance tuning topic than any other, since, its my very favourate topics. I will update tomorrow.

Day Four:

Today we tried Middle-tier cloning and creating cluster and joining multiple instances to the cluster. We failed to do the same because the Middle-tier which we cloned was already registered with infrastructure, therefore, we couldn't succeed. What we did is that, we removed the cloned middle-tier instance and droped from the cluster. Created a new middle-tier instance and joined to the cluster.

Day Five:

Deployed application on one of the instance in the cluster, which replicated in all the instances associated with this cluster.
Also learned how to tune the middle-tier, using oracle supplied tool, AggreSpy and dcmtool.

Well, we haved completed the 10 day course in 5 day of time, due to time constraints, course run on fast track.
I am planning to take the OCA for Oracle 10g AS in couple of weeks time. Hopefully, partitipates in AS forums on OTN.

Thank you for your valuable time to read this blog.