Re: Index management

"Howard J. Rogers" <hjr_at_dizwell.com> wrote in message news:<409d9b3b$0$441$afc38c87_at_news.optusnet.com.au>...
> Roger S Gay wrote:
>
> > "Howard J. Rogers" <hjr_at_dizwell.com> wrote in message
> > news:409d7a18$0$442$afc38c87_at_news.optusnet.com.au...
> >
> >
> >>>Mike Ault wrote:
> >
> >
> >>Well, it appears we are never going to get an explanation of what a
> >>dirty base table block is (I've only asked three times, and Daniel's
> >>asked too).
> >>
> >>But whatever this mysterious beastie happens to be, what possible
> >>difference can it make to such a ratio to rebuild an index??
> >>
> >
> > Please, please Mike, add me to the list of requestors for this explanation.
> > Hopefully a clear definition of "dirty base table block" will add more light
> > than heat to this discussion.
> >
> >>>By definition the order of rows in a relational table is random.
> >>
> >>Also absolutely true.
> >>
> >
> > Howard, are you sure of this? Relational theorists please correct me if I am
> > wrong, but I thought the best we could say about the order of rows is that
> > it is indeterminate, which means we can't even make statistical arguments
> > based on assumed randomness.
>
> Well, OK.... what do we mean by "random" and what do we mean by
> "indeterminate"?
>
> Now you maths wonks will tell me that the two are not the same. But for
> us poor schmucks trying to find a pair of socks to wear of a morning,
> the fact that the ordering in my clothes closet is not random but merely
> indeterminate is of no practical consequence. It still takes me
> excessive minutes to find what I am looking for.

I don't know about your closet, but in mine there are a couple of physically ordered subsets within, basically "clothes favored for work" and "clothes favored for play" subtypes within various organizations such as "shirts," "pants," "underwear, guns and balloons," "sock drawer" and so forth. There is physical ordering since the clothes are laundered in batch jobs, ie, clothes worn to work tend to be washed together once a week, and then put away together, all jammed into the open space left by previous removal. The practical consequence is I go directly to them when selecting for work, usually when rushing about in the morning when excessive minutes are to be avoided. The only full closet scans occur when purging or if looking for a particular article of clothing not used in a while.

>
> I doubt that Mike was arguing for "randomness" in its
> mathematically-rigorous incarnation. I certainly wouldn't. But a mess of
> clothes on the bedroom floor certainly *looks* random enough, and that
> is all that was being said here, I think.

Whether you're a theory wonk or looking at physical ordering, there is a very great difference between random and un-ordered. As an overly-broad generalization, I usually see the result of an ordered load from the use of tools followed by some amount of chaos (depending on design, of course) with additional transactions. Some of the performance issues that may result from this may not be visible to Oracle. For example, I went to look at a system that I was going to be doing some ETL on, expecting to see usual late-morning transactions, and then evaluate whether those people would notice degradation when I started. There were no transactions, only one program maxing out 1 cpu. Such unexpected always gets my attention, so I investigated further. There actually was a lull in transactions, just one of those random times when no one is calling in orders. The one program was one that normally takes about 3 hours. Oracle doesn't show any waits, including cpu. Explain plan shows sort (order by)/header table by index rowid/ header index range scan/ detail table by index rowid/detail index range scan. So, why would cpu be maxed out if Oracle isn't waiting on it? (The answer is the application program is actually doing homegrown temporary tables in memory to summarize data). Certainly a case for throwing hardware (cpu Hz) at it, were anybody to want it faster than 3 hours. But if it wasn't cpu-bound, the physical ordering of the indices getting range-scanned might make a difference - and there is a detail version of the program that may be doing just that, taking 12+ hours. So that is why I'm interested in this whole CF argument, and it got my attention when Mike mentioned batches. I see in my test db (loaded by imp and very few subsequent transactions) the header index CF is 99045 and the detail index CF is 210097. In my production db (many months and many [would be monotonically increasing, except for 3 leading characters of low cardinality] transactions since loaded) the header index CF is blank and the detail index CF is blank. 1.3M rows in detail, 150K rows in header, 8i, hpux, DMT, RBO.

So do I need to rebuild indexes? (Pretty sure the answer would be "no" since there is no key updating and hasn't yet been deletion... but there will be, and massive... hopefully, after going to 9 and partitioning. But it sure looks bad in Tablespace Map. Of course, this whole thread only applies to CBO, right?).

(Richard, I think slide 24 has a typo in the first line)

>
> > My only experience with this was with a third party vendor who slipped a
> > weekly "rebuild every index in the system" job into a production system I
> > was babysitting without saying a word to anyone (Grrrr!) So I can add a
> > note here that index rebuilds of any real size are expensive in both CPU and
> > log archive space as well as being almost always ineffective and pointless.
>
> That is a very important point, of course, and one that hasn't been done
> justice so far, because we are all still trying to work out what on
> Earth Mike is talking about. But when we do, and when we finally get
> over the clustering factor as an issue, we will still have to do
> hand-to-hand combat with the fact that index rebuilds are bloody
> expensive, so you'd better be pretty certain before you start that the
> benefits are going to outweigh the costs.
>
> > Thank you folks for an informative and so far entertaining thread. I, for
> > one, promise faithfully never ever to rebuild an index (not that I ever
> > would or did, mark you) without revisiting this thread and rereading every
> > post in it.
> >
> > Roger S Gay
>
> I think that is a victory of sorts, then, Roger!

Maybe the OP should summarize :-)

>
> Thanks
> HJR
jg

--
@home.com is bogus.
http://www.signonsandiego.com/news/metro/20040507-9999-7m7breach.html