Re: Episode 2: the EntMgr Green Light!!

I get a kick out of listening to you guys. Do I have to draw you guys pictures?

The free space per block is evenly distributed after a rebuild with the correct pctfree setting. You arrive at that correct setting by trial and error after several rebuilds and watching the growth rate afterwards. If my system has low write activity, I set a low pctfree, otherwise it's set higher. I'm not rebuilding these things daily you know - once every few months!! One size doesn't fit all. I'm saying it really depends on your system's particular behavior.

Running stats generates database workload also -- let's not do that either then because the benefit really isn't that much. I really don't see any change in execution plans (for the better) when stats are rerun more often than not.

Let's just install the database and forget about it. "self-managing" as they say in 10g. Import the vendor's schema and play cards on the internet all day. Oh geez, the vendor forgot to index that fk -- let's not touch it. Oh, this query would perform so much better if an index was added here -- nope, can't touch it. You're promoting a "hands off" approach to database management. I make mistakes, but I learn from them. Most vendors do not tune for Oracle, so we have to do it -- otherwise, just turn autoextend on and forget about the database.

Dom.

"Richard Foote" <richard.foote_at_bigpond.com> wrote in message news:<w8rrb.3851$aT.229_at_news-server.bigpond.net.au>...
> Hi (again) Domenic,
>
> Comments embedded.
>
> "Domenic G." <domenicg_at_hotmail.com> wrote in message
> news:c7e08a19.0311081901.60899479_at_posting.google.com...
> > Guys,
> >
> > This is a bunch of BS. If your system is read-only most of the time,
> > DSS, OLAP, then rebuilding your indexes after a huge load of data
> > increases read performance -- don't tell me it doesn't. Smaller
> > index, less I/O.
>
> It doesn't. Or rather as I mentioned previously it doesn't in the majority
> of cases.
>
> Look at the simple example in my previous post, unless you describe a 0.2%
> improvement on a "poorly" fragmented index as a significant increase in read
> performance ...
>
> The "costs" associated with rebuilding an index vs. the "benefits"
> afterwards *in most cases* makes the rebuild an unjustified operation.
>
> This is the first time you mentioned your system being read only with
> changes only after huge data loads. Interesting.
>
> What happened to your deletes, remember you're checking for 20% deleted
> entries ?
> What happened to your sequence based indexes ?
> What happened to checking for too many levels in an index ?
>
> None of these are symptoms of a read only database.
>
> Also, if you're doing huge bulk inserts, consideration should be given for
> invalidating your indexes and rebuilding afterwards. Not so much for
> subsequent index performance but for the performance of the bulk loads
> themselves.
>
> >
> > Second, if the index starts growing again too quickly, the pctfree
> > setting used on the rebuild was too low. The rebuild is not the
> > culprit, you didn't use the correct setting. The base of the index is
> > much better after a rebuild -- go write a b tree routine and you'll
> > see for yourself.
>
> Need to be careful here. If you're rebuilding to make your indexes smaller,
> then you need to packing the buggers by definition (else why bother). If
> you're now arguing "wasted" space is good, then what criteria do you use.
> You admit to no metrics so what pctfree value do you use. How did you
> determine such a value.
>
> What do you define as a "much better" base of an index. Does a (say) 0.2%
> performance improvement at best justify having such a better base.
>
> >
> > Geez, you guys make a mountain out of a mole hill. Thank God I'm the
> > only DBA in my shop. Next thing you'll say it's not a good idea to
> > index FKs.
>
> Your lucky. Your the only DBA in your shop, you have plenty of free time to
> get busy in and you have the luxury of a quiet time in which to waste
> resources.
>
> Many sites have DBAs that don't have much free time on their hands and more
> importantly don't have the luxury of a quiet time in which to waste
> resources. Validating structure and rebuilding of indexes and the vast
> resources they consume and the availability and performance issues they
> create means that many sites need to *justify* such operations. If there are
> no measurable performance improvements, then why the hell do we subject our
> users and our batch operations to these considerable inconveniences.
>
> The key, the secret is to know and pick the right indexes. And as I've
> suggested, you're criteria is not accurate enough.
>
> If someone, anyone, reads this thread and at least *thinks* first before
> rebuilding an index, at least *considers* what am I actually achieving by
> doing all this and at least takes some *metrics* before/after a rebuild to
> see whether such an operation was worthwhile, then I thank you Domenic for
> helping to make someone out there see the light.
>
> Domenic, it's time for you to see the light also ;)
>
> Cheers
>
> Richard
Received on Sun Nov 09 2003 - 12:33:31 CST