Performance issue with Parallel Degree Policy parameter in Oracle
PARALLEL parameters introduced in Oracle 11g. Default value for PARALLEL_DEGREE_POLICY is set to be MANUAL.
If you change its value to AUTO. It will pick the parallel with some factors.
Check the parameter Parallel Degree Policy
SQL> show parameter parallel_degree_policy
NAME TYPE VALUE -------------------------- ----------- ------ parallel_degree_policy string AUTO
DOP is depend upon the operation in the SQL Query and size of tables, it use parallelism for larger tables instead of small tables.
Oracle parallel slaves may use buffered IO rather than direct IO. Oracle calls this “in-memory parallel execution”.
Performance issue caused:
In 11gR2 if PARALLEL_DEGREE_POLICY is AUTO, then the default behavior is to block the SQL from executing until enough parallel slaves become available.
Sometime it will hang the used application for the user. Because statements is waiting for run in parallel queue.
If you find these foreground wait event in AWR Report as shown in image:
enq: JX - SQL statement queue
PX Queuing: statement queue
PX Queueing: statement queue It indicates that there aren’t sufficient parallel processes available to start execution servers, and the statement is queued until such time that parallel execution servers are available.
This event also indicates that the statement is next in the queue to be selected for processing.
enq: JX – SQL statement queue Contrary to the PX queueing: statement queue event, this event indicates that the statement is in the queue; however, it has not been selected to be released yet. More statements in this queue indicate insufficient DOP or high allocation of DOP to other statements such that all statements do not get sufficient DOPs to start processing.
Suppose four SQL statements is executing on larger tables of the database. Parameter is set with AUTO field.
SQL 1 is executing in parallel.
SQL 2 is waiting for SQL 1 to complete. SQL 2 is holding “enq: JX – SQL statement queue” wait: it holds the JX enqueue indicating that it is next in line to get the parallel servers.
SQL 3 and SQL 4 wait for the JX enqueue held by SQL 2 and placed in Queue “enq: JX – SQL statement queue”.
Change the PARALLEL_DEGREE_POLICY to LIMITED, MANUAL
— Best solution is to change it manually and use hints for query which you want to run in parallel
--Another option is also good, Change to LIMITED. (its disable statement queuing and in-memory parallel)
ALTER SYSTEM SET PARALLEL_DEGREE_POLICY = LIMITED SCOPE=BOTH;
-- Disable the PARALLEL use.
ALTER SYSTEM SET PARALLEL_DEGREE_POLICY = MANUAL SCOPE=BOTH;
MANUAL: Disables automatic degree of parallelism, statement queuing, and in-memory parallel execution.
LIMITED: Enables automatic degree of parallelism for some statements but statement queuing and in-memory Parallel Execution are disabled
AUTO: Enables automatic degree of parallelism, statement queuing, and in-memory parallel execution.
Queries for fetch other details regarding parallel operations
Find Queued Query for parallel operation.
SELECT s.sql_id, s.sql_text FROM v$SQL_MONITOR m, v$SQL s
WHERE m.status='QUEUED' AND m.sql_id = s.sql_id;
Find session running in Parallel.
WITH px_session AS
MAX (degree) degree,
MAX (req_degree) req_degree,
COUNT ( * ) nb_of_processes
FROM gv$px_session p
GROUP BY qcsid,
SELECT s.INST_ID, s.sid,s.username,degree,
FROM gv$session s JOIN px_session p
ON (s.sid = p.qcsid AND s.serial# = p.qcserial#)
JOIN gv$sql SQL ON (sql.sql_id = s.sql_id
AND sql.child_number = s.sql_child_number);