Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Index management

Re: Index management

From: Joel Garry <joel-garry_at_home.com>
Date: 3 May 2004 13:21:27 -0700
Message-ID: <91884734.0405031221.3a68bcdf@posting.google.com>


"Noons" <wizofoz2k_at_yahoo.com.au> wrote in message news:<4094e557$0$32558$afc38c87_at_news.optusnet.com.au>...
> "Niall Litchfield" <niall.litchfield_at_dial.pipex.com> wrote in message
> news:4094002d$0$25328$cc9e4d1f_at_news-text.dial.pipex.com...
>
> > It seems to me that your scenario rather assumes that nothing else of
> > interest is happening on this device - as soon as you allow other segments
> > to be placed on this device, or other processes to be interested in this
> > device then any head movement during the scan will likely be lost amongst
> > head movement due to the multi-user nature of the system. You could also ask
>
>
> Well, pushing that concept to the extreme means also that we should never
> bother with clustering anything because multi-user access will destroy any
> benefits that may be gained. Which is probably why SAME came about?

I think this extreme ignores the reality that so many "typical business systems" will still have times of batch processing. Even with multiple, er, streams, of batches, whatever scheduling algorithm is used will hopefully allow some chunks of time for each.

>
> Average the I/O wait across all tablespaces and forget about any space
> allocation optimisation other than the basics of blocking and reducing
> excessive recursive SQL? Maybe that is the way we should face this?

The idea is to account for common possibilities. SAME may be ok for high-transactional times, but I think the allocation optimisation this thread is talking about may make some difference during "other" times.

>
> I'm all for simplification and if it means all I have to look at is
> the system I/O distribution counters and balancing I/O across disk
> subsystems, I love it! I'd rather control I/O distribution through
> the OS itself alone rather than have to learn two or three layers of
> optimization in order to tune I/O. Or alternatively, "trust it all to
> ASM and think of England"? ;)

I'd rather have the tools to optimizer multiple layers for different possible configurations, _and have the tools work together_. This "trust us, we're experts" stuff from Oracle and MS is condescending crap inevitably leading to stuff like NT defragmentation and http://metalink.oracle.com/metalink/plsql/ml2_documents.showDocument?p_database_id=BUG&p_id=3455402 (During large insert into a table, create unrelated RO TS; drop RO TS cascade including contents and datafiles; SMON barfs on restart, need to recreate db ;).

We've seen over and over again that increasing the number and depth of abstraction layers with the hope that Moore's law will bail us out leads to putting stuff in the wrong layers.

>
> > Interestingly though the conventional wisdom among sql-server folks (and I
> > remain to be convinced of it but smart folks do argue it) is that one should
> > do exactly what you describe above as a routine maintenance procedure, in
> > fact on that system since most tables will in fact have a clustered index
> > upon them you will likely be reorganising tables, probably weekly if not
> > daily, exactly to avoid this 'fragmentation' type of issue. dbcc showcontig
> > is the command that is what you are looking for in that environment. I'm not
> > aware of an equivalent in the Oracle world.

Somehow, I don't think it would be all that difficult to add an option to "Tablespace Map" in OEM to color-code contiguity. But if everyone thinks it would be useless...

>
> Well, their rows are logically clustered around a given value of a non-unique
> key. This has been one of their "workhorses" for performance for well over
> a decade, when it first appeared in Sybase. I'm not sure it means anything
> in a NTFS environment where physical allocation is all over the place unless
> one takes precautions? Or with any modern SAN?

I think predictive read-ahead could mean a lot in a modern SAN.

jg

--
@home.com is bogus.
http://www.thememoryhole.org/war/coffin_photos/dover/clarification.htm
Received on Mon May 03 2004 - 15:21:27 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US