Configure parallel execution

Go back to OCM 12c Preparation Project 

Hands On Lab – Index

[1] Introduction to Parallel Execution
[2] How Parallel Execution Works
[3] Managing Parallel Degree
[4] Monitoring Parallel Execution Performance
[5] Configure Parallel Execution.
[6] Tune Parallel Execution

——————————————————————————————————————–
HOL : Configure and use parallel execution for queries.

[1] Enable the trace on the session level and query a table using 4 parallel processes.
[2] Query a table with parallel_index hint with 6 parallel processes.
[3] Enable parallelism during creation of an object.
[4] Enable the parallel hint with other DML Command.
[5] Create table under USR1 user with default 6 parallelism degree. Then query the table and check the explain plan. Then use a hint to disable the parallel query on this table.
[6] Create an empty table based on DBA_OBJECTS view. Then query DBA_OBJECTS view with parallel degree 4 and insert the result to the table with parallel degree 6.
——————————————————————————————————————–

[1] Introduction to Parallel Execution

-> Parallel execution is the ability to apply multiple CPU and I/O 
resources to the execution of a single database operation.

-> It dramatically reduces response time for data-intensive operations on 
large databases typically associated with a decision support system (DSS) 
and data warehouses. 

-> You can also implement parallel execution on an online transaction 
processing (OLTP) system for batch processing or schema maintenance 
operations such as index creation.

-> Parallel execution is sometimes called parallelism. Parallelism is the 
idea of breaking down a task so that, instead of one process doing all of 
the work in a query, many processes do part of the work at the same time.

-> Parallel execution improves processing for:

[1] Queries requiring large table scans, joins, or partitioned index scans
[2] Creation of large indexes
[3] Creation of large tables (including materialized views)
[4] Bulk insertions, updates, merges, and deletions


[2] How Parallel Execution Works & Managing Parallel Degree


-> Dividing Work Among Parallel Execution Servers

EXPLAIN PLAN FOR
SELECT /*+ PARALLEL(4) */ customers.cust_first_name, customers.cust_last_name, 
MAX(QUANTITY_SOLD), AVG(QUANTITY_SOLD) FROM sales, customers
WHERE sales.cust_id=customers.cust_id
GROUP BY customers.cust_first_name, customers.cust_last_name;

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------
Plan hash value: 4060011603
--------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | TQ |IN-OUT| PQ Distrib |
--------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 925 | 25900 | | | |
| 1 | PX COORDINATOR | | | | | | |
| 2 | PX SEND QC (RANDOM) | :TQ10003 | 925 | 25900 | Q1,03 | P->S | QC (RAND) |
| 3 | HASH GROUP BY | | 925 | 25900 | Q1,03 | PCWP | |
| 4 | PX RECEIVE | | 925 | 25900 | Q1,03 | PCWP | |
| 5 | PX SEND HASH | :TQ10002 | 925 | 25900 | Q1,02 | P->P | HASH |
|* 6 | HASH JOIN BUFFERED | | 925 | 25900 | Q1,02 | PCWP | |
| 7 | PX RECEIVE | | 630 | 12600 | Q1,02 | PCWP | |
| 8 | PX SEND HASH | :TQ10000 | 630 | 12600 | Q1,00 | P->P | HASH |
| 9 | PX BLOCK ITERATOR | | 630 | 12600 | Q1,00 | PCWC | |
| 10 | TABLE ACCESS FULL| CUSTOMERS | 630 | 12600 | Q1,00 | PCWP | |
| 11 | PX RECEIVE | | 960 | 7680 | Q1,02 | PCWP | |
| 12 | PX SEND HASH | :TQ10001 | 960 | 7680 | Q1,01 | P->P | HASH |
| 13 | PX BLOCK ITERATOR | | 960 | 7680 | Q1,01 | PCWC | |
| 14 | TABLE ACCESS FULL| SALES | 960 | 7680 | Q1,01 | PCWP | |
--------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

6 - access("SALES"."CUST_ID"="CUSTOMERS"."CUST_ID")

26 rows selected.

[3] Managing Parallel Degree

#Degree of Parallelism

-> Manually Specifying the Degree of Parallelism

For example, you can set a fixed DOP at a table level with the following:
ALTER TABLE sales PARALLEL 8;
ALTER TABLE customers PARALLEL 4;

In this example, queries accessing just the sales table use a requested 
DOP of 8 and queries accessing the customers table request a DOP of 4.
A query accessing both the sales and the customers tables is processed 
with a DOP of 8 and potentially allocates 16 parallel servers 
(producer or consumer); whenever different DOPs are specified, 
Oracle Database uses the higher DOP.

#Default Parallelism

For a single instance, DOP = PARALLEL_THREADS_PER_CPU x CPU_COUNT

For an Oracle RAC configuration, 
DOP = PARALLEL_THREADS_PER_CPU x CPU_COUNT x INSTANCE_COUNT

For example, on a 4-node Oracle RAC cluster, with each node having 8 CPU 
cores and no Oracle RAC services, the default DOP would be 2 x 8 x 4 = 64.

The default DOP algorithm is designed to use maximum resources and assumes 
that the operation finishes faster if it can use more resources. 
Default parallelism targets the single-user workload.<-------

In a multiuser environment, default parallelism is not recommended.

The DOP for a SQL statement can also be limited by Oracle Database 
Resource Manager.

#Automatic Degree of Parallelism

When the parameter PARALLEL_DEGREE_POLICY is set to AUTO, Oracle Database 
automatically decides if a statement should execute in parallel or not 
and what degree of parallelism (DOP) it should use.

The following is a summary of parallel statement processing when parallel 
degree policy is set to automatic.

[1] A SQL statement is issued.
[2] The statement is parsed and the optimizer determines the execution plan.
[3] The threshold limit specified by the PARALLEL_MIN_TIME_THRESHOLD 
initialization parameter is checked.
If the expected execution time is less than the threshold limit, the 
SQL statement is run serially.
If the expected execution time is greater than the threshold limit, 
the statement is run in parallel based on the DOP that the optimizer calculates, 
including factoring for any defined resource limitations.

Note: If all tables referenced by a SQL statement use In-Memory Column Store 
(IM column store), then PARALLEL_MIN_TIME_THRESHOLD defaults to 1.

-> Controlling Automatic DOP, Parallel Statement Queuing, and 
   In-Memory Parallel Execution

You can either use ORACLE's Automatic Degree of Parallelism (DOP) 
or
You control it manually.

When using Auto DOP, Oracle will decide the degree of parallelism based 
on the one resource requirement of the statement.

[1] As DOP might be very high because of requirement of the running statement, 
it is limited based on the following algorithm.
DOP = PARALLEL_THREADS_PER_CPU * SUM(CPU_COUNT across all cluster nodes)

[2] You can also use PARALLEL_DEGREE_LIMIT parameter to set a limit to 
the DOP. The default value is CPU which is calculated using the following 
formula.

PARALLEL_THREADS_PER_CPU * CPU_COUNT * (Number of availble instances)

[3] If the estimated time is less than value of PARALLEL_MIN_TIME_THRESHOLD 
parameter, then query will run serially.

PARALLEL_MIN_TIME_THRESHOLD default value is 10 seconds
-> Specifies the execution time, as estimated by the optimizer, above which 
a statement is considered for automatic parallel query and automatic derivation 
of DOP.

[4] This feature is disabled by default. To enable Auto DOP, change 
PARALLEL_DEGREE_POLICY parameter to AUTO.

[5] You can also use hint /*+ PARALLEL(AUTO) */ to enable Auto DOP for specific query.

SQL> set autotrace traceonly exp

SQL> select /*+ PARALLEL(AUTO) */ COUNT(1) FROM dba_extents;

...............
...............
Note
-----
- automatic DOP: Computed Degree of Parallelism is 2

Automatic DOP is set to 2. To calculate and see how it happened, 
get the value of cpu_count
and parallel_threads_per_cpu parameters.

SQL> show parameter cpu
SQL> show parameter cpu

NAME TYPE VALUE
------------------------------------------------------------
cpu_count integer 1
parallel_threads_per_cpu integer 2
resource_manager_cpu_allocation integer 1

This value (2*1*1) = 2 is derived from the following formula

PARALLEL_THREADS_PER_CPU * CPU_COUNT * (Number of available instances)


Types of Parallelism

This section discusses the types of parallelism in the following topics:

About Parallel Queries
About Parallel DDL Statements
ALTER SESSION FORCE PARALLEL DDL

About Parallel DML Operations
About Parallel Execution of Functions
About Other Types of Parallelism


[4] Monitoring Parallel Execution Performance
Reference
Database VLDB and Partitioning Guide --> Monitoring Parallel Execution Performance


Check the list of views that are used to monitor parallel execution from 
the following documentation:

There are a lot of different views that are used to monitor parallel execution.
The first and most useful view is V$PQ_SESSTAT view which is used to display
session statistics of parallel executions.

#In the following example we will disable adaptive parallelism to prevent 
it to reduce parallelism degree.


SQL> show parameter parallel

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
fast_start_parallel_rollback string LOW
parallel_adaptive_multi_user boolean TRUE
parallel_automatic_tuning boolean FALSE
parallel_degree_level integer 100
parallel_degree_limit string CPU
parallel_degree_policy string MANUAL
parallel_execution_message_size integer 16384
parallel_force_local boolean FALSE
parallel_instance_group string
parallel_io_cap_enabled boolean FALSE
parallel_max_servers integer 80
parallel_min_percent integer 0
parallel_min_servers integer 8
parallel_min_time_threshold string AUTO
parallel_server boolean FALSE
parallel_server_instances integer 1
parallel_servers_target integer 32
parallel_threads_per_cpu integer 2
recovery_parallelism integer 0
SQL>

SQL> alter system set parallel_adaptive_multi_user=false;

System altered.

SQL> create table tbl_dop_test2 
     tablespace user2 parallel 5 as select * from dba_tables;

Table created.

SQL> select count(*) from tbl_dop_test2;

COUNT(*)
----------
2338

SQL> set pagesize 1000
SQL> set linesize 100
SQL> select * from v$pq_sesstat;

STATISTIC LAST_QUERY SESSION_TOTAL CON_ID
------------------------------ ---------- ------------- ----------
Queries Parallelized 1 1 0
DML Parallelized 0 0 0
DDL Parallelized 0 1 0
DFO Trees 1 2 0
Server Threads 5 0 0
Allocation Height 5 0 0 <<<----------------
Allocation Width 1 0 0
Local Msgs Sent 203 1792 0
Distr Msgs Sent 0 0 0
Local Msgs Recv'd 203 1792 0
Distr Msgs Recv'd 0 0 0
DOP 5 0 0
Slave Sets 1 0 0

13 rows selected.


#The following example illustrates forcing the statement to be executed serially:

SQL> select /*+ NO_PARALLEL */ COUNT(1) FROM TBL_DOP_TEST2;

COUNT(1)
----------
2338

SQL> select * from v$pq_sesstat;

STATISTIC LAST_QUERY SESSION_TOTAL CON_ID
------------------------------ ---------- ------------- ----------
Queries Parallelized 0 2 0
DML Parallelized 0 0 0
DDL Parallelized 0 1 0
DFO Trees 0 3 0
Server Threads 0 0 0
Allocation Height 0 0 0
Allocation Width 0 0 0
Local Msgs Sent 0 2010 0
Distr Msgs Sent 0 0 0
Local Msgs Recv'd 0 2010 0
Distr Msgs Recv'd 0 0 0
DOP 0 0 0
Slave Sets 0 0 0

13 rows selected.

#The following example illustrates forcing the statement to be executed in parallel:

SQL> select /*+ PARALLEL */ COUNT(1) FROM TBL_DOP_TEST2;

COUNT(1)
----------
2338

SQL>
SQL> select * from v$pq_sesstat;

STATISTIC LAST_QUERY SESSION_TOTAL CON_ID
------------------------------ ---------- ------------- ----------
Queries Parallelized 1 3 0
DML Parallelized 0 0 0
DDL Parallelized 0 1 0
DFO Trees 1 4 0
Server Threads 2 0 0
Allocation Height 2 0 0 <<<
Allocation Width 1 0 0
Local Msgs Sent 54 2064 0
Distr Msgs Sent 0 0 0
Local Msgs Recv'd 54 2064 0
Distr Msgs Recv'd 0 0 0
DOP 2 0 0
Slave Sets 1 0 0

13 rows selected.

#The following example illustrates computing the DOP the statement should use:

SQL> select /*+ PARALLEL(AUTO) */ COUNT(1) FROM TBL_DOP_TEST2;

COUNT(1)
----------
2338

SQL> select * from v$pq_sesstat;

STATISTIC LAST_QUERY SESSION_TOTAL CON_ID
------------------------------ ---------- ------------- ----------
Queries Parallelized 0 3 0
DML Parallelized 0 0 0
DDL Parallelized 0 1 0
DFO Trees 0 4 0
Server Threads 0 0 0
Allocation Height 0 0 0
Allocation Width 0 0 0
Local Msgs Sent 0 2064 0
Distr Msgs Sent 0 0 0
Local Msgs Recv'd 0 2064 0
Distr Msgs Recv'd 0 0 0
DOP 0 0 0
Slave Sets 0 0 0

13 rows selected.

#The following example forces the statement to use Oracle 11g R1 behavior

SQL> select /*+ PARALLEL(MANUAL) */ COUNT(1) FROM TBL_DOP_TEST2;

COUNT(1)
----------
2338

SQL> select * from v$pq_sesstat;

STATISTIC LAST_QUERY SESSION_TOTAL CON_ID
------------------------------ ---------- ------------- ----------
Queries Parallelized 1 4 0
DML Parallelized 0 0 0
DDL Parallelized 0 1 0
DFO Trees 1 5 0
Server Threads 5 0 0
Allocation Height 5 0 0
Allocation Width 1 0 0
Local Msgs Sent 203 2267 0
Distr Msgs Sent 0 0 0
Local Msgs Recv'd 203 2267 0
Distr Msgs Recv'd 0 0 0
DOP 5 0 0
Slave Sets 1 0 0

13 rows selected.

SQL>

#Following example illustrates forcing the statement to be executed in 
parallel with a degree of 10:

SQL> select /*+ PARALLEL(10) */ COUNT(1) FROM TBL_DOP_TEST2;

COUNT(1)
----------
2338

SQL> select * from v$pq_sesstat;

STATISTIC LAST_QUERY SESSION_TOTAL CON_ID
------------------------------ ---------- ------------- ----------
Queries Parallelized 1 5 0
DML Parallelized 0 0 0
DDL Parallelized 0 1 0
DFO Trees 1 6 0
Server Threads 10 0 0
Allocation Height 10 0 0
Allocation Width 1 0 0
Local Msgs Sent 218 2485 0
Distr Msgs Sent 0 0 0
Local Msgs Recv'd 218 2485 0
Distr Msgs Recv'd 0 0 0
DOP 10 0 0
Slave Sets 1 0 0

13 rows selected.

If you list processes of OS, You will find 10 running parallel servers.

SQL> !ps -ef | grep p0
oracle 5570 1 0 10:48 ? 00:00:00 ora_psp0_cdb1
oracle 5636 1 0 10:48 ? 00:00:00 ora_p000_cdb1
oracle 5638 1 0 10:48 ? 00:00:00 ora_p001_cdb1
oracle 5663 1 0 10:48 ? 00:00:00 ora_p002_cdb1
oracle 5665 1 0 10:48 ? 00:00:00 ora_p003_cdb1
oracle 5667 1 0 10:48 ? 00:00:00 ora_p004_cdb1
oracle 5669 1 0 10:48 ? 00:00:00 ora_p005_cdb1
oracle 5671 1 0 10:48 ? 00:00:00 ora_p006_cdb1
oracle 5673 1 0 10:48 ? 00:00:00 ora_p007_cdb1
oracle 6134 1 0 10:57 ? 00:00:00 ora_p008_cdb1
oracle 6136 1 0 10:57 ? 00:00:00 ora_p009_cdb1
oracle 6209 3241 0 10:58 pts/0 00:00:00 /bin/bash -c ps -ef | grep p0
oracle 6212 6209 0 10:58 pts/0 00:00:00 grep p0

SQL>

Oracle Database real-time monitoring feature enables you to monitor the 
performance of SQL statements while they are executing. SQL monitoring 
is automatically started when a SQL statement runs parallel or when it 
has consumed at least 5 seconds of CPU or I/O time for a single execution.

After your system has run for a few days, you should monitor parallel 
execution performance statistics to determine whether your parallel 
processing is optimal. Do this using any of the views discussed in this section.

V$PX_BUFFER_ADVICE : Provides statistics about PX buffers. Useful to resize the SGA.

V$PX_SESSION : Information sessions, DEGREE....
shows data about query server sessions, groups, sets, and server numbers. 
It also displays real-time data about the processes working 
on behalf of parallel execution. This table includes information about 
the requested degree of parallelism (DOP) and the actual DOP granted to 
the operation.

Use GV$PX_SESSION to determine the configuration of the server group 
executing in parallel. In this example, session 9 is the query coordinator, 
while sessions 7 and 21 are in the first group, first set. 
Sessions 18 and 20 are in the first group, second set. The requested and 
granted DOP for this query is 2, as shown by the output from the following query:

SELECT QCSID, SID, INST_ID "Inst", SERVER_GROUP "Group", SERVER_SET "Set",
DEGREE "Degree", REQ_DEGREE "Req Degree"
FROM GV$PX_SESSION ORDER BY QCSID, QCINST_ID, SERVER_GROUP, SERVER_SET;

QCSID SID Inst Group Set Degree Req Degree 
---------- ---------- ---------- ---------- ---------- ---------- ---------- 
9 9 1 
9 7 1 1 1 2 2 
9 21 1 1 1 2 2 
9 18 1 1 2 2 2 
9 20 1 1 2 2 2

The next example shows the execution of a join query to determine the 
progress of these processes in terms of physical reads. 
Use this query to track any specific statistic:

SELECT QCSID, SID, INST_ID "Inst", SERVER_GROUP "Group", SERVER_SET "Set",
NAME "Stat Name", VALUE
FROM GV$PX_SESSTAT A, V$STATNAME B
WHERE A.STATISTIC# = B.STATISTIC# 
AND NAME LIKE 'PHYSICAL READS'
AND VALUE > 0 ORDER BY QCSID, QCINST_ID, SERVER_GROUP, SERVER_SET;

QCSID SID Inst Group Set Stat Name VALUE 
------ ----- ------ ------ ------ ------------------ ---------- 
9 9 1 physical reads 3863 
9 7 1 1 1 physical reads 2 
9 21 1 1 1 physical reads 2 
9 18 1 1 2 physical reads 2 
9 20 1 1 2 physical reads 2

V$PX_SESSTAT : Cross Information between V$PX_SESSION $ V$SESSTAT
provides a join of the session information from V$PX_SESSION and the 
V$SESSTAT table. Thus, all session statistics available to a standard 
session are available for all sessions performed using parallel execution.

V$PX_PROCESS : Information about the PX processes
view contains information about the parallel processes, including status, 
session ID, process ID, and other information.

SELECT * FROM V$PX_PROCESS;

V$PX_PROCESS_SYSSTAT : Information about the PX processes with statistics 
about buffers shows the status of query servers and provides buffer allocation 
statistics.

V$PQ_SESSTAT : Statistics about query execution with PX. Useful to adjust 
certain parameters. view shows the status of all current server groups in 
the system such as data about how queries allocate processes and how the 
multiuser and load balancing algorithms are affecting the default and 
hinted values.

You might need to adjust some parameter settings to improve performance 
after reviewing data from these views. Query these views periodically to
monitor the progress of long-running parallel operations.

V$PQ_TQSTAT : Traffic information level message queue each PX process

V$RSRC_CONS_GROUP_HISTORY
view displays a history of consumer group statistics for each entry in 
V$RSRC_PLAN_HISTORY that has a non-NULL plan,including information about 
parallel statement queuing.

V$RSRC_CONSUMER_GROUP
view displays data related to currently active resource consumer groups, 
including information about parallel statements.

V$RSRC_PLAN
view displays the names of all currently active resource plans, including 
the state of parallel statement queuing. 

V$RSRC_PLAN_HISTORY
displays a history of when a resource plan was enabled, disabled, or 
modified on the instance. The history includes the state of parallel statement 
queuing

V$RSRC_SESSION_INFO
view displays resource manager statistics per session, including parallel 
statement queue statistics. Columns include PQ_SERVERS and PQ_STATUS.

V$RSRCMGRMETRIC
Statistics related to parallel statement queuing are added to the resource 
manager metrics that takes statistics for a given one-minute window 
and retains them for approximately one hour. Columns include 
AVG_ACTIVE_PARALLEL_STMTS, AVG_QUEUED_PARALLEL_STMTS, AVG_ACTIVE_PARALLEL_SERVERS, 
AVG_QUEUED_PARALLEL_SERVERS, and PARALLEL_SERVERS_LIMIT.

Monitoring System Statistics

The V$SYSSTAT and V$SESSTAT views contain several statistics for monitoring 
parallel execution. Use these statistics to track the number of parallel 
queries, DMLs, DDLs, data flow operators (DFOs), and operations.

SELECT NAME, VALUE FROM GV$SYSSTAT
WHERE UPPER (NAME) LIKE '%PARALLEL OPERATIONS%'
OR UPPER (NAME) LIKE '%PARALLELIZED%' OR UPPER (NAME) LIKE '%PX%';

Your output should resemble the following:

NAME VALUE 
-------------------------------------------------- ---------- 
queries parallelized 347 
DML statements parallelized 0 
DDL statements parallelized 0 
DFO trees parallelized 463 
Parallel operations not downgraded 28 
Parallel operations downgraded to serial 31 
Parallel operations downgraded 75 to 99 pct 252 
Parallel operations downgraded 50 to 75 pct 128 
Parallel operations downgraded 25 to 50 pct 43 
Parallel operations downgraded 1 to 25 pct 12 
PX local messages sent 74548 
PX local messages recv'd 74128 
PX remote messages sent 0 
PX remote messages recv'd 0

The following query shows the current wait state of each slave 
(child process) and query coordinator process on the system:

SELECT px.SID "SID", p.PID, p.SPID "SPID", px.INST_ID "Inst",
px.SERVER_GROUP "Group", px.SERVER_SET "Set",
px.DEGREE "Degree", px.REQ_DEGREE "Req Degree", w.event "Wait Event"
FROM GV$SESSION s, GV$PX_SESSION px, GV$PROCESS p, GV$SESSION_WAIT w
WHERE s.sid (+) = px.sid AND s.inst_id (+) = px.inst_id AND
s.sid = w.sid (+) AND s.inst_id = w.inst_id (+) AND
s.paddr = p.addr (+) AND s.inst_id = p.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;


Miscellaneous Parallel Execution Tuning Tips

Example 8-8 Parallelizing INSERT SELECT
INSERT /*+ PARALLEL(employees) */ INTO employees
SELECT /*+ PARALLEL(ACME_EMP) */ * FROM ACME_EMP;

Example 8-9 Parallelizing UPDATE and DELETE

UPDATE /*+ PARALLEL(employees) */ employees
SET salary=salary * 1.1 WHERE job_id='CLERK' AND department_id IN
(SELECT department_id FROM DEPARTMENTS WHERE location_id = 'DALLAS');

Example 8-10 Parallelizing UPDATE and DELETE

DELETE /*+ PARALLEL(PRODUCTS) */ FROM PRODUCTS 
WHERE category_id = 39;

Automatic Big Table Caching

Automatic big table caching integrates queries with the buffer cache to 
enhance the in-memory query capabilities of Oracle Database, 
in both single instance and Oracle RAC environments.

In Oracle Real Application Clusters (Oracle RAC) environments, this feature 
is supported only with parallel queries. 
In single instance environments, this feature is supported with both 
parallel and serial queries.

The cache section reserved for the big table cache is used for caching 
data for table scans. While the big table cache is primarily designed to 
enhance performance for data warehouse workloads, 
it also improves performance in Oracle Database running mixed workloads.

Automatic big table caching uses temperature and object based algorithms 
to track medium and big tables. 
Oracle does cache very small tables, but automatic big table caching does 
not track these tables.

To enable automatic big table caching for serial queries, you must set a 
value (percentage) for the DB_BIG_TABLE_CACHE_PERCENT_TARGET initialization 
parameter.

The DB_BIG_TABLE_CACHE_PERCENT_TARGET parameter determines the percentage 
of the buffer cache size used for scans. 

If DB_BIG_TABLE_CACHE_PERCENT_TARGET is set to 80 (%), then 80 (%) of the 
buffer cache is used for scans and the remaining 20 (%) is used for OLTP 
workloads.

The DB_BIG_TABLE_CACHE_PERCENT_TARGET parameter is only enabled in an 
Oracle RAC environment if PARALLEL_DEGREE_POLICY is set to AUTO or ADAPTIVE. 
The default for DB_BIG_TABLE_CACHE_PERCENT_TARGET is 0 (disabled) and 
the upper limit is 90 (%) reserving at least 10% buffer cache for usage 
besides table scans. When the value is 0, in-memory queries run with the 
existing least recently used (LRU) mechanism. 
You can adjust the DB_BIG_TABLE_CACHE_PERCENT_TARGET parameter dynamically.

[5] Configure Parallel Execution.

Default Parameter Settings

Parameter : PARALLEL_ADAPTIVE_ MULTI_USER
Default : TRUE
Comments : Causes parallel execution SQL to throttle degree of parallelism 
           (DOP) requests to prevent system overload.

Parameter : PARALLEL_DEGREE_LIMIT
Default : CPU_COUNT X PARALLEL_THREADS_PER_CPU X number of instances available
Comments : Controls the maximum DOP a statement can have when automatic DOP is in use.

Parameter : PARALLEL_DEGREE_POLICY
Default : MANUAL
Comments : Controls whether auto DOP, parallel statement queuing and 
in-memory parallel execution are used. By default, all of these features 
are disabled.


When PARALLEL_DEGREE_POLICY is set to AUTO, Oracle Database determines 
whether the statement should run in parallel based on the cost of the 
operations in the execution plan and the hardware characteristics. 
The hardware characteristics include I/O calibration statistics so these 
statistics should be gathered.

If I/O calibration is not run to gather the required statistics, a default 
calibration value is used to calculate the cost of operations and the degree 
of parallelism. I/O calibration statistics can be gathered with the PL/SQL 
DBMS_RESOURCE_MANAGER.CALIBRATE_IO procedure. I/O calibration is a one-time 
action if the physical hardware does not change.

Parameter : PARALLEL_MIN_TIME_THRESHOLD
Default : 10 seconds
Comments : Specifies the execution time, as estimated by the optimizer, 
above which a statement is considered for automatic parallel query and 
automatic derivation of DOP. 

============================
PARALLEL_DEGREE_POLICY=Auto
============================
If We enable Auto DOP, the parallelism degree will be calculated automatically.
However during an exam, you might be asked to have the DOP automatically 
calculated, but fail because of PARALLEL_MIN_TIME_THRESHOLD parameter.

As we have mentioned above, this parameter is used by Auto DOP feature 
and statement runs serially if the estimated query time is less than the 
value of this parameter.

So in the following example, We will enable Auto DOP feature and run the 
previous query again.


SQL> alter system set parallel_degree_policy=AUTO;

System altered.

SQL> select count(*) from tbl_dop_test2;

COUNT(*)
----------
2338

SQL> select * from v$pq_sesstat;

STATISTIC LAST_QUERY SESSION_TOTAL CON_ID
------------------------------ ---------- ------------- ----------
Queries Parallelized 0 5 0
DML Parallelized 0 0 0
DDL Parallelized 0 1 0
DFO Trees 0 6 0
Server Threads 0 0 0
Allocation Height 0 0 0
Allocation Width 0 0 0
Local Msgs Sent 0 2485 0
Distr Msgs Sent 0 0 0
Local Msgs Recv'd 0 2485 0
Distr Msgs Recv'd 0 0 0
DOP 0 0 0
Slave Sets 0 0 0

13 rows selected.

SQL> show parameter parallel_min

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
parallel_min_percent integer 0
parallel_min_servers integer 8
parallel_min_time_threshold string AUTO

SQL> alter system set parallel_min_time_threshold=1;

System altered.

SQL> select count(*) from tbl_dop_test2;

COUNT(*)
----------
2338

SQL> select * from v$pq_sesstat;

STATISTIC LAST_QUERY SESSION_TOTAL CON_ID
------------------------------ ---------- ------------- ----------
Queries Parallelized 0 5 0
DML Parallelized 0 0 0
DDL Parallelized 0 1 0
DFO Trees 0 6 0
Server Threads 0 0 0
Allocation Height 0 0 0
Allocation Width 0 0 0
Local Msgs Sent 0 2485 0
Distr Msgs Sent 0 0 0
Local Msgs Recv'd 0 2485 0
Distr Msgs Recv'd 0 0 0
DOP 0 0 0
Slave Sets 0 0 0

13 rows selected.

SQL> INSERT INTO TBL_DOP_TEST2 SELECT * FROM TBL_DOP_TEST2;

2338 rows created.

SQL> /

4676 rows created.

SQL> /

9352 rows created.

SQL> COMMIT;

Commit complete.

SQL> EXPLAIN PLAN FOR
2 SELECT * FROM TBL_DOP_TEST2 WHERE TABLE_NAME ='TEST' ORDER BY 1,2,3,4,5,6;

Explained.

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
Plan hash value: 1323191461

------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 24 | 20880 | 208 (1)| 00:00:01 |
| 1 | SORT ORDER BY | | 24 | 20880 | 208 (1)| 00:00:01 |
|* 2 | TABLE ACCESS FULL| TBL_DOP_TEST2 | 24 | 20880 | 207 (0)| 00:00:01 |
------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

2 - filter("TABLE_NAME"='TEST')

Note
-----
- dynamic statistics used: dynamic sampling (level=2)
- automatic DOP: Computed Degree of Parallelism is 1 because of parallel threshold

19 rows selected.

SQL> EXPLAIN PLAN FOR
2 SELECT * FROM TBL_DOP_TEST2 WHERE TABLE_NAME !='TEST' ORDER BY 1,2,3,4,5,6;

Explained.

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
Plan hash value: 1323191461

--------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 18216 | 15M| | 3537 (1)| 00:00:03 |
| 1 | SORT ORDER BY | | 18216 | 15M| 17M| 3537 (1)| 00:00:03 |
|* 2 | TABLE ACCESS FULL| TBL_DOP_TEST2 | 18216 | 15M| | 208 (1)| 00:00:01 |
--------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

2 - filter("TABLE_NAME"<>'TEST')

Note
-----
- dynamic statistics used: dynamic sampling (level=2)
- automatic DOP: Computed Degree of Parallelism is 1 because of no expensive parallel operation

19 rows selected.

=================================
PARALLEL_DEGREE_POLICY = LIMITED
=================================

LIMITED - Enables automatic DOP for some statements 
but parallel statement queuing and in-memory parallel execution are disabled. 
Automatic DOP is applied only to statements that access tables or indexes 
declared explicitly with the PARALLEL clause, or are defined as PARALLEL 
without an explicit DOP specified. Tables and indexes that have a DOP 
specified use that explicit DOP setting.


SQL> alter system set parallel_degree_policy=limited;

System altered.

SQL> EXPLAIN PLAN FOR
2 SELECT * FROM TBL_DOP_TEST2 WHERE TABLE_NAME ='TEST' ORDER BY 1,2,3,4,5,6;

Explained.

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
Plan hash value: 214504727
----------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | TQ |IN-OUT| PQ Distrib |
----------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 24 | 20880 | 47 (3)| 00:00:01 | | | |
| 1 | PX COORDINATOR | | | | | | | | |
| 2 | PX SEND QC (ORDER) | :TQ10001 | 24 | 20880 | 47 (3)| 00:00:01 | Q1,01 | P->S | QC (ORDER) |
| 3 | SORT ORDER BY | | 24 | 20880 | 47 (3)| 00:00:01 | Q1,01 | PCWP | |
| 4 | PX RECEIVE | | 24 | 20880 | 46 (0)| 00:00:01 | Q1,01 | PCWP | |
| 5 | PX SEND RANGE | :TQ10000 | 24 | 20880 | 46 (0)| 00:00:01 | Q1,00 | P->P | RANGE |
| 6 | PX BLOCK ITERATOR | | 24 | 20880 | 46 (0)| 00:00:01 | Q1,00 | PCWC | |
|* 7 | TABLE ACCESS FULL| TBL_DOP_TEST2 | 24 | 20880 | 46 (0)| 00:00:01 | Q1,00 | PCWP | |
----------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

7 - filter("TABLE_NAME"='TEST')

Note
-----
- dynamic statistics used: dynamic sampling (level=2)

23 rows selected.

SQL> EXPLAIN PLAN FOR
2 SELECT * FROM TBL_DOP_TEST2 WHERE TABLE_NAME !='TEST' ORDER BY 1,2,3,4,5,6;

Explained.

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
Plan hash value: 214504727
----------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time | TQ |IN-OUT| PQ Distrib |
----------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 18216 | 15M| | 47 (3)| 00:00:01 | | | |
| 1 | PX COORDINATOR | | | | | | | | | |
| 2 | PX SEND QC (ORDER) | :TQ10001 | 18216 | 15M| | 47 (3)| 00:00:01 | Q1,01 | P->S | QC (ORDER) |
| 3 | SORT ORDER BY | | 18216 | 15M| 17M| 47 (3)| 00:00:01 | Q1,01 | PCWP | |
| 4 | PX RECEIVE | | 18216 | 15M| | 46 (0)| 00:00:01 | Q1,01 | PCWP | |
| 5 | PX SEND RANGE | :TQ10000 | 18216 | 15M| | 46 (0)| 00:00:01 | Q1,00 | P->P | RANGE |
| 6 | PX BLOCK ITERATOR | | 18216 | 15M| | 46 (0)| 00:00:01 | Q1,00 | PCWC | |
|* 7 | TABLE ACCESS FULL| TBL_DOP_TEST2 | 18216 | 15M| | 46 (0)| 00:00:01 | Q1,00 | PCWP | |
----------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

7 - filter("TABLE_NAME"<>'TEST')

Note
-----
- dynamic statistics used: dynamic sampling (level=2)

23 rows selected.

SQL>

===============================
PARALLEL_DEGREE_POLICY=MANUAL
===============================
MANUAL - Disables automatic DOP, statement queuing and in-memory parallel execution. 
It reverts the behavior of parallel execution to what it was previous to 
Oracle Database 11g Release 2 (11.2), which is the default.


SQL> alter system set PARALLEL_DEGREE_POLICY=MANUAL;

System altered.

SQL> EXEC DBMS_STATS.GATHER_TABLE_STATS('SYS','TBL_DOP_TEST2');

PL/SQL procedure successfully completed.


SQL> EXPLAIN PLAN FOR
2 SELECT * FROM TBL_DOP_TEST2 WHERE TABLE_NAME ='TEST' ORDER BY 1,2,3,4,5,6;

Explained.

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
Plan hash value: 214504727
----------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | TQ |IN-OUT| PQ Distrib |
----------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 24 | 20880 | 47 (3)| 00:00:01 | || |
| 1 | PX COORDINATOR | | | | | | || |
| 2 | PX SEND QC (ORDER) | :TQ10001 | 24 | 20880 | 47 (3)| 00:00:01 | Q1,01 | P->S | QC (ORDER) |
| 3 | SORT ORDER BY | | 24 | 20880 | 47 (3)| 00:00:01 | Q1,01 | PCWP | |
| 4 | PX RECEIVE | | 24 | 20880 | 46 (0)| 00:00:01 | Q1,01 | PCWP | |
| 5 | PX SEND RANGE | :TQ10000 | 24 | 20880 | 46 (0)| 00:00:01 | Q1,00 | P->P | RANGE |
| 6 | PX BLOCK ITERATOR | | 24 | 20880 | 46 (0)| 00:00:01 | Q1,00 | PCWC | |
|* 7 | TABLE ACCESS FULL| TBL_DOP_TEST2 | 24 | 20880 | 46 (0)| 00:00:01 | Q1,00 | PCWP | |
----------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

7 - filter("TABLE_NAME"='TEST')

Note
-----
- dynamic statistics used: dynamic sampling (level=2)
- Degree of Parallelism is 4 because of table property

24 rows selected.

SQL> EXPLAIN PLAN FOR
2 SELECT * FROM TBL_DOP_TEST2 WHERE TABLE_NAME !='TEST' ORDER BY 1,2,3,4,5,6;

Explained.

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
Plan hash value: 214504727
----------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |TQ |IN-OUT| PQ Distrib |
----------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 18216 | 15M| | 47 (3)| 00:00:01 || | |
| 1 | PX COORDINATOR | | | | | | || | |
| 2 | PX SEND QC (ORDER) | :TQ10001 | 18216 | 15M| | 47 (3)| 00:00:01 | Q1,01 | P->S | QC (ORDER) |
| 3 | SORT ORDER BY | | 18216 | 15M| 17M| 47 (3)| 00:00:01 | Q1,01 | PCWP | |
| 4 | PX RECEIVE | | 18216 | 15M| | 46 (0)| 00:00:01 | Q1,01 | PCWP | |
| 5 | PX SEND RANGE | :TQ10000 | 18216 | 15M| | 46 (0)| 00:00:01 | Q1,00 | P->P | RANGE |
| 6 | PX BLOCK ITERATOR | | 18216 | 15M| | 46 (0)| 00:00:01 | Q1,00 | PCWC | |
|* 7 | TABLE ACCESS FULL| TBL_DOP_TEST2 | 18216 | 15M| | 46 (0)| 00:00:01 | Q1,00 | PCWP | |
------------------------------------------------------------------------------------------------------------------------------


Predicate Information (identified by operation id):
---------------------------------------------------

7 - filter("TABLE_NAME"<>'TEST')

Note
-----
- dynamic statistics used: dynamic sampling (level=2)
- Degree of Parallelism is 4 because of table property

24 rows selected.

===============================
PARALLEL_DEGREE_POLICY=ADAPTIVE
===============================
ADAPTIVE - Enables automatic degree of parallelism, statement queuing, 
and in-memory parallel execution, similar to the AUTO value. 
In addition, performance feedback is enabled.


SQL> ALTER SYSTEM SET PARALLEL_DEGREE_POLICY=ADAPTIVE;

System altered.

SQL> EXPLAIN PLAN FOR
2 SELECT * FROM TBL_DOP_TEST2 WHERE TABLE_NAME ='TEST' ORDER BY 1,2,3,4,5,6;

Explained.

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
Plan hash value: 1323191461
------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 8 | 2088 | 208 (1)| 00:00:01 |
| 1 | SORT ORDER BY | | 8 | 2088 | 208 (1)| 00:00:01 |
|* 2 | TABLE ACCESS FULL| TBL_DOP_TEST2 | 8 | 2088 | 207 (0)| 00:00:01 |
------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

2 - filter("TABLE_NAME"='TEST')

Note
-----
- automatic DOP: Computed Degree of Parallelism is 1 because of parallel threshold

18 rows selected.

SQL> EXPLAIN PLAN FOR
2 SELECT * FROM TBL_DOP_TEST2 WHERE TABLE_NAME !='TEST' ORDER BY 1,2,3,4,5,6;

Explained.

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
Plan hash value: 1323191461

--------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 18696 | 4765K| | 1265 (1)| 00:00:01 |
| 1 | SORT ORDER BY | | 18696 | 4765K| 7488K| 1265 (1)| 00:00:01 |
|* 2 | TABLE ACCESS FULL| TBL_DOP_TEST2 | 18696 | 4765K| | 208 (1)| 00:00:01 |
--------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

2 - filter("TABLE_NAME"<>'TEST')

Note
-----
- automatic DOP: Computed Degree of Parallelism is 1 because of no expensive parallel operation

18 rows selected.

SQL>


Parameter : PARALLEL_EXECUTION_MESSAGE_SIZE
Default : 16 KB
Comments : Specifies the size of the buffers used by the parallel execution 
servers to communicate among themselves and with the query coordinator. 
These buffers are allocated out of the shared pool.

Parameter : PARALLEL_FORCE_LOCAL
Default : FALSE
Comments : Restricts parallel execution to the current Oracle RAC instance.

To limit inter-node parallel execution, you can control parallel execution 
in an Oracle RAC environment using the PARALLEL_FORCE_LOCAL initialization 
parameter. By setting this parameter to TRUE, the parallel server processes can 
only execute on the same Oracle RAC node where the SQL statement was started.

Parameter : PARALLEL_MAX_SERVERS
Default : 2 x DOP x NUMBER_OF_CONCURRENT_USERS
Comments : Specifies the maximum number of parallel execution processes 
and parallel recovery processes for an instance. As demand increases, 
Oracle Database increases the number of processes from the number created 
at instance startup up to this value.

If you set this parameter too low, some queries may not have a parallel 
execution process available to them during query processing. If you set 
it too high, memory resource shortages may occur during peak periods, 
which can degrade performance.


Parameter : PARALLEL_MIN_SERVERS
Default : 0
Comments : Specifies the number of parallel execution processes to be started 
and reserved for parallel operations, when Oracle Database is started up. 
Increasing this setting can help balance the startup cost of a parallel 
statement, but requires greater memory usage as these parallel execution 
processes are not removed until the database is shut down.

SQL> startup
ORACLE instance started.

SQL> !ps -ef | grep p0
oracle 8953 1 0 11:57 ? 00:00:00 ora_psp0_cdb1
oracle 9040 1 2 11:57 ? 00:00:00 ora_p000_cdb1
oracle 9042 1 0 11:57 ? 00:00:00 ora_p001_cdb1
oracle 9044 1 1 11:57 ? 00:00:00 ora_p002_cdb1
oracle 9046 1 0 11:57 ? 00:00:00 ora_p003_cdb1
oracle 9048 1 0 11:57 ? 00:00:00 ora_p004_cdb1
oracle 9050 1 0 11:57 ? 00:00:00 ora_p005_cdb1
oracle 9052 1 0 11:57 ? 00:00:00 ora_p006_cdb1
oracle 9054 1 0 11:57 ? 00:00:00 ora_p007_cdb1
oracle 9210 8945 0 11:57 pts/0 00:00:00 /bin/bash -c ps -ef | grep p0
oracle 9212 9210 0 11:57 pts/0 00:00:00 grep p0


SQL> show parameter parallel_min_servers

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
parallel_min_servers integer 8


Parameter : PARALLEL_MIN_PERCENT
Default : 0
Comments : Specifies the minimum percentage of requested parallel execution 
processes required for parallel execution. With the default value of 0, 
a parallel statement executes serially if no parallel server processes 
are available.

Parameter : PARALLEL_SERVERS_TARGET
Default :
Comments : Specifies the number of parallel execution server processes 
available to run queries before parallel statement queuing is used.
Note that parallel statement queuing is only active if PARALLEL_DEGREE_POLICY 
is set to AUTO.

When PARALLEL_DEGREE_POLICY is set to AUTO, statements that require parallel 
execution are queued if the number of parallel processes currently in use 
on the system equals or is greater than PARALLEL_SERVERS_TARGET. This is 
not the maximum number of parallel server processes allowed on a system 
(that is controlled by PARALLEL_MAX_SERVERS). However, PARALLEL_SERVERS_TARGET 
and parallel statement queuing is used to ensure that each statement that 
requires parallel execution is allocated the necessary parallel server 
resources and the system is not flooded with too many parallel server processes.

Parameter : PARALLEL_THREADS_PER_CPU
Default : 2
Comments : Describes the number of parallel execution processes or threads 
that a CPU can handle during parallel execution.

#Forcing Parallel Execution for a Session

If you are sure you want to execute in parallel and want to avoid setting 
the DOP for a table or modifying the queries involved, you can force parallelism 
with the following statement:

ALTER SESSION FORCE PARALLEL QUERY;
All subsequent queries are executed in parallel provided no 
restrictions are violated.

ALTER SESSION FORCE PARALLEL DDL;
statement to override the parallel clauses of subsequent DDL 
statements in a session.

ALTER SESSION ENABLE PARALLEL DML;
ALTER SESSION DISABLE PARALLEL DML;
A DML statement can be parallelized only if you have explicitly enabled 
parallel DML in the session or in the SQL statement.

To enable parallel DML mode in a specific SQL statement, 
include the ENABLE_PARALLEL_DML SQL hint. For example:
INSERT /*+ ENABLE_PARALLEL_DML */ …

You can disable the mode for a specific SQL statement with the 
DISABLE_PARALLEL_DML SQL hint.
Parallel DML is disabled for a session by default.
When parallel DML is disabled, no DML is executed in parallel 
even if the PARALLEL hint is used.

ALTER SESSION FORCE PARALLEL DML;
statement to override parallel clauses for subsequent INSERT operations 
in a session. Parallel hints in insert operations override the 
ALTER SESSION FORCE PARALLEL DML statement.

The UPDATE or DELETE operation is parallelized if and only if at least 
one of the following is true:
1. The table being updated or deleted has a PARALLEL specification.
2. The PARALLEL hint is specified in the DML statement.
3. An ALTER SESSION FORCE PARALLEL DML statement has been issued previously during the session.

[6] Tune Parallel Execution

Additional Consideration for Tune Parallel Execution.

Shared_pool_size : depends on several factors When we use PX is advisable 
to extend this parameter (See doc.)
In our case we use AMM so we do not worry about it

SHARED_POOL_SIZE
Parallel execution requires memory resources in addition to those required 
by serial SQL execution. Additional memory is used for communication and 
passing data between query server processes and the query coordinator.
Database allocates memory for query server processes from the shared pool.

After you determine the initial setting for the shared pool, you must 
calculate additional memory requirements for message buffers 
and determine how much additional space you need for cursors.

Monitor Memory Usage After Processing Begins
SELECT POOL, NAME, SUM(BYTES) FROM V$SGASTAT 
WHERE POOL LIKE '%pool%' GROUP BY ROLLUP (POOL, NAME);

SELECT * FROM V$PX_PROCESS_SYSSTAT WHERE STATISTIC LIKE 'Buffers%';

#Parameters Affecting Resource Consumption for Parallel DML and Parallel DDL 
There are other parameters that affect or are related to PX 
TRANSACTIONS
FAST_START_PARALLEL_ROLLBACK
DML_LOCKS

A possible example we can make tuning PX is to calculate the additional 
memory size we are handling our request to evaluate whether to expand the memory or not.

[1] We estimate the number of buffers that are using right now (Buffers HWM)

SELECT * FROM V$PX_PROCESS_SYSSTAT WHERE STATISTIC LIKE 'Buffers%';

- STATISTIC VALUE
- ------------------------------ ----------
- 376 Allocated Buffers
- Freed Buffers 376
- Current Buffers 0
- Buffers HWM 107

[2] We get the buffer size

SHOW PARAMETER PARALLEL_EXECUTION_MESSAGE_SIZE
- NAME TYPE VALUE
- ------------------------------------ ----------- - -----------------------------
- Parallel_execution_message_size integer 16384

[3] We get the current size dedicated to PX
SELECT POOL, NAME, BYTES FROM V$sgastat WHERE NAME = 'PX msg pool';

- POOL NAME BYTES
- ------------ ---------- --------------------------
- Msg pool large pool PX 8192000

To calculate the size multiply the value of 
" Buffers HWM " * PARALLEL_EXECUTION_MESSAGE_SIZE = 107 * 16K = 1.7MB.

The size of memory dedicated to PX is 8MB , which is far superior to what 
we are using so we would not have to take any action . Also keep in mind 
that we are using automatic memory management (AMM ), then if necessary , 
the size of the SGA increase to expand this pool . If we see that our system 
uses enough memory to PX , we can evaluate the need to increase the total 
memory size and / or raise the minimum dedicated to the SGA to avoid expanding 
operations ( MEMORY_TARGET , SGA_TARGET and SHARED_POOL_SIZE parameters).

------------
Test Case-2
------------
SQL> SELECT * FROM V$PX_PROCESS_SYSSTAT WHERE STATISTIC LIKE 'Buffers%';

STATISTIC VALUE CON_ID
------------------------------------------------ ---------- ----------
Buffers Allocated 7165 0
Buffers Freed 7165 0
Buffers Current 0 0
Buffers HWM 153 0

SQL> SHOW PARAMETER PARALLEL_EXECUTION_MESSAGE_SIZE

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
parallel_execution_message_size integer 16384
SQL> SELECT POOL, NAME, BYTES FROM V$sgastat WHERE NAME = 'PX msg pool';

POOL NAME BYTES
------------ -------------------------- ----------
large pool PX msg pool 983040
large pool PX msg pool 122880

To calculate the size multiply the value of 
" Buffers HWM " * PARALLEL_EXECUTION_MESSAGE_SIZE = 153 * 16K = 2.3MB .

The size of memory dedicated to PX is 1 , which is far less to what we are using

#Adjust parameter.

Sat Aug 24 16:29:34 2019
ALTER SYSTEM SET shared_pool_size=100M SCOPE=BOTH;

#Restart instance
shutdown immediate
startup

session-1
select /*+ parallel (6) */ count(*) from dba_objects;

session-2

SQL> SELECT * FROM V$PX_PROCESS_SYSSTAT WHERE STATISTIC LIKE 'Buffers%';

STATISTIC VALUE CON_ID
---------- --------------------------
Buffers Allocated 15895 0
Buffers Freed 15895 0
Buffers Current 0 0
Buffers HWM 357 0

SQL> SHOW PARAMETER PARALLEL_EXECUTION_MESSAGE_SIZE

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
parallel_execution_message_size integer 16384

SQL> SELECT POOL, NAME, BYTES FROM V$sgastat WHERE NAME = 'PX msg pool';

POOL NAME BYTES
------------ -------------------------- ----------
large pool PX msg pool 6021120


To calculate the size multiply the value of 
" Buffers HWM " * PARALLEL_EXECUTION_MESSAGE_SIZE = 357 * 16K = 5.5 MB. 

The size of memory dedicated to PX is 5.7 , which is tune to what we are using.

--------------------------------------------------------
HOL : Configure and use parallel execution for queries.
--------------------------------------------------------

[1] Enable the trace on the session level and query a table using 4 
    parallel processes.

SQL> alter system reset parallel_degree_policy;

System altered.

SQL> startup force;
ORACLE instance started.

SQL> show parameter parallel

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
fast_start_parallel_rollback string LOW
parallel_adaptive_multi_user boolean FALSE
parallel_automatic_tuning boolean FALSE
parallel_degree_level integer 100
parallel_degree_limit string CPU
parallel_degree_policy string MANUAL
parallel_execution_message_size integer 16384
parallel_force_local boolean FALSE
parallel_instance_group string
parallel_io_cap_enabled boolean FALSE
parallel_max_servers integer 80
parallel_min_percent integer 0
parallel_min_servers integer 8
parallel_min_time_threshold string 1
parallel_server boolean FALSE
parallel_server_instances integer 1
parallel_servers_target integer 32
parallel_threads_per_cpu integer 2
recovery_parallelism integer 0

-bash-4.1$ sqlplus sh/oracle_4U@pdb1

SQL> set autotrace traceonly exp
SQL> select /*+ parallel (a,4) */ * from customers a;

Execution Plan
----------------------------------------------------------
Plan hash value: 2487033814
---------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | TQ |IN-OUT| PQ Distrib |
---------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 55500 | 10M| 117 (0)| 00:00:01 | | | |
| 1 | PX COORDINATOR | | | | | | | | |
| 2 | PX SEND QC (RANDOM)| :TQ10000 | 55500 | 10M| 117 (0)| 00:00:01 | Q1,00 | P->S | QC (RAND) |
| 3 | PX BLOCK ITERATOR | | 55500 | 10M| 117 (0)| 00:00:01 | Q1,00 | PCWC | |
| 4 | TABLE ACCESS FULL| CUSTOMERS | 55500 | 10M| 117 (0)| 00:00:01 | Q1,00 | PCWP | |
---------------------------------------------------------------------------------------------------------------

Note
-----
- Degree of Parallelism is 4 because of table property

[2] Query a table with parallel_index hint with 6 parallel processes.

SQL> create table tbl_dimention5 as select * from customers;

Table created.

SQL> insert into tbl_dimention5 select * from tbl_dimention5;

55500 rows created.

Execution Plan
----------------------------------------------------------
Plan hash value: 747043403
-------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------
| 0 | INSERT STATEMENT | | 55500 | 10M| 423 (1)| 00:00:01 |
| 1 | LOAD TABLE CONVENTIONAL | TBL_DIMENTION5 | | | | |
| 2 | TABLE ACCESS FULL | TBL_DIMENTION5 | 55500 | 10M| 423 (1)| 00:00:01 |
-------------------------------------------------------------------------------------------

SQL> commit;

Commit complete.

SQL> insert into /*+ parallel */ tbl_dimention5
2 select /*+ parallel */ * from tbl_dimention5;

111000 rows created.


Execution Plan
----------------------------------------------------------
Plan hash value: 3056153762
------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | TQ |IN-OUT| PQ Distrib |
------------------------------------------------------------------------------------------------------------------------
| 0 | INSERT STATEMENT | | 55500 | 10M| 235 (1)| 00:00:01 | | | |
| 1 | LOAD TABLE CONVENTIONAL | TBL_DIMENTION5 | | | | | | | |
| 2 | PX COORDINATOR | | | | | | | | |
| 3 | PX SEND QC (RANDOM) | :TQ10000 | 55500 | 10M| 235 (1)| 00:00:01 | Q1,00 | P->S | QC (RAND) |
| 4 | PX BLOCK ITERATOR | | 55500 | 10M| 235 (1)| 00:00:01 | Q1,00 | PCWC | |
| 5 | TABLE ACCESS FULL | TBL_DIMENTION5 | 55500 | 10M| 235 (1)| 00:00:01 | Q1,00 | PCWP | |
------------------------------------------------------------------------------------------------------------------------

Note
-----
- automatic DOP: Computed Degree of Parallelism is 2
- PDML is disabled in current session

SQL> commit;

Commit complete.

SQL> create index ttt on tbl_dimention5(CUST_ID);

Index created.

SQL> select /*+ parallel_index(a,ttt,6) */ cust_id from tbl_dimention5 a;

Execution Plan
----------------------------------------------------------
Plan hash value: 1795365773
-----------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | TQ |IN-OUT| PQ Distrib |
-----------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 55500 | 270K| 135 (1)| 00:00:01 | | | |
| 1 | PX COORDINATOR | | | | | | | | |
| 2 | PX SEND QC (RANDOM) | :TQ10000 | 55500 | 270K| 135 (1)| 00:00:01 | Q1,00 | P->S | QC (RAND) |
| 3 | PX BLOCK ITERATOR | | 55500 | 270K| 135 (1)| 00:00:01 | Q1,00 | PCWC | |
| 4 | INDEX FAST FULL SCAN| TTT | 55500 | 270K| 135 (1)| 00:00:01 | Q1,00 | PCWP | |
-----------------------------------------------------------------------------------------------------------------

[3] Enable parallelism during creation of an object.

You can assign fixed parallelism degree for the table or the index during creation.
Use parallel (degree int) clause at the end of the CREATE command. In the following 
example, we create a table and set a default degree of parallelism (DOP) to 6.
It means that anytime a user accesses the table will get DOP of 6.

SQL> create table tbl_dop_test as select * from dba_tables;

Table created.

SQL> alter table tbl_dop_test parallel (degree 4);

SQL> SET AUTOTRACE TRACEONLY EXP
SQL> SET LINESIZE 150
SQL> SELECT * FROM TBL_DOP_TEST;

Execution Plan
----------------------------------------------------------
Plan hash value: 865146542

------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | TQ |IN-OUT| PQ Distrib |
------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2502 | 615K| 8 (0)| 00:00:01 | | | |
| 1 | PX COORDINATOR | | | | | | | | |
| 2 | PX SEND QC (RANDOM)| :TQ10000 | 2502 | 615K| 8 (0)| 00:00:01 | Q1,00 | P->S | QC (RAND) |
| 3 | PX BLOCK ITERATOR | | 2502 | 615K| 8 (0)| 00:00:01 | Q1,00 | PCWC | |
| 4 | TABLE ACCESS FULL| TBL_DOP_TEST | 2502 | 615K| 8 (0)| 00:00:01 | Q1,00 | PCWP | |
------------------------------------------------------------------------------------------------------------------

Note
-----
- Degree of Parallelism is 4 because of table property

[4] Enable the parallel hint with other DML Command.

SQL> INSERT /*+ parallel(tbl_dop_test,2) */ into tbl_dop_test
2 SELECT /*+ parallel(tbl_dop_test,2) */ * from tbl_dop_test;

2502 rows created.


Execution Plan
----------------------------------------------------------
Plan hash value: 865146542

----------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | TQ |IN-OUT| PQ Distrib |
----------------------------------------------------------------------------------------------------------------------
| 0 | INSERT STATEMENT | | 2502 | 615K| 29 (0)| 00:00:01 | | | |
| 1 | LOAD TABLE CONVENTIONAL | TBL_DOP_TEST | | | | | | | |
| 2 | PX COORDINATOR | | | | | | | | |
| 3 | PX SEND QC (RANDOM) | :TQ10000 | 2502 | 615K| 29 (0)| 00:00:01 | Q1,00 | P->S | QC (RAND) |
| 4 | PX BLOCK ITERATOR | | 2502 | 615K| 29 (0)| 00:00:01 | Q1,00 | PCWC | |
| 5 | TABLE ACCESS FULL | TBL_DOP_TEST | 2502 | 615K| 29 (0)| 00:00:01 | Q1,00 | PCWP | |
----------------------------------------------------------------------------------------------------------------------

Note
-----
- Degree of Parallelism is 2 because of table property
- PDML is disabled in current session

[5] Create table under USR1 user with default 6 parallelism degree. 
Then query the table and check the explain plan. Then use a hint to disable 
the parallel query on this table.


SQL> alter session set container=pdb1;

Session altered.

SQL> grant connect,resource to USR1 identified by user1;

Grant succeeded.

SQL> alter user USR1
2 quota unlimited on users;

User altered.

SQL> connect sys/oracle_4U@pdb1 as sysdba
Connected.
SQL> create table USR1.mytable 
     tablespace users parallel 6 as select * from dba_tables;

Table created.

SQL> connect USR1/user1@pdb1
Connected.
SQL>

SQL> set autotrace traceonly exp
SQL> select count(*) from mytable;

Execution Plan
----------------------------------------------------------
Plan hash value: 3812909056
--------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time | TQ |IN-OUT| PQ Distrib |
--------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 6 (0)| 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 | | 2503 | 6 (0)| 00:00:01 | Q1,00 | PCWC | |
| 6 | TABLE ACCESS FULL| MYTABLE | 2503 | 6 (0)| 00:00:01 | Q1,00 | PCWP | |
--------------------------------------------------------------------------------
------------------------
Note
-----
- Degree of Parallelism is 4 because of table property

SQL> select /*+ NO_PARALLEL */ COUNT(*) FROM mytable;

Execution Plan
----------------------------------------------------------
Plan hash value: 1675061203

----------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
----------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 30 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | | |
| 2 | TABLE ACCESS FULL| MYTABLE | 2503 | 30 (0)| 00:00:01 |
----------------------------------------------------------------------

Note
-----
- Degree of Parallelism is 1 because of hint

[6] Create an empty table based on DBA_OBJECTS view. Then query DBA_OBJECTS 
view with parallel degree 4 and insert the result to the table with parallel 
degree 6.

SQL> connect sys/oracle_4U@pdb1 as sysdba
Connected.
SQL>
SQL> CREATE TABLE USR1.TBL_OBJECTS AS SELECT * FROM DBA_OBJECTS WHERE 1 = 2;

Table created.

SQL> SELECT COUNT(*) FROM USR1.TBL_OBJECTS;

COUNT(*)
----------
0

SQL> SET AUTOTRACE TRACEONLY EXP


SQL> INSERT /*+ PARALLEL (a,6) */ INTO USR1.TBL_OBJECTS a
SELECT /*+ PARALLEL (b,4) */ * FROM DBA_OBJECTS b; 2

92069 rows created.


Execution Plan
----------------------------------------------------------
Plan hash value: 240859625
-------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | TQ |IN-OUT| PQ Distrib |
-------------------------------------------------------------------------------------------------------------------------------------------
| 0 | INSERT STATEMENT | | 91010 | 31M| 82802 (1)| 00:00:04 | | | |
| 1 | LOAD TABLE CONVENTIONAL | TBL_OBJECTS | | | | | | | |
| 2 | PX COORDINATOR | | | | | | | | |
| 3 | PX SEND QC (RANDOM) | :TQ20001 | 91010 | 31M| 82802 (1)| 00:00:04 | Q2,01 | P->S | QC (RAND) |
| 4 | VIEW | DBA_OBJECTS | 91010 | 31M| 82802 (1)| 00:00:04 | Q2,01 | PCWP | |
| 5 | UNION-ALL | | | | | | Q2,01 | PCWP | |
|* 6 | TABLE ACCESS BY INDEX ROWID | SUM$ | 1 | 8 | 1 (0)| 00:00:01 | Q2,01 | PCWP | |
|* 7 | INDEX UNIQUE SCAN | I_SUM$_1 | 1 | | 0 (0)| 00:00:01 | Q2,01 | PCWP | |
|* 8 | TABLE ACCESS FULL | USER_EDITIONING$ | 1 | 6 | 2 (0)| 00:00:01 | Q2,01 | PCWP | |
| 9 | TABLE ACCESS BY INDEX ROWID BATCHED| OBJ$ | 1 | 30 | 3 (0)| 00:00:01 | Q2,01 | PCWP | |
|* 10 | INDEX RANGE SCAN | I_OBJ1 | 1 | | 2 (0)| 00:00:01 | Q2,01 | PCWP | |
| 11 | BUFFER SORT | | | | | | Q2,01 | PCWC | |
| 12 | PX RECEIVE | | | | | | Q2,01 | PCWP | |
| 13 | PX SEND ROUND-ROBIN | :TQ20000 | | | | | | S->P | RND-ROBIN |
|* 14 | FILTER | | | | | | | | |
| 15 | PX COORDINATOR | | | | | | | | |
| 16 | PX SEND QC (RANDOM) | :TQ10001 | 91283 | 12M| 392 (1)| 00:00:01 | Q1,01 | P->S | QC (RAND) |
|* 17 | HASH JOIN | | 91283 | 12M| 392 (1)| 00:00:01 | Q1,01 | PCWP | |
| 18 | TABLE ACCESS FULL | USER$ | 141 |2397 | 4 (0)| 00:00:01 | Q1,01 | PCWP | |
|* 19 | HASH JOIN | | 91283 | 11M| 388 (1)| 00:00:01 | Q1,01 | PCWP | |
| 20 | PX RECEIVE | | 141 |3384 | 1 (0)| 00:00:01 | Q1,01 | PCWP | |
| 21 | PX SEND BROADCAST | :TQ10000 | 141 |3384 | 1 (0)| 00:00:01 | Q1,00 | S->P | BROADCAST |
| 22 | PX SELECTOR | | | | | | Q1,00 | SCWC | |
| 23 | INDEX FULL SCAN | I_USER2 | 141 |3384 | 1 (0)| 00:00:01 | Q1,00 | SCWP | |
| 24 | PX BLOCK ITERATOR | | 91283 |9181K| 386 (1)| 00:00:01 | Q1,01 | PCWC | |
|* 25 | TABLE ACCESS FULL | OBJ$ | 91283 |9181K| 386 (1)| 00:00:01 | Q1,01 | PCWP | |
|* 26 | TABLE ACCESS FULL | USER_EDITIONING$ | 1 | 6 | 2 (0)| 00:00:01 | | | |
| 27 | NESTED LOOPS SEMI | | 1 | 29 | 2 (0)| 00:00:01 | | | |
|* 28 | INDEX SKIP SCAN | I_USER2 | 1 | 20 | 1 (0)| 00:00:01 | | | |
|* 29 | INDEX RANGE SCAN | I_OBJ4 | 1 | 9 | 1 (0)| 00:00:01 | | | |
|* 30 | TABLE ACCESS FULL | USER_EDITIONING$ | 1 | 6 | 2 (0)| 00:00:01 | | | |
| 31 | NESTED LOOPS | | 3 | 138 | 5 (0)| 00:00:01 | Q2,01 | PCWP | |
| 32 | PX BLOCK ITERATOR | | | | | | Q2,01 | PCWC | |
| 33 | TABLE ACCESS FULL | LINK$ | 3 | 87 | 2 (0)| 00:00:01 | Q2,01 | PCWP | |
| 34 | TABLE ACCESS CLUSTER | USER$ | 1 | 17 | 1 (0)| 00:00:01 | Q2,01 | PCWP | |
|* 35 | INDEX UNIQUE SCAN | I_USER# | 1 | | 0 (0)| 00:00:01 | Q2,01 | PCWP | |
-------------------------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

6 - filter(BITAND("S"."XPFLAGS",8388608)=8388608)
7 - access("S"."OBJ#"=:B1)
8 - filter("UE"."TYPE#"=:B1 AND "UE"."USER#"=:B2)
10 - access("EO"."OBJ#"=:B1)
14 - filter( NOT EXISTS (SELECT 0 FROM SYS."USER_EDITIONING$" "UE" WHERE "TYPE
#"=:B1 AND "UE"."USER#"=:B2) AND "O"."TYPE#"<>88

OR BITAND("O"."FLAGS",1048576)=1048576 OR BITAND("U"."SPARE1",16)=
0 OR (SYS_CONTEXT('userenv','current_edition_name')='ORA$BASE'

AND "U"."TYPE#"<>2 OR "U"."TYPE#"=2 AND "U"."SPARE2"=TO_NUMBER(SYS
_CONTEXT('userenv','current_edition_id')) OR EXISTS (SELECT 0

FROM SYS."USER$" "U2",SYS."OBJ$" "O2" WHERE "O2"."OWNER#"="U2"."US
ER#" AND "O2"."TYPE#"=88 AND "O2"."DATAOBJ#"=:B3 AND

"U2"."TYPE#"=2 AND "U2"."SPARE2"=TO_NUMBER(SYS_CONTEXT('userenv','
current_edition_id')))) AND EXISTS (SELECT 0 FROM

SYS."USER_EDITIONING$" "UE" WHERE "UE"."TYPE#"=:B4 AND "UE"."USER#
"=:B5))

17 - access("O"."SPARE3"="U"."USER#")
19 - access("O"."OWNER#"="U"."USER#")
25 - filter("O"."NAME"<>'_NEXT_OBJECT' AND "O"."NAME"<>'_default_auditing_opti
ons_' AND "O"."TYPE#"<>10 AND "O"."LINKNAME" IS

NULL AND BITAND("O"."FLAGS",128)=0)
26 - filter("TYPE#"=:B1 AND "UE"."USER#"=:B2)
28 - access("U2"."TYPE#"=2 AND "U2"."SPARE2"=TO_NUMBER(SYS_CONTEXT('userenv','
current_edition_id')))

filter("U2"."TYPE#"=2 AND "U2"."SPARE2"=TO_NUMBER(SYS_CONTEXT('userenv','
current_edition_id')))

29 - access("O2"."DATAOBJ#"=:B1 AND "O2"."TYPE#"=88 AND "O2"."OWNER#"="U2"."US
ER#")

30 - filter("UE"."TYPE#"=:B1 AND "UE"."USER#"=:B2)
35 - access("L"."OWNER#"="U"."USER#")

Note
-----
- Degree of Parallelism is 4 because of table property
- PDML is disabled in current session

SQL>

======================
Path to Documentation:
======================
Database VLDB and Partitioning Guide
8 Using Parallel Execution

Introduction to Parallel Execution
How Parallel Execution Works
Types of Parallelism
About Initializing and Tuning Parameters for Parallel Execution
Tuning General Parameters for Parallel Execution
Monitoring Parallel Execution Performance
Miscellaneous Parallel Execution Tuning Tips
Automatic Big Table Caching

Thank you for visiting this blog…