Re: Locally Managed Tablespaces ... again!!!

<ctcgag_at_hotmail.com> wrote in message
news:20030116163442.650$u0_at_newsreader.com...
> "Howard J. Rogers" <howardjr2000_at_yahoo.com.au> wrote:
> > <ctcgag_at_hotmail.com> wrote in message
> > news:20030116134537.384$IO_at_newsreader.com...
> > > "Howard J. Rogers" <howardjr2000_at_yahoo.com.au> wrote:
>
> > > > Or that multiple DBWx's deliberately parallelize (and thus create
> > > > contention) writes to indexes and tables?
> > >
> > > If the index and the table are on the same spindle, deliberation is
not
> > > required for this contention to arise.
> > >
> >
> > Precisely. But it is then just as likely to arise between a table and
its
> > index as it is between a table and a table, or an IOT and a cluster, or
a
> > table and an IOT, or an index and another index... and so on.
>
> I doubt this. An insert, delete, or update (of an indexed
> column), almost guarantees that both table block and index block will be
> changed within one DBWR cycle. The other pairings you mentioned are
> less likely to be so strongly correlated.
>

And your evidence for this assertion is....?

> > > > Like, DBWR has a clue *what*
> > > > it's writing?
> > >
> > > My point exactly.
> >
> > So follow your point to its inevitable conclusion: contention does not
> > inherently arise between tables and their indexes.
>
> I think DBWR contention does inherently arise between a (OLTP) table and
> it's index if they are on the same spindle.
>

Yup, if all else fails, ignore the evidence, and just re-assert that which is demonstrably untrue.

>
> > If DBWR doesn't know what its writing, then we can re-write your earlier
> > sentence to read
> >
> > "If the segment and the other segment are on the same spindle,
> > deliberation is not
> > required for this contention to arise."
>
> "However, the severity of the contention will depend on how correlated
> the activity of the two segments are."
>
>
> > That's a fair re-write, because you've just agreed that DBWR doesn't
know
> > what *sort* of segment it's writing, so we shouldn't use words like
> > 'table' and 'index', which are things that only we (humans) know about.
>
> The RDBMS "knows" about them when it automatically changes one
> to reflects changes in the other. DBWR doesn't know this, but it
> has to deal with the aftermath.
>

You stated that DBWR would be more likely to cause contention doing writes for indexes and tables than for any other pair of segments. Since it doesn't know what it's writing, it can't go out of its way to create that contention specially for that pair. So you'll have to invent some other mechanism to explain why this pairing is particularly prone to write contention. Merely asserting that the index blocks and table blocks will be flushed in the same DBWR cycle doesn't do it, I'm afraid.

> >
> > And as re-written, that statement is perfectly true. But that's an
> > argument for lots of spindles, not specifically to house indexes in a
> > separate tablespace for performance reasons.
>
> Right, I wouldn't argue to separate tablespace from indices for
performance
> reasons.

But if you're going to assert that tables and indexes are especially prone to write contention, that's exactly what you should do. You can't have it both ways.

>I like SAME. I view this as an intellectual puzzle, not
> some practical debate. But, if you didn't have RAID, and DBWR performance
> was a real system bottle-neck, and you thought this bottleneck was do to
> avoidable contention rather than just sheer throughput limitations,
> I would recommend separating a table from it's indices.

Why? What if the contention is between simultaneous writes of EMP and DEPT? You still going to recomend splitting EMP from PK_EMP?

>And separating
> (frequently updated) indices on the same table from each other. And
> separating tables tied together by triggers from each other. Thank God
> for RAID.

Precisely. It's the contention that's the issue, not whether a segment is an index or a table.

HJR
> Now, if I thought reading contention was the main bottleneck, it would
> be different. As far as I can tell, reading index and table is inherently
> serial for any one query. Sure, one query may be reading the index while
> another incidentally wants to read the data, but thinks that incidentally
> collide probably less sever than ones that systematically collide.
> Therefore, separating the index from the index and the table from the
table
> is probably more important than separating the index from the table. So
> in this case, I think I agree with you.
>
> Xho
>
> --
> -------------------- http://NewsReader.Com/ --------------------
> Usenet Newsgroup Service New Rate! $9.95/Month 50GB
Received on Thu Jan 16 2003 - 16:02:33 CST