index creation online - performance impact on database

1061104

hi,

I have oracle 11.1.0.7 database running on Linux as 3 node RAC.

I have a huge table which has more than 255 columns and is about 400GB in size which is also highly fragmented because of constant DML activities.

Questions:

1. For now i am trying to create an index Online while the business applications are running.

Will there be any performance impact on the database to create index Online on a single column of a table 'TBL' while applications are active against the same table? So basically my question will index creation on a object during DML operations on the same object have performance impact on the database? is there a major performance impact difference in the database in creating index online and not online?

2. I tried to build an index on a column which has NULL value on this same table 'TBL' which has more than 255 columns and is about 400GB in size highly fragmented and has about 140 million rows.

I requested the applications to be shutdown, but the index creation with parallel of 4 a least took more than 6 hours to complete.

We have a Pre-Prod database which has the exported and imported copy of the Prod data. So the pre-Prod is a highly de-fragmented copy of the Prod.

When i created the same index on the same column with NULL, it only took 15 minutes to complete.

Not sure why on a highly fragmented copy of Prod it took more than 6 hours compared to highly defragmented copy of Pre-Prod where the index creation took only 15 minutes.

Any thoughts would be helpful.

Thanks.

Phil.

JuanM

Hi,

Read this Oracle Documentation carefully.

Creating Indexes

Edit:

Note:
 Keep in mind that the time that it takes on online index build to complete is proportional to the size of the table and the number of concurrently executing DML statements. Therefore, it is best to start online index builds when DML activity is low.

HTH.

Juan M

Jonathan Lewis

How are you measuring the "fragmentation" of the table ?

Is the pre-prod database running single instance or RAC ?

Did you collect any workload stats (AWR / Statspack) on the pre-prod and production systems while creating (or failing to create) the index ?

Did you check whether the index creation ended up in-memory, single pass or multi pass in in the two environments ?

The commonest explanation for this type of difference is two-fold:

a) the older data needs a lot of delayed block cleanout, which results in a lot of random I/O to the undo tablespace - slowing down I/O generally

b) the newer end of the table is subject to lots of change, so needs a lot of work relating to read-consistency - which also means I/O on the undo system

  --  UPDATED:  but you did say that you had stopped the application so this bit wouldn't have been relevant.

On top of this, an online (re)build has to lock the table briefly at the start and end of the build, and in a busy system you can wait a long time for the locks to be acquired - and if the system has been busy while the build has been going on it can take quite a long time to apply the journal file to finish the index build.

Regards

Jonathan Lewis

jgarry

Have you checked the table for row chaining and row migration?  I'm guessing that is what you are calling fragmentation.  Also wondering if the quick version had another index with the null column in it that it could read instead of full scanning.  I take it there are some values that are not null?

Yes, building an index will have performance implications.  It is by necessity going to do a lot of work on disks, might also use more cpu than one would expect, and can conceivably push a moderately loaded system over the edge.  In addition to what Jonathan said about locking and consistency.  Also, have you tried it twice?  If it's the delayed block cleanout, that would likely be quicker the second time.

It could be worthwhile to see what statspack or AWR has to say about the system over 15 minutes in that 6 hours.

Also, show parameter para.

Iordan Iotzov

When you have a large DB, you are better off creating the Pre-Prod DB as a bit-wise copy of your production. That way you will be better able to detect performance problems.
You can do that with RMAN DUPLICATE (or RESTORE) or utilize third-party solutions (Delphix).

If you need more info about how the online index rebuild works, you can check this presentation:

http://www.nocoug.org/download/2012-02/Internals%20of%20online%20index%20build.pdf

HTH,
Iordan Iotzov