Parallelize or Perish! Implementing Oracle 11gR2 Parallelism Features for Maximum Performance

1. Parallelize or Perish!Implementing Oracle 11gR2 Parallelism Features for Maximum Performance Jim Czuprynski Zero Defect Computing, Inc. April 19, 2013

2. My Credentials • 30+ years of database-centric IT experience • Oracle DBA since 2001 • Oracle 9i, 10g, 11g OCP • 95+ articles on databasejournal.com and ioug.org • Teach core Oracle DBA courses (G/I+RAC, Exadata, Performance Tuning, Data Guard) • 2009: Oracle Education Partner Trainer of the Year • Speaker at Oracle OpenWorld, IOUG COLLABORATE11, and IOUG COLLABORATE13 • Oracle-centric blog (Generally, It Depends)

3. Our Agenda In this session , we will: • Explore the new parallelism features in Oracle Database 11.2.0.2, especially ADOP and IMPE • Demonstrate how to implement appropriate initialization parameters, hints, and object decoration to influence parallelism for maximum effectiveness • Demonstrate how to use the feature set of the new DBMS_PARALLEL_EXECUTE package to leverage massive parallelism for DML statements

4. So … Why Parallelize? • Because we can • CPU resources aren’t as limited as in the past • Engineered systems make this even more possible • Because we should • Doing twice (or 4X or 8X or 16X) as much work in same amount of time may be the only way to satisfy SLAs • Degrees of parallelism are readily controllable with DBRM • Because we must • “You’ll just have to do more with less.” • Our CIOs and CFOs need to justify CAPEX / OPEX

5. Parallelizing Single Operations

6. Auto Degree of Parallelism (ADOP) • Parallelism is automatically determined when query is parsed • Objects can either be “decorated” with appropriate degrees of parallelism … • … or the optimizer can automatically determine the appropriate parallelism based on resources available • Also possible to limit queries to a maximum degree of parallelism • Services offer ability to parallelize data warehousing application workloads across multiple RAC instances for improved response times • No changes to existing SQL code required!

7. Object “Decoration” • Tables and indexes can now be “decorated” with a desired degree of parallelism • Objects can be decorated during creation, or decoration can be added later, via the ALTER <object type> <object_name> PARALLEL clause: • ADOP considers defined parallelism of decorated objects when constructing a parallel execution plan • Beware: The decorated parallelism degrees are no guarantee of the actual degrees of parallelism that will be used SQL> ALTER TABLE ap.randomized_sortedPARALLEL 4;

8. Parameters Controlling ADOP Five key initialization parameters influence ADOP in Oracle Database 11.2.0.2: • PARALLEL_DEGREE_POLICY • PARALLEL_DEGREE_LIMIT • PARALLEL_MIN_TIME_THRESHOLD • PARALLEL_SERVERS_TARGET • PARALLEL_FORCE_LOCAL Other initialization parameters are now deprecated or retasked as part of ADOP in Oracle Database 11.2.0.2

9. PARALLEL_DEGREE_POLICY PARALLEL_DEGREE_POLICY determines how to apply ADOP: • MANUAL: (default) DBA specifies desired parallelism via object decoration • LIMITED: DOP is derived from elapsed time of most costly operation during the first serial compilation • Default:10 seconds • Can be modified via initialization parameter PARALLEL_MIN_TIME_THRESHOLD • AUTO: Turns on ADOP, statement queuing, and in-memory parallel execution (IMPE)

10. PARALLEL_DEGREE_LIMIT PARALLEL_DEGREE_LIMIT limits the total degree of parallelism (DOP) that any “parallelizable” statement can utilize • CPU (default): CPU resources available via the following formula: threads_per_CPUX total CPUs in the cluster • N > 1: Specific value of one or greater • IO:I/O resource constraints • AUTO: Optimizer automatically determines value

11. PARALLEL_MIN_TIME_THRESHOLD PARALLEL_MIN_TIME_THRESHOLD defines the minimum execution time before which parallelism would provide little benefit to a statement’s execution • AUTO: (default) Oracle determines the optimum threshold value • N > 0: The number of seconds to be used as the minimum threshold value (default: 10 seconds)

12. PARALLEL_SERVERS_TARGET Specifies the number of parallel server processes allowed to run before parallel statement queuing is enforced • Based on following formula: 2 * (PARALLEL_THREADS_PER_CPU * CPU_COUNT * concurrent parallel users) • where concurrent parallel users will be set to: • Automatic Memory Management disabled: 1 • Automatic PGA Management enabled: 2 • Automatic SGA Management enabled: 4

13. PARALLEL_FORCE_LOCAL For Real Application Clustered(RAC) databases only, this determines whether parallel server processes can be distributed across all participating instances in the cluster • FALSE: (Default) Parallel query server processes may be mapped to any node in a clustered environment • TRUE: Parallel query server processes may only be mapped to the instance at which the initial connection was mapped

14. Retasked & Deprecated Parameters As of Oracle Database 11.2.0.2, the following parameters have been retasked or deprecated:

15. Parallelism: Rules of Precedence SQL statement issued Generate serial plan 1st Pass: Compute serial cost 2nd Pass: Compute parallel cost Generate parallel plan via DOP Is estimated elapsed time >PARALLEL_MIN_TIME_THRESHOLD? Yes! Is estimated parallel elapsed time < estimated serial elapsed time? No. No. Yes! Execute serial plan Execute parallel plan

16. What About Optimizer Hints? As of 11.2.0.2, the scope of PARALLEL hints is applied at the statement level: • Hints now supercede parallelism that’s been specified at the table or object level • Initialization parameter PARALLEL_DEGREE_POLICY also dictates different handling of parallelism: • If set to MANUAL (the default), then PARALLELhints dictate the degree of parallelism for the specific object • If set to LIMITED, then PARALLELhints dictate degree of parallelism for the statement itself and not the object

17. Statement-Level PARALLEL Hints Some examples of this new PARALLEL hinting: • Set degrees of parallelism for tables AP.INVOICES and AP.INVOICE_ITEMS to 3 and none, respectively: ALTER TABLE ap.invoicesPARALLEL 3; ALTER TABLE ap.invoice_itemsNOPARALLEL; • If PARALLEL_DEGREE_POLICY = MANUAL, then the +PARALLEL hint is applied to the object: SELECT /*+ PARALLEL(invoices 8) */ I.vendor_id, L.extd_amt FROM ap.invoices I, ap.invoice_items L WHERE I.invoice_id = L.invoice_id; • If PARALLEL_DEGREE_POLICY = LIMITED, then the +PARALLEL hint is applied to the statement: SELECT /*+ PARALLEL(8) */ I.vendor_id, L.extd_amt FROM ap.invoices I, ap.invoice_items L WHERE I.invoice_id = L.invoice_id;

18. ADOP and IMPE:Parallelism In Combat

19. ADOP: Prerequisites DBMS_RESOURCE_MANAGER.CALIBRATE_IO procedure must be run before implementing ADOP! SQL> COL start_dtm FORMAT A19 HEADING "Start Time“ COL end_dtm FORMAT A19 HEADING "End Time“ COL nbr_disks FORMAT 99999 HEADING "# of|Phys|Disks“ COL max_iops FORMAT 99999999 HEADING "Maximum|IOPS“ COL max_mbps FORMAT 99999999 HEADING "Maximum|Thruput|(MBPS)“ COL max_pmbps FORMAT 99999999 HEADING "Max Large|I/O Rqsts|PerPrcs“ COL latency FORMAT 99999999 HEADING "Latency“ TTITLE "Results of CALIBRATE_IO Operations|(from DBA_RSRC_IO_CALIBRATE)" SELECT TO_CHAR(start_time, 'yyyy-mm-dd hh24:mi:ss') start_dtm ,TO_CHAR(end_time, 'yyyy-mm-dd hh24:mi:ss') end_dtm ,num_physical_disksnbr_disks ,max_iops ,max_mbps ,max_pmbps ,latency FROM dba_rsrc_io_calibrate ORDER BY 1,2; TTITLE OFF SET SERVEROUTPUT ON DECLARE nIOPS INTEGER; nMBPS INTEGER; nLatency INTEGER; BEGIN DBMS_RESOURCE_MANAGER.CALIBRATE_IO ( num_physical_disks => 36 ,max_latency => 10 ,max_iops => nIOPS ,max_mbps => nMBPS ,actual_latency => nLatency); DBMS_OUTPUT.PUT_LINE ('Maximum Througput (MBPS): ' || nMBPS); DBMS_OUTPUT.PUT_LINE ('Maximum IOPS: ' || nIOPS); DBMS_OUTPUT.PUT_LINE ('Actual Latency(ms): ' || nLatency); END; / Maximum Througput (MBPS): 789 Maximum IOPS: 858 Actual Latency(ms): 8 Results of CALIBRATE_IO Operations (from DBA_RSRC_IO_CALIBRATE) # of Maximum Max Large Phys Maximum Thruput I/O Rqsts Start Time End Time Disks IOPS (MBPS) Per Prcs Latency ---------- ---------- ------- --------- --------- --------- --------- 2013-03-24 2013-03-24 36 858 789 250 8 21:49:26 22:07:26

20. Example: ADOP In Action SELECT /*+ MONITOR PARALLEL OPT_PARAM('cell_offload_processing' 'FALSE') */ DISTINCT C.cust_id,C.cust_last_name ,P.prod_id ,P.prod_name ,S.channel_id ,S.promo_id ,S.quantity_sold ,S.amount_sold FROM sh.sales S, sh.customers C, sh.products P WHERE S.cust_id = C.cust_id AND S.prod_id = P.prod_id AND ((C.cust_last_name LIKE 'Smith%') OR (S.amount_sold BETWEEN 1000 AND 5000)) ORDER BY 8, 1 DESC, 3, 4 DESC, 5, 6, 2 DESC, 7; Execution Plan ---------------------------------------------------------- Plan hash value: 1647334853 ---------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time | ---------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 29M| 1755M| | 494K (1)| 00:00:16 | | 1 | PX COORDINATOR | | | | | | | | 2 | PX SEND QC (ORDER) | :TQ10003 | 29M| 1755M| | 248K (1)| 00:00:08 | | 3 | SORT UNIQUE | | 29M| 1755M| 2237M| 248K (1)| 00:00:08 | | 4 | PX RECEIVE | | 29M| 1755M| | 2440 (9)| 00:00:01 | | 5 | PX SEND RANGE | :TQ10002 | 29M| 1755M| | 2440 (9)| 00:00:01 | |* 6 | HASH JOIN | | 29M| 1755M| | 2440 (9)| 00:00:01 | . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . | 14 | PX SEND BROADCAST | :TQ10001 | 72 | 2160 | | 2 (0)| 00:00:01 | | 15 | PX BLOCK ITERATOR | | 72 | 2160 | | 2 (0)| 00:00:01 | | 16 | TABLE ACCESS STORAGE FULL| PRODUCTS | 72 | 2160 | | 2 (0)| 00:00:01 | | 17 | BUFFER SORT | | 55500 | 704K| | 1967 (1)| 00:00:01 | | 18 | PX BLOCK ITERATOR | | 55500 | 704K| | 225 (1)| 00:00:01 | | 19 | TABLE ACCESS STORAGE FULL | CUSTOMERS | 55500 | 704K| | 225 (1)| 00:00:01 | ---------------------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 6 - access("S"."CUST_ID"="C"."CUST_ID") filter("C"."CUST_LAST_NAME" LIKE 'Smith%' OR "S"."AMOUNT_SOLD">=1000 AND "S"."AMOUNT_SOLD"<=5000) Note ----- - automatic DOP: Computed Degree of Parallelism is 2 ADOP was successfully used for this query as indicated in the EXPLAIN PLAN NOTES section

21. In-Memory Parallel Execution (IMPE) • Leverages available buffer cache memory of multiple 11gR2 Real Application Cluster (RAC) instances to scale out query execution • For queries that can take advantage of parallelism, IMPE provides methods to subdivide objects required to answer a query across multiple database instance buffer caches • The result: A significant reduction in total physical I/O required to retrieve all required blocks • The more database instances available, the larger the potential “scale out” capability • No changes to existing code!

22. Monitoring Parallel Operations

23. Monitoring via SQL Monitor

24. SQL Monitor: IMPE In Action SET LONG 10000000 SET LONGCHUNKSIZE 10000000 SET LINESIZE 230 SET PAGESIZE 10000 SELECT DBMS_SQLTUNE.REPORT_SQL_MONITOR(sql_id => '74r61jam587ns', report_level => 'ALL') FROM dual; `Parallel Execution Details (DOP=2 , Servers Allocated=4) ` Instances : 2 ` `======================================================================================== `| Instance | Name | Type | Server# | Elapsed | Queuing | Cpu | IO | `| | | | | Time(s) | Time(s) | Time(s) | Waits(s) | `======================================================================================== `| 1 | PX Coordinator | QC | | 2.41 | 0.01 | 2.06 | | `| 2 | p000 | Set 1 | 1 | 3.84 | | 3.16 | 0.67 | `| | | | | | | | | `| 1 | p000 | Set 1 | 2 | 4.66 | | 3.90 | 0.76 | `| 2 | p001 | Set 2 | 1 | 13 | | 13 | | `| 1 | p001 | Set 2 | 2 | 10 | | 10 | | `======================================================================================== . . . =================================================================================================== | Id | Operation | Name | Rows | Cost | Time | Start | | | | | (Estim) | | Active(s) | Active | =================================================================================================== | -> 0 | SELECT STATEMENT | | | | 240 | +14 | | -> 1 | PX COORDINATOR | | | | 253 | +1 | | | | | | | | | | -> 2 | PX SEND QC (ORDER) | :TQ10003 | 29M | 248K | 238 | +16 | | -> 3 | SORT UNIQUE | | 29M | 248K | 252 | +2 | | | | | | | | | | 4 | PX RECEIVE | | 29M | 2440 | 104 | +2 | | 5 | PX SEND RANGE | :TQ10002 | 29M | 2440 | 13 | +2 | | 6 | HASH JOIN | | 29M | 2440 | 13 | +2 | | 10 | TABLE ACCESS STORAGE FULL | SALES | 919K | 294 | 1 | +2 | . . . | 18 | PX BLOCK ITERATOR | | 55500 | 225 | 1 | +2 | | 19 | TABLE ACCESS STORAGE FULL | CUSTOMERS | 55500 | 225 | 1 | +2 | =================================================================================================== `

25. Parallelizing Batch Operations

26. Parallelizing Batch Operations DBMS_PARALLEL_EXECUTE is a new Oracle-supplied package in 11.2.0.2: • Extends the benefits of DOP to batch SQL • Leverages parallel processing by “chunking” work into smaller executable batched tasks • Tasks are implicitly constructed and executed via DBMS_SCHEDULER • Three “chunking” methods provided: • CREATE_CHUNKS_BY_ROWID • CREATE_CHUNKS_BY_NUMBER_COL • CREATE_CHUNKS_BY_SQL

27. “Chunking” Parallel Batch Operations (1) DECLARE vc_chunk_sql VARCHAR2(1000); vc_sql_stmt VARCHAR2(1000); n_try NUMBER; n_status NUMBER; BEGIN DBMS_PARALLEL_EXECUTE.CREATE_TASK ( task_name => 'PET_2' ,comment => 'Example #2: DBMS_PARALLEL_EXECUTE.CREATE_CHUNKS_BY_SQL' ); vc_chunk_sql := 'SELECT (DECODE(MOD(invoice_id,1000),0,invoice_id,0) - 999), ' || '(DECODE(MOD(invoice_id,1000),0,invoice_id,0)) FROM ap.invoices ' || 'WHERE DECODE(MOD(invoice_id,1000),0,invoice_id,0) > 0 ORDER BY 1'; DBMS_PARALLEL_EXECUTE.CREATE_CHUNKS_BY_SQL('PET_2', vc_chunk_sql, false); vc_sql_stmt := 'UPDATE /*+ ROWID (dda) */ ap.invoices SET taxable_amt = (balance_due * 0.20) WHERE invoice_id BETWEEN :start_id AND :end_id';

28. “Chunking” Parallel Batch Operations (2) DBMS_PARALLEL_EXECUTE.RUN_TASK( task_name => 'PET_2' ,sql_stmt => vc_sql_stmt ,language_flag => DBMS_SQL.NATIVE ,parallel_level => 10 ,job_class => 'DEFAULT' ); n_try := 0; n_status := DBMS_PARALLEL_EXECUTE.TASK_STATUS(task_name => 'PET_2'); WHILE(n_try < 2 and n_status != DBMS_PARALLEL_EXECUTE.FINISHED) LOOP n_try := n_try + 1; DBMS_PARALLEL_EXECUTE.RESUME_TASK(task_name => 'PET_2'); n_status := DBMS_PARALLEL_EXECUTE.TASK_STATUS(task_name => 'PET_2'); END LOOP; DBMS_PARALLEL_EXECUTE.DROP_TASK(task_name => 'PET_2'); END; /

29. Monitoring Parallelized Batches COL chunk_id FORMAT 99999 HEADING "Chunk|#" COL task_owner FORMAT A08 HEADING "Task|Owner" COL task_name FORMAT A08 HEADING "Task Name" COL status FORMAT A12 HEADING "Status" COL start_rowid FORMAT A18 HEADING "Start|ROWID" COL end_rowid FORMAT A18 HEADING "End|ROWID" COL start_id FORMAT 99999 HEADING "Start|ID" COL end_id FORMAT 99999 HEADING "End|ID" COL start_dtm FORMAT A21 HEADING "Start Time" COL end_dtm FORMAT A21 HEADING "End Time" COL job_name FORMAT A15 HEADING "Job Name" COL task_comment FORMAT A20 HEADING "Task Comment" WRAP COL error_code FORMAT 99999 HEADING "Error|Code" COL error_message FORMAT A40 HEADING "Error Message" WRAP TTITLE "Chunked Operations Controlled Via DBMS_PARALLEL_EXECUTE |(From DBA_PARALLEL_EXECUTE_CHUNKS)" SELECT chunk_id ,task_owner ,task_name ,status ,start_rowid ,end_rowid ,start_id ,end_id ,TO_CHAR(start_ts, 'yyyy-mm-dd.hh24:mi:ss') start_dtm ,TO_CHAR(end_ts, 'yyyy-mm-dd.hh24:mi:ss') end_dtm ,job_name ,error_code ,error_message FROM dba_parallel_execute_chunks ORDER BY chunk_id ; TTITLE OFF Chunked Operations Controlled Via DBMS_PARALLEL_EXECUTE (From DBA_PARALLEL_EXECUTE_CHUNKS) Chunk Task Start End # Owner Task Nam Status ID ID Start Time End Time Job Name ------ -------- -------- --------------- ------ ------ --------------------- --------------------- --------------- 211 SYS PET_2 PROCESSED 1 1000 2013-04-08.22:52:52 2013-04-08.22:52:52 TASK$_1932_2 212 SYS PET_2 PROCESSED 1001 2000 2013-04-08.22:52:52 2013-04-08.22:52:52 TASK$_1932_10 213 SYS PET_2 PROCESSED 2001 3000 2013-04-08.22:52:52 2013-04-08.22:52:52 TASK$_1932_2 214 SYS PET_2 PROCESSED 3001 4000 2013-04-08.22:52:52 2013-04-08.22:52:52 TASK$_1932_2 215 SYS PET_2 PROCESSED 4001 5000 2013-04-08.22:52:52 2013-04-08.22:52:52 TASK$_1932_2 216 SYS PET_2 PROCESSED 5001 6000 2013-04-08.22:52:52 2013-04-08.22:52:52 TASK$_1932_2 217 SYS PET_2 PROCESSED 6001 7000 2013-04-08.22:52:52 2013-04-08.22:52:52 TASK$_1932_10 218 SYS PET_2 PROCESSED 7001 8000 2013-04-08.22:52:52 2013-04-08.22:52:52 TASK$_1932_10 219 SYS PET_2 PROCESSED 8001 9000 2013-04-08.22:52:52 2013-04-08.22:52:52 TASK$_1932_2 220 SYS PET_2 PROCESSED 9001 10000 2013-04-08.22:52:52 2013-04-08.22:52:52 TASK$_1932_10 221 SYS PET_2 PROCESSED 10001 11000 2013-04-08.22:52:52 2013-04-08.22:52:52 TASK$_1932_2 222 SYS PET_2 PROCESSED 11001 12000 2013-04-08.22:52:52 2013-04-08.22:52:52 TASK$_1932_10 223 SYS PET_2 PROCESSED 12001 13000 2013-04-08.22:52:52 2013-04-08.22:52:52 TASK$_1932_2 224 SYS PET_2 PROCESSED 13001 14000 2013-04-08.22:52:52 2013-04-08.22:52:52 TASK$_1932_10 225 SYS PET_2 PROCESSED 14001 15000 2013-04-08.22:52:52 2013-04-08.22:52:52 TASK$_1932_4 Tasks Controlled Via DBMS_PARALLEL_EXECUTE (From DBA_PARALLEL_EXECUTE_TASKS) Task Chunk Job Owner Task Nam Type Status Pfx SQL Statement Text Flag Level Class -------- -------- ------------ -------- ------ --------------------------------------- ------ ------ -------- SYS PET_2 NUMBER_RANGE FINISHED TASK$ UPDATE /*+ ROWID (dda) */ ap.invoices SE 1 10 DEFAULT _1932 T taxable_amt = (balance_due * 0.20) WHE COL task_owner FORMAT A08 HEADING "Task|Owner" COL task_name FORMAT A08 HEADING "Task Name" COL chunk_type FORMAT A12 HEADING "Chunk|Type" COL status FORMAT A08 HEADING "Status" COL table_owner FORMAT A08 HEADING "Table|Owner" COL table_name FORMAT A24 HEADING "Table Name" COL job_class FORMAT A08 HEADING "Job|Class" COL number_column FORMAT A12 HEADING "Number|Column" COL sql_stmt FORMAT A40 HEADING "SQL Statement Text" WRAP COL job_prefix FORMAT A05 HEADING "Job|Pfx" COL language_flag FORMAT 99999 HEADING "Lang|Flag" COL parallel_level FORMAT 99999 HEADING "Para-|llel|Level" TTITLE "Tasks Controlled Via DBMS_PARALLEL_EXECUTE |(From DBA_PARALLEL_EXECUTE_TASKS)" SELECT task_owner ,task_name ,chunk_type ,status ,table_owner ,table_name ,number_column ,job_prefix ,sql_stmt ,language_flag ,parallel_level ,job_class FROM dba_parallel_execute_tasks ORDER BY task_owner, task_name; TTITLE OFF

30. Parallelism: Conclusions Parallelism in Oracle 11.2.0.2 Database is … • Easier to configure, with less guesswork • Leverages “decoratability” of objects • Automatically applied (once it’s configured properly) • Available for single statements or batch operations • IMPE leverages available power of RAC instances • Easier to monitor, especially with SQL Monitor

31. Thank You For Your Kind Attention Please fill out a session evaluation form! • Session P52 • Parallelize or Perish!Implementing Oracle 11gR2 Parallelism Features for Maximum Performance • If you have any questions or comments, feel free to: • E-mail me at jczuprynski@zerodefectcomputingcom • Follow my blog (Generally, It Depends): http://jimczuprynski.wordpress.com • Follow me on Twitter (@jczuprynski) • Connect with me on LinkedIn (Jim Czuprynski)