Basic question regarding parallelism and cpu usage

From: <oracle-l-bounce_at_freelists.org>
Date: Wed, 31 Jan 2007 16:11:29 -0500
Message-ID: <D6424CD4C8A3C044BBC49877ED51C51804E02777@ex2003.metratech.com>

Hi,

We have a 8 cpu machine and I can see that oracle is not using more than 1 cpu at a time and there are always >2 processes in runnable state (vmstat) and mpstat is showing that 1 cpu is mostly busy and most of that cpu time is spent is system mode and oracle waits are reporting that most of the time is spent of cpu waits. Do we need to set any parameter to enable Oracle to use all the cpu's on the machine?
Also is parallel query is enabled by default in 10g or we have set parameter at instance level, We want to avoid setting it at table level?

Environment: Oracle 10g on linux RHAT4

Here is the output of few parameters:

NAME                                 TYPE


------------------------------------ --------------------------------

VALUE

cpu_count                            integer
8
parallel_threads_per_cpu             integer
2
parallel_adaptive_multi_user         boolean
TRUE
parallel_automatic_tuning            boolean
FALSE
parallel_execution_message_size      integer
2148
parallel_instance_group              string
parallel_max_servers                 integer

160

Thanks
--Harvinder

--

http://www.freelists.org/webpage/oracle-l Received on Wed Jan 31 2007 - 15:11:29 CST