Database performance issue with Parallel Degree Policy parameter in Oracle

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

Foreground Wait Events

Means:
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.

Example:
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”.

Solution
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;

Meaning:
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
(SELECT qcsid,
qcserial#,
MAX (degree) degree,
MAX (req_degree) req_degree,
COUNT ( * ) nb_of_processes
FROM gv$px_session p
GROUP BY qcsid,
qcserial#
)
SELECT s.INST_ID, s.sid,s.username,degree,
req_degree,nb_of_processes, s.sql_id,
SUBSTR(sql_text,1,100)
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);

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.