Frederick Tang Weblog

Stories about Oracle concepts I have learnt from work, and the occasional brain-dump…

Lessons learnt from creating indexes…

with 2 comments

A project required a DBA to execute a SQL script for them to create a few indexes on their production database. The script was provided to them by their vendor. I have learnt quite a few lessons from this exercise, being such a complete Oracle n00b, I would like to write about my experiences:


  1. Ask a few questions to understand the reasons behind the index.

    * How will the index used, why the initial extent has to be 4GB, is the index necessary… etc.

  2. Size of the table.

    * The segment size (table) is 10GB.

  3. Free space to fit the index.

    * There weren’t enough space in the PROD_INDEX tablespace, so I checked whether there’s enough disk space in the filesystem for the tablespace to expand. Some datafiles are created with AUTOTEXTENSIBLE set to NO, also beware of MAXSIZE set on the datafiles.

  4. Database version.

  5. Are there enough TEMP space for this work? because I didn’t…

    * According to Metalink Note 19047.1 (OERR: ORA 1652 unable to extend temp segment…): “The index create performs a SORT in the users default TEMP tablespace and ALSO uses a TEMP segment to build the final index in the INDEX tablespace. Once the index build is complete the segment type is changed.”
    * Metalink Note 100492.1 (ORA-01652: Estimate Space Needed to CREATE INDEX) describes the method to avoid ORA-01652 errors.
    * Also take into account TEMP space required to keep the application running (if it cannot be shutdown).

  6. Get a feel how fast the database server is.

    * Solaris 9, 4x 750MHz CPUs, 8GB Memory, datafiles on SAN

  7. Allow plenty of time.

    * The index took 1 hour 20 minutes to create.

  8. Consider CREATE INDEX with ONLINE option.

    * The ONLINE option allows DML to continue whilst CREATE INDEX is running, changes to the table are kept and applied when CREATE INDEX is completed. Without this option, CREATE INDEX will hold an exclusive DDL lock, preventing any DML (insert, update, delete) on the table.
    * There’s still a few things to consider with this feature though, large amount of DMLs is still not recommended (reference).
    * Furthermore, it is important to check whether there are problems with this feature for a particular database version. Bug 3566511 (ONLINE index rebuild can cause DML sessions to hang) affects and, but does this bug affect create index?

  9. Discuss with the business owner about stopping the application, reports, scheduled dbms jobs that might impact the environment.

    * It isn’t always possible to stop the application if uptime is critical, then that’s a whole different scenario to mine. The business owner was okay to stop the application, reports running during the maintenance given to me. Even with ONLINE option, the CREATE INDEX cannot start unless all DML transactions on the table is completed because of locks (I will demonstrate this in my next blog). For a table like production_log, this may mean *hardly*.
    * Another thing to consider is system resources, the application, reports and the CREATE INDEX will compete for CPU, Memory, I/O resources. Will this have a major impact on the environment? Will this cause CREATE INDEX to take longer to complete?
    * The most important thing to note, if the CREATE INDEX is not used with ONLINE option, and the application is not shutdown, there will most likely be sessions issuing DMLs against the table. These tables will be in lockwait whilst CREATE INDEX is running. In my case, I observed there were > 100 sessions in lockwait 10 minutes after CREATE INDEX started… I really had to cancel it.

  10. Even after the application is stopped or shutdown, check the locks.

    * So after I learnt my first lesson (see my last point), we re-scheduled another maintenance window to try again. This time, I asked the application owner to stop the application before I start. However, application owner could only stop the application processes that would write to the database, a few database sessions still remained.
    * I used a script supplied by Metalink Note 232815.1 (Script to Show current Users and SQL being Executed – which uses v$session and v$sqltext_with_newlines), to see whether there are any DMLs running on the table.
    * I also used a script supplied by Metalink Note 1020008.6 (Script to Show Locking), to see whether there are any locks on the table. It turned out there’s an uncommitted transaction (a simple INSERT statement) by a session that hasn’t been active for a few days (last_call_et=1022617 – 284 hours). So I had to kill the session before proceeding with the CREATE INDEX.

  11. Beware of Internet and VPN dropouts.

    * It was scheduled maintenance work at night, so I remotely login to the Production environment via VPN. The VPN dropped out after 1 hour into creating the index. Had to start all-over again – not much fun.

  12. Monitor the listener and alert logs for abnormal activities.

    * These logs are tell-tale signs when things go wrong…

Anyone’s got good experiences to add?


Written by fredericktang

January 24, 2008 at 7:40 am

Posted in 9i, Oracle

2 Responses

Subscribe to comments with RSS.

  1. For a DBA it is always needed to ask if that index can be avoided or not, especially on tables which are having heavy oltp write load.

    Instead of online creation or rebuilding choosing an idle system time frame and doing the index build nologging and parallel will grant you to take the direct path operation, otherwise you will experience lots of SGA waits which will increase the time of the build.

    Choosing the appropriate index type is also very important, there are several best fit options like table types for indexes; bitmap, function, reverse, etc.

    If you are using CBO using the compute statistics option during creation avoids another statistics collection step, this behaviour is the default after 10g since RBO is not supported any more.

    For much more I advise this blog –

    H.Tonguç Yılmaz

    January 25, 2008 at 1:33 pm

  2. […] A good resource is “lessons learnt from creating indexes.” […]

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: