Re: schema health check

From: Joel Garry <joel-garry_at_home.com>
Date: 28 Aug 2003 15:33:31 -0700
Message-ID: <91884734.0308281433.c36f4ad@posting.google.com>

Daniel Morgan <damorgan_at_exxesolutions.com> wrote in message news:<3F4D00E3.52A0B5BE_at_exxesolutions.com>...
> Imran Rahim wrote:
>
> > Ed Stevens <nospam_at_noway.nohow> wrote in message news:<msh9kvkfqsm9h72hp8t352295isvs6v66i_at_4ax.com>...
> > > On 21 Aug 2003 01:13:54 -0700, i_rahim_at_hotmail.com (Imran Rahim)
> > > wrote:
> > >
> > > >"DJ" <nospamplease_at_goaway.com> wrote in message news:<NrO0b.176$L15.69_at_newsfep4-winn.server.ntli.net>...
> > > >> "Imran Rahim" <i_rahim_at_hotmail.com> wrote in message
> > > >> news:e7519cfe.0308200710.315afa98_at_posting.google.com...
> > > >> > Hi,
> > > >> > I'm writing a small script that can be run against all my schemas to
> > > >> > give me some sort of idea as to the "health" of each schema
> > > >> >
> > > >> > So far I've come up with
>
> > > >> > Indexes in Data Tblspce?
> > > >> > Tables in Index Tblspce?
> > > >>
> > > >> Why you checking these?
> > > >
> > > >I am checking these because we seperate the disks for indexes and data
> > > >to minimise contention
> > >
> > > Uh oh . . . .
> > >
> > > You might want to search the archives for some lengthy threads on
> > > "Oracle Myths"
> >
> > Why is this a myth guys? - pls explain. thanks.
>
> Reread the sentence before you post:
>
> Here it is again in case you missed it:
>
> You might want to search the archives for some lengthy threads on "Oracle Myths"
>
> Why would you think we would want to rehash this yet again?

Here is the thread:
http://groups.google.com/groups?hl=en&lr=&ie=UTF-8&newwindow=1&th=6cffe9e53d1b67a4&rnum=1 http://groups.google.com/groups?hl=en&lr=&ie=UTF-8&newwindow=1&th=77f66d59f13375ec&rnum=4

Briefly, the index is much more cacheable, so you are unbalancing the I/O - in some cases, Oracle can read the index before the table. They are not saying separation's a bad thing, just that proactive performance is not the reason to do so. Data management (ie, backup/restore) and measurable disk hot spots are good reasons.

Perhaps the rationales given do not globally apply to all situations? Most likely I missed something, since I never saw any real answer to several questions. For example, minimizing the total time for a bunch of month-end reports to run serially can often be shown to be faster. If for no other reason than a read-ahead buffer on an array can more readily anticipate Oracle's full-table-scan block requests if it doesn't have to reject parts of other tables or indices. I saw some assertions that the unix filesystem will scatter disk blocks all over the place, even though that is at odds with what I see with od (of course, with putting everything into one extent on a fresh filesystem - ouch! stop hitting me!).