Re: Parralelizing Oracle processes

I don't know what Oracle would say, but I know what your poor notebook would say, which would be AIIIIIGHH!!, as Courage the Cowardly Dog would say (or scream). [Cartoon Channel on most USA cable networks, highly recommended viewing for all DBA's; Oracle should sponsor it.]

I think I tried that thought experiment once on an SMP server (obviously not production), and Oracle seems to definitely almost always (sorry, I am not Clinton reincarnated) know how many CPU's you have. (Can you imagine a US Congressional debate on the meaning of what 'IN' or 'WHERE' actually mean?)

But more importantly, humor aside, unless you have CPU and storage diversity, PQ will not get you a big gain, and might end you up with a performance loss, maybe a little, sometimes horrible.

I try to keep parallelism at half my actual CPU count, and definitely the ceiling being the number of diverse spindle data locations. I try to judiciously use it and direct it in particular to full table processing, whether it be full table scans or massive loadings.

I only cautiously activate parallelism (that cannot be a real word) or increment it after experiential data shows (why don't DBA's take the Hippocratic Oath, especially the part that says "Do No Harm"?) that the changes made haven't worsened anything, at the very least.

Tuning all that muck is probably at least two courses at Oracle Education.

And, then, of course, there is the matter of object cache decisions.

In Virginia, I wrote something I roughly recollect as 'shove_it_all', to first force a full load of the SGA, then preload and pin all the 10,000 things that Oracle Applications uses, then all the things the whining_plsql_customizing_experts wanted.

I believe, after that, we still had enough unbound memory left to actually function and not force the UNIX kernel to swap or page out.

I'm slightly kidding, by some miracle, it all worked. But we did have big machines.

RSH. "Daniel Morgan" <damorgan_at_exesolutions.com> wrote in message news:3CC4950B.1CBB2B88_at_exesolutions.com...
> Interestingly the text in the sample init.ora Oracle provides says:
"Typically
> two parallel process per CPU provides good performance. However for
systems with
> a smaller number of CPUs, more parallel processes may be desired.
>
> Well I have one CP on my notebook so theoretically I should conclude that
I can
> set parallel_threads_per_cpu = 4.
>
> I wonder what the Oracle folks would say about that. <g>
>
> Daniel Morgan
>
>
>
> RSH wrote:
>
> > Frederic,
> >
> > you have to tell Oracle what operations you want "parallellized" <-
cringing
> > at that word.
> >
> > There are many places and different ways to let Oracle know this; none
of
> > this is of much use to you if you are not working on a multiprocessor
> > system, and of dubious benefit to you if you do not have enough disk
space
> > (in spindles, or drives) to spread certain things out, and enough
available
> > memory to accomodate a larger Oracle SGA and to help buffer these
> > operations.
> >
> > Thank goodness it defaults to none instead of presuming what you might
want!
> >
> > For example:
> >
> > If you have large SQL*LOADER loads from flat files into Oracle tables to
do,
> > you might set up to break the input file into several different pieces
on
> > different drives, create a SQL*Loader script that includes DIRECT,
PARALLEL,
> > etc; all that stuff is in the Oracle Utilities Reference Manual. (It's
in
> > English, sort of.)
> >
> > Or, if you have say a massive flat table that needs to be processed
either
> > for derivative processing (like in a data warehouse) or is frequently
> > involved in full table scans, you might wish to (say it's a 4 GB table
and
> > overhead and change) create a tablespace to house that table that is
> > composed of 4 1 Gb datafiles, each on a different drive, and precreate
and
> > allocate that space before you SQL*LOAD the table, and of course, never
DROP
> > it, but if appropriate, only TRUNCATE it if you wish you empty it out
> > completely.
> >
> > In this case, supposing you have an 8 processor SMP (symmetrical
> > multiprocessor) system, CREATEing, or ALTERing that table to have a
DEGREE
> > of 4, supposing you have enough CPU and memory, could greatly help.
> >
> > RSH.
> > "Frederic Payant" <fpayant_at_club-internet.fr> wrote in message
> > news:bur8cus0c9gvhtu61q9gauk4khs0o6v1qm_at_4ax.com...
> > > Hi,
> > >
> > > I've a little problem with Oracle 817 on Solaris : Is it parallel. I
> > > mean : all documentation, about orders, packages, init files seems to
> > > meet the idea that, by default, Oracle always parallelize operations,
> > > big scans, statistics and so on.
> > > But when I launch large jobs or stats, I very well see that these jobs
> > > are all done by one single processor (I currently work on a 8 procs
> > > domain).
> > > Did I miss something in doc; Must I set any parameter ? Is it becaus I
> > > use a 32bits version of Oracle ?
> > >
> > > Any info will be welcome (and don't hesitate to ask me any further
> > > information if needed)
> > >
> > > Thanks a lot
> > >
> > > Regards
> > > Frederic PAYANT - junior DBA ;-)
>
Received on Mon Apr 22 2002 - 18:30:32 CDT