Parallel Execution - Expert Oracle Database Architecture - page 661

We simply enable parallel query against this table via the ALTER TABLE command:
EODA@ORA12CR1> alter table big_table parallel;
Table altered.
That is all there is to it—parallel query will now be considered for operations against this table. When we rerun
the explain plan, this time we see the following:
EODA@ORA12CR1> explain plan for select count(status) from big_table;
Explained.
EODA@ORA12CR1> select * from table(dbms_xplan.display(null, null,
2 'TYPICAL -ROWS -BYTES -COST'));
------------------------------------------------------------------------------------
| Id | Operation | Name | Time | TQ |IN-OUT| PQ Distrib |
------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 00:00:01 | | | |
| 1 | SORT AGGREGATE | | | | | |
| 2 | PX COORDINATOR | | | | | |
| 3 | PX SEND QC (RANDOM) | :TQ10000 | | Q1,00 | P->S | QC (RAND) |
| 4 | SORT AGGREGATE | | | Q1,00 | PCWP | |
| 5 | PX BLOCK ITERATOR | | 00:00:01 | Q1,00 | PCWC | |
| 6 | TABLE ACCESS FULL| BIG_TABLE | 00:00:01 | Q1,00 | PCWP | |
------------------------------------------------------------------------------------
Notice the aggregate time for the query running in parallel was 00:00:01 as opposed to the previous estimate of
00:00:03 for the serial plan. Remember, these are estimates , not promises!
If you read this plan from the bottom up, starting at ID=6 , it shows the steps described in Figure 14-1 . The full
table scan would be split up into many smaller scans (step 5). Each of those would aggregate their COUNT(STATUS)
values (step 4). These subresults would be transmitted to the parallel query coordinator (steps 2 and 3), which would
aggregate these results further (step 1) and output the answer.
DeFaULt paraLLeL eXeCUtION SerVerS
When an instance starts, oracle uses the value of the PARALLEL_MIN_SERVERS initialization parameter to
determine how many parallel execution servers to automatically start. these processes are used to service
parallel execution statements. in oracle 11 g , the default value of PARALLEL_MIN_SERVERS was 0; meaning,
by default, no parallel processes start at instance startup.
Starting with oracle 12 c , the minimum value of PARALLEL_MIN_SERVERS is calculated from
CPU_COUNT * PARALLEL_THREADS_PER_CPU * 2. in a linux/unix environment, you can view these processes
using the ps command:
$ ps -aef | grep '^oracle.*ora_p00._ORA12CR1'
oracle 18518 1 0 10:13 ? 00:00:00 ora_p000_ORA12CR1
oracle 18520 1 0 10:13 ? 00:00:00 ora_p001_ORA12CR1