Parallel hint in Oracle Database – ways to use and how to monitor

The parallel hint in Oracle Database is used to specify that a particular query or operation should be executed in parallel mode, using multiple parallel execution servers (PX servers) to process the query. Parallel execution can improve query performance by dividing the work of processing the query among multiple processors or CPUs. But, you have to keep in mind that you MUST have enough CPU power available on the server, or else you can cause performance issues to the system.

Here are a few examples of how the parallel hint can be used in Oracle Database:

With SELECT statement:

SELECT /*+ parallel(4) */ ...

With INSERT statement:

INSERT /*+ parallel(8) */ INTO ...

With CREATE INDEX statement:

CREATE /*+ parallel(2) */ INDEX ...

With UPDATE statement:

UPDATE /*+ parallel(6) */ ...

With DELETE statement:

DELETE /*+ parallel(8) */ ...

With MERGE statement:

MERGE /*+ parallel(4) */ INTO ...

With ALTER TABLE statement:

ALTER /*+ parallel(8) */ TABLE ...

With ANALYZE statement:

ANALYZE /*+ parallel(4) */ ...

As mentioned earlier also that parallel execution can consume a significant amount of system resources, including CPU and memory. Therefore, it is important to monitor the system’s resources when using parallel execution and to adjust the degree of parallelism as necessary.

There are several ways to track the status of a query that is run with the parallel hint in the Oracle Database:

To track the status of all parallel queries currently running on the system:

SELECT sql_id, status, rows_processed, time_remaining,plan_hash_value, sql_text,degree, instances,executions, rows_processed, elapsed_time
FROM V$SQL
WHERE parallel = 'YES'
ORDER BY time_remaining;

To view the performance statistics of parallel queries over a period of time, use the following query on dba_hist_sqlstat table:

SELECT sql_id, executions, rows_processed, elapsed_time, begin_interval_time,plan_hash_value, operation, options
FROM dba_hist_sqlstat
WHERE parallel = 'YES'
AND begin_interval_time BETWEEN 'start_time' AND 'end_time'
ORDER BY begin_interval_time;

To view the performance statistics of parallel queries over a period of time, use the following query on gv$sql_monitor table:

SELECT sql_id, executions, rows_processed, elapsed_time
FROM gv$sql_monitor
WHERE parallel = 'YES'
AND begin_interval_time BETWEEN 'start_time' AND 'end_time'
ORDER BY begin_interval_time;

To check the parallel execution status of the query:

SELECT * FROM v$session_longops WHERE opname = 'parallel query';

To check the parallel execution statistics of the query:

SELECT * FROM v$px_session;

To check the number of parallel slaves used by the query:

SELECT * FROM v$px_process;

To check the parallel execution details of the query:

SELECT * FROM v$px_session_info;

To check the parallel execution statistics for a specific parallel query operation:

SELECT * FROM v$px_operation_stats;

To check the parallel execution statistics for a specific parallel query coordinator process:

SELECT * FROM v$px_coordinator_stats;

To check the parallel execution statistics for a specific parallel query execution plan:

SELECT * FROM v$px_execution_plan;

Below is a more sophisticated script that combines several of the queries I provided earlier to monitor the performance of a query that is using the parallel hint:

WITH parallel_info AS (
SELECT 
sid, serial#,
opname,
target,
sofar,
totalwork,
time_remaining,
round(sofar/totalwork*100,2) as "Percent Complete"
FROM v$session_longops
WHERE opname = 'parallel query'
), parallel_stats AS (
SELECT 
sid,
px_servers_executions,
px_servers_status,
round(elapsed_time/1000000) as elapsed_time_secs
FROM v$px_session
), parallel_slaves AS (
SELECT 
sid,
px_process_pid,
px_process_status
FROM v$px_process
)
SELECT 
parallel_info.*,
parallel_stats.*,
parallel_slaves.*
FROM parallel_info
JOIN parallel_stats
ON parallel_info.sid = parallel_stats.sid
JOIN parallel_slaves
ON parallel_stats.sid = parallel_slaves.sid
ORDER BY parallel_info.sid;

