Welcome Guest ( Log In | Register )


 
 
 
 
 
 

 
 
Oracle 

Performance Tuning Reference poster
 
Oracle training in Linux 

commands
 
Oracle training Weblogic Book
 
Easy Oracle Jumpstart
 
Oracle training & performance tuning books
 
Burleson Consulting Remote DB Administration
 
 
 
Reply to this topicStart new topic
> Index Rebuilding
Shaikh
post Jan 7 2008, 10:54 PM
Post #1


Newbie
*

Group: Members
Posts: 5
Joined: 7-January 08
Member No.: 15,816



In which cases DBA goes for index rebuilding ? how to know that these indexes have to rebuild ?
Go to the top of the page
 
+Quote Post
thbaig
post Jan 8 2008, 02:10 AM
Post #2


Member
**

Group: Members
Posts: 13
Joined: 29-October 07
Member No.: 13,408



QUOTE (Shaikh @ Jan 8 2008, 03:55 AM) *
In which cases DBA goes for index rebuilding ? how to know that these indexes have to rebuild ?


when user are performing DML(update,del) very much. It cause index fragmentation. You should analyze index and rebuild it.
Go to the top of the page
 
+Quote Post
dave
post Jan 8 2008, 05:30 AM
Post #3


Advanced Member
***

Group: Members
Posts: 5,206
Joined: 8-October 04
Member No.: 785



QUOTE (thbaig @ Jan 8 2008, 07:11 AM) *
when user are performing DML(update,del) very much. It cause index fragmentation. You should analyze index and rebuild it.


what? what a load of rubbish

you need to read Richard Foote's index presentation
Go to the top of the page
 
+Quote Post
burleson
post Jan 8 2008, 07:50 AM
Post #4


Advanced Member
***

Group: Members
Posts: 11,401
Joined: 26-January 04
Member No.: 13



Hi, Dave,

>> what a load of rubbish


No, the junk that indexes never need rebuilding is anotther of those myths, don't buy it!

>> you need to read Richard Foote's index presentation

I think Foote has recognized that there are indeed cases where index rebuilding has proven improvements.

Index rebuild/coalesce not only reduces disk space consumption, it improves SQL performance:

>> In which cases DBA goes for index rebuilding ?

Usually, indexes that become physically sparese (not full blocks) as a result of deletes:

http://www.dba-oracle.com/t_index_rebuilding_issues.htm

See Oracle MetaLink note 122008.1 for the officially authorized script to detect indexes that benefit from rebuilding. This script detects indexes for rebuilding using these rules: Rebuild the index when these conditions are true:

- deleted entries represent 20% or more of the current entries.
- the index depth is more then 4 levels.

Oracle's index rebuilding guidelines appear in Metalink note 77574.1 (dated April 2007) recommends that indexes be periodically examined to see if they are candidates for an index rebuild:

“When an index is skewed, parts of an index are accessed more frequently than others. As a result, disk contention may occur, creating a bottleneck in performance.

It is important to periodically examine your indexes to determine if they have become skewed and might need to be rebuilt.”

The note also says that the index rebuilding criteria has changed since the advent of Oracle9i, where a blevel > 4 was a good threshold. It also notes that the pct_deleted column in index_stats from “alter index xxx validate structure” provides a guideline for index rebuilding candidates:

“Prior to 9i, if the BLEVEL is more than 4, it is recommended to rebuild the index. . .

The PCT_DELETED column shows what percent of leaf entries (index entries) have been deleted and remain unfilled.

The more deleted entries exist on an index, the more unbalanced the index becomes. If the PCT_DELETED is 20% or higher, the index is candidate for rebuilding.”

Metalink Note:46757.1 titled "Notes on Choosing an Optimal DB BLOCK SIZE" says that there are some tangible benefits to using a larger blocksize:

- Using bigger blocks means more data transfer per I/O call; this is an advantage since the cost of I/O setup dominates the cost of an I/O. . .

- Using bigger blocks means more space for key storage in the branch nodes of B*-tree indexes, which reduces index height, which improves the performance of indexed queries.

- Using a block size that is k times bigger than your current one will save you (k-1)f/(kb-f) bytes of space for large segments, where f is the size of a block's fixed block header (61 bytes for tables, 57+4n for n-table clusters, 113 for indexes). For example, you will conserve about 4% of data storage (4GB on every 100GB) for every large index in your database by moving from a 2KB database block size to an 8KB database block size.

- When using large block there are less probability of chained and migrated rows, which in turn reduced the number of reads required to get the information.

Other evidence on index rebuilding
Ken Adkins, a respected Oracle author, notes that it is often difficult to pinpoint the exact reason that indexes benefit from a rebuild:

“The DBAs were pulling out their hair until they noticed that the size of the indexes were too large for the amount of data in the tables, and remembered this old “myth”, and decided to try rebuilding the indexes on these tables.

The DELETE with the multiple NOT EXISTS went from running for 2 hours to delete 30,000 records, to deleting over 100,000 records in minutes. Simply by rebuilding the indexes….”


--------------------
Hope this helps. . .

Donald K. Burleson
Oracle Press author
Author of Oracle Tuning: The Definitive Reference
Go to the top of the page
 
+Quote Post

Reply to this topicStart new topic
1 User(s) are reading this topic (1 Guests and 0 Anonymous Users)
0 Members:

 

Lo-Fi Version Time is now: 23rd August 2014 - 10:19 AM