OraFAQ Forum: Performance Tuning » Scripts to find Indexes which are candidate for Rebuild

Home » RDBMS Server » Performance Tuning » Scripts to find Indexes which are candidate for Rebuild (Oracle 11.2.0.3, Linux x86 64-bit)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Scripts to find Indexes which are candidate for Rebuild [message #612573]

Tue, 22 April 2014 03:10

hitesh.bhatt
Messages: 84
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.

Report message to a moderator

Re: Scripts to find Indexes which are candidate for Rebuild [message #612575 is a reply to message #612573]

Tue, 22 April 2014 03:21

Roachcoach
Messages: 1576
Registered: May 2010
Location: UK

Senior Member

Why do you want to rebuild them?

Report message to a moderator

Re: Scripts to find Indexes which are candidate for Rebuild [message #612579 is a reply to message #612575]

Tue, 22 April 2014 03:42

hitesh.bhatt
Messages: 84
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?

Report message to a moderator

Re: Scripts to find Indexes which are candidate for Rebuild [message #612580 is a reply to message #612579]

Tue, 22 April 2014 03:46

Roachcoach
Messages: 1576
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.

Report message to a moderator

Re: Scripts to find Indexes which are candidate for Rebuild [message #612581 is a reply to message #612579]

Tue, 22 April 2014 04:20

John Watson
Messages: 8960
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.

Report message to a moderator

Re: Scripts to find Indexes which are candidate for Rebuild [message #612583 is a reply to message #612581]

Tue, 22 April 2014 04:31

hitesh.bhatt
Messages: 84
Registered: February 2014
Location: INDIA

Member

Ok, so as per your advice it's better not to rebuild indexes?

Report message to a moderator

Re: Scripts to find Indexes which are candidate for Rebuild [message #612596 is a reply to message #612583]

Tue, 22 April 2014 09:00

Roachcoach
Messages: 1576
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.

Report message to a moderator

Re: Scripts to find Indexes which are candidate for Rebuild [message #612597 is a reply to message #612596]

Tue, 22 April 2014 09:16

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

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?

Report message to a moderator

Re: Scripts to find Indexes which are candidate for Rebuild [message #612672 is a reply to message #612597]

Wed, 23 April 2014 03:23

hitesh.bhatt
Messages: 84
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

Report message to a moderator

Re: Scripts to find Indexes which are candidate for Rebuild [message #612705 is a reply to message #612672]

Wed, 23 April 2014 08:17

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

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?

Report message to a moderator

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:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Tue Nov 26 21:18:44 CST 2024