Home » RDBMS Server » Performance Tuning » Scripts to find Indexes which are candidate for Rebuild (Oracle 11.2.0.3, Linux x86 64-bit)
Scripts to find Indexes which are candidate for Rebuild [message #612573] Tue, 22 April 2014 03:10 Go to next message
hitesh.bhatt
Messages: 40
Registered: February 2014
Location: INDIA
Member
Hello Experts,

Could you please help me with any script to find the indexes which are candidate for rebuild?
Fyi...I check in "INDEX_STATS" view but its empty.

Thanks in advance.
Re: Scripts to find Indexes which are candidate for Rebuild [message #612575 is a reply to message #612573] Tue, 22 April 2014 03:21 Go to previous messageGo to next message
Roachcoach
Messages: 1202
Registered: May 2010
Location: UK
Senior Member
Why do you want to rebuild them?
Re: Scripts to find Indexes which are candidate for Rebuild [message #612579 is a reply to message #612575] Tue, 22 April 2014 03:42 Go to previous messageGo to next message
hitesh.bhatt
Messages: 40
Registered: February 2014
Location: INDIA
Member
We are facing some performance issue, so I would also like to find the name of indexes so will deep dive into the same and check if it really wanted to rebuild or no performance post rebuild?
Re: Scripts to find Indexes which are candidate for Rebuild [message #612580 is a reply to message #612579] Tue, 22 April 2014 03:46 Go to previous messageGo to next message
Roachcoach
Messages: 1202
Registered: May 2010
Location: UK
Senior Member
If you dont have any index stats and you dont have any idea of how to identify ones you may (or not) need to rebuild - how can you know they are your problem?

My advice is to post the actual problem up here (read the sticky first) and folks here will try to assist.

Indexes can run into problems needing the odd bit of work done, but better to prove that before you go off rebuilding things.
Re: Scripts to find Indexes which are candidate for Rebuild [message #612581 is a reply to message #612579] Tue, 22 April 2014 04:20 Go to previous messageGo to next message
John Watson
Messages: 4557
Registered: January 2010
Location: Global Village
Senior Member
hitesh.bhatt wrote on Tue, 22 April 2014 09:42
We are facing some performance issue, so I would also like to find the name of indexes so will deep dive into the same and check if it really wanted to rebuild or no performance post rebuild?
In my experience, rebuilding indexes will usually cause performance to degrade for several days. The rebuild will remove all the free space, so all your inserts after a rebuild will cause block splits. It takes some time for the index to stabilize again with enough free space.
Re: Scripts to find Indexes which are candidate for Rebuild [message #612583 is a reply to message #612581] Tue, 22 April 2014 04:31 Go to previous messageGo to next message
hitesh.bhatt
Messages: 40
Registered: February 2014
Location: INDIA
Member
Ok, so as per your advice it's better not to rebuild indexes?
Re: Scripts to find Indexes which are candidate for Rebuild [message #612596 is a reply to message #612583] Tue, 22 April 2014 09:00 Go to previous messageGo to next message
Roachcoach
Messages: 1202
Registered: May 2010
Location: UK
Senior Member
Well like everything else, it depends.

We dont have sufficient information to advise, you're proposing a specific (I'd go as far as niche) fix whilst presenting an extremely generic problem with no detail or reasoning behind the fix.
Re: Scripts to find Indexes which are candidate for Rebuild [message #612597 is a reply to message #612596] Tue, 22 April 2014 09:16 Go to previous messageGo to next message
BlackSwan
Messages: 22786
Registered: January 2009
Senior Member
>We are facing some performance issue,
how many different factors can result in "performance issue"?

It appears you believe in the
Ready, Fire, AIM
school of performance tuning.

You don't know what is wrong but you hope to get lucky by doing index rebuild.
What will you do next after the performance issue remains after the index rebuild?
Re: Scripts to find Indexes which are candidate for Rebuild [message #612672 is a reply to message #612597] Wed, 23 April 2014 03:23 Go to previous messageGo to next message
hitesh.bhatt
Messages: 40
Registered: February 2014
Location: INDIA
Member
Sorry for the confusion here,
as per the analysis looking into physical writes, DB Block changes, Row lock waits, Buffer busy waits one of table and related indexes are top consumers.
Its mainly merge query.
So would just wanted to check initially that if any of the indexes are fragmented and will rebuild the indexes will help?
Also I am checking if NOLOGGING will help here.

Thanks for the reply.

Hitesh
Re: Scripts to find Indexes which are candidate for Rebuild [message #612705 is a reply to message #612672] Wed, 23 April 2014 08:17 Go to previous message
BlackSwan
Messages: 22786
Registered: January 2009
Senior Member
>So would just wanted to check initially that if any of the indexes are fragmented
post SQL & results which shows both cases below
a) fragmented indexed
b) non-fragmented index
how do you decide which index to rebuild?
Previous Topic: Please help me for getting best execution plan as my create view script is taking more time
Next Topic: Need to Understand Explain plan
Goto Forum:
  


Current Time: Wed Sep 17 17:05:03 CDT 2014

Total time taken to generate the page: 0.05334 seconds