 rebuilding indexes [message #144748] |
Fri, 28 October 2005 00:38  |
deeptimadan
Messages: 17
Registered: October 2005
|
Junior Member |
|
|
Is it true that after rebuilding indexes the performance increases...
My index tablespace is really increasing at very rate...
I want to rebuild my indexes...my database is Oracle8.1.7.0..
Are there any issues in doing this...
what is the best way to do it...
Please reply soon..
Thanks
Deepti Madan
|
|
|
|
| Re: rebuilding indexes [message #144798 is a reply to message #144748] |
Fri, 28 October 2005 07:05  |
 |
Mahesh Rajendran
Messages: 10708
Registered: March 2002
Location: oracleDocoVille
|
Senior Member
Account Moderator |
|
|
>> Is it true that after rebuilding indexes the performance increases...
May be the most debated topic in Oracle world.
General assertion:
Rebuilding will increase performance.
My observation:
NEVER. It never worked for me in last n years.
Facts :
It may or may not ( As said by Tom Kyte).
But there is no proof that, rebuilding an index will increase performance. An index should be rebuilt only if there is structural issue or after you have completely blown the data (truncate?).
Whatever it is , rebuilding indexes regularly is disastrous as it can be.
In your case
>> index tablespace is really increasing at very rate.
So rebuilding will halt it?
No.
Infact if you do a online index rebuild, your index tablespace will be doubled in size.
If you do not use online rebuild, your database needs a downtime.
[Updated on: Fri, 28 October 2005 07:07] Report message to a moderator |
|
|
|
| Re: rebuilding indexes [message #144863 is a reply to message #144748] |
Fri, 28 October 2005 11:13 |
mjschwenger
Messages: 137
Registered: July 2005
Location: US
|
Senior Member |
|
|
I use to have benefit of rebuilding indexes in the Dbs where LMT (locally managed tablespaces) were not in use.
Also, I would rebuild some of my indexes if there's a specific problem discovered like the clustering factor is too high, etc.
Sometimes I use to just drop and recreate the index instead of rebuilding it(free resources required). It's another questionable area if better...
As a general thought: regularly analize your indexes and before start doing anything with them collect enough information about them like CF, height, etc. Also, make sure there's no too many of them and/or duplicated ones.
Hope this helps.mj
|
|
|
|
| Re: rebuilding indexes [message #144913 is a reply to message #144748] |
Fri, 28 October 2005 17:52 |
mchadder
Messages: 224
Registered: May 2005
Location: UK
|
Senior Member |
|
|
<quote>
Also, I would rebuild some of my indexes if there's a specific problem discovered like the clustering factor is too high, etc.
</quote>
So, just out of interest, how does rebuilding your indexes solve the "problem" of the clustering factor being too "high"? Does the rebuild suddenly cause the index to be sorted in a different way relative to the underlying data? Hypothetical.. no, of course not.
Have a gander at http://www.dizwell.com/html/clustering_factor.html.
Regards
|
|
|
|
|
|
| Re: rebuilding indexes [message #144943 is a reply to message #144924] |
Sat, 29 October 2005 05:12 |
deeptimadan
Messages: 17
Registered: October 2005
|
Junior Member |
|
|
thanks to all of you
I index rebuilding is not much helpful then
what about reorgnizing the table ?...would that impact the performance....What is the best way to do that..
and in case of indexes how to know...which indexes are to be rebuilt..
Warm regards
Deepti Madan
|
|
|
|
| Re: rebuilding indexes [message #144956 is a reply to message #144748] |
Sat, 29 October 2005 16:04 |
mchadder
Messages: 224
Registered: May 2005
Location: UK
|
Senior Member |
|
|
Hello.
Let's get the story straight.
Are you experiencing performance issues BECAUSE of your index tablespace increasing at a "rate"?
You've said you want to consider reorg'ing the table, but how will this help your "index" tablespace growth (which, of course, is really based on your table "growth")? OR, do you have queries which are running "slowly"?
Can you be a bit more specific about WHAT is actually going slowly here?
The facts are that index rebuilds ( as nicely put in the original reply to your question ) are, 99.9% of the time, extremely unnecessary. Table "reorgs" is a multi-faceted term, i.e. it can have many different meanings, for example, "reorg'ing" to an IOT can be beneficial or a killer, "reorg'ing" to try and coalesce free space CAN be beneficial (or can have no effect), "reorg'ing" to a tablespace with a higher / lower block size CAN be beneficial or a killer. See the pattern? You need to be specific about WHAT the real issue is.
Regards
|
|
|
|
| Re: rebuilding indexes [message #145674 is a reply to message #144748] |
Fri, 04 November 2005 13:34 |
smartin
Messages: 1803
Registered: March 2005
Location: Jacksonville, Florida
|
Senior Member |
|
|
|
You would be better off, performance wise and other wise, devoting your time and resources not towards rebuilding indexes but towards upgrading to a version of oracle that came out this century. Get on 10gR2, get processes in place so that you can stay upgraded, and then re-evaluate if you still have issues.
|
|
|
|
| Re: rebuilding indexes [message #145698 is a reply to message #145674] |
Fri, 04 November 2005 22:19 |
deeptimadan
Messages: 17
Registered: October 2005
|
Junior Member |
|
|
hi
We can't upgrade to higher version...but i desperately want to improve the performance ...
I can't tune my SQL statements as we are using BaaN Application..
but in the month end we face problems like responce get so slow..
we are using indexes in each query..and at times in one table there are 8 or 9 indexes... My Index tablespace is growing at very fast rate ..faster than data tablespace..so i thought index rebuilding will improve my performance... actually it's been only two months since i have got DBA JOb...so you can say i am a fresher DBA..
Suggest me what i should do ...
|
|
|
|
| Re: rebuilding indexes [message #145708 is a reply to message #145698] |
Sat, 05 November 2005 01:25 |
Frank
Messages: 7901
Registered: March 2000
|
Senior Member |
|
|
If it's a third party application that is not performing, complain to them. You are not their first customer with problems, so they are likely to have a solution ready for you (else they should come up with one). That is why you pay license-fees !
hth
|
|
|
|
| Re: rebuilding indexes [message #145709 is a reply to message #145708] |
Sat, 05 November 2005 02:47 |
deeptimadan
Messages: 17
Registered: October 2005
|
Junior Member |
|
|
Hi
Its true that there is third party application...but how can i know that performance problem is due to application not due to database...i can't trace sql statements..as our database is OLTP (production) one..and i can't set sql trace on..there were some issues that i have resolved at database level..but those were very basic ones...i don't know on which points i should proceed..
Regards
|
|
|
|
| Re: rebuilding indexes [message #145716 is a reply to message #145709] |
Sat, 05 November 2005 04:57 |
deeptimadan
Messages: 17
Registered: October 2005
|
Junior Member |
|
|
| deeptimadan wrote on Sat, 05 November 2005 02:47 |
Hi
Its true that there is third party application...but how can i know that performance problem is due to application not due to database...i can't trace sql statements..as our database is OLTP (production) one..and i can't set sql trace on..there were some issues that i have resolved at database level..but those were very basic ones...i don't know on which points i should proceed..
Is it true that beyond 8i one can tune SQL statements or control the behaviour of Sql statements within ERP applications..If yes then How?
Regards
|
|
|
|
|
| Re: rebuilding indexes [message #145731 is a reply to message #145709] |
Sat, 05 November 2005 10:08 |
Frank
Messages: 7901
Registered: March 2000
|
Senior Member |
|
|
| deeptimadan wrote on Sat, 05 November 2005 09:47 |
I can't trace sql statements, as our database is OLTP (production)
|
So? You can set sql_trace from another session, you could use statspack to get an overall image of the performance of your database.
My advice is to create some statspack reports (google, search this site, read the docs on statspack) and try to figure out where exactly the problem is. If you are unable to improve performance, I would ask Baan. (I take it you have a support contract). As I said, it is what you pay for!
hth
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
