1 / 19

Chapter 13 Parallel SQL

Chapter 13 Parallel SQL. Understanding Parallel SQL. Enables a SQL statement to be: Split into multiple threads Each thread processed simultaneously Used with multi-CPU systems Serial method only uses one CPU Only available in Oracle Enterprise Edition. Serial processing.

elmo-ware
Download Presentation

Chapter 13 Parallel SQL

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. Chapter 13Parallel SQL

  2. Understanding Parallel SQL • Enables a SQL statement to be: • Split into multiple threads • Each thread processed simultaneously • Used with multi-CPU systems • Serial method only uses one CPU • Only available in Oracle Enterprise Edition

  3. Serial processing • Only single CPU used for a query SELECT * FROM customers ORDER BY cust_first_name, cust_last_name, cust_year_of_birth; • Single process proceses all rows in above query • Rows fetched • Rows sorted • Rows returned

  4. Parallel processing • Multiple CPU’s can be used for a query SELECT /+* parallel(c,2) */ * FROM customers c ORDER BY cust_first_name, cust_last_name, cust_year_of_birth; • Multiple processes for all rows in above query • Fetching process split into two • At end of fetch, two sort processes are invoked • Then, sorted results combined and returned • The “query coordinator” coordinates the parallelism processing stream

  5. Parallel processing • Restricted to operations needing a scan • Table • Index • Table partition • Can become bottleneck is misused • Shouldn’t be used for non-tuned SQL

  6. Degree of Parallelism • Also known as “DOP” • Defines number of streams that will be created • Number of actual processes 2x or greater than DOP • Data fed between steps • Two steps required to maintain parallel stream • Example statement with below requires 5 processes for DOP of 2 (see. Figure 13-2 p. 398) • Full table scan • ORDER_BY • GROUP_BY

  7. Parallel Slave Pool • Configured with Oracle parameters • PARALLEL_MIN_SERVERS • PARALLEL_MAX_SERVERS • Slaves created as needed for parallel SQL • Slaves shut down after period of inactivity • If more slaves required than maximum allowed • Query DOP may be reduced • Query may run serially • Query may generate error • Query may be delayed until slaves available

  8. Direct Path IO • Normal IO path for parallel operations • Bypasses buffer cache (avoids contention) • Perform IO directly • Used when reading/writing temporary segments • For sorting • For intermediate result sets

  9. When to use Parallelism • Server has multiple CPU’s • Data is spread across multiple disk drives • SQL is long-running or resource-intensive • Long running reports • Large table bulk updates • Index builds/rebuilds on large tables • Analytic or other ad-hoc intensive processing • If above criteria not met, using parallelism may be slower than running serial process • Not suitable for OLTP systems

  10. Configuring Parallelism • Key parameters include • PARALLEL_THREADS_PER_CPU • PARALLEL_DEGREE_POLICY • PARALLEL_ADAPTIVE_MULTI_USER • PARALLEL_IO_CAP • PARALLEL_MAX SERVERS • PARALLEL_MIN_SERVERS • See pp. 407-408 for full list • Default DOP is 2x CPU if not configured

  11. Parallel Hints • Invoked within SQL statements • Examples: SELECT /*+ parallel */ * FROM sales s SELECT /*+ parallel(s) */ * FROM sales s SELECT /*+ parallel(s,8) */ * FROM sales s SELECT /*+ noparallel */ COUNT(*) FROM sales s SELECT /*+ parallel(auto) */ COUNT(*) FROM sales s

  12. Parallel Explain Plans • More complex than serial SQL plans SQL> select /*+ parallel(a,8) */ count(*) 2 from emp a ----------------------------------------------------------- | Id | Operation | Name | Cost (%CPU)| ----------------------------------------------------------- | 0 | SELECT STATEMENT | | 266 (1)| | 1 | SORT AGGREGATE | | | | 2 | PX COORDINATOR | | | | 3 | PX SEND QC (RANDOM) | :TQ10000 | | | 4 | SORT AGGREGATE | | | | 5 | PX BLOCK ITERATOR | | 266 (1)| PLAN_TABLE_OUTPUT ------------------------------------------------------------------------------------------------------------------------------------ | 6 | TABLE ACCESS FULL| EMP | 266 (1)| -----------------------------------------------------------

  13. Parallel Explain Plans (cont.) • PX Block Iterator • First Step • Breaks table up into chunks • PX Send • Indicates data being sent from one process to another • PX Receive • Indicates data being received by one process from another • PX SEND QC • Send operation to the query coordindator • PX COORDINATOR • Indicates query coordinator receiving data from parallel streams and returning it to the SQL statement

  14. Parallel Explain Plans (cont.) • PARALLEL_FROM_SERIAL (or S->P) • Represents a serial bottleneck in a parallel operation • See table 13-1 (p. 411) for full list • Parallel data distribution options • RANGE – Rows distributed based on value range • HASH – Based on hash value • RANDOM – Rows randomly assigned to slave • ROUND ROBIN – Done in circular fashion between slaves

  15. Tracing Parallel Execution • Use DBMS_SESSION to set session identifier • Use DBMS_MONITOR to enable tracing • Run the parallel SQL • Use trcsess utility to create trace file • Analyze results • Should be used as last resort

  16. Other Parallel Statistics • V$PQ_TQSTAT view • Contains information about • Actual DOP used • Data transferred between parallel processes • Limited use: • Only visible within session that executed the parallel SQL • Only shows most recent statement • V$PX_SESSION • Information about slaves currently running SQL • Join to V$SESSION and V$SQL to get all details

  17. Optimizing Parallel Performance • Start with SQL optimized for serial execution • Ensure the SQL is suitable for parallelism • Ensure server is configured for parallelism • Ensure all parts of the execution plan is parallelized • Ensure the DOP is realistic • Monitor actual DOP • Check for workload and data skew

  18. Other Parallel Topics • Parallel Index Lookups • Usually not parallelizable • Can be parallelized on partitioned indexes • Done with PARALLEL_INDEX hint • Parallel DML • Enable with ‘alter session enable parallel dml;’ • Parallel Insert particularly effective • Parallelize both SELECT and INSERT operations • Uses APPEND method (bypasses buffer cache) • Parallel Merge (Merge combines UPDATE & INSERT)

  19. Other Parallel Topics (cont.) • DBMS_PARALLEL_EXECUTE • Introduced in 11g • Can execute operations in smaller chunks • Committed individually • Restartable • Done with PARALLEL_INDEX hint • Parallel DDL • Controlled by parallel clause in the DDL statement • DOP then set at specified degree for subsequent queries

More Related