Oracle Database Parallel Processsing Decoded



Last updated on August 25th, 2016 at 04:46 am

What is Parallel Processsing
This allows a single SQL statement to be executed by a multiple processes executing concurrently on different CPUs.It increases throughput by utilizing more resources (Memory, CPU & I/O).It uses a pool of instance background processes (query slaves) .Based on Parallel Execution you are able to use more system resources like CPU or I/O at the same time for a specific operation. As a consequence the total runtime can significantly decrease.How Parallel Processsing happens

Step 1 -Query is submitted and parsed, parallel plan is considered
Step 2 -If parallel plan is chosen, shadow process becomes query coordinator (QC).
Step 3 -The QC divides the workload among producer slaves using ROWID ranges.
Step 4 -Producer slaves read the data directly from disk and place it on table queues ready from consumer slaves.
Step 5 -Consumer slaves only used when sort operation is required, otherwise data is passed directly to the QC for output.
The communication and data transfer between the processes is based on queues. The communication structures are part of the shared pool:
PX msg pool:Communication area for the parallel query processes
Size depends mainly on PARALLEL_EXECUTION_MESSAGE_SIZE, PARALLEL_MAX_SERVERS and the actually used parallel query slaves
PX subheap:Memory structure with additional parallel query related information Usually small compared to “PX msg pool”
Blocks that are read via Parallel Execution are always read directly from disk bypassing the Oracle buffer pool

How to enable Parallel Processsing

1)First the appropiate  init.ora parameters  should be set
PARALLEL_EXECUTION_MESSAGE_SIZE:This parameter determines the size of the communication buffer between the differen Parallel Execution processes.
In order to avoid a bottleneck it’s recommended to set this parameter to 16384.

PARALLEL_INSTANCE_GROUP:This parameter can be used in RAC environments in order to restrict Parallel Executions to a sub set of the existing RAC instances.

PARALLEL_MAX_SERVERS:With this parameter the maximum number of simultaneously active slave processes can be specified. If more slaves are requested at a certain time the requests are downgraded and a smaller parallelism is used.

PARALLEL_MIN_SERVERS:This parameter specifies how many slave processes are created during database startup. If more slaves are needed, they are created on demand.It’s recommended to keep this parameter on the default of 0 in order to avoid unnecessarily started slave processes.

2) Table/Index DEGREE storage parameter can be modified to enable them to user parallel query
3) Parallel hint /*+ PARALLEL(table_alias,4) */ used in query, where 4 is the degree of parallelism, best to leave degree blank and let the optimizer choose.
4) parallel keyword for DDL statements

Operation that can be parallized
Table/Index Scan
Nested loop Join and Sort Merge Join
Union All
Group by and order by
Distinct
Union,Intersect and Minus
Aggregation
CTAS (Create Table as Select)
Hash Join
Create Index

Query to find out the parallel execution in the system

SELECT
PS.SID,
DECODE(SERVER_SET, NULL, ‘COORDINATOR’, 1, ‘  CONSUMER’,
‘    PRODUCER’) ROLE,
DECODE(SW.WAIT_TIME, 0, SW.EVENT, ‘CPU’) ACTION,
SQ.SQL_TEXT
FROM
V$PX_SESSION PS,
V$SESSION_WAIT SW,
V$SQL SQ,
V$SESSION S,
AUDIT_ACTIONS AA
WHERE
PS.SID = SW.SID AND
S.SID = PS.SID AND
S.SQL_ADDRESS = SQ.ADDRESS (+) AND
AA.ACTION = S.COMMAND
ORDER BY PS.QCSID, NVL(PS.SERVER#, 0), PS.SERVER_SET;


Leave a Reply