Speed up index creation in Local Managed tablespace !

[Chris Fung]

Hello All,

When I moved my original index from dictionary managed tablespace to local managed tablespace (Autoallocate), the index creation time increased dramatically.

As told by the previous DBA, the index for some large tables have to be dropped and recreated in the month end batch processing. That's why we bother to do this.

When I check the drop index command in the Oracle manuals, I couldn't find any storage reuse cause......and I am wondering...if there is any way to reduce the extents allocation time during the index creation step.

I did think about using "uniform size clause for the index local managed tables" but I will treat it as the last resort.....and I think I need to minimize the dynamic extent allocation anyway even it is locally managed.

Any comments. Appreciated for you advice.

Cheers,

Chris,

[twang]

Index creation.
The CREATE INDEX statement causes the server process (or processes if the index is being created in parallel) to sort the index values before building the tree. After the sort a final index is built in the INDEX tablespaces by using a temporary segment; once the index has been built completely, the segment type is changed to index.
So my suggestion is to reduce or avoid sorts when possible. you can use the NOSORT clause when you build the index to eliminate sort phase of the index build.

Hope this can help you.

[malay boy]

Chris,
Instead of creating/droping the index,the fast and easy way is to do a rebuilt.

Just issue command :

alter index rebuild online ;

if you need to changed the storage clause,you could always do :

alter index storage(...);

regards
mB

[Yogeeraj_1]

hi Chris,

why are you rebuilding indexes?

what is the goal behind that?

do you have metrics that prove you are doing more good then harm?

did you know that rebuilding indexes can have a massive negative impact on your performance?

i would recommend you just stop -- just don't do it.


regards
Yogeeraj

[Chris Fung]

Hi all,

Thanks for your suggestions,

Yogeeraj, I think the reason for dropping and recreate the index (probably not rebuilding the index) in our situation is because we have month end batch job to run.

As such, we drop the index before the batch and re-create it afterward.

After I revisit the create index script....and I found that it has not been modified since I created the local managed index tablespace...and therefore the index creation time should be static compare to the pervious month.

However, the 2 hours job increased dramatically to 3 hours today....that raise my concern on what's going on my system.

Is there anything I can do to check what's going on ? Any dynamic performance view or log file I should visit ?

Many thanks,

Chris,

[Yogeeraj_1]

hi again,

If you really feel the index needed rebuilding -- you must have had some material numbers that made you do it -- see if those numbers improve. I would run Statspack during that period and see if the IO's go down, transaction rates go up, IO's decrease, performance improves.

If you had to do it, you would use:
alter index rebuild tablespace LMT_FROM_ABOVE storage ( initial 1k );

You can also investigate into rebuild in parallel/nologging the indexes.
Also, where you cannot specify tablespace parameters, you can do an "alter user default tablespace " before the operation.

Again, you will rebuild an index in response to IDENTIFIED and DEGRADED performance


hope this helps too!

regards
Yogeeraj

[Yogeeraj_1]

hi again,

In general, you need to BENCHMARK. Before rebuilding -- capture statistics, performance metrics, IO's, etc -- against that index. After rebuilding -- compare the results. If you got nothing back, no increased performance, decreased IO, etc -- don't rebuild it again later -- it doesn't buy you anything.

In your place, i would run STATSPACK during the "problem" period and analyse the report.

regards
Yogeeraj

[Steven E. Protter]

Sounds to me like they drop the indexes before some kind of month end processing. In some databases, this speeds up selects that select large protions of the database.

We don't do a drop/rebuild sequence in our oracle or pre-oracle(Software AG Adabas) databases.

A problem we've run into is when we add or modify records in heavily indexed tables, the update takes forever(in machine time). One possible solution is to drop them and build them later, but that usually costs more time total.

Another possible solution is to run analysis on the database and find indexes that are totally unused or infrequently used. Then you can reduce the processing time on updates with a more efficient index structure. The problem there is programmers rarely give up indexed fields and such without a nasty fight.

Good Luck,

SEP

[Brian Crabtree]

Chris,

You might want to think about invalidating the index, and rebuilding it with the rebuild clause. This will cause the index to not updated, but will not require the segment to be dropped and re-created.

Also, you can specify the initial extent size for the index, and it will preallocate the space in extent blocks, but for a large index, should automaticlly use larger blocks (64m in size). I doubt that an extra hour would be used due to extent sizes, but Oracle has said that objects with over 1024 extents (about 2g with autoallocate and no options) see performance degradation.

Thanks,

Brian

[Stan_17]

Chris,

I'd always stick on to LMTs with uniform extent size to keep things simple and clean. it doesn't mean autoallocate is bad and do not worry about extents allocation time with LMTs as they are all managed through bitmaps.

anyways, was there any changes in sort_area_size after you moved from DMTs to LMT

did your table size increased dramatically after the batch load as opposed to the table size that were in DMT

what version of oracle your running. I remember there were some issues with LMT's prior to 8.1.7.4 release but they are solid in 8.1.7.4

As everyone pointed out statspack report should help to figure out the slowness of index creation.

i'd look into these areas for faster index creation

-- larger sort_area_size the better
-- use tempfiles with uniform extent size. at least make sure the uniform extent size are in multiples of sort_area_size or equal to sort_area_size
-- if possible try parallel creation
-- create the index with nologging. of course you backup the index tablespace after rebuild to avoid recovery issues.

hth,
Stan