This script uses a combination of several views such as v$session_longops, v$px_session and v$px_process to provide a detailed overview of the parallel query performance. You can customize this script to suit your requirements.

Below will be the sample output of the above query.

And below are the column definition that many DBAs are already aware of;

SID, SERIAL#: These columns indicate the session and serial number of the parallel query.
OPNAME: The operation being performed by the parallel query, which is “parallel query” in this case.
TARGET: The estimated number of rows that will be processed by the parallel query.
SOFAR: The number of rows that have been processed so far.
TOTALWORK: The total number of rows that the parallel query will process.
TIME_REMAINING: The estimated time remaining until the parallel query completes.
PERCENT_COMPLETE: The percentage of the total work that has been completed so far.
PX_SERVERS_EXECUTIONS: The number of parallel servers executing the query.
PX_SERVERS_STATUS: The status of the parallel servers executing the query.
ELAPSED_TIME_SECS: The elapsed time of the parallel query in seconds
PX_PROCESS_PID: The process id of the parallel server.
PX_PROCESS_STATUS: The status of the parallel server (ACTIVE, IDLE, etc.).

Parallel Process Details including Master/Slave relationships

set lines 200
set pages 100
col username format a10
col qcslave format a10
col slaveset format a8
col program format a30
col sid format a5
col slvinst format a7
col state format a8
col waitevent format a30
col qcsid format a5
col qcinst format a6
col reqdop format 999
col actdop format 999
col secelapsed format 999,999
SELECT DECODE(px.qcinst_id,NULL,username, ' - '||LOWER(SUBSTR(pp.SERVER_NAME,LENGTH(pp.SERVER_NAME)-4,4) ) ) USERNAME, 
DECODE(px.qcinst_id,NULL, 'QC', '(Slave)') "QCSLAVE" ,
TO_CHAR( px.server_set) SLAVESET, 
s.program PROGRAM, 
TO_CHAR(s.SID) SID,
TO_CHAR(px.inst_id) SLVINST, 
DECODE(sw.state,'WAITING', 'WAIT', 'NOT WAIT' ) STATE,
CASE sw.state WHEN 'WAITING' THEN SUBSTR(sw.event,1,30) ELSE NULL END WAITEVENT ,
DECODE(px.qcinst_id, NULL ,TO_CHAR(s.SID) ,px.qcsid) QCSID,
TO_CHAR(px.qcinst_id) QCINST, 
px.req_degree REQDOP, 
px.DEGREE ACTDOP,
DECODE(px.server_set,'',s.last_call_et,'') SECELAPSED
FROM gv$px_session px, 
gv$session s, 
gv$px_process pp, 
gv$session_wait sw
WHERE px.SID=s.SID (+)
AND px.serial#=s.serial#(+)
AND px.inst_id = s.inst_id(+)
AND px.SID = pp.SID (+)
AND px.serial#=pp.serial#(+)
AND sw.SID = s.SID
AND sw.inst_id = s.inst_id
ORDER BY DECODE(px.QCINST_ID, NULL, px.INST_ID, px.QCINST_ID), 
px.QCSID,
DECODE(px.SERVER_GROUP, NULL, 0, px.SERVER_GROUP), 
px.SERVER_SET, 
px.INST_ID

Author
Recent Posts

Brijesh Gogia

I’m an experienced Cloud/Oracle Applications/DBA Architect with more than 15 years of full-time DBA/Architect experience. I have gained wide knowledge on Oracle and Non-Oracle software stack running on-prem and on Cloud and have worked on several big projects for multi-national companies. I enjoy working with leading-edge technology and have a passion for Cloud architecture, automation, database performance, and stability. Thankfully my work allows me time for researching new technologies (and to write about them).

Parallel hint in Oracle Database – ways to use and how to monitor

Related posts:

One Comment