regarding index analyze.

All posts relating to Oracle database administration.

Moderator: Tim...

regarding index analyze.

Postby sivakumarocp » Wed Apr 04, 2012 5:22 am

Hi Tim,

How do we identify index fragmentation.
Usually we can achive this by doing ANALYZE INDEX <Index_name> VALIDATE STRUCTURE;
if we do so informaiton about that index has been populated on Index_stats table, from this we can see the deleted entries percentage. usually if particular index deleted entries has more than 20% we can say this index got fragmented and its candidate for rebuild!

Please tell me upto this my understanding on Index Fragmentation is correct or not.

In production environment can we do ANALYZE INDEX <Index_name> VALIDATE STRUCTURE; if we do so does it make lock on this index?

Please post any scripts to find index fragmentation.
Regards
Sivakumar.A
www.sivakumardba.com
sivakumarocp
Advisor
 
Posts: 256
Joined: Sat Jun 27, 2009 3:53 am
Location: India

Re: regarding index analyze.

Postby Tim... » Wed Apr 04, 2012 10:07 am

Hi.

For the most part you can leave indexes alone. Oracle manages them pretty well. Lots of people have tried to write a script or process to predict when an index needs rebuilding, but the truth is it is not predictable. An index needs rebuilding if changes internally affect its usage by the optimizer. That is almost impossible to predict.

Read Tom Kyte's post on this subject: http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:3035290021641

Cheers

Tim...
Tim...
Oracle ACE Director
Oracle ACE of the Year 2006 - Oracle Magazine Editors Choice Awards
OakTable Member
OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
OCP Advanced PL/SQL Developer
Oracle Database: SQL Certified Expert
My website: http://www.oracle-base.com
My blog: http://www.oracle-base.com/blog
Tim...
Site Admin
 
Posts: 17935
Joined: Mon Nov 01, 2004 5:56 pm
Location: England, UK


Return to Oracle Database Administration

Who is online

Users browsing this forum: No registered users and 1 guest

cron