Parallelism Concepts

1. Parallelism Concepts

2. Objectives • After completing this lesson, you should be able to: • Identify the benefit of using parallel operations • Describe system conditions under which to use parallel operations • Describe how parallel operations work • Control the parallel execution server pool • List operations that can be parallelized

3. Introduction to Parallel Execution CPU scan CPU scan CPU idle CPU scan CPU idle CPU scan CPU idle CPU scan Server without parallelism Server with parallelism

4. System Conditions toImplement Parallelism • SMP, MPP, clusters using RAC • I/O bandwidth • CPUs used less than 30% • Sufficient memory

5. Operations That Can Be Parallelized • Access methods: • Table scans, fast full index scans • Partitioned index range scans • Various SQL operations • Joins: • Nested loop, sort merge • Hash, star transformation, partitionwise join • DDL statements: • CTAS, CREATE INDEX, REBUILD INDEX [PARTITION] • MOVE, SPLIT, COALESCEPARTITION • DML statements: • INSERTSELECT, UPDATE, DELETE, MERGE • SQL*Loader

6. Parallelization Rules • A SQL statement can be parallelized if: • It includes a PARALLEL hint • Parallelization is forced using the ALTER SESSION FORCE command • The object operated on is or was declared with a PARALLEL clause (dictionary DOP greater than one) • Degree of parallelism (DOP) is determined by looking at referenced objects: • Parallel queries use the largest specified or dictionary DOP • Parallel DDL sets the DOP to the one specified by the PARALLEL clause

7. Enabling Parallel DML/DDL/QUERY • The ALTER SESSION statement enables parallel mode: • Used to override dictionary DOPs with FORCE ALTER SESSION ENABLE DISABLE FORCE PARALLEL n PARALLEL DML DDL QUERY

8. Enabling Parallel DML/DDL/QUERY • You can use V$SESSION to look at session status: • PDML_STATUS • PDDL_STATUS • PQ_STATUS • Values for the columns listed above can be: • ENABLED • DISABLED • FORCED

9. SELECT COUNT(*) FROM sales How Parallel Execution Works • With serial execution, only one process is used. • With parallel execution: • One parallel execution coordinator process is used • Many parallel execution servers are used • Table is dynamically divided into granules Serial process Coordinator process SELECT COUNT(*) FROM sales SALES SALES Parallel execution servers

10. The Granule • The basic unit of work in parallelism is called the granule. Type of granules: • Block range granules are dynamically generated at execution time. • Partition granules are statically determined by the number of partitions. • One granule is read per parallel execution server. • Parallel execution servers progress from one granule to the next. • The type of granule used is dependent on the kind of parallel operation being performed.

11. Parallel Operations SELECT cust_last_name, cust_first_name FROM customers ORDER BY cust_last_name; Execution Servers Consumers Producers Table on disk SQL Data scan sort A-K Dispatchingresults scan sort L-S Coordinator sort T-Z scan Table’s dynamic partitioning (granules) Intraparallelism Intraparallelism DOP=3 Interparallelism

12. Parallel Execution withReal Application Clusters • Execution slaves have node affinity for execution coordinator but will expand if needed. Node 1 Node 2 Node 3 Node 4 Execution coordinator Shared disks Parallel execution server

13. How Parallel ExecutionServers Communicate • Rows distribution: • PARTITION • HASH • RANGE • ROUND-ROBIN • BROADCAST • QC(ORDER) • QC(RANDOM) QC Parallel Execution Server Set 1 Parallel Execution Server Set 2 DOP=3

14. Degree of Parallelism • Degree of parallelism (DOP) is the number of parallel execution servers used by one parallel operation. • DOP applies only to intraoperation parallelism. • If interoperation parallelism is used, then the number of parallel execution servers can be twice the DOP. • No more than two sets of parallel execution servers can be used for one parallelized statement. • When using partition granules, use a relatively high number of partitions.

15. Default Degree of Parallelism • The default DOP: • Is used for a parallel operation that does not specify a DOP • Is dynamically calculated at run time • Depends on: • Total number of CPUs • PARALLEL_THREADS_PER_CPU • May be reduced depending on the availability of parallel execution servers or by the Oracle Resource Manager

