Re: Oracle 8i (8.1.7.0.1) + Redhat Linux 7.2 = Cannot create tablespace file > 2 gb

"Joe Salmeri" <JoeSalmeri_at_comcast.net> wrote in message news:QIXS8.25752$Ca2.1690472_at_bin2.nnrp.aus1.giganews.com...
> > No, extents still can't cross the physical file boundary. But you are
> > mistaken in believing you need a 600Mb extent. What's wrong with 3 200Mb
> > extents? Contiguity of extents is a complete and utter mirage anyway,
and
> > there is precisely zero performance improvement to be gained or noticed
> > between a single-extent 600Mb segment, and a 3-200Mb extent segment, or
> even
> > a 30-20Mb extent segment.
>
> Maybe this is true with 8i, maybe this is true with locally managed
> tablespaces, but it DEFINITELY did not used to be true.

Oh yes it did. I chose my figures carefully. Go back to Oracle 7, and you won't find a performance difference between 30-extent segments, and 1-extent segments.

>I have seen
> terrible performance problems solved when the table was broken up into a
> large number of extents. And no it had nothing to do with indexes, they
> were rebuilt regularly. The only change in that situation was to
> consolidate the table into large extents. In that particular example
there
> were less than 100 extents and the performance increase was 10x better.
>

I hate to say I don't believe it. But I don't believe it. There's no physical explanation you can come up with that justifies that sort of peformance improvement. And Oracle isn't voodoo.

> > In dictionary managed tablespace, a 3000-0.2Mb segment would pose
> problems,
> > true. Much, much less so in locally managed tablespace. I myself would
not
> > be comfortable with *quite* that many extents, but 100-60Mb extents
would
> > not be a issue.
>
> If you had bothered to follow the thread you would have read that
everything
> is in dictionary managed tablespaces because until recently that was the
> only option, locally managed tablespaces were not available.
>

You originally asked about large files. The thread has been about you not needing large files. You *especially* (but not particularly) don't need them with locally managed tablespaces. Therefore, a solution to your problem is to migrate to locally managed tablespaces, and you'll find that you needn't worry in the slightest about large files ever again.

However, if you'd read carefully, you'd have noticed the point (which you choose not to believe, but that's up to you) that multiple extents in dictionary managed tablespace is also not an issue, and hence the issue similarly disappears whether you choose to use locally managed tablespaces or not, provided you don't go utterly beserk with the number of extents in dictionary managed tablespace. If you've a 4K block size, as you should do on Linux with a file system, then 250ish extents in dictionary managed tablespace is not, and can not, be a problem.

> >And you are also missing out on a definite
> > performance advantage that ensues from multi-file tablespaces -namely,
> > Oracle's propensity to round-robin the various extents of a segment so
> that
> > they are stored on separate files (and hence, potentially, on separate
> > disks).
>
> That is old school as far as I am concerned. I would never build a
database
> that way. All of my databases use RAID which does a much better job of
> spreading the data across all of my disks than any other option.
>

So what was all that nonsense about demanding 'contiguous extents' then? You know, the bit where you said "Extents (initial allocations or next allocations) MUST be contiguous"??

HJR
>
Received on Fri Jun 28 2002 - 15:17:15 CDT