Re: Oracle Parallel Server... multi processor machine

Comments inline.

Paul Bennett wrote:

> So, if I had one SMP machine, I would just use OPQ to use the multiple
> processors. This would allow for certain queries to use more than one
> processor, as well as to allow more queries at once to be processed. [given
> that the queries and schema are written correctly]. There would be one SGA. Is
> this correct?
>
> All I need to do to turn on OPQ, as far as oracle is concerend, is a few
> paramaters in init.ora letting oracle know how many processors I have. Correct?

Not quite. You need to define a degree of parallelism as far as the tables are concerned, either at the table level or the statement level via hints. You would hopefully have already analyzed the tables so we have the best data to work on to determine a good execution path.

> Now, If I have even more queries that I need satisfied at the same time, I can
> use OPS and add another node. [this node could use OPQ as the first one]. I
> would either access the datafiles over the network or use a multi-port disk
> controller. Correct?

No. OPS runs two instances against one shared database, so the database needs to be on shared disks rather than accessed across the network. In every OS other than VAX, the datafiles need to be located on raw devices, since the OS's can't shared cooked partitions.

> The next option is just to have several nodes, each with there own copy of all
> the data. Is this an OPS option as well? Will OPS handle updating across all
> the instances, while using the local instance for reads? Is this just a
> replication issue?

You're confusing OPS with distributed databases here. As I mentioned above, OPS is a configuration with multiple instances (SGA's and background processes) and ONE database (datafiles, redo log files, control files). If each machine had its own database you're in a distributed database configuration. This wouldn't normally be done on a cluster but on separate machines, thus meaning single instance / database configurations usually. In this case you could use replication to synchronize the data between the different databases.

HTH. Pete

> Thanks for answering these questions, as well as the other 10 you have already
> answered for me. :)
>
> -- Paul
>
> Sybrand Bakker wrote:
>
> > Hi Paul,
> > You are confusing Parallel Query (OPQ) and Parallel Server.(OPS)
> > OPQ is for SMP machines, so one server with multiple CPUs
> > OPS is about multiple servers having common disks, and using the same
> > database.
> > This is done mainly for distribution of workload/failover/high availablity.
> > So you would need to use OPQ, which is included in the Enterprise Edition.
> > There won't be multiple instances
> > instead you need to configure your init.ora and/or your tables and/or your
> > queries to use the multiple processors.
> > If you don't do anything, the workload will be distributed using O/S
> > mechanisms, if available.
> >
> > Hth,
> >
> > --
> > Sybrand Bakker, Oracle DBA
> > Paul Bennett <bennett_at_cc.gatech.edu> wrote in message
> > news:3859322E.7E4BBFD0_at_cc.gatech.edu...
> > > Not counting writing the application for using a parralel server
> > > effectivly, if I wanted to run oracle on a multi processor machine I
> > > would need to use oracle parallel server, correct? If so, if I had 8
> > > processors, I would run 8 instances? If so, would each instance have an
> > > SGA? If so, isn't that a waste of memory to potential have the same
> > > table data cached in serveral SGA's on the same machine? Is there a way
> > > to share that buffer pool?
> > >
> > > It makes sence to have an SGA for each instance if the instances are
> > > running on completely different machines, of course.
> > >
> > > Thanks
> > >
> > > -- Paul
> > >

Received on Thu Dec 16 1999 - 15:54:30 CST