16. Parallel Execution Plan • For the same statement, a parallel plan generally differs from the corresponding serial plan. • In order to generate the execution plan, use the EXPLAIN PLAN command, or execute the statement. • In order to view the execution plan: • Select directly from PLAN_TABLE • Select directly from V$SQL_PLAN • Run $ORACLE_HOME/rdbms/admin/utlxplp.sql • Use the DBMS_XPLAN.DISPLAY table function • Columns of interest: • OBJECT_NODE • OTHER_TAG • DISTRIBUTION • OTHER (Prior to 10g)

17. OTHER_TAG Column • OTHER_TAG • Interpretation • SERIAL • Serial execution • SERIAL_FROM_REMOTE (S -> R) • Serial execution at a remote site • PARALLEL_FROM_SERIAL (S -> P) • Serial execution: Output partitioned or broadcast to PX • PARALLEL_TO_PARALLEL (P -> P) • Parallel execution: Output repartitioned to second set of PX • PARALLEL_TO_SERIAL (P -> S) • Parallel execution: Output returns to coordinator • PARALLEL_COMBINED_WITH_ PARENT (PCWP) • Parallel execution: Output used by the same PX in the next step • PARALLEL_COMBINED_WITH_ CHILD (PCWC) • Parallel execution: Input from previous step used by same PX

18. Serial and Parallel Execution Plans HASH GROUP BY HASH JOIN TABLE ACCESS FULL CUSTOMERS PARTITION RANGE ALL TABLE ACCESS FULL SALES SELECT cust_city,sum(amount_sold) FROM sales s, customers c WHERE s.cust_id=c.cust_id GROUP BY cust_city; PX COORDINATOR PX SEND QC P->S QC(RANDOM) HASH GROUP BY PCWP PX RECEIVE PCWP PX SEND P->P HASH HASH GROUP BY PCWP HASH JOIN PCWP PX RECEIVE PCWP PX SEND P->P (BROADCAST) PX BLOCK ITERATOR PCWC TABLE ACCESS FULL CUSTOMERS PCWP PX BLOCK ITERATOR PCWC TABLE ACCESS FULL SALES PCWP

19. Parallel Plan Interpretation SELECT /*+ PARALLEL */ cust_city, sum(amount_sold) FROM sales s, customers c WHERE s.cust_id=c.cust_id GROUP BY cust_city; PX Coordinator PX Send QC Hash Group By Hash Join PX Receive PX Receive PX Block Iterator PX Send Hash PX Send Broadcast Full Scan Sales PX Block Iterator Hash Group By Full Scan Customers

20. Parallel Plan Interpretation PX Coordinator PX Send QC Set 2 PCWP P->P(B) Hash Group By Hash Join PX Receive PX Block Iterator PX Receive :TQ10 Full Scan Sales Set 1 PX Send Hash PX Send Broadcast PX Block Iterator Hash Group By Full Scan Customers

21. Parallel Plan Interpretation PX Coordinator PX Send QC Set 2 Hash Group By Hash Join PX Block Iterator PX Receive Full Scan Sales PX Send Hash Hash Group By

22. Parallel Plan Interpretation PX Coordinator P->P(H) PX Send QC Set 2 Set 1 Hash Group By Hash Join :TQ11 PX Receive PX Send Hash Hash Group By PCWP

23. Parallel Plan Interpretation P to S PX Coordinator Set 1 :TQ12 PX Send QC Hash Group By PX Receive

24. Parallel Execution Server Pool • A pool of servers are created at instance startup. • Minimum pool size is determined by PARALLEL_MIN_SERVERS. • Pool size can increase based on demand. • Maximum pool size is determined by PARALLEL_MAX_SERVERS. • If a parallel execution server is idle for more than a threshold period of time, it is terminated. • Slaves specified in the minimum set are never terminated.

25. Minimum Number of ParallelExecution Servers • In order for an operation to be parallelized, at least two parallel execution servers must be started. • You can specify a minimum percentage of available parallel execution servers for one operation to be parallelized. • PARALLEL_MIN_PERCENT specifies this minimum percentage. • If this minimum percentage is not available, an error is reported (ORA-12827). • Default value is 0.

26. Object’s PARALLEL Clause • Can be specified for tables and indexes • View degree of parallelism in the DEGREE column of DBA_TABLES (dictionary DOP) • Modified by using corresponding ALTER command • Used to specify the DOP during object’s DDL • CREATE INDEX • CREATE TABLE … AS SELECT • Partition maintenance commands NOPARALLEL PARALLEL integer

27. PARALLEL Clause: Examples CREATE INDEX ord_customer_ix ON orders (customer_id) NOLOGGING PARALLEL; ALTER TABLE customers PARALLEL 5; ALTER TABLE sales SPLIT PARTITIONsales_q4_2000 AT ('15-NOV-2000') INTO (PARTITION sales_q4_1, PARTITION sales_q4_2) PARALLEL 2;

