Frederick Tang Weblog

Now it is time to try the ONLINE feature of CREATE INDEX.

Session Application User #1 (SID=13):

SQL> CREATE INDEX ix_big_track_id ON big_music_track(songid) ONLINE;

Session Application User #2 (SID=17):

SQL> INSERT INTO big_music_track select * from big_music_track where rownum=1;

Session SYSDBA:

Username    SID Lock Held       Lock Requested  Lock Type
---------- ---- --------------- --------------- ----------------------------------------
FTANG        13 Row Exclusive   NONE            DL - Direct loader parallel index create
                Row Share       NONE            TM - DML enqueue lock
                Share           NONE            TM - DML enqueue lock
FTANG        17 Row Exclusive   NONE            TM - DML enqueue lock

Notice SID=13 no longer holds a Exclusive TX – Transaction enqueue lock.

Whilst the INSERT is completed but keeping the transaction uncommitted, the CREATE INDEX will not complete. Another look at the locks:

Session SYSDBA:

Username    SID Lock Held       Lock Requested  Lock Type
---------- ---- --------------- --------------- ----------------------------------------
FTANG        13 Row Exclusive   NONE            DL - Direct loader parallel index create
                Row Share       Share           TM - DML enqueue lock
                Share           NONE            TM - DML enqueue lock
                Exclusive       NONE            TX - Transaction enqueue lock
FTANG        17 Row Exclusive   NONE            TM - DML enqueue lock
                Exclusive       NONE            TX - Transaction enqueue lock

Once the INSERT transaction commits, the INDEX is created.

In my last blog, I issued DML – INSERT and keep it uncommitted, then issued a DDL – CREATE INDEX, which resulted in the ORA-00054 error. This time, I am going to do this the other way around… and see what locks are held and requested.

A CREATE INDEX on the big_music_track table takes 30 seconds to run, before the DDL completes, I am going to issue a INSERT statement in another session. The experiment showed the INSERT statement is held in wait until the CREATE INDEX is completed.

Session Application User #1 (SID=17):

SQL> CREATE INDEX ix_big_track_id ON big_music_track (songid);

Session Application User #2 (SID=13):

SQL> INSERT INTO big_music_track select * from big_music_track where rownum=1;

Session SYSDBA:

Username    SID Lock Held       Lock Requested  Lock Type
———- —- ————— ————— —————————————-
FTANG        13 NONE            Row Exclusive   TM – DML enqueue lock
FTANG        17 Row Exclusive   NONE            DL – Direct loader parallel index create
                Share           NONE            TM – DML enqueue lock
                Row Exclusive   NONE            TM – DML enqueue lock
                Exclusive       NONE            TX – Transaction enqueue lock

This shows SID=13 is held in wait requesting a Row Exclusive lock. As opposed as the previous example (blog), CREATE INDEX immediately terminates with an OERR.

I started learning about locks when I was trying to CREATE INDEX (see my last blog). Yes yes… I am a complete Oracle n00b, but it’s never too late to learn right?

I read that uncommitted DMLs or transactions will hold a lock preventing DMLs, so I decided to reproduced it. I used the script provided by Metalink Note 1020008.6 (Fully Decoded Locking) for the following output.

For this exercise, I started three connections to my database: 2x Application user, 1x SYSDBA.

Before I started, I created a big table:

SQL> select count(*) from big_music_track;

  COUNT(*)
———-
   1542728

Session Application User #1 (SID=13):

SQL> INSERT INTO big_music_track select * from big_music_track where rownum=1;

1 row created.

SQL>

Session Application User #2 (SID=17):

SQL> create index ix_big_track_id on big_music_track (songid);
create index ix_big_track_id on big_music_track (songid)
                                *
ERROR at line 1:
ORA-00054: resource busy and acquire with NOWAIT specified

SQL>

Session SYSDBA:

Username    SID Lock Held       Lock Requested  Lock Type
———- —- ————— ————— —————————–
FTANG        13 Row Exclusive   NONE            TM – DML enqueue lock
                Exclusive       NONE            TX – Transaction enqueue lock

The script supplied produces more information than presented here.

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:

CREATE INDEX IDX_PL_N1 ON PRODUCTION_LOG (TIMESTAMP)
STORAGE(INITIAL 4096M NEXT 160M)
TABLESPACE PROD_INDEX
/

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.
   

   9.2.0.5

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 9.2.0.4 and 9.2.0.5, 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?