"Joel Garry" <joel-garry_at_home.com> wrote in message
news:91884734.0301201622.2f5c13a4_at_posting.google.com...
> "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.
>
OK, I give up. Who, what, why or when did the idea arise that somehow its OK
to keep indexes with their tables for reads, but it all gets a bit iffy with
writes? DBWR doesn't *know* nor *care* what it's writing. So why should
indexes and tables suddenly be more prone to write contention than read
contention?
Please tell me. 'Cos where I'm looking from, this is a whole bunch of people
desperately clinging to olde mythes they learnt as a junior dba, and don't
want to relinquish.
This isn't dogma. It's science. There's no contention between an index and
its tables, period.
Invent some explanatory mechanism if you want to go further down this road.
HJR
> >
> >
> > --
> > 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.
Received on Tue Jan 21 2003 - 00:07:15 CST
