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

Niall Litchfield wrote:

> <ctcgag_at_hotmail.com> wrote in message
> news:20030118140726.498$s2_at_newsreader.com...
> > > 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.
> >
> > It seems pretty prima-facie to me. If you know of something that operates
> > here to prevent that contention, I'm all ears. But I'm no more inclined
> to
> > accept your dogma than I am the old dogma.
>
> I wonder, do you also seperate parent/child tables. It seems to me that any
> argument that suggests tables and their indexes *inherently* content applies
> in spades to parent/child master/detail type relationships.
>
> I obviously cannot speak for Howard, especially so recently after suggesting
> block dumps were not often needed and then resorting to them in a discussion
> with him and Richard, but I suspect you will find that he agrees that
> contention for the same device can and does occur in Oracle databases, the
> 'problem' that he and I amongst others seek to address is that ovcer many
> years it has been stated that this is especially prone to occur between
> tables and the indexes on them. There is no justification for this.
> Contention occurs between different segments and different sessions with no
> regard for the nature of those segments and the nature of those sessions.
> The best recourse is to spread IO as evenly as possible over all available
> devices (and to keep the redo and archive logs seperate). Seperating tables
> and indexes may achieve this, but it is no silver bullet. Case in point. The
> tablespaces with index in their name hold indexes, the tablespaces without
> don't.
>
> Tablespace
> ------------------------------
> Av Av Av Av Buffer Av
> Buf
> Reads Reads/s Rd(ms) Blks/Rd Writes Writes/s Waits
> Wt(ms)
> -------------- ------- ------ ------- ------------ -------- ---------- -----
> -
> TEMP
> 9,770,099 0.0 6.7 1,601,221 54
> 0.6
> AGRHISTR4
> 1,891,262 2.8 7.8 22,749 0
> 0.0
> AGRTRANS4
> 1,800,065 4.6 16.4 13,597 0
> 0.0
> LOB_DATA
> 735,726 0.0 3.7 1,960 0
> 0.0
> AGRSTATIC4
> 571,810 3.8 11.3 17,648 0
> 0.0
> AGRINDEX
> 409,118 1.7 1.1 27,642 0
> 0.0
> AGRINDEX4
> 250,248 1.3 1.0 35,124 0
> 0.0
> RBS
> 720 4.5 1.0 121,202 -586
> 0.1
> AGRTRANS
> 74,190 3.5 5.9 2,593 110
> 2.9
> SYSTEM
> 41,639 4.1 2.3 29,207 54
> 0.6
> AGRSTATIC
> 32,632 4.4 5.4 1,777 3
> 0.0
> TOOLS
> 14,796 6.3 8.7 16,817 53
> 0.8
> AGRHISTR
> 29,446 4.0 4.7 782 6,981
> 3.3
> AGRTEMP
> 6,298 3.8 13.4 4,845 0
> 0.0
> AGRCOMPELLO
> 990 3.9 1.0 54 0
> 0.0
>
> Now obviously this is a blunt instrument, but you will note massively
> different amounts of IO on tablespaces which is not especially dependent
> upon IO. In fact in this case you do get the greatest contention if you
> stick the datafiles for AGRHISTR4 and AGRTRANS4 on the same device. There
> are no indexes in either of these tablespaces.
>
> --
> Niall Litchfield
> Oracle DBA
> Audit Commission UK
> *****************************************
> Please include version and platform
> and SQL where applicable
> It makes life easier and increases the
> likelihood of a good answer
>
> ******************************************

Well said. I am in complete agreement. I think Tom, Howard, et al have made their point eloquently.

I'm now trying to put together a demo for my students and that is a bit more challenging.

Daniel Morgan Received on Sat Jan 18 2003 - 13:59:29 CST