| Index rebuild [message #277689] |
Wed, 31 October 2007 03:30  |
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  |
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 |
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 #277697 is a reply to message #277692] |
Wed, 31 October 2007 04:12 |
Frank
Messages: 7901
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 |
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 |
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 |
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 |
pablolee
Messages: 2882
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 |
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 |
Frank
Messages: 7901
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 |
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 |
joy_division
Messages: 4963
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 |
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 |
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 |
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 #279268 is a reply to message #279192] |
Wed, 07 November 2007 09:57 |
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.
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
