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

From: Niall Litchfield <n-litchfield_at_audit-commission.gov.uk>
Date: Mon, 20 Jan 2003 09:33:59 -0000
Message-ID: <3e2bc287$0$241$ed9e5944@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.

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

******************************************

Received on Mon Jan 20 2003 - 03:33:59 CST