Oracle parallel query to scan full table

vpar — Wed, 25 Aug 2010 09:51:08 GMT

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%

Re: Oracle parallel query to scan full table

ManojK_1 — Thu, 26 Aug 2010 05:48:02 GMT

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

Re: Oracle parallel query to scan full table

Dennis Handly — Thu, 26 Aug 2010 06:22:11 GMT

Have you asked Oracle why this happens?

Re: Oracle parallel query to scan full table

Volker Borowski — Fri, 27 Aug 2010 07:54:40 GMT

Hi,

what are Oracle parameters

cpu_count
parallel_threads_per_cpu

set to ?

Volker

Re: Oracle parallel query to scan full table

vpar — Fri, 27 Aug 2010 08:05:02 GMT

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?

topic Re: Oracle parallel query to scan full table in Operating System - HP-UX

Oracle parallel query to scan full table

Re: Oracle parallel query to scan full table

Re: Oracle parallel query to scan full table

Re: Oracle parallel query to scan full table

Re: Oracle parallel query to scan full table