Database Reference
In-Depth Information
| 4 | PX RECEIVE | | Q1,01 | PCWP | |
| 5 | PX SEND RANGE | :TQ10000 | Q1,00 | P->P | RANGE |
| 6 | PX BLOCK ITERATOR | | Q1,00 | PCWC | |
| 7 | TABLE ACCESS FULL| T | Q1,00 | PCWP | |
-------------------------------------------------------------------------
The preceding execution plan is composed of a single data flow operation (whose ID is 1). Operations 5 to 7
have the same value for the column TQ ( Q1,00 ), which means they're executed by one set of slave processes (set 1 in
Figure 15-7 ). On the other hand, operations 2 to 4 have another value ( Q1,01 ), and so are executed by another set of
slave processes (set 2 in Figure 15-7 ). Set 1, the producer, scans the table based on block range granules (operation 6)
and sends the retrieved data to set 2. In turn, set 2, the consumer, receives the data, sorts it, and sends the sorted result
set to the query coordinator. Set 1 and set 2 do their processing concurrently. Because the two sets are communicating
with each other, the set that processes data faster waits for the other one.
Basic Configuration
This section describes the basic initialization parameters that you have to know to successfully set up a database
instance for parallel processing. The initialization parameters involve the slave process pool and memory usage.
Slave Processes Pool
The maximum number of slave processes per database instance is limited and maintained by a database instance
as a pool of slave processes. A query coordinator requests slave processes from the pool, uses them to execute one
SQL statement, and finally, when the execution is complete, returns them to the pool. The following initialization
parameters are set to configure the pool:
parallel_min_servers specifies the number of slave processes that are started at database
instance startup. These slave processes are always available and don't need to be started when
a server process requires them. The slave processes exceeding this minimum are dynamically
started when required and, once returned to the pool, stay idle for five minutes. If they aren't
reused in that period, they're shut down. By default, this initialization parameter is set to 0.
This means that no slave processes are created at startup. I advise changing this value only if
some SQL statements are waiting too long for the startup of the slave processes. The wait event
related to this operation is os thread startup .
parallel_max_servers specifies the maximum number of slave processes available in the
pool. It's difficult to give advice on how to set this parameter. Nevertheless, a value of 10-20
times the number of CPU cores is a good starting point. The default value depends on several
other initialization parameters, the version, and the platform. The maximum value that
parallel_max_servers can be set to is 15 less than the value of the processes initialization
parameter. If you try to set parallel_max_servers to a higher value, then at database instance
startup, the value of parallel_max_servers is automatically adjusted, and a message is
written into the alert log.
To display the status of the pool, you can use the following query:
SQL> SELECT *
2 FROM v$px_process_sysstat
3 WHERE statistic LIKE 'Servers%';
 
Search WWH ::




Custom Search