(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?
(Q) Which parameters affect the automatic degree of parallelism adopted in the system?
- 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
- 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