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
- Building a Scalable and Secure AI Infrastructure in the Cloud - May 12, 2024
- Harnessing the Power of AI in the Cloud for Business Transformation - March 2, 2024
- Building a Resilient Cybersecurity Framework in the Oracle Cloud - January 16, 2024
Hi,
Thanks for providing this information, but Only 1 select statement is running fine.
Parallel Process Details including Master/Slave relationships
Does Parallel hint support SELECT with JOIN of multiple tables?
Thanks!