Oracle Database - (Parallel|PX) Server

> Database > Oracle Database

1 - 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:

Advertising

3 - Data Dictionary

The view V$PX_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