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

"Niall Litchfield" <n-litchfield_at_audit-commission.gov.uk> 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.

No. I only have spindle-level control over one database, and it only has one spindle. For the big databases, it's all handled by the SAN and the people behind the curtain. And that's the way I like it.

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

Do you have summary fields in the parent/master tables, such that everytime something in the child/detail table is updated/inserted/deleted, it automatically updates the parent table correspondingly?

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

Well, I'd certainly agree with that.

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

Actually, it appears it would be for AGRINDEX and AGRINDEX4, as this whole threadlet has been on the topic of write contention, not read contention.

Xho

-- 
-------------------- http://NewsReader.Com/ --------------------
Usenet Newsgroup Service              New Rate! $9.95/Month 50GB