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:gj5T8.447996$%y.31863810_at_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.

Well, put it this way then: I don't trust your capability to accurately measure and (most importantly) explain a performance improvement, largely because other posts in this thread indicate a complete ignorance of basic backup and recovery techniques.

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

I would deal with it by being flexible, and by planning properly. Were I planning a 12Tb database, I would ensure beforehand that it runs on an operating system/ file system that didn't suffer from a 2Gb filesize limitation, and move up to 4Gb files. Or 8Gb. Or anything else as the need arises.

Recall that Oracle permits around 65000 datafiles in a database. You match your database software with your hardware/file system.

Are *you* running a 12Tb database?

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

Any performance improvement you saw was not the result of the reduction in the number of extents.

I don't know how you did the re-organisation, obviously, but if you exported, truncated and then imported, I hope that when you measured the performance increase, you first bounced your instance. Otherwise, your buffer cache would have been stuffed full of the data being selected, and you'd have had a run of logical I/Os and not physical I/Os.

It's also possible that when you re-organised, you chose a new extent size that was an exact multiple of your db_file_multiblock_read_count parameter, where the old extents weren't. That would reduce the I/O too.

Did you control for these sorts of things in your testing? Or did you just do a select before and after and deduce that the improvement "must have" arisen from the reduction in the number of extents?

What you saw first hand is a performance improvement. The reasons for that improvement are probably many and varied, but the number of extents has nothing to do with it.

I have posted here not so very long ago a test I did on both dictionary and locally managed tablespace, creating segments comprising 700+ extents or 12 extents, contiguous and non-contiguous. A full scan of the table took the same amount of time regardless. A few hundred extents make no difference (several hundred for locally managed). If you think otherwise, fine. But thems just not the facts.

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

Which you can achieve by not fragmenting a tablespace. It doesn't demand that all extents are contiguous one with another, or be housed within the one datafile.

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

You can lead a horse to water, but if it turns out to be a stubborn mule, it will refuse to drink. You are barking up the wrong tree, with ideas which have no basis in fact, and with evident lack of knowledge on backup and recovery issues as well as performance tuning issues. If you're still stuck, I can only try and suggest that you go and actually learn something about Oracle internals and architecture.

HJR Received on Fri Jun 28 2002 - 17:48:19 CDT