1 / 42

Extreme Performance with Oracle Database 11g and In-Memory Parallel Execution

Extreme Performance with Oracle Database 11g and In-Memory Parallel Execution. Agenda . <Insert Picture Here>. Introduction to Parallel Execution Automatic Degree Of Parallelism Parallel Statement Queuing In Memory Parallel Execution Summary. <Insert Picture Here>.

miyo
Download Presentation

Extreme Performance with Oracle Database 11g and In-Memory Parallel Execution

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. Extreme Performance with Oracle Database 11g and In-Memory Parallel Execution

  2. Agenda <Insert Picture Here> • Introduction to Parallel Execution • Automatic Degree Of Parallelism • Parallel Statement Queuing • In Memory Parallel Execution • Summary

  3. <Insert Picture Here> Introduction to Parallel Execution

  4. How Parallel Execution works When user issues a parallel SQL statement the background process becomes the Query Coordinator User QC gets parallel servers from global pool and distributes the work to them Parallel servers communicate among themselves & the QC using messages that are passed via memory buffers in the shared pool Parallel servers - individual sessions that perform work in parallel Allocated from a pool of globally available parallel server processes & assigned to a given operation Background process is spawned User connects to the database

  5. Query Coordinator Parallel Servers do majority of the work Parallel Execution Plan SELECT c.cust_name, s.purchase_date, s.amount FROM sales s, customers c WHERE s.cust_id = c.cust_id;

  6. Parallel Execution of a Query SELECT c.cust_name, s.date, s.amount FROM sales s, customers c WHERE s.cust_id = c.cust_id; Consumers Producers

  7. Consumers Consumers Query Coordinator Producers Producers Producers and Consumer in the execution plan

  8. Full scan of the sales table PQ 1 PQ 2 PQ 3 Parallel Execution of a Scan • Data is divided into Granules • block range or partition • Each Parallel Server is assigned one or more Granules • No two Parallel Servers ever contend for the same Granule • Granules are assigned so that the load is balanced across all Parallel Servers • Dynamic Granules chosen by the optimizer • Granule decision is visible in execution plan

  9. Identifying Granules of Parallelism during scans in the plan

  10. Enabling Parallel Execution There are three ways to enable parallel Execution • Enable the table(s) for parallel execution: alter table sales parallel ; alter table customers parallel ; • Use a parallel hint select /*+ parallel(c) parallel(s) */ c.state_province, sum(s.amount) revenue from customers c, sales s where s.customer_id = c.id and s.purchase_date=to_date('01-JAN-2007','DD-MON-YYYY') and c.country = 'United States' group by c.state_province; • Use alter session force parallel query ;

  11. Controlling Parallel Execution on RAC Use RAC Services Create two services Srvctl add service –d database_name -s ETL -r sid1, sid2 Srvctl add service –d database_name -s AHOC -r sid3, sid4 Ad-Hoc queries ETL Note:New Parameter to force a parallel statement to run on just node the query was issued on called PARALLEL_FORCE_LOCAL default FALSE

  12. How could we enhance Parallel Execution? Current Issues • Difficult to determine ideal DOP for each table without manual tuning • One DOP does not fit all queries touching an object • Not enough PX server processes can result in statement running serial • Too many PX server processes can thrash the system • Only uses IO resources Solution • Oracle automatically decides if a statement • Executes in parallel or not and what DOP it will use • Can execute immediately or will be queued • Will take advantage of aggregated cluster memory or not

  13. <Insert Picture Here> Automatic Degree of Parallelism

  14. Automatic Degree of Parallelism Business Requirement Parallelism is completely manual Tuning typically required to determine ideal DOP Generally reserved for well-defined workload(large SQL) One DOP does not fit all queries touching an object • Solution • Oracle automatically decides if a statement • Executes in parallel or not • What DOP the statement will use

  15. Automatic Degree of ParallelismAuto DOP Statement with elapse times less than the threshold go serial Statement with elapse times greater than threshold are candidates for Parallel Optimizer derives the DOP for the statement based on resource requirements for all scans operations Applies to all types of statements Query, DML, or DDL Explain plan has been enhanced to show DOP selected

  16. How Auto Degree of Parallelism works If estimated time greater than threshold Optimizer determines ideal DOP Actual DOP = MIN(PARALLEL_DEGREE_LIMIT, ideal DOP) If estimated time less than threshold Statement executes in parallel Statement executes serially Statement is hard parsed And optimizer determines the execution plan SQLstatement

  17. Controlling Auto DOP Controlled by two init.ora parameters: PARALLEL_DEGREE_POLICY Controls whether or not auto DOP will be used Default is MANUAL which means no Auto DOP Set to AUTO to enable auto DOP PARALLEL_MIN_TIME_THRESHOLD Controls which statements are candidate for parallelism Default is 10 seconds PARALLEL_DEGREE_LIMIT Controls maximum DOP that can be used Default is CPU meaning DEFAULT DOP

  18. Explain plan enhancement for Auto DOP PLAN_TABLE_OUTPUT Note - Computed Degree of Parallelism is 16 because of parallel threshold

  19. <Insert Picture Here> Parallel Statement Queuing

  20. Parallel Statement Queuing Business Requirement With the introduction of Auto DOP More statements will run in parallel Possible to exhaust all parallel execution server processes Potential system thrashing due to too many processes • Solution • Parallel Statement Queuing • Oracle automatically decides if a statement can execute immediately or not • Prevents serializing parallel queries when parallel servers are not available • Prevents system thrashing

  21. Parallel Statement Queuing When a parallel statement starts checks if PX servers are available Let it run if there are enough PX servers available Queue the statement if there are not enough PX servers available Monitors RAC-wide availability of PX servers Adaptive to dynamic environments Services The service your session belongs to determines the limits on queuing Cluster reconfiguration Queue is aware of nodes leaving and joining the cluster and adjusts the limits accordingly

  22. How Parallel Statement Queuing Works When the required number of parallel servers become available the first stmt on the queue is dequeued and executed 128 16 32 64 8 128 16 32 64 Statement is parsed and oracle automatically determines DOP SQLstatements If not enough parallel servers available queue the statement FIFO Queue If enough parallel servers available execute immediately

  23. Controlling Parallel Statement Queuing Enabled when PARALLEL_DEGREE_POLICY is set to AUTO The Statement queue is enforced with a strict FIFO policy PARALLEL_SERVER_TARGET indicates how many PX servers are available to run queries before queuing kicks-in Default values 4 X PARALLEL_THREADS_PER_CPU X CPU_COUNT This a soft limit and does not replace PARALEL_MAX_SERVERS Parallel Max Server 160 Parallel Server Target 64 CPU Count 8 On an 8 CPU system Total PX servers available PX server 1- 64 available to run queries before queuing kicks in

  24. Controlling Parallel Statement Queuing Two new hints To by-passes parallel statement queuing SELECT /*+ NO_STMT_QUEUING */ col1 FROM foo; To delay statement execution until resources are available without having PARALLEL_DEGREE_POLICY is set to AUTO SELECT /*+ STMT_QUEUING */ col1 FROM foo; V$SQL_PLAN_MONITOR has a new status value for SQL that is queued SELECT s.sql_id, s.sql_text FROM v$SQL_MONITOR m, v$SQL s WHERE m.status='QUEUED’ AND m.sql_id = s.sql_id; Two new wait events PX Queuing: Statement queue Indicates the first query in the queue ENQ JX SQL statement queue All other queries in the queue wait on this enqueue

  25. Monitoring Statement Queuing in EM Click on the SQL ID for more info Clock symbol indicated a queued statement Awaiting screen shot from EM

  26. Monitoring Statement Queuing in EM Wait event indicates stmt is at the head of the queue

  27. Monitoring Statement Queuing in EM Wait event indicates stmt is queued

  28. <Insert Picture Here> In-Memory Parallel Execution

  29. In-Memory Parallel Execution Business Requirement Traditionally Parallel Execution takes advantage of the IO capacity of a system Disk speeds are not keeping up with Moore’s law while CPU and Memory are • Solution • In-Memory Parallel Execution harness the memory capacity of the entire system • Scan data nearly 10 X faster than scanning from disk

  30. Prior to Oracle Database 11gR2Parallel Execution and the buffer cache

  31. Prior to Oracle Database 11gR2Parallel Execution and the buffer cache

  32. How In-Memory Parallel Execution Works Detect if the object fits in the aggregated buffer cache of the cluster If so, distribute & affinitizes the blocks among the nodes and make PQ aware of the affinity If not, continue to by-pass the buffer cache and read directly from disk Subsequent access to the object will be conducted only by PX servers on the node to each the data was affinitized

  33. In-Memory Parallel Execution

  34. How In-Memory Parallel Execution Works in detail Decision to use the buffer cache is based on set of heuristics including Ratio between buffer cache size and object size Frequency at which the object is accessed How much the object changes between accesses In RAC fragments of the object are affinitized in the buffer cache on each of the active instances Affinity is based on FileNumber and ExtentNumber unless hash partitioned Automatically prevents multiple instances reading the same data from disk Only PX process on the same RAC node can access each of the fragments of the object

  35. How In-Memory Parallel Execution Works Table is a good candidate for In-Memory Parallel Execution Fragments of Table are read into each node’s buffer cache Table is extremely small Table is extremely Large Read into the buffer cache on any node Only parallel server on the same RAC node will access each fragment Always use direct read from disk Determine the size of the table being looked at SQLstatement

  36. Controlling In-Memory Parallel Execution Controlled by PARALLEL_DEGREE_POLICY Active only when set to AUTO No way to turn it off

  37. <Insert Picture Here> Summary

  38. New Parallel Execution Init.ora Parameters

  39. Gradually introduce Auto Parallel Execution • PARALLEL_DEGREE_POLICY has three possible modes • Manual • As before, DBA must manually specify all aspects of parallelism • No AUTO DOP, Stmt Queuing, In-Memory Parallel Execution • Useful for well-understood existing applications • Limited • Restricted AUTO DOP for queries with tables decorated with default PARALLEL • No Stmt Queuing, In-Memory Parallel Execution • Useful in a mixed-world environment when a limited number of statements would benefit from parallel execution • Auto • All qualified statements subject to executing in parallel • Statements can be queued • IN-memory PQ available • Useful when deploying new applications in 11g that would benefit from parallel execution

  40. For More Information search.oracle.com Parallel Execution Or http://www.oracle.com/technology/products/bi/db/11g/pdf/twp_parallel_execution_fundamentals_11gr2.pdf

  41. Q & A

More Related