Hello folks,
I am a longtime DB developer but have been doing alot more DBA (Oracle
8i) type responsabilities lately. One thing I am somewhat struggling
on is the maintainence of indices. I have read many posts on
fragmentation, most of which tell you to look in the data dictionary,
and have been running queries against several of the system views
including dba_indexes and index_stats.

I'm finding it strange that index_stats is empty even though I have
computed stats for most application tables??

What are good rules of thumb to look at in regards to when looking for
signs of fragmentation and other problems of the like?

"DiggidyMack69" <DiggidyMack69@hotmail.com> wrote in message
news:c86ce4f.0404300601.269e175d@posting.google.co m...
> Hello folks,
> I am a longtime DB developer but have been doing alot more DBA (Oracle
> 8i) type responsabilities lately. One thing I am somewhat struggling
> on is the maintainence of indices. I have read many posts on
> fragmentation, most of which tell you to look in the data dictionary,
> and have been running queries against several of the system views
> including dba_indexes and index_stats.
>
> I'm finding it strange that index_stats is empty even though I have
> computed stats for most application tables??
>
> What are good rules of thumb to look at in regards to when looking for
> signs of fragmentation and other problems of the like?

Hi

Don't get me started on indexes ...

index_stats is only populated with details of the last index which has been
analyzed with the VALIDATE STRUCTURE clause.

Probably the best rule of thumb is not to worry about indexes fragmentation
and focus on other things instead.

You may find a presentation of mine that I did recently for our local user
group useful (check download section at www.actoug.org.au). The accompanying
white paper that discusses things in much more detail is coming out soon.

Hopefully if you get through it all, you'll find out that index
fragmentation is rarely an issue.

Cheers

Richard

DiggidyMack69 wrote:

> Hello folks,
> I am a longtime DB developer but have been doing alot more DBA (Oracle
> 8i) type responsabilities lately. One thing I am somewhat struggling
> on is the maintainence of indices. I have read many posts on
> fragmentation, most of which tell you to look in the data dictionary,
> and have been running queries against several of the system views
> including dba_indexes and index_stats.
>
> I'm finding it strange that index_stats is empty even though I have
> computed stats for most application tables??
>
> What are good rules of thumb to look at in regards to when looking for
> signs of fragmentation and other problems of the like?

A good rule of thumb?

Don't bother.

Seriously. If by fragmentation you mean tablespace fragmentation, then
just use consistent extent sizes for everything within a tablespace, and
you won't actually *get* any fragmentation. Better still, since you're
on 8i, use locally managed tablespace with a uniform extent size clause
and you'll never have to worry about fragmentation again.

If you mean index fragmentation, then don't bother either, because
99.99% of indexes on the planet are quite capable of looking after their
own space needs when left entirely to their own devices. I smell a
suggestion that you want to rebuild your indexes to compact them: please
don't go down that route. An index on a monotonically incrementing
sequence number that is subject to sporadic deletes might, occasionally
and just possibly, if the wind is in the right direction and it's a full
moon, benefit from a rebuild. But an index on a monotonically
incrementing sequence number that is subject to bulk deletes won't need
one, and indexes not on monotonically incrementing sequence numbers
won't need one either. Given enough time and sufficient subsequent DML,
space vacated by old index entries will quite happily be automatically
re-used by new ones.

Index_stats is a statistics view on indexes which has to be populated by
a different mechanism than the DBA_INDEXES view. For the latter, you'd
do analyze index X compute statistics, but for the former you have to do
analyze index X validate structure. The view only contains the
statistics for the last index validated, so don't go doing batch
validates and expect to cut corners!

Regards
HJR

Richard Foote wrote:

> You may find a presentation of mine that I did recently for our local user
> group useful (check download section at www.actoug.org.au). The accompanying
> white paper that discusses things in much more detail is coming out soon.
>
> Richard

Hadn't known about ACTOUG. I've added the group to the PSOUG web site.

Are there any others in that region other than AUSOUG?

Thanks.

--
Daniel Morgan
http://www.outreach.washington.edu/e...ad/oad_crs.asp
http://www.outreach.washington.edu/e...oa/aoa_crs.asp
damorgan@x.washington.edu
(replace 'x' with a 'u' to reply)

"Daniel Morgan" <damorgan@x.washington.edu> wrote in message
news:1083386593.449840@yasure...
> Richard Foote wrote:
>
> > You may find a presentation of mine that I did recently for our local
user
> > group useful (check download section at www.actoug.org.au). The
accompanying
> > white paper that discusses things in much more detail is coming out
soon.
> >
> > Richard
>
> Hadn't known about ACTOUG. I've added the group to the PSOUG web site.
>
> Are there any others in that region other than AUSOUG?
>

Hi Daniel,

Not that I know of. There might be some state OUGs that have a website but
we're actually an independent group that are not directly affiliated with
the national AUSOUG body.

Cheers

Richard

Richard Foote wrote:
> "Daniel Morgan" <damorgan@x.washington.edu> wrote in message
> news:1083386593.449840@yasure...
>
>>Richard Foote wrote:
>>
>>
>>>You may find a presentation of mine that I did recently for our local
>
> user
>
>>>group useful (check download section at www.actoug.org.au). The
>
> accompanying
>
>>>white paper that discusses things in much more detail is coming out
>
> soon.
>
>>>Richard
>>
>>Hadn't known about ACTOUG. I've added the group to the PSOUG web site.
>>
>>Are there any others in that region other than AUSOUG?
>>
>
>
> Hi Daniel,
>
> Not that I know of. There might be some state OUGs that have a website but
> we're actually an independent group that are not directly affiliated with
> the national AUSOUG body.
>
> Cheers
>
> Richard

Thanks.

--
Daniel Morgan
http://www.outreach.washington.edu/e...ad/oad_crs.asp
http://www.outreach.washington.edu/e...oa/aoa_crs.asp
damorgan@x.washington.edu
(replace 'x' with a 'u' to reply)