Re: 30 instances on one host

On Sun, 23 Jun 2002 19:55:37 +0100, Niall Litchfield <niall.litchfield_at_dial.pipex.com> wrote:
>"Joel Garry" <joel-garry_at_home.com> wrote in message
>news:91884734.0206222315.17c129a3_at_posting.google.com...
>> > Well, as we've seen on this newsgroup, this doesn't stand up, and Ault
>> > doesn't/didn't know what he was talking about. If he'd said tables and
>> > rollback segments, fair enough.
>>
>> How about reading the first sentence of this Abstract:
>> http://www.orapub.com/cgi/genesis.cgi?p1=sub&p2=abs106
>>
>> Now, what part of "faster" do you not understand?
>
>I have two comments to make on this.
>
>1. A paper written in 1995 for oracle 6 & 7 may well suffer when applied in
>a 2002 environment in oracle 9i.

That appears to be Howards misapprehension. I'll answer it in my answer to him.

>
>2. Where does the paper refer to seperating tables and indexes for
>performance gain anyway. Once I'd registered I only found reference to
>
>Tablespace contents must be separated to a) minimize free space
>fragmentation, b) minimise IO request contention and c) maximise
>administrative flexibility.

The paper doesn't. Ault's book recommended it. My own testing at the time confirmed it for the particular app that needed it. Ault was not wrong, and there is no discrepancy between what he said and the paper. They are reconciled by the fact that in applications of the day on the hardware of the day, if you had an app that randomly added records with the data and indexes for that table in the same tablespace, you would get a bunch of blocks for the index interspersed with the blocks of the data. This would cause both fragmentation and I/O request contention. You could _hear_ the difference before and after imp/exp, not to mention measure it. This led to a rule of thumb to separate out data and index TS's, because the app would be likely to be contending for both as you generated reports or did online lookups.

Frankly, with some of the advice given nowadays, I've seen the same thing happen with modern hardware and recent Oracle. It's just easier to see with TS Mapping in OEM.

The real mistake I see people making is extrapolating from the fact that there doesn't have to be I/O interference to saying that there won't be. Just as Codd proved that performance may be maximized with R db's, doesn't mean denormalization isn't still worthwhile for a given app.

>
>none of these requirements (except possibly c) but there I remain
>unconvinced) can be held to show that you should separate data and index
>segments.

Nowadays between SGA buffering, n-tier cacheing and smart I/O devices, it is not so critical. If the performance is such that it is so critical, there are probably other problems anyways. "Do not use this software to control nuclear power plants."

>
>These are the principles the 'rule' given is "For each database create the
>following special tablespaces in addition to those needed for application
>segments
>
>SYSTEM - data dictionary only
>TEMP - temporary segments only
>RBS - rollback.
>TOOLS - general purpose tools only
>USERS - miscellaneous user segments. "
>
>Note no separation of data and indexes. just separating by type (of access
>primarily). Indeed if there was this huge benefit why do we have a system
>tablespace and not a system_data tablespace and a system_index tablespace.

If oracle used those tablespaces as some apps do, it might be a good idea. But again, such a generalization would be difficult to support, because both scale and access differ. Perhaps a better question would be, if the benefit is so small why separate out a system TS? We don't have the excuse of limited TS size anymore.

I state it more plainly: Some of the stuff in the original paper is obsolete, particularly the disk layouts. It is neither optimal nor particularly flexible. People who claim OFA is not about performance need to explain why the white paper says it is.

jg

-- 
These opinions are my own. 
http://www.garry.to                                       Oracle and unix guy.
mailto:joel-garry_at_nospam.cox.net                       Remove nospam to reply.