About
Execution Thread in Oracle.
Parallel server are the individual sessions that perform work in parallel in a parallel SQL execution.
PX server processes can be easily identified on the OS level, for example on Linux they are the oracle processes ORA_P***
The Parallel servers communicate among themselves and with the Query Coordinator by passing messages via memory buffers.
In order for a parallel operation to execute in an optimal fashion there has to be enough parallel servers available. If there are no parallel servers available the operation will actually be executed serially.
<MATH>32 \text{(parallels server processes)} = 4 \text{(sessions)} * 4 \text{(DOP)} * 2 \text{(slave sets)}</MATH>
where:
- The number 2 in the formula comes from the fact that parallel execution are on a producer/consumer model.
Articles Related
Data Dictionary
The view VPX_PROCESS contains information about the sessions running parallel execution.
| Column | Datatype | Description |
|---|---|---|
| SERVER_NAME | VARCHAR2(4) | The name of the PX server (P000, P001, and so on) |
| STATUS | VARCHAR2(9) | The state of the PX server (IN USE or AVAILABLE) |
| PID | NUMBER | The process identifier |
| SPID | VARCHAR2(12) | OS process ID |
| SID | NUMBER | The session ID of the PX server, if in use |
| SERIAL# | NUMBER | The session serial number of the PX server, if in use |