| |
 |
|
Oracle Tips by Burleson Consulting
|
Parallel Query background
process (Pnnn)
The Oracle parallel query slave processes are used whenever Oracle
invokes a parallel full-table scan. Oracle partitions the target
table and then fires-off a UNIX process for each table partition.
When not in use, the parallel query processes disappear from UNIX.
Since most Oracle database that perform parallel query are doing
full-table scans against very large tables, the Oracle DBA can watch
the parallel query slaves appear in the UNIX environment. For
example, if you are doing a query with parallel degree five, you can
use the UNIX ps command to watch Oracle direct UNIX to create six
parallel query slaves, with names like ora_p000_prodsid through
ora_p005_prodsid.
The extra parallel query process is the parallel query coordinator,
and this UNIX process will remain alive until all of the factotum
process have completed their sub-table scans. Once all of the data
has been retrieved, you can sometime observe Oracle invoke a disk
sort in the TEMP tablespace. Whenever a parallel large-table
full-table scan contains an order by or a group by on a large result
set, the Oracle parallel query coordinator, will pass the unsorted
result set to Oracle, where the racle DBA can watch the creation of
temporary segments in the TEMP tablespace to sort the result set.
 |
For more details and scripts, see my new book "
Oracle
Tuning: The Definitive Reference", over 900 pages
of BC's favorite tuning tips & scripts.
You can buy it direct from the publisher for 30%-off and get
instant access to the code depot. |
|
