topic Re: How to find out which indexes want to rebuild in the database in Operating System - HP-UX

How to find out which indexes want to rebuild in the database

Nirmalkumar — Thu, 10 May 2007 09:58:23 GMT

Hi,

Is any datadictionary view or dynamic performance view are there to find out which indexes are want to rebuild in the database.

Help apperciated..

Thanks,
Nirmal.

Re: How to find out which indexes want to rebuild in the database

Piergiacomo Perini — Thu, 10 May 2007 10:20:52 GMT

Hi Nirmal (nice to meet u again),

as far as i know doesn't exist a view like you're looking for but
try this "ir.sql" (in attch) as start point.
I have never used neither mainteined neither created;
so use it in test environment.
(again : this seems to work with Oracle Server 7.3.x to 9.x).

note :
Rebuild the index when:
- deleted entries represent 20% or more of the current entries.
- the index depth is more then 4 levels.
Possible candidate for bitmap index:
- when distinctiveness is more than 99%

hth
regards
pg

Re: How to find out which indexes want to rebuild in the database

spex — Thu, 10 May 2007 10:45:05 GMT

Hello,

If you can afford the time, your best bet is to rebuild every index in your user tablespace(s). I've scheduled the attached script to run every weekend on my production database server (please note that it's not my script, although I may have modified it a bit). It loops through all the indexes in the target tablespace and generates a dynamic script (rebuild_indexes_dynamic.sql) which does an online rebuild of each one.

If rebuilding every index isn't an option for you, see this article for tips on how to identify which indexes should be rebuilt:

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

PCS

Re: How to find out which indexes want to rebuild in the database

HGN — Thu, 10 May 2007 15:34:26 GMT

Hi

Please leave feedback you have left feeback only for 3 out of 31 responses, pleas read the forum guidelines, leaving a feedback is a nice way to say thanks for the people who have been helping you.

Rgds

HGN

Re: How to find out which indexes want to rebuild in the database

Arturo Galbiati — Fri, 11 May 2007 03:14:38 GMT

Hi Nirmal,
you can use the attached script.

HTH,
Art

Re: How to find out which indexes want to rebuild in the database

Yogeeraj_1 — Fri, 11 May 2007 04:24:03 GMT

hi Nirmal,

If you are using Locally managed tablespaces at the backend, this operation should be be arising.

In fact, in normal database operations rebuild of indexes should not be really done.

what version of database are you using?

kind regards
yogeeraj

Re: How to find out which indexes want to rebuild in the database

Nirmalkumar — Fri, 11 May 2007 07:39:02 GMT

Hi all,

Thanks 2 all for providing the scripts.

yogeraaj- we are using oracle 9i version database.

Thanks,
Nirmal.

Re: How to find out which indexes want to rebuild in the database

Yogeeraj_1 — Fri, 11 May 2007 07:45:53 GMT

hi Again,

If your tablespaces are Locally managed and the indexes have been created on them, then there is nothing to worry about!

To check your tablespaces, run:
select TABLESPACE_NAME, EXTENT_MANAGEMENT
from dba_tablespaces;

hope this helps too!
kind regards
yogeeraj

Re: How to find out which indexes want to rebuild in the database

John D Wimmer — Wed, 16 May 2007 12:16:36 GMT

Why do you want to rebuild your indexes? You probably don't need to.

http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:2913600659112

Re: How to find out which indexes want to rebuild in the database

Mahesh Rajendran — Wed, 16 May 2007 12:48:24 GMT

I would second that.
Rebuilding indexes as scheduled job is just a waste of time and resource.
And Switching to LMT does not automatically fix all the problems. LMT with uniform extent size could avoid Extent level fragments, but still there are some holes in block level which you cannot avoid in most cases.

Re: How to find out which indexes want to rebuild in the database

TwoProc — Wed, 16 May 2007 15:10:41 GMT

I would disagree with not rebuilding at all, even scheduled items.
I would disagree with rebuilding everything often.
I could and would agree or disagree with all of this, depending on the case.

Do what you need to make your system perform for you (despite all pundits).

I "bought in" to the fact that if you're using locally managed tables with uniform allocation its never necessary, and that you never have to rebuild indexes, as long as you've kept up with leaf node depth... etc. etc. I agree that it is MUCH LESS necessary with uniform allocation, but never needed is different thing.

HOWEVER, after 3 years of steadily declining FULLY MEASURED performance (despite monumental recurring tuning tasks which helped tremendously, but still left us with "less performance" than the previous year, I moved/rebuilt all of my hottest and largest target tables and indexes, and total I/O at peak went down to 1/4 of what it used to be.

Those rebuilds were and still are measured via perfview, and I can see the differences at each reorg, over the multi-month peak periods, comparing year to year.

Can I summarize those total changes in Oracle? Probably, but it doesn't show as easily and readily there, even using statspack, it's more inferential. What I can see easily before and after is that latch contention is down, I'm servicing requests faster, I'm doing less physical I/O.

But Perfview shows more about total system throughput than anything else, and it clearly demonstrates that the system handles load more efficiently than before. Like 30% easier and better, despite having much, much more data and simultaneous queries and demands placed on the server from years of custom development and IT requirements growth and features and deliverables.

Put simply, I don't like doing these yearly targeted reorgs, and I certainly don't enjoy it. No on makes any OT money for it (not even comp time sadly enough), and the extra "cost" to our company simply does not exist in any realistic sense, no more than inferring that someone looking over logs for access issues, errors, possible problems and not finding any is a "wasted cost."

However, we've got sustained renewed performance out of older hardware that we've measured, and are quite happy with.

Keep in mind that this was a focused exercise, on only our biggest problem areas, and nothing else (that is just 8 tablespaces out of 500 or so, and most of them only the top 4 to 20 (depending) largest tables/indexes or just indexes in each). The overwhelming majority of tables and indexes in the system were left alone.

So, do what you need to do to make your system run well for yourself. Be cautious of following in lock step "all purpose" rules of thumb, as they usually don't always apply everywhere to everything. Question things more for yourself, measure when possible and use your knowledge to treat issues.