Re: Oracle only utilising single processor

Mladen Gogala <mgogala_at_adelphia.net> wrote in message news:<ac9sff$ntsaf$1_at_ID-82084.news.dfncis.de>...
> On Sun, 19 May 2002 10:10:56 -0400, Justin Richardson wrote:
>
> > We are evaluating various configurations to improve performance on an
> > Oracle Server. Using a Sun V880, with 8 processors, 32GB memory and
> > Oracle 8.1.7.
> >
> > Spread the tables over various spindles so there is little or no IO
> > queues.
> >
> > However performance is still poor as the benchmark/job we have to test is
> > only using a single processor. The other 7 do relatively little, with
> > one processor maxed out for the 2 hour test.
> >
> > Is there any Oracle tuning that can spread the load?
> >
> > Would Parallel Query Server help? If so how can I tell if this is
> > configured or installed? Any recommendations for configuring/tuning the
> > database for PQS?
> >
> > Many thanks
>
>
> PQO (Parallel Query Option) is installed together with enterprise edition
> of Oracle 8i (and 8.1.7 is 8i) which means that you most probably have it
> installed. To set it up and forget it, turn on the following two
> parameters:
> parallel_adaptive_multi_user
> parallel_automatic_tuning

Don't forget:

parallel_max_servers, make it > 1, otherwise you won't have parallel query. optimizer_percent_parallel. This one is tricky. You can set it on the session level, in this case 100 would be a good idea, for the session with parallel query execution.
Also:
hash_area_size. Documentation suggests you need at least 8M for parallel query. Give your parallel session as much as you can.

Couple more suggestions.

1. Parallel query is good for very big tables only. If table is less that 100M in size, forget about it. And you either have to hint the table in the query as parallel, or set it parallel when creating. And of course it's good for full table scans only.
2. Even if you table is big, it should be spread through several disks for efficient parallel query. It works best when you are using RAID with big cache.

>
> That, in itself, will not help you a bit. There are other small and
> unimportant thingies like thesize of the buffer cache, shared pool size,
> log buffer size (3M should be an initial rule of thumb), frequency of
> checkpoints ( parameters like log_checkpoint_interval and
> log_checkpoint_timeout), size of redo log files, number of DBW latches,
> enqueue and locks parameters and alike. If you set it all up properly, you
> may improve your performance for about 20%-30% Then you should get down to
> business, analyze your tables and tune your queries. to use hash joins,
> function based indexes, bitmap indexes (if not OLTP) and star
> transformation. That can give you up to the few thousand percent of
> improvement. If full table scans are still a problem, it is then when you
> asign degree of parallelism to a table and/or use things like /*+
> parallel(tbl,8) */ in your queries. When that is done, you get somebody to
> tune the SUN box (priority paging is a big thing, turning off all the
> unneeded things like NFS drivers, unwanted tty controlling processes, all
> the graphics - CDE and/or GNOME are humunous monsters which take huge
> amounts of memory and CPU power - console should be a VT100 terminal,
> nothing more) If you still have a performance problem after that, get
> yourself a bigger, meaner SUN box. They have a box that can scale up to
> 128 CPUs, several TB RAM and has FCAL built in. Attach an EMC Symmetrix
> with 64GB cache to it and you'll get a perfect box to work with EMP and
> DEPT tables.
Received on Tue May 21 2002 - 10:56:19 CDT