1 / 29

Parallelism Concepts

Parallelism Concepts. Objectives. After completing this lesson, you should be able to do the following: Explain the benefit of using parallel operations Describe system conditions under which to use parallel operations Describe how parallel operations work

aquila-sanford
Download Presentation

Parallelism Concepts

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Parallelism Concepts

  2. Objectives • After completing this lesson, you should be able to do the following: • Explain 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. 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

  5. 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

  6. 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.

  7. 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

  8. 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

  9. How Parallel ExecutionServers Communicate • Rows distribution: • PARTITION • HASH • RANGE • BROADCAST • QC(RANDOM) QC Parallel execution server set 1 Parallel execution server set 2 DOP=3

  10. Degree of Parallelism (DOP) • Degree of parallelism 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.

  11. 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

  12. 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

  13. OTHER_TAG Column • Interpretation • OTHER_TAG • 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

  14. Serial and Parallel Execution Plans Serial plan SELECT cust_city,sum(amount_sold) FROM sales s, customers c WHERE s.cust_id=c.cust_id GROUP BY cust_city; HASH GROUP BY HASH JOIN TABLE ACCESS FULL CUSTOMERS PARTITOION RANGE ALL TABLE ACCESS FULL SALES Parallel plan 13. PX COORDINATOR 12. PX SEND QC P->S QC(RANDOM) 11.HASH GROUP BY PCWP 10. PX RECEIVE PCWP 9. PX SEND P->P HASH 8.HASH GROUP BY PCWP 7.HASH JOIN PCWP 6. PX RECEIVE PCWP 5. PX SEND P->P (BROADCAST) 4. PX BLOCK ITERATOR PCWP 3. TABLE ACCESS FULL CUSTOMERS PCWP 2. PX BLOCK ITERATOR PCWP 1. TABLE ACCESS FULL SALES PCWP

  15. 13 12 11 10 1 2 3 4 5 6 7 9 8 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 Block Iterator PX Receive PX Send Hash PX Send Broadcast Full Scan Sales PX Block Iterator Hash Group By Full Scan Customers

  16. Parallel Plan Interpretation PX Coordinator PX Send QC Set 1 Hash Group By Hash Join PCWP Set 1 PX Receive PX Block Iterator PX Receive PX Send Hash PX Send Broadcast Full Scan Sales PX Block Iterator Hash Group By Full Scan Customers

  17. Parallel Plan Interpretation PX Coordinator P to P PX Send QC Set 1 Hash Group By Hash Join :TQ10 PX Receive PX Receive PX Block Iterator Set 2 PX Send Hash Full Scan Sales PX Send Broadcast PX Block Iterator Hash Group By Full Scan Customers

  18. Parallel Plan Interpretation PX Coordinator P to P PX Send QC Set 1 Set 2 Hash Group By Hash Join :TQ11 PX Receive PX Receive PX Block Iterator Set 1 PX Send Hash Full Scan Sales PX Send Broadcast PX Block Iterator Hash Group By Full Scan Customers PCWP

  19. Parallel Plan Interpretation P to S PX Coordinator Set 2 :TQ12 PX Send QC Hash Group By Hash Join PX Receive PX Receive PX Block Iterator PX Send Hash Full Scan Sales PX Send Broadcast PX Block Iterator Hash Group By Full Scan Customers

  20. 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.

  21. 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.

  22. 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

  23. 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;

  24. 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

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

  26. 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';

  27. 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) • 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.

  28. 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

  29. Practice 7: Overview • This practice covers the following topics: • Manipulating the parallel execution server pool • Determining the value of the default degree of parallelism • Changing the dictionary DOP of a table using the PARALLEL clause

More Related