28. Using Parallelization Hints • The following parallelization hints are used to override existing DOPs. • PARALLEL (table_name, DOP_value) • NOPARALLEL (table_name) • PARALLEL_INDEX (table_name, index, DOP_value) • NOPARALLEL_INDEX (table_name, index) SELECT /*+PARALLEL(SALES,9)*/ * FROM SALES; SELECT /*+PARALLEL_INDEX(c,ic,3)*/ * FROM customers c WHERE cust_city ='CLERMONT';

29. Parallelization Hints SELECT /*+ FULL(s) ORDERED USE_HASH(c) PARALLEL(s) PARALLEL(c) PQ_DISTRIBUTE(c,NONE,BROADCAST) */ c.channel_desc, SUM(amount_sold)FROM sales s, channels cWHERE s.channel_id = c.channel_idGROUP BY c.channel_desc; SORT GROUP BY |P->S|QC (RANDOM) SORT GROUP BY |P->P|HASH HASH JOIN |PCWP| PARTITION RANGE ALL |PCWP| TABLE ACCESS FULL SALES |PCWP| TABLE ACCESS FULL CHANNELS |P->P|BROADCAST ALTER SESSION SET PARALLEL_BROADCAST_ENABLED=TRUE

30. Parallelization Hints • PQ_DISTRIBUTE(<inner table>, <outer distribution>, <inner distribution>) • Possible distribution combinations: • HASH,HASH • NONE,BROADCAST • BROADCAST,NONE • PARTITION,NONE • NONE,PARTITION • NONE,NONE

31. Parallelism and Cost-Based Optimization • Always analyze tables used in parallel by using: • DBMS_STATS package to gather statistics: • DEGREE parameter specifies DOP to be used • If not specified, or set to NULL, the tables dictionary DOP is used. • Use DBMS_STATS.DEFAULT_DEGREE to use default DOP. • Statistics on indexes are not gathered in parallel. • It is also recommended to gather system statistics. • New GATHER AUTO option • ANALYZE commands (always run serially)

32. Summary • In this lesson, you should have learned how to: • Benefit from using parallel operations • Validate system conditions under which to use parallel operations • Control the parallel execution server pool • Read a parallel execution plan

33. Types of Parallel Operations

34. Objectives • After completing this lesson, you should be able to: • List the types of parallel operations • Describe parallel query, parallel DDL, and parallel DML • Determine when partitionwise join is used • Describe locking behavior for parallel DML operations • Explain parallelism and undo • Discuss parallel execution of functions

35. Parallelization Rules Revisited • A SQL statement can be parallelized if: • It includes a parallel hint • Parallelization is forced using the ALTERSESSIONFORCE command • The object operated on is/was declared with a parallel clause (dictionary DOP greater than one) • Not prevented from doing so by the Resource Manager • DOP is determined by looking at referenced objects: • Parallel queries use the largest specified or dictionary DOP. • Parallel DML sets the DOP to the number of partitions of the manipulated object. • Parallel DDL sets the DOP to the one specified by the PARALLEL clause.

36. Enabling Parallel DML/DDL/QUERY • The ALTER SESSION statement enables parallel mode: • Used to override dictionary DOPs with FORCE • QUERY only starting with Oracle 8i R2 ALTER SESSION ENABLE DISABLE FORCE PARALLEL n PARALLEL DML DDL QUERY

37. Enabling Parallel DML/DDL/QUERY • You can use V$SESSION to look at sessions status: • PDML_STATUS • PDDL_STATUS • PQ_STATUS • Values for the columns listed above can be: • ENABLED • DISABLED • FORCED

38. Parallel Query • The various query types that can be parallelized are: • Access methods: • Table scans, index full scans • Partitioned index range scans • Various SQL operations: • GROUP BY, ORDER BY, NOT IN, EXISTS, IN, SELECT DISTINCT, UNION, UNION ALL, MINUS, INTERSECT, CUBE, ROLLUP, aggregates • Join methods: • Nested loop, sort merge • Hash, star transformation, partitionwise join

39. Parallel Partitioned Table Scan • Scan by ROWID for partitioned tables. SELECT /*+ PARALLEL(SALES,9)*/ * FROM SALES; PQ6 PQ4 PQ5 PQ3 PQ7 PQ2 PQ8 PQ1 PQ9 SALES

