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

"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.

> > > 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.

> 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.

>
> 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. 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. 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.

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