Oracle Database - (Parallel|PX) Server

Card Puncher Data Processing


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>


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

Discover More
Card Puncher Data Processing
Oracle Database - (Degree|Degree of Parallelism (DOP))

Oracle's parallel execution framework enables you to either explicitly chose - or even enforce - a specific degree of parallelism (DOP) or to rely on Oracle to control it. A degree of 1 is the default...
Card Puncher Data Processing
Oracle Database - CPU_COUNT parameter

CPU_COUNT specifies the number of CPUs core (processor) available for Oracle Database to use. CPU count is an automatically derived parameter by the Oracle system and is used to determine: the default...
Oracle Database Hash Redistribution
Oracle Database - Data Redistribution (Parallel)

Data redistribution is not unique to the Oracle Database. In fact, this is one of the most fundamental principles of parallel processing, being used by every product that provides parallel capabilities....
Oracle Database Block Based Granule
Oracle Database - Granule (Parallel Data Access)

A granule is the smallest unit of work when accessing data. Because Oracle database uses a shared everything architecture (unlike the shared nothing architecture), Oracle can – and will - choose this...
Card Puncher Data Processing
Oracle Database - How to (enable|disable) parallel query and get (degree of parallelism|DOP) ?

How to enable a parallel execution: for a for a operation Small tables/indexes (up to thousands of records; up to 10s of data blocks) should never be enabled for parallel execution. Operations...
Card Puncher Data Processing
Oracle Database - PARALLEL_MAX_SERVERS parameter

This parameter determines the maximum number of parallel servers that may be started for a database instance, should there be demand for them. The default value on Oracle Database 10g and higher is:...
Card Puncher Data Processing
Oracle Database - PARALLEL_MIN_PERCENT parameter

This initialization parameter is to guarantee a minimal DOP in a adaptive mode to a parallel query. This parameter controls the minimal percentage of parallel server processes that must be available...
Card Puncher Data Processing
Oracle Database - PARALLEL_MIN_SERVERS parameter

This parameter determines the number of parallel servers that will be started during database startup. By default the value is 0. It is recommended that you set parallel_min_servers to: This will...
Card Puncher Data Processing
Oracle Database - PARALLEL_SERVERS_TARGET parameter

PARALLEL_SERVERS_TARGET is a initialization parameter that specifies the number of parallel server processes allowed to run parallel statements before statement queuing will be used. Where: ...
Card Puncher Data Processing
Oracle Database - PARALLEL_THREADS_PER_CPU Parameter

This parameter describes the number of parallel execution processes or threads that a CPU can handle during parallel execution. It is used to: calculate the default degree of parallelism for the...

Share this page:
Follow us:
Task Runner