40. Parallel Partitioned Index Scan • Scans can be performed by partition for partitioned indexes and tables. The hint must contain the table name, index name, and degree of parallelism. SELECT /*+ PARALLEL_INDEX(c,ic,3)*/ * FROM customers c WHERE cust_city = 'MARSEILLE'; PQ1 Nonprefixed index IC Indexed column cust_city PQ2 PQ3

41. Partitionwise Joins QS QS QS QS QS QS Partial partitionwise join Full partitionwise join Partitioned table Query slave Partition

42. Non-Partitionwise Join: Example • Consider the table: • SALES to be hash partitioned on PROD_ID • TIMES to be hash partitioned on CALENDAR_YEAR SELECT * FROM sales s, times t WHERE s.time_id = t.time_id; HASH JOIN P->S QC PARTITION HASH (ALL)[1 --> m] PCWP TABLE ACCESS FULL (s) P->P PARTITION HASH (ALL)[1 --> n] PCWP TABLE ACCESS FULL (t) P->P

43. Partial Partitionwise Join: Example • Consider the table: • SALES to be hash partitioned on TIME_ID • TIMES to be hash partitioned on CALENDAR_YEAR SELECT * FROM sales s, times t WHERE s.time_id = t.time_id; HASH JOIN P->S QC PARTITION HASH (ALL)[1 --> m] PCWP TABLE ACCESS FULL (s) PCWP PARTITION HASH (ALL)[1 --> n] PCWP TABLE ACCESS FULL (t) P->P

44. Full Partitionwise Join: Example • Consider the table: • SALES to be partitioned on TIME_ID • TIMES to be partitioned on TIME_ID • Both tables are equipartitioned. SELECT * FROM sales s, times t WHERE s.time_id = t.time_id; HASH JOIN P->S QC PARTITION HASH (ALL)[1 --> m] PCWP TABLE ACCESS FULL (s) PCWP PARTITION HASH (ALL)[1 --> n] PCWP TABLE ACCESS FULL (t) PCWP

45. Full Partitionwise Join: Example • Consider the table: • SALES to be range partitioned on PROD_ID and to be subpartitioned by hash on TIME_ID • TIMES to be hash partitioned on TIME_ID • Both tables are equipartitioned on hash dimension. SELECT * FROM sales s, times t WHERE s.time_id = t.time_id; HASH JOIN P->S QC PARTITION RANGE (ALL)[1 --> n] PCWP PARTITION HASH (ALL)[1 --> m] PCWP TABLE ACCESS FULL (s) PCWP PARTITION HASH (ALL)[1 --> m] PCWP TABLE ACCESS FULL (t) PCWP

46. Partitionwise Join Compatibility • Number of partitions should be a multiple of DOP. • Partitions should be of equal size. Use preferably: • Hash partitioning or composite partitioning • Number of hash partitions as a power of two • Possible cases for full partitionwise join: • R/S • Range • Hash • List • Composite • Range • Range • N/A • N/A • Range • Hash • N/A • Hash • N/A • Hash • List • N/A • N/A • List • List • Composite • Range • Hash • List • Range, hash, or list

47. Parallel DDL • The parallel DDL statements for nonpartitioned tables and indexes are: • CREATE INDEX • CREATE TABLE ... AS SELECT • ALTER INDEX ... REBUILD • The parallel DDL statements for partitioned tables and indexes are: • CREATE INDEX • CREATE TABLE ... AS SELECT • ALTER TABLE ... MOVE PARTITION • ALTER TABLE ... SPLIT PARTITION • ALTER TABLE ... COALESCE PARTITION • ALTER INDEX ... REBUILD PARTITION • ALTER INDEX ... SPLIT PARTITION

48. Space Management for Parallel DDL • Creating tables or indexes in parallel could lead to: • External fragmentation • Internal fragmentation • Each parallel execution server creates its own temporary segment using specified storage attributes. • Internal fragmentation will almost always happen. • External fragmentation should be taken care of only for dictionary-managed tablespaces: • Temporary segments might be trimmed. • Use MINIMUM EXTENT at tablespace level.

49. Fragmentation and Parallelism PQ2 PQ1 PQ3 PQ4 … … … HWM HWM HWM HWM Internal Fragmentation External Fragmentation HWM … … … Data Unused portion below HWM Free space Unused portion above HWM

50. Creating Indexes in Parallel Nonpartitioned index Object Parallel execution server Index piece Data