TROUBLESHOOT PARALLEL SERVERS IN ORACLE
To have a basic understanding of parallel, use the below link which has set of queries to diagnose.
https://alphaoragroup.com/2023/03/21/monitoring-parallel-queries/
Below parameter’s should be calculated and set accordingly. If below parameters are not aligned to the below calculations, then parallel should be either over utilized or under utilized.
PARALLEL_MAX_SERVERS = PARALLEL_THREADS_PER_CPU * CPU_COUNT * concurrent_parallel_users * 5 (If PGA_AGGREGATE_TARGET =0 , then 1 else 2)
PARALLEL_SERVERS_TARGET = 0.75 * PARALLEL_MAX_SERVERS
Below are the parameters which can help to perform a basic check for parallel settings. parallel_force_local is a parameter in RAC, to force the parallel to specific instance at local level rather than global degree.
parallel_min_servers and parallel_max_servers determine the soft and hard limit of the parallel server usage. If parallel_max_servers value is too high, then more parallel will be used unless, it is limited by parallel_degree_limit
parallel_execution_message_size also called as PEMS determines the message queue size of parallel chunks and the amount of information that parallel slaves can carry to retrieve the data similar to network packets. Larger PEMS value require larger SGA.
Note: If there are more CPU resource and value for parallel_max_servers is too low, then parallel may not be used if all parallel slaves are used.
Example: If there are 70 cpu cores with hyperthreading and value for parallel_max_servers is 20, and also all parallel slaves are engaged with other activity, then parallel may not be used for any other sessions.
If the DEGREE is 16 for session and parallel_max_servers is 20, then there are four slaves which are sitting idle for other new sessions. If the DEGREE is 20, then none of the other queries are parallelized but statement queueing may happen depending on parallel_degree_policy
SQL> set lines 200 pages 1000
col NAME for a20
SELECT pss.sid,pss.serial#,pss.qcsid,pss.qcserial#,pss.degree,pss.req_degree,sn.name,sum(pss.value) Value
FROM v$px_sesstat pss
INNER JOIN v$statname sn on ( pss.statistic# = sn.statistic# )
WHERE pss.value <> 0
AND name like '%physical reads%'
GROUP BY pss.sid,pss.serial#,pss.qcsid,pss.qcserial#,pss.degree,pss.req_degree,sn.name
ORDER BY sn.name;
SQL> SQL> 2 3 4 5 6 7
SID SERIAL# QCSID QCSERIAL# DEGREE REQ_DEGREE NAME VALUE
---------- ---------- ---------- ---------- ---------- ---------- -------------------- ----------
1044 1105 522 2501 16 16 physical reads 10
522 2501 522 physical reads 325
28 14695 522 2501 16 16 physical reads 10Parallel parameters.
kIsH@xHydra<>show parameter parallel_force_local
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
parallel_force_local boolean FALSE
kIsH@xHydra<>show parameter parallel_m
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
parallel_max_servers integer 20
parallel_min_degree string 1
parallel_min_percent integer 0
parallel_min_servers integer 2
parallel_min_time_threshold string AUTO
kIsH@xHydra<>show parameter parallel_execution
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
parallel_execution_message_size integer 32768
Here, we troubleshoot, if the parallel is used for a query or not. Also, why parallel is not used for a query.
Make sure that the below settings are altered before any session level query or job.
kIsH@xHydra<>alter session force parallel ddl;
Session altered.
kIsH@xHydra<>alter session force parallel query;
Session altered.
kIsH@xHydra<>alter table XTBL parallel 8;
Table altered.
kIsH@xHydra<>alter session force parallel dml;
Session altered.
Scrutinize the execution plan for the parallel operations. If there is no QC or PX words in the plan, then query execute in serial mode. There is also a note at the bottom of the plan which denotes parallel usage.
kIsH@Xhydra<>set autot traceonly explain
kIsH@Xhydra<>select /*+ parallel(4) */ count(*) from kish.xtbl;
Execution Plan
----------------------------------------------------------
Plan hash value: 1571207904
--------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time | TQ |IN-OUT| PQ Distrib |
--------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 303 (1)| 00:00:01 | | | |
| 1 | SORT AGGREGATE | | 1 | | | | | |
| 2 | PX COORDINATOR | | | | | | | |
| 3 | PX SEND QC (RANDOM) | :TQ10000 | 1 | | | Q1,00 | P->S | QC (RAND) |
| 4 | SORT AGGREGATE | | 1 | | | Q1,00 | PCWP | |
| 5 | PX BLOCK ITERATOR | | 2000K| 303 (1)| 00:00:01 | Q1,00 | PCWC | |
| 6 | INDEX FAST FULL SCAN| SYS_C007705 | 2000K| 303 (1)| 00:00:01 | Q1,00 | PCWP | |
--------------------------------------------------------------------------------------------------------------
Note
-----
- Degree of Parallelism is 4 because of hint
If the real cause behind parallel usage need to be determined, then enable px trace for the session
kIsH@Xhydra<>alter session set "_px_trace"=HIGH,ALL;
Session altered.
kIsH@Xhydra<>select /*+ parallel(4) */ count(*) from kish.xtbl;
COUNT(*)
----------
2213000
kIsH@Xhydra<>alter session set "_px_trace"=none;
Session altered.
Check the execution plan and dop parameter.
Search with PX_Messaging:kxfp in the trace which has a message “Got It. n so far ..”. This means, parallel is used. kxfpcre means kernel execution parallel query process and queue manager which
Generate PXHC report to identify the parallel behavior and observations.
SQL> @pxhcdr.sql
Parameter 1:
Oracle Pack License (Tuning, Diagnostics or None) [T|D|N] (required)
Enter value for 1: T
Parameter 2:
Target directory path for script output (optional)
Enter value for 2:
/home/oracle