Home » SQL & PL/SQL » SQL & PL/SQL » Index rebuild
Index rebuild [message #277689] Wed, 31 October 2007 03:30 Go to next message
ora_2007
Messages: 430
Registered: July 2007
Location: Mumbai
Senior Member
Hi Experts,

I am rebuilding indexes, and it takes 50 minutes to complete.

But i want to know is it possible start index rebuilding for 5 minutes then wait for 1 min. Again continue same index for 5 min then again 1 min break.

Is there any way to do this?

I guess it is not possible.

But, i want to confirm from experts.
Your suggetions are very important.

Thanks in advane.
Re: Index rebuild [message #277690 is a reply to message #277689] Wed, 31 October 2007 03:36 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
1) No, its not possible
2) Why are you rebuilding your indexes. Do you have data that definitely confirms the existance of a performance problem that rebuilding the indexes fixes. If you don't then don't rebuild your indexes.
There is no reason to routinely rebuild your indexes - at best it creates downtime for your users, and at worst it will slow down the database as Oracle has to split up all the blocks that the rebuild has consolidated when it needs to add new keys to the leaf blocks.

{typo}

[Updated on: Wed, 31 October 2007 03:37]

Report message to a moderator

Re: Index rebuild [message #277692 is a reply to message #277689] Wed, 31 October 2007 03:43 Go to previous messageGo to next message
ora_2007
Messages: 430
Registered: July 2007
Location: Mumbai
Senior Member
Hi JRowbottom,

Thanks for your information.

As index containing tablespace size is growing consistantly.
So i use to rebuild the indexes within a month, then the tablespace gets reduced after that.

Your comment:
Quote:


Do you have data that definitely confirms the existance of a performance problem that rebuilding the indexes fixes.



I have data, but how can i find that there is perofomance problem in the databse?

Please comment on the same.

Thanks,

[Updated on: Wed, 31 October 2007 03:56] by Moderator

Report message to a moderator

Re: Index rebuild [message #277694 is a reply to message #277692] Wed, 31 October 2007 03:58 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
If you don't know if there is a performances problem then there is no performances problem.

Regards
Michel
Re: Index rebuild [message #277696 is a reply to message #277689] Wed, 31 October 2007 04:01 Go to previous messageGo to next message
ora_2007
Messages: 430
Registered: July 2007
Location: Mumbai
Senior Member
Thanks. Smile
Re: Index rebuild [message #277697 is a reply to message #277692] Wed, 31 October 2007 04:12 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
ora_2007 wrote on Wed, 31 October 2007 09:43

As index containing tablespace size is growing consistantly.
So i use to rebuild the indexes within a month, then the tablespace gets reduced after that.

And then what happens?
Let me tell you what (very probably) happens:
The database is getting slower after the rebuild. Somehow inserts and updates don't fly like they did before.
And at the end of th month, look: the tablespace has grown again.
Inserts and updates are back to normal speed, but hey, we want the diskspace back, so let's restart the cycle..

This is what happens in 90% of the cases where people rebuild indexes on a regular base.
Re: Index rebuild [message #277701 is a reply to message #277697] Wed, 31 October 2007 04:23 Go to previous messageGo to next message
ora_2007
Messages: 430
Registered: July 2007
Location: Mumbai
Senior Member
Hi Frank,

Perfect reply.

So do i assume that i am doing the right activity in index rebuiding? i..e rebuilding indexes for space utilization?

Please assist.

Thanks
Re: Index rebuild [message #277704 is a reply to message #277701] Wed, 31 October 2007 04:33 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
NO!!!

Read Franks reply again, and pay attention to the details.

By rebuilding the indexes every month, you are (probably) slowing down the database.

Do not do it unless you have solid evidence that there is a performance problem that will be solved by doing it.
Don't do it because 'we always have'.

If your indexes are on columns that are populated by sequences, you can look at decreasing the PCT_REE value to get more rows into a leaf block.
Re: Index rebuild [message #277715 is a reply to message #277704] Wed, 31 October 2007 05:06 Go to previous messageGo to next message
ora_2007
Messages: 430
Registered: July 2007
Location: Mumbai
Senior Member
Hi JRowbottom,

As you says 'By rebuilding the indexes every month, you are (probably) slowing down the database'.

I am agree with you.

But, what about the space utilization.
If i am rebuilding for the space utilization then is it ok? (Not always rebuilding)
Because everyday tablsepace size is increasing consistantly.
And after index rebuilding it decreases. Otherwise i have to go for adding data file in the tablespace.

What are your thaughts on it?

Thanks,
Re: Index rebuild [message #277744 is a reply to message #277715] Wed, 31 October 2007 06:17 Go to previous messageGo to next message
pablolee
Messages: 2835
Registered: May 2007
Location: Scotland
Senior Member
If you would rather have your database be slow and teke up slightly less space than have it be performant and take up slightly more disc space then by all means go against the excellent advice that has been proffered here.
Re: Index rebuild [message #277745 is a reply to message #277715] Wed, 31 October 2007 06:18 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
How often do you have to add new datafiles to the index tablespaces at the moment, with the re-organisations that you're doing - I'm guessing not very often.

What I suspect is happening is this:

You rebuild the indexes - all the leaf blocks are fully populated, and the index takes up as little space as possible.
Then, over the days/weeks that follow, you keep inserting and updating records in the indexes. Every time you try to add a new key to a full leaf block, Oracle has to go away and split that leaf block into two seperate blocks, each of which is half full.
This process generates a lot of Redo, and can be quite time consuming.

So, if you change quite a lot of data in the index, it can potentially grow to double its compressed size.
Once it reaches this sort of size however, (the size Oracle wants it to be) it will only increase in size vers slowly as the (now half full) leaf blocks fill up.

I reckon that you are just fighting a continual rearguard action against the way oracle wants the indexes to be, and to do this you are increasing the amount of downtime, slowing down the database, and increasing the amount of Redo it generates.
Re: Index rebuild [message #277772 is a reply to message #277745] Wed, 31 October 2007 08:05 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Or, as Tom Kyte once put it (free interpretation):

It seems that every human has something like his or her ideal weight. Whether they eat a lot, or diet like hell, they tend to fall back to that weight. It may not be ideal in their minds, so the tend to diet: they go through a lot of trouble, lose some weight, but in the end they fall back to the initial weight.

Indexes are like that, they have an ideal size. You may not like this size, so you rebuild your index, thereby going through a lot of trouble (performance loss, caused by the splitting JRowbottom described), only to find it back at the initial size after a while.
Re: Index rebuild [message #278702 is a reply to message #277772] Mon, 05 November 2007 07:35 Go to previous messageGo to next message
ora_2007
Messages: 430
Registered: July 2007
Location: Mumbai
Senior Member
Hi Experts,

As you suggested in the previous mails instead of rebuilding indexes i used to resize(increase) the tablsepace size in which the index contains.

In last week ,the tablespace %used size was 92%. So i increased datafile size. So %used was reduced to 80%

But, in this week again %used size is 90%.

So you suggest i need to again add datafile size?

Please suggest.
Your suggetions are very important.

As i am a fresher DBA i could not able to decide what to do?

Thanks

Re: Index rebuild [message #278713 is a reply to message #278702] Mon, 05 November 2007 08:01 Go to previous messageGo to next message
joy_division
Messages: 4643
Registered: February 2005
Location: East Coast USA
Senior Member
Why don't you give us specific numbers? Maybe you are adding 10 Mb to your tablespace and you have a locally managed tablespace with 1 Mb uniform extents. That's what I am assuming.
Re: Index rebuild [message #278717 is a reply to message #277689] Mon, 05 November 2007 08:16 Go to previous messageGo to next message
ora_2007
Messages: 430
Registered: July 2007
Location: Mumbai
Senior Member
Hi Experts,

I give you exact figures. The figures what i have given in the last reply was vague figures.

TABLESPACE_NAME         Alloc MB    Free MB    Used MB   % Used
----------------------------------------------------------------
TEST_IDX                 27044       3299      23745 87.8013607


Then i increased datafile size by 600MB. Then the %used was reduced.

Then please find the todays status.
Then its %used was increased.

TABLESPACE_NAME         Alloc MB    Free MB    Used MB   % Used
----------------------------------------------------------------
TEST_IDX                  27748        3110      24638 88.791985


So size is increasing continously.
Please let me know your thaughts on the same.

Thanks,

[Updated on: Mon, 05 November 2007 08:17]

Report message to a moderator

Re: Index rebuild [message #278723 is a reply to message #278717] Mon, 05 November 2007 08:32 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
What you need to do is work out how much of this increase data is being added to the index by new row creation, and how much of it is the index structure still expanding from the last rebuild.

When you do a rebuild, what did the tablespace size generally shrink down to?

Re: Index rebuild [message #279192 is a reply to message #278723] Wed, 07 November 2007 05:07 Go to previous messageGo to next message
ora_2007
Messages: 430
Registered: July 2007
Location: Mumbai
Senior Member
Hi Experts,

I done monitoring of the database and i find that 124123 records are added in a table within a day.

I think that might be the reason to increase tablespace size in which that table index contains.

So i think that there must be need to add/resize tablespace.
There is no other way(No need to rebuild index for space utilization)

What you think experts?

Thanks in advance.

Thanks,
Re: Index rebuild [message #279256 is a reply to message #277689] Wed, 07 November 2007 09:07 Go to previous messageGo to next message
BlackSwan
Messages: 25046
Registered: January 2009
Location: SoCal
Senior Member
>What you think experts?
I think you suffer from Compulsive Tuning Disorder
Re: Index rebuild [message #279260 is a reply to message #279256] Wed, 07 November 2007 09:18 Go to previous messageGo to next message
ora_2007
Messages: 430
Registered: July 2007
Location: Mumbai
Senior Member
Hi anacedent,
If you dont have any answer then please dont give any other answer.
Thanks
Re: Index rebuild [message #279268 is a reply to message #279192] Wed, 07 November 2007 09:57 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
I think that if your indexes are growing, then they will need more space adding to the tablespace sooner or later.

You could just set the tablespace to AUTOEXTEND, give it a maximum size that will prevent runaway processes from causing you disk space shortages, and stop worrying about it.
Re: Index rebuild [message #279363 is a reply to message #277689] Wed, 07 November 2007 23:34 Go to previous message
ora_2007
Messages: 430
Registered: July 2007
Location: Mumbai
Senior Member
Hi JRowbottom,

I really appreciate your reply.

Thanks for your suggetions. Smile

Thanks,
Previous Topic: ora 01013 error
Next Topic: Help in refing the Sql..
Goto Forum:
  


Current Time: Thu Dec 08 18:47:35 CST 2016

Total time taken to generate the page: 0.13429 seconds