Q&As: Parallelism




(Q) What types of statements and tasks can be improved through parallel execution?

  • Queries requiring: large table scans, joins, or partitioned index scans
  • Creation of large indexes
  • Creation of large tables (and Materialized Views)
  • Bulk Inserts, Updates, Meges and Deletes
  • Scanning large objects (LOBs)


(Q) What are some of the characteristics of systems that can benefit from parallel execution?

  • Have SMPs, clusters, or MPPs
  • Sufficient IO bandwidth
  • Underutilized CPUs (<30%)
  • Enough memory free


(Q) When can OLTP systems mostly benefit from parallel execution?

  • OLTP systems may benefit during batch processing and schema maintenance operations.


(Q) What are three key parameters controlling automatic parallel execution?

  • PARALLEL_DEGREE_LIMIT
  • PARALLEL_DEGREE_POLICY = { MANUAL | LIMITED | AUTO } <== MANUAL => disabled, AUTO => Auto DOP on
  • PARALLEL_MIN_TIME_THRESHOLD = AUTO (default, about 10sec)


(Q) What is Intra-parallelism and Inter-parallelism?

  • Intra-operation parallelism: parallelism of an individal operation
  • Inter-operation parallelism: parallelism between operations in a data flow tree (in an execution plan)


(Q) What is the default degree of parallelism assumed by an Oracle Server?

  • By Default, PARALLEL_DEGREE_POLICY = Manual (NO PARALLELISM)
  • By default, the system only uses parallel execution when a parallel degree has been explicitly set on an object or if a parallel hint is specified in the SQL statement
  • If PARALLEL_DEGREE_POLICY = AUTO, then
    • Single Instance: DOP = PARALLEL_THREADS_PER_CPU X CPU_COUNT
    • RAC: DOP = PARALLEL_THREADS_PER_CPU X CPU_COUNT x INSTANCE_COUNT
    • important: in a multiuser environment, default parallelism is not recommended


(Q) What is the function of the PARALLEL_DEGREE_POLICY parameter? What vaules it takes?

  • PARALLEL_DEGREE_POLICY = { MANUAL | LIMITED | AUTO }
  • Enable/Disable (a) automatic degree of parallelism, (b) statement queueing, and (c) in-memory parallel execution
  • MANUAL => Default. Revert to behavior prior to 11g. No parallelism automatically enabled
  • LIMITED => Enables automatic degree of parallelism for some stmts only.
  • AUTO => all three enabled.


(Q) How does the optimizer determines the degree of parallelism for a statement?

  • Based on the resource requirements of the statement.
  • Limit on parallelization is set by
    • (a) PARALLEL_DEGREE_LIMIT (default = PARALLEL_THREADS_PER_CPU * CPU_COUNT * num instances)
    • (b) PARALLEL_MIN_TIME_THRESHOLD (default = 10sec
(Q) Which parameters affect the automatic degree of parallelism adopted in the system?
  • PARALLEL_DEGREE_POLICY = { MANUAL | LIMITED | AUTO }
  • PARALLEL_DEGREE_LIMIT
  • PARALLEL_MIN_TIME_THRESHOLD
==> The default degree of parallelism is MANUAL

SQL> show parameter parallel

NAME                           TYPE        VALUE
------------------------------ ----------- -------------
..
parallel_degree_limit           string      CPU
parallel_degree_policy      string      MANUAL    
...
parallel_min_time_threshold    string      AUTO
...

SQL> alter system set parallel_degree_policy=auto;
System altered.

SQL> conn sh/sh
Connected.

SQL> select degree from user_tables where table_name ='SALES';

DEGREE
----------
  1

SQL> alter table sales parallel 4;
Table altered.

SQL> select degree from user_tables where table_name ='SALES';

DEGREE
----------
  4

SQL> explain plan for select * from sales;
Explained.

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

PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------------
Plan hash value: 1550251865

---------------------------------------------------------------------------------------------
| Id  | Operation     | Name  | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
---------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |       | 918K|  25M| 526   (9)| 00:00:07 |     |     |
|   1 |  PARTITION RANGE ALL|       | 918K|  25M| 526   (9)| 00:00:07 |   1 |  28 |
|   2 |   TABLE ACCESS FULL | SALES | 918K|  25M| 526   (9)| 00:00:07 |   1 |  28 |
---------------------------------------------------------------------------------------------
Note
PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------------
   - automatic DOP: Computed Degree of Parallelism is 1 because of parallel threshold
13 rows selected.

==> Note here that no parallel execution took plase. 
    The estimated serial execution time (7sec) is still below the 10sec threshold used when the parallel_min_time_threshold is set to AUTO.  


==> Now, lets change the threshold to 1sec:

SQL> conn / as sysdba
Connected.
SQL> show parameter parallel

NAME                           TYPE        VALUE
------------------------------ ----------- -------------
..
parallel_degree_limit           string      CPU
parallel_degree_policy      string      MANUAL    
...
parallel_min_time_threshold    string      AUTO
...

SQL> alter system set parallel_min_time_threshold=1;
System altered.

SQL> show parameter parallel_min
NAME                             TYPE        VALUE
-------------------------------- ----------- -------
parallel_min_time_threshold      string      1

SQL> Conn sh/sh
Connected
SQL> explain plan for select * from sales;
Explained.

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

PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 3060979429

------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation            | Name     | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |    TQ  |IN-OUT| PQ Distrib |
------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |          |   918K|    25M|   291   (9)| 00:00:04 |       |       |        |      |            |
|   1 |  PX COORDINATOR      |          |       |       |            |          |       |       |        |      |            |
|   2 |   PX SEND QC (RANDOM)| :TQ10000 |   918K|    25M|   291   (9)| 00:00:04 |       |       |  Q1,00 | P->S | QC (RAND)  |
|   3 |    PX BLOCK ITERATOR |          |   918K|    25M|   291   (9)| 00:00:04 |     1 |    28 |  Q1,00 | PCWC |            |
|   4 |     TABLE ACCESS FULL| SALES    |   918K|    25M|   291   (9)| 00:00:04 |     1 |    28 |  Q1,00 | PCWP |            |
------------------------------------------------------------------------------------------------------------------------------

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------
Note
-----
   - automatic DOP: Computed Degree of Parallelism is 2
15 rows selected.


No comments:

Post a Comment