Re: Oracle only utilising single processor

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

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 Sun May 19 2002 - 23:01:47 CDT