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

"Niall Litchfield" <n-litchfield_at_audit-commission.gov.uk> wrote in message news:<3e2bc287$0$241$ed9e5944_at_reading.news.pipex.net>...
> <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.

Yes, this would be correct, unless you are doing a whole lot of reads and not a whole lot of writes. I wonders if the table cluster option was implemented with the idea of dealing with these types of things. Of course, I've never used clustering.

In the olden days, whilst layering relational design on top of hierarchical files, we did indeed separate parent/childs if possible. Of course, we often would use serial files to keep immediate transactions, with a messaging system to write out the data asynchronously to the data files. So then you have to keep the info about relations and whether the transactions were complete manually, and have ways to deal with integrity violations and read consistency and...

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

But if all you are doing is reading without indices, it's not as big of a deal than if you are reading and writing with indices. If your disk has predictive algorithms, those read statistics might be less than enlightening because it could be smoothly grabbing large chunks of data instead of thrashing the heads all about.

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

jg

--
@home. is. bogus.
Sea World now has a show where they herd cats.