Controlling the number of active PE processes was an important
task in older Oracle database releases; too many PE processes would overload the
machine, leading to resource bottlenecks and performance degradation. A high degree
of parallelism will also force full-table scans and this may or may not be appropriate.
Figure 7-6 illustrates transparent parallelism within and between sets of PE processes.
Figure 7-6. Intra-operation and inter-operation parallelism
??? Coordinator allocates PE processes and divides task into subtasks
??? Each ???set??? of PE processes performs a different task (e.g., sorting, joining)
??? Results are ???pipelined??? from one set of PE processes to the next
Results
Coordinator
PE
process
PE
process
PE
process
PE
process
PE
process
PE
process
Coordinator
SQL
174 | Chapter 7: Oracle Performance
Determining the optimal degree of parallelism in the presence of multiple users and
varying workloads proved challenging. For example, a degree of 8 for a query would
provide excellent performance for 1 or 2 users, but what if 20 users queried the same
table? This scenario called for 160 PE processes (8 PEs for each of the 20 users),
which could overload the machine.
Pages:
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387