Re: Databse File layout on only 4 drives Ideas?

From: Joel Garry <joel-garry_at_home.com>
Date: 5 Feb 2003 15:42:56 -0800
Message-ID: <91884734.0302051542.291aa4bf@posting.google.com>

"Howard J. Rogers" <howardjr2000_at_yahoo.com.au> wrote in message news:<gTK%9.40103$jM5.102055_at_newsfeeds.bigpond.com>...

> "Joel Garry" <joel-garry_at_home.com> wrote in message


> > > >

> > > > Even in the case of, say, doing an update on everything in a partition

> > > > that takes an hour during an otherwise low-usage time?

> > >
> > > Probably even in such a case, no. The fact that you're doing a big

>  update

> > > means a lot of stuff will be flushed, fine. Will that be to adjacent

>  blocks

> > > on disk? Not necessarily. The blocks within an extent are not physically
> > > adjacent to each other, raw partitions aside.
> >
> > Well, I decided not to take this at face value. I copied a quiet db
> > file, ran it into od -c|more, and searched for some data that I knew
> > would be there (ie, a knew a character key was 300007, so I entered /3
> > 0 0 0 0 7), and sure enough, I could see the next row, and
> > the next, and the next, just like import loads them in.

> 
> The import business is irrelevant. The question is: are blocks in an extent
> necessarily contiguous with each other, physically, on disk.
> 
> And the answer is no, they are not. Not necessarily. If nothing is going on
> in your database, and you allocate an extent, then in all likelihood, the
> blocks may well be allocated contiguously. But if you have a busy database,
> then the extent allocation will nab whatever O/S blocks it can lay its hands
> on as they wander past the disk head, in the moments when it can.
>

> >
> > So "not necessarily" is NOT the same as what one can expect is going
> > to be out there on a well-managed disk.

> 
> When your example is from a "quiet database", it doesn't surprise me to see
> the results you see (and so do I, being fresh out of hammered OLTP databases
> at home right now). But on a typical production databases, blocks will
> likely not be physically contiguous.

If they are randomly added, no, but if they are reorganized into a pre-extended area, yes they are, and they don't move unless you row-chain or split. So this means that using a large initial extent is good if you are doing lots of full table scans or updates plus sequential adds vs. few random adds. Blocks will likely not be physically contiguous if you set it up that way, which appears to be what you are often advocating, which I think is a mistake arising from the bad assumption that because the bottleneck is so often CPU/latches/whatever, therefore bad physical layout is irrelevant (yes, I'm referring to lots of small extents with LMT, given multiple tables in a tablespace).

And I should mention, this is a copy of what I consider a typical production database, happened to be a job-costing system with some data that would be easy to pick out visually from od -c.

> 
> We are back to 'inherent', 'likely' and 'possible', I'm afraid. I believe
> the phrase is 'it depends'. So as to the original poster's question: would a
> 'cache hot spot' inevitably mean a 'disk hot spot'. Probably not, but it
> depends.

Inherent and possible are red herrings compared to likely. If I set things up so that extents _are_ likely to be contiguous, and it turns out Oracle buffering is too stooopid to understand a full-table update, well, we would all want to know that! I would hope that isn't the case...

Then of course, there's always the spot where all the sequences are being updated...

--
@home is bogus.
Home was a place by the sand.

Received on Wed Feb 05 2003 - 17:42:56 CST