Home » RDBMS Server » Performance Tuning » rebuilding index
rebuilding index [message #248397] Thu, 28 June 2007 21:01 Go to next message
adee_saleh
Messages: 30
Registered: May 2007
Location: Yemen
Member

i need a real help in this
i need to know that how i can know how i can know that the blocks of the index is 50% empty in order to know if it need to rebuild or coalace or not

with my regards
Adee
Re: rebuilding index [message #248398 is a reply to message #248397] Thu, 28 June 2007 21:13 Go to previous messageGo to next message
BlackSwan
Messages: 25045
Registered: January 2009
Location: SoCal
Senior Member
What is the ratio of INSERTs to DELETEs?
Re: rebuilding index [message #248404 is a reply to message #248398] Thu, 28 June 2007 22:24 Go to previous messageGo to next message
DreamzZ
Messages: 1666
Registered: May 2007
Location: Dreamzland
Senior Member
Tip :- When to rebuid an index ?

http://www.samoratech.com/tips/swrebuildindex.htm
Re: rebuilding index [message #248435 is a reply to message #248397] Fri, 29 June 2007 01:14 Go to previous messageGo to next message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Who say you need to rebuild the index?
Who say that 50% ratio?
50% of what? On the average? In each block?...

You never rebuild indexes unless you have proofs they are at the root of performances issue.

Regards
Michel
Re: rebuilding index [message #248485 is a reply to message #248397] Fri, 29 June 2007 03:11 Go to previous messageGo to next message
pablolee
Messages: 2834
Registered: May 2007
Location: Scotland
Senior Member
In agreement with Michel:- Read
http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:6601312252730
Re: rebuilding index [message #248565 is a reply to message #248485] Fri, 29 June 2007 09:53 Go to previous messageGo to next message
adee_saleh
Messages: 30
Registered: May 2007
Location: Yemen
Member

i am sorry maybe i did not get it but i read in the performance tuning guid and refrence that "the size can increase to the point where each block is 50% full or even less " but i don't how to know that ?
Re: rebuilding index [message #248574 is a reply to message #248565] Fri, 29 June 2007 10:35 Go to previous messageGo to next message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Always post link to what you quote.
Without the context we can't know what is the meaning.

Regards
Michel
Re: rebuilding index [message #248652 is a reply to message #248574] Fri, 29 June 2007 22:13 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
This is an adaption of something I have used before. I haven't tested this one, but it should be reasonably close if your column and table statistics are up to date. It doesn't take block overheads into account, and it won't work on bitmap indexes.

You will need to adapt it yourself for partitioned indexes

SELECT  table_name
,       index_name
,       num_rows
,       leaf_blocks
,       rows_per_leaf_block
,       avg_key_len
,       rows_per_leaf_block * avg_key_len AS avg_bytes_per_leaf_block
,       db_block_size
,       TRUNC(100 * rows_per_leaf_block * avg_key_len / db_block_size, 2) AS utilisation_pct
FROM   (
         SELECT  i.table_name
         ,       i.index_name
         ,       i.num_rows
         ,       i.leaf_blocks
         ,       i.num_rows / i.leaf_blocks AS rows_per_leaf_block
         ,       kl.avg_key_len
         ,       s.bytes / s.blocks AS db_block_size
         FROM    user_indexes i
         JOIN    user_segments s
         ON      s.segment_name = i.index_name
         AND     s.segment_type = 'INDEX'
         JOIN (
                 SELECT ic.index_name, SUM(c.avg_col_len) AS avg_key_len
                 FROM   user_tab_columns c
                 JOIN   user_ind_columns ic
                 ON     ic.table_name = c.table_name
                 AND    ic.column_name = c.column_name
                 GROUP BY ic.index_name
         ) kl
         ON      kl.index_name = i.index_name
         WHERE   i.num_rows >= 5000
         AND     i.leaf_blocks > 0
)
ORDER BY utilisation_pct DESC


Ross Leishman
Re: rebuilding index [message #248695 is a reply to message #248652] Sat, 30 June 2007 10:58 Go to previous messageGo to next message
adee_saleh
Messages: 30
Registered: May 2007
Location: Yemen
Member

the link of the statmnet have mentioned is
http://download-east.oracle.com/docs/cd/B10501_01/server.920/a96533/data_acc.htm#7531

read the Re-creating Indexes subject to know what i mean

about rleishman you get it

actully i tested your code and it is amazing how exactly gave me what i want
thanks a lot man

and thanks for every helped me for solving this problem by all the subject that mentioned me to read and i realy have a usefull information

thanks a lot
Re: rebuilding index [message #248731 is a reply to message #248695] Sun, 01 July 2007 02:10 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
I've created another version of the SQL above that ignores function-based, bitmap, bitmap-join, and domain indexes. It also covers partitioned indexes, and to a very rough degree of accuracy - compressed indexes, and prefixed partitioned indexes.

It can be found here as part of an online tuning guide.

Also see here for those interested in receiving updates to this guide.

Ross Leishman
Re: rebuilding index [message #248744 is a reply to message #248731] Sun, 01 July 2007 08:07 Go to previous messageGo to next message
adee_saleh
Messages: 30
Registered: May 2007
Location: Yemen
Member

thank you very much this eliminate the problem of partitioned index
thanks a lot
Re: rebuilding index [message #261071 is a reply to message #248731] Tue, 21 August 2007 14:01 Go to previous messageGo to next message
faiz_hyd
Messages: 294
Registered: February 2006
Senior Member
Thanks Ross for that script,
I ran that script on our PRODUCTION DB, out of 540 indexes the ouput is showing 510 indexes, does that mean 510 indexes are candidates for rebuild,
Basically what i wanted is a list of indexes which has more emtpy blocks so that i can plan for rebuilding them,
it's an OLTP application with constant insertion but also regular purging of old data, so there are empty blocks.

Is there a script which can give a list of indexes which has more empty blocks.

Oracle Version is 9.2



Thanks
Re: rebuilding index [message #261075 is a reply to message #248397] Tue, 21 August 2007 14:14 Go to previous messageGo to next message
BlackSwan
Messages: 25045
Registered: January 2009
Location: SoCal
Senior Member
>Is there a script which can give a list of indexes which has more empty blocks.

More than what?
Why are empty blocks harmful/bad?
IMO, you don't have to worry about rebuild/reorg when tablespace is LMT.
I did my last reorg/rebuild more than 4 years ago.

How do you quantify that things (response time) are better after a rebuild?

Do you suffer from Compulsive Tuning Disorder?
Re: rebuilding index [message #261087 is a reply to message #261075] Tue, 21 August 2007 15:02 Go to previous messageGo to next message
faiz_hyd
Messages: 294
Registered: February 2006
Senior Member
Thanks for follow up,

Even we didn't rebuild indexes since the Application went live which is almost 4 years back ,But since the Index TS is growing like anything,
what we have noticed is when a new Environment was build using a copy of Production database ( Export / Import ) the index TS occupied just 140 gb in new region, and where as in PROD index Tablespace is more than 270 GB which is double, this gave us a clue that we have empty blocks, and we knew since there are regular purging happening and at same time insertions also, but still Index TS is growing.. on PROD

I want to get a list on hand first,
then select a query before rebuilding atleast 2-3 index on main table and compate in Stress test environemnt first .

Script which can give index list with say more than 25or 40% empty blocks.

Thanks
Re: rebuilding index [message #261089 is a reply to message #248397] Tue, 21 August 2007 15:15 Go to previous messageGo to next message
BlackSwan
Messages: 25045
Registered: January 2009
Location: SoCal
Senior Member
How many minutes/hours of YOUR time = same as cost of 200GB disk?
I'll stipulate that you can "reclaim" some disk space.
I seriously wonder if the time, effort & CPU cycles results in a worthwhile net gain for the organization.
Re: rebuilding index [message #261128 is a reply to message #261071] Tue, 21 August 2007 22:33 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
faiz_hyd wrote on Wed, 22 August 2007 05:01
I ran that script on our PRODUCTION DB, out of 540 indexes the ouput is showing 510 indexes, does that mean 510 indexes are candidates for rebuild,


The script should show every index that contains more than 5000 rows. The last column shown is Utilisation%. Values less than 50 are sub-optimal and indicate significant deletes or updates (of indexed values) on the table.

Whether there is any benefit to rebuilding these sub-optimal indexes will depend.

If you have lots of new rows coming in with indexed values similar to those deleted or updated, those empty spaces will fill up naturally.

If you recently performed a major delete or update, then the space may not be reclaimed naturally, and a rebuild will be beneficial.

If the table is loaded with indexed values that are then updated or deleted AND NEVER REPLACED, you will definitely need to rebuild. The classic example of this is a PK allocated from a sequence: it allocates a bunch of values (say 100-200) the rows live for a while and are then archived (deleted) - those slots in the index will probably never be refilled. If you perform lots of range and full index scans, they will benefit from a rebuild.

Ross Leishman
Re: rebuilding index [message #263343 is a reply to message #261128] Wed, 29 August 2007 14:58 Go to previous messageGo to next message
feroze
Messages: 144
Registered: September 2005
Location: chicago
Senior Member
Thanks Ross

As per your previous reply
Quote:

The last column shown is Utilisation%. Values less than 50 are sub-optimal and indicate significant deletes or updates (of indexed values) on the table.



if Utilisation%. is more than 50% and indexes are on huge tables like more than 100 million(+) rows table, Should we consider rebuilding those indexes or not.

And Is there a way to get Index list with most Empty blocks( say if more than 30% blocks are empty out of total No of blocks for a particular index)
Thanks

[Updated on: Thu, 30 August 2007 01:31] by Moderator

Report message to a moderator

Re: rebuilding index [message #263376 is a reply to message #263343] Wed, 29 August 2007 22:03 Go to previous message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
Rebuilding indexes with a utilisation >50% is probably a waste of time.

In an optimal situation where there are no deletes or updates, every block will be between 50% and 100-PCTFREE (say 90%). So on average you would expect utilisation to be around 70%. Remember that is the OPTIMAL situation.

Under normal circumstances, the utilisation could drop to 60% before you would call it abnormal - ie. far enough away from the 70% average to seem strange.

Then you have to weigh up the cost of rebuilding the index. Are you really going to bother rebuilding an index with 50% utilisation when it's only 10% worse than something you would consider "normal"?

In my opinion, an index at 50% or below is not only suspect, it is far enough below average to warrant a rebuild.


To my knowlege, you cannot probe an individual index block in SQL to tell whether it is empty, full, or otherwise. So getting a list of indexes with the most "empty" blocks - in SQL - is not possible (to my knowlege).

This article describes a method of dumping an index. You could use the dump to determine empty blocks, but this would probably take more effort and CPU time than rebuilding the index.

Ross Leishman
Previous Topic: slow in scanning documents
Next Topic: warning message
Goto Forum:
  


Current Time: Wed Dec 07 18:21:44 CST 2016

Total time taken to generate the page: 0.08489 seconds