Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Oracle 8i (8.1.7.0.1) + Redhat Linux 7.2 = Cannot create tablespace file > 2 gb

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

From: Joe Salmeri <JoeSalmeri_at_comcast.net>
Date: Fri, 28 Jun 2002 22:21:00 GMT
Message-ID: <gj5T8.447996$%y.31863810@bin4.nnrp.aus1.giganews.com>

"Howard J. Rogers" <dba_at_hjrdba.com> wrote in message news:afiga5$o8f$1_at_lust.ihug.co.nz...
> > 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.

I don't care if you believe me or not I'm not trying to convince you of what really happened I know what really happened because I did the reorg and measured the before and after performance difference. It was not an isolated case either.

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

And just how would you deal with the issues that Sean stated when he said:

"Because, maybe, they have 12 TB databases which would require 6000 datafiles to manage if they were only 2 GB apiece? You can start hitting OS barries with that many datafiles, like openfiles or, in our case, nflocks since we're on a NAS."

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

You seem to have missed an important point, I don't care whether my "opinion" is right or wrong, what I care about is the correct answer. It is not that I choose not to believe it, I saw first hand proof that it did make a difference with dictionary managed tablespaces.

> 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"??

It's not nonsense, Oracle is the one with the requirement that contiguous space must exist to create the extent.

If I'm using hardware RAID Oracle does not know that the data is not residing on the same disk or that it is not really contiguous, as far as it is concerned I have 1 large 500 gb hard drive.

Bottom line: We are never going to agree, and I am still stuck trying to find the answer to my original question. Gee, thanks alot for your help. Received on Fri Jun 28 2002 - 17:21:00 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US