Oracle parallel query to scan full table

[vpar]

HP-UX 11.23 and Oracle 10G and superdom with 16 cpus.

When oracle parallel query for full table scanning start, parallel query threads should be run on all processors(16cpus in this case).
Is it right?

But my customer said parallel query thread are running on only 7cpus. The other cpus are idle like below top output


This is top ouput and as you see, Parallel query isusing only 1-7 cpus.

CPU TTY PID USERNAME PRI NI SIZE RES STATE TIME %WCPU %CPU COMMAND
7 ? 3361 oracle 149 20 7683M 31048K sleep 10:46 40.98 40.91 ora_p029_NPSDB
2 ? 3345 oracle 149 20 7670M 30720K sleep 10:43 38.49 38.43 ora_p021_NPSDB
3 ? 3353 oracle 149 20 7683M 30416K sleep 10:43 37.92 37.86 ora_p025_NPSDB
2 ? 3328 oracle 239 20 7683M 30380K run 10:40 37.79 37.72 ora_p013_NPSDB
5 ? 3320 oracle 149 20 7681M 30436K sleep 10:37 37.69 37.62 ora_p009_NPSDB
5 ? 3311 oracle 240 20 7683M 30836K run 10:48 36.47 36.41 ora_p005_NPSDB
2 ? 3303 oracle 154 20 7683M 30452K sleep 10:39 36.28 36.22 ora_p001_NPSDB
5 ? 3336 oracle 148 20 7670M 27812K sleep 10:35 35.39 35.33 ora_p017_NPSDB
6 ? 3375 oracle 148 20 7651M 3968K sleep 64:05 34.32 34.26 ora_p036_NPSDB
6 ? 3383 oracle 148 20 7652M 4160K sleep 5:24 33.08 33.02 ora_p040_NPSDB
4 ? 3412 oracle 148 20 7651M 4060K sleep 79:41 29.94 29.88 ora_p054_NPSDB
2 ? 3389 oracle 148 20 7668M 3832K sleep 34:29 23.19 23.15 ora_p043_NPSDB
4 ? 19385 oracle 154 20 7652M 6596K sleep 31:58 10.22 10.20 ora_p072_NPSDB
7 ? 3394 oracle 154 20 7731M 87064K sleep 48:22 8.39 8.37 ora_p045_NPSDB
3 ? 3426 oracle 154 20 7731M 87236K sleep 28:43 7.38 7.37 ora_p061_NPSDB
1 ? 3410 oracle 154 20 7731M 87412K sleep 60:57 7.20 7.19 ora_p053_NPSDB
System: npsdbtri Fri Aug 13 22:07:19 2010
Load averages: 0.34, 0.22, 0.21
962 processes: 925 sleeping, 36 running, 1 zombie
Cpu states:
CPU LOAD USER NICE SYS IDLE BLOCK SWAIT INTR SSYS
0 0.55 27.9% 0.0% 4.4% 67.7% 0.0% 0.0% 0.0% 0.0%
1 0.52 9.0% 3.2% 10.6% 77.3% 0.0% 0.0% 0.0% 0.0%
2 0.59 15.7% 0.0% 4.2% 80.1% 0.0% 0.0% 0.0% 0.0%
3 0.64 15.5% 0.0% 4.6% 79.9% 0.0% 0.0% 0.0% 0.0%
4 0.66 29.9% 0.0% 4.0% 66.1% 0.0% 0.0% 0.0% 0.0%
5 0.69 34.7% 0.0% 2.8% 62.5% 0.0% 0.0% 0.0% 0.0%
6 0.67 21.4% 0.0% 1.4% 77.2% 0.0% 0.0% 0.0% 0.0%
7 0.76 46.3% 0.0% 1.2% 52.5% 0.0% 0.0% 0.0% 0.0%
8 0.03 1.0% 0.2% 4.2% 94.6% 0.0% 0.0% 0.0% 0.0%
9 0.03 0.0% 0.4% 1.8% 97.8% 0.0% 0.0% 0.0% 0.0%
10 0.12 0.0% 0.4% 1.8% 97.8% 0.0% 0.0% 0.0% 0.0%
11 0.03 0.0% 0.8% 1.6% 97.6% 0.0% 0.0% 0.0% 0.0%
12 0.04 0.6% 0.4% 2.6% 96.4% 0.0% 0.0% 0.0% 0.0%
13 0.02 0.0% 1.4% 1.4% 97.2% 0.0% 0.0% 0.0% 0.0%
14 0.04 0.4% 0.6% 1.8% 97.2% 0.0% 0.0% 0.0% 0.0%
15 0.04 0.0% 0.0% 1.2% 98.8% 0.0% 0.0% 0.0% 0.0%
--- ---- ----- ----- ----- ----- ----- ----- ----- -----
avg 0.34 12.6% 0.4% 3.0% 84.0% 0.0% 0.0% 0.0% 0.0%

[ManojK_1]

Hi,

For all the oracle jobs the scenarion is same as explained or for only specifoic process.


Have you enabled processor set in you box.

Please paste the output of the command.

psrset

Manoj K

[Dennis Handly]

Have you asked Oracle why this happens?

[Volker Borowski]

Hi,

what are Oracle parameters

cpu_count
parallel_threads_per_cpu

set to ?

Volker

[vpar]

Hi, Thank you for your reply

I have solved it to change oracle configuration file.

workaround:

> _enable_NUMA_optimization=FALSE
>

Next step is processing by oracle.Why?