Parallel Execution With Oracle Database 12c

Transcript

1. Parallel Execution With Oracle Database 12c Ivica Arsov
2. Ivica Arsov Database Consultant • Based in Skopje, Macedonia • Oracle Certified Master 12c & 11g • Oracle ACE • Blogger • Founder of MKOUG Twitter: @IvicaArsov Blog: https://iarsov.com 2
3. ABOUT PYTHIAN Pythian’s 400+ IT professionals help companies adopt and manage disruptive technologies to better compete 3
4. AGENDA Parallel execution basics Parallel execution administration Data distribution methods (including bloom filters) Blocking and non-blocking operations 4
5. Parallel execution basics
6. The basic idea of parallelism “Parallel computing is a type of computation in which many calculations or the execution of processes are carried out simultaneously.” source: Wikipedia 6
7. The basic theory of “divide and conquer” • “Divide and conquer” algorithms are adapted for multi-processor architectures • Problem is break down into more than one sub-problem of same or related type • The solutions of each sub-problem are combined to give a solution to the original problem • Oracle’s technique: divide the data in granules and do the processing 7
8. Understanding the granules • The basic unit of work • Block range granules • Partitions granules • Partition granules limit the (DoP) PX servers used. Every partition is scanned by only one PX server. 8
9. What can be parallelized ? • Table scans, index fast full scans, partitioned index range scans and tables • Nested loops, hash joins, sort merge, star transformation • Queries and subqueries • DDL and DML statements INSERT, UPDATE, DELETE, CTAS, CREATE/REBUILD INDEX, MOVE/SPLIT/COALESCE PARTITION 9
10. Parallel execution terminology • DoP – number of server processes used for single operation • Default DoP: PARALLEL_THREADS_PER_CPU * CPU_COUNT * INSTANCE_COUNT • Producer and consumer model = 2xDoP Acronym Description QC Query coordinator DoP Degree of parallelism TQ Table queue DFO Data flow operation DFO Tree Data flow operation tree 10
11. Understanding the role of query coordinator (QC) • The server (foreground) process becomes the query coordinator • The DoP is determined before actual query execution • QC assigns each of the PX slaves with work • Starting 12c, one of the available PX servers will execute serial steps from the execution plan • Responsible to deliver the result to the client process 11
12. Understanding the producer and consumer model select customers.column from customers, sales where customers.cust_id = sales.cust_id 12
13. Understanding the producer and consumer model … continued S1 S3 S4 S2 Producers Consumers CUSTOMERS 13
14. Understanding the producer and consumer model … continued S1 S2 S3 S4 SALES S2 S1 CUSTOMERS 14
15. Understanding the producer and consumer model … continued CUSTOMERS S1 S2 S3 S4 SALES S2 S1 QC 15
16. ------------------------------------------------------------------------------------------------ | Id | Operation | Name | Rows | Cost (%CPU)| TQ |IN-OUT| PQ Distrib | ------------------------------------------------------------------------------------------------ | 0 | SELECT STATEMENT | | 918K| 165 (2)| | | | | 1 | PX COORDINATOR | | | | | | | | 2 | PX SEND QC (RANDOM) | :TQ10002 | 918K| 165 (2)| Q1,02 | P->S | QC (RAND) | |* 3 | HASH JOIN BUFFERED | | 918K| 165 (2)| Q1,02 | PCWP | | | 4 | PX RECEIVE | | 55500 | 74 (0)| Q1,02 | PCWP | | | 5 | PX SEND HASH | :TQ10000 | 55500 | 74 (0)| Q1,00 | P->P | HASH | | 6 | PX BLOCK ITERATOR | | 55500 | 74 (0)| Q1,00 | PCWC | | | 7 | TABLE ACCESS FULL| CUSTOMERS | 55500 | 74 (0)| Q1,00 | PCWP | | | 8 | PX RECEIVE | | 918K| 90 (2)| Q1,02 | PCWP | | | 9 | PX SEND HASH | :TQ10001 | 918K| 90 (2)| Q1,01 | P->P | HASH | | 10 | PX BLOCK ITERATOR | | 918K| 90 (2)| Q1,01 | PCWC | | | 11 | TABLE ACCESS FULL| SALES | 918K| 90 (2)| Q1,01 | PCWP | | 16
17. Producer and consumer model impact on DoP What we’ve learned from previous example ? • DoP represents the number of processes (PX slaves) associated with a single operation (i.e. table scan, hash join etc.) • Due to the producer and consumer model, 2xDoP might be allocated. The DoP is allocated per set of slaves (max. of two sets) • The rule of 2xDoP is not always enforced. Example: single table scan needs only one set of PX processes 17
18. DFO (data flow operation) and DFO trees • Data flow operation - basic unit of work carried by PX servers • Identified by PX SEND operation in the execution plan • Multiple DFO’s can appear • One DFO = 1 PX server set is being used • Multiple DFO’s = most of 2 PX server sets are being used • DFO’s are grouped under one DFO tree (PX coordinator) 18
19. DFO (data flow operation) and DFO trees … continued --------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | TQ |IN-OUT| PQ Distrib | --------------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 55500 | 1083K| 147 (0)| 00:00:01 | | | | | 1 | PX COORDINATOR | | | | | | | | | | 2 | PX SEND QC (RANDOM)| :TQ10000 | 55500 | 1083K| 147 (0)| 00:00:01 | Q1,00 | P->S | QC (RAND) | | 3 | PX BLOCK ITERATOR | | 55500 | 1083K| 147 (0)| 00:00:01 | Q1,00 | PCWC | | | 4 | TABLE ACCESS FULL| CUSTOMERS | 55500 | 1083K| 147 (0)| 00:00:01 | Q1,00 | PCWP | | --------------------------------------------------------------------------------------------------------------- Data flow operation DFO tree root 19
20. Parallel execution administration
21. Ways to enable parallel execution • Parallel degree on object level ALTER TABLE ... PARALLEL • Parallel degree with DDL CREATE TABLE ... PARALLEL [n] CREATE TABLE ... PARALLEL [n] AS SELECT ... FROM ... • Enable parallel query, DML and DDL at session level ALTER SESSION FORCE PARALLEL (QUERY|DML|DDL) [PARALLEL n] ALTER SESSION [ENABLE|DISABLE] PARALLEL DML ALTER SESSION [ENABLE|DISABLE] PARALLEL DDL 21
22. Ways to enable parallel execution … continued • Hints SELECT /*+ PARALLEL(n) */ ... FROM ... WHERE ... • Auto DoP • The optimizer determines the needed DoP 22
23. Why PDML is not enabled by default ? Some restrictions exist (not all listed) • PDML is not supported on tables with triggers • PDML is not supported for some RI constraints (self-referential, delete cascade and deferred constraints) • Data is not accessible until commit or rollback • Cluster tables are not supported • Distributed transactions are not supported • PDML is not supported on tables with bitmap indexes or LOB columns if the table is not partitioned 23
24. SELECT TABLE_NAME, DEGREE FROM DBA_TABLES WHERE TABLE_NAME LIKE 'T%' ... TABLE_NAME DEGREE ---------- ---------- T1 8 T2 DEFAULT T3 4 CREATE TABLE T1(owner) PARALLEL DEGREE 8; CREATE TABLE T2(owner) PARALLEL; ALTER TABLE T3 PARALLEL DEGREE 4; CREATE INDEX IX1 ON T1(owner) PARALLEL 4; ALTER INDEX IX1 PARALLEL 2; Identify defined parallel degree • Determine parallel degree from DBA_INDEXES or DBA_TABLES 24
25. Parallel degree - object level vs. statement level • Statement level degree has precedence over object level degree • Statement level is controlled with query hints • Object level parallel degree definition does not support hints 25
26. V$PQ_TQSTAT • TQs - serve as communication containers between parallel execution servers • Contains session-level information for parallel execution Some important columns: DFO_NUMBER – data flow tree TQ_ID – table queue SERVER_TYPE – producer, consumer, QC or ranger NUM_ROWS – number of rows processed BYTES – total bytes processed 26
27. V$PQ_SESSTAT Session statistics for parallel queries STATISTIC LAST_QUERY SESSION_TOTAL ------------------------- ---------- ------------- Queries Parallelized 1 3 DML Parallelized 0 0 DDL Parallelized 0 0 DFO Trees 1 4 Server Threads 2 0 Allocation Height 2 0 Allocation Width 1 0 Local Msgs Sent 40 106 Distr Msgs Sent 0 0 Local Msgs Recv'd 38 100 Distr Msgs Recv'd 0 0 DOP 2 0 Slave Sets 1 0 • Aggregated session level statistics • In12.2 “Server Threads” statistic reports real number of PX servers used with multiple DFO trees SELECT STATISTIC, LAST_QUERY, SESSION_TOTAL FROM V$PQ_SESSTAT 27
28. How to determine DoP for current or past executions • Your own session’s DoP can be determined by looking at V$PQ_TQSTAT and V$PQ_SESSTAT. But, those views are not relevant for other sessions. • Views: V$PX_SESSION, V$PX_PROCESS, V$TRANSACTION • SQL Monitor • Look at PX_FLAGS column in [G]V$ACTIVE_SESSION_HISTORY Get query’s actual DoP with TRUNC(PX_FLAGS/2097152) 28
29. V$PX_SESSION • Contains information about current running sessions in parallel • Sessions are easily identified with SADDR, SID and SERIAL# columns • DoP downgrades can be identified by looking at DEGREE and REQ_DEGREE columns • In RAC you can identify from which instance the QC is running • Identify how many PX servers run concurrently in case of multiple parallelizers 29
30. V$PX_PROCESS • Shows pre-allocated PX servers • Processes can be AVAILABLE or IN USE, the STATUS column • Operating system process ID can be identified from SPID column • Starting 12c, an indicator IS_GV has been added to easily identify if the PX process is a normal one or a GV$ process • Selects on GV$ views use PX processes named PZ9n across nodes 30
31. V$TRANSACTION • Provides information about active transactions • Parallel execution related columns: PTX_XIDUSN, PTX_XIDSLT, PTX_XIDSQN • Self-join V$TRANSACTION to identify PX processes specific per transaction 31
32. Initialization parameters changes for parallel execution • Parameter changes PARALLEL_MIN_SERVERS: CPU_COUNT * PARALLEL_THREADS_PER_CPU * 2 • Obsolete (removed) starting 12.2 PARALLEL_SERVER PARALLEL_SERVER_INSTANCES PARALLEL_IO_CAP_ENABLED PARALLEL_AUTOMATIC_TUNING • Deprecated starting 12.2 PARALLEL_ADAPTIVE_MULTI_USER = FALSE 32
33. Data distribution methods Including Bloom Filters
34. • HASH (HYBRID HASH 12c): Hash function is applied on the join key. - Improvement in 12c • BROADCAST: Used when one table is significantly smaller compared to other table. - Algorithm change in 12c • RANGE: Used when statement contains ORDER BY. Rows are distributed by ranges on the sort key. - Ranging is managed by QC at query runtime • ROUND - ROBIN: Randomly distributes rows to query servers. • QC (RANDOM): Rows are randomly sent to QC. Used when statement does not have ORDER BY. • QC (ORDER): Rows are sent to QC in order. Used when statement contains ORDER BY. Data distribution methods 34
35. Broadcast distribution Consumer PX slave 1 Consumer PX slave 2 Table rows Producer PX slave 1 Producer PX slave 2 35
36. Broadcast distribution Consumer PX slave 1 Consumer PX slave 2 Table rows Producer PX slave 1 Producer PX slave 2 36
37. Broadcast distribution method • Most effective when one of the result sets is much smaller than the other • Data is duplicated to all PX consumer servers • Larger results set doesn’t have to be re-distributed • Statistics are very important to CBO to pick effective distribution 37
38. Broadcast distribution method -------------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | TQ |IN-OUT| PQ Distrib | -------------------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 2014 | 450 (1)| 00:00:01 | | | | |* 1 | COUNT STOPKEY | | | | | | | | | | 2 | PX COORDINATOR | | | | | | | | | | 3 | PX SEND QC (RANDOM) | :TQ10001 | 1000K| 1920M| 450 (1)| 00:00:01 | Q1,01 | P->S | QC (RAND) | |* 4 | COUNT STOPKEY | | | | | | Q1,01 | PCWC | | | 5 | VIEW | | 1000K| 1920M| 450 (1)| 00:00:01 | Q1,01 | PCWP | | |* 6 | HASH JOIN | | 1000K| 49M| 450 (1)| 00:00:01 | Q1,01 | PCWP | | | 7 | PX RECEIVE | | 1000K| 24M| 208 (1)| 00:00:01 | Q1,01 | PCWP | | | 8 | PX SEND BROADCAST | :TQ10000 | 1000K| 24M| 208 (1)| 00:00:01 | Q1,00 | P->P | BROADCAST | | 9 | PX BLOCK ITERATOR | | 1000K| 24M| 208 (1)| 00:00:01 | Q1,00 | PCWC | | | 10 | TABLE ACCESS FULL| T1 | 1000K| 24M| 208 (1)| 00:00:01 | Q1,00 | PCWP | | | 11 | PX BLOCK ITERATOR | | 1000K| 24M| 241 (1)| 00:00:01 | Q1,01 | PCWC | | | 12 | TABLE ACCESS FULL | T2 | 1000K| 24M| 241 (1)| 00:00:01 | Q1,01 | PCWP | | -------------------------------------------------------------------------------------------------------------------- 38
39. Broadcast distribution method … continued -------------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | TQ |IN-OUT| PQ Distrib | -------------------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 2014 | 450 (1)| 00:00:01 | | | | |* 1 | COUNT STOPKEY | | | | | | | | | | 2 | PX COORDINATOR | | | | | | | | | | 3 | PX SEND QC (RANDOM) | :TQ10001 | 1000K| 1920M| 450 (1)| 00:00:01 | Q1,01 | P->S | QC (RAND) | |* 4 | COUNT STOPKEY | | | | | | Q1,01 | PCWC | | | 5 | VIEW | | 1000K| 1920M| 450 (1)| 00:00:01 | Q1,01 | PCWP | | |* 6 | HASH JOIN | | 1000K| 49M| 450 (1)| 00:00:01 | Q1,01 | PCWP | | | 7 | PX RECEIVE | | 1000K| 24M| 208 (1)| 00:00:01 | Q1,01 | PCWP | | | 8 | PX SEND BROADCAST | :TQ10000 | 1000K| 24M| 208 (1)| 00:00:01 | Q1,00 | P->P | BROADCAST | | 9 | PX BLOCK ITERATOR | | 1000K| 24M| 208 (1)| 00:00:01 | Q1,00 | PCWC | | | 10 | TABLE ACCESS FULL| T1 | 1000K| 24M| 208 (1)| 00:00:01 | Q1,00 | PCWP | | | 11 | PX BLOCK ITERATOR | | 1000K| 24M| 241 (1)| 00:00:01 | Q1,01 | PCWC | | | 12 | TABLE ACCESS FULL | T2 | 1000K| 24M| 241 (1)| 00:00:01 | Q1,01 | PCWP | | -------------------------------------------------------------------------------------------------------------------- 39
40. Broadcast improvements in 12c • One virtual table • One set of parallel query slaves • Inter-process communication is eliminated • Cost-based decision • Outer source is fully read by each PX server 40
41. Broadcast improvements in 12c --------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | TQ |IN-OUT| PQ Distrib | --------------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1000K| 49M| 450 (1)| 00:00:01 | | | | | 1 | PX COORDINATOR | | | | | | | | | | 2 | PX SEND QC (RANDOM) | :TQ10000 | 1000K| 49M| 450 (1)| 00:00:01 | Q1,00 | P->S | QC (RAND) | |* 3 | HASH JOIN | | 1000K| 49M| 450 (1)| 00:00:01 | Q1,00 | PCWP | | | 4 | TABLE ACCESS FULL | T1 | 1000K| 24M| 208 (1)| 00:00:01 | Q1,00 | PCWP | | | 5 | PX BLOCK ITERATOR | | 1000K| 24M| 241 (1)| 00:00:01 | Q1,00 | PCWC | | | 6 | TABLE ACCESS FULL| T2 | 1000K| 24M| 241 (1)| 00:00:01 | Q1,00 | PCWP | | --------------------------------------------------------------------------------------------------------------- 41
42. Table rows Consumer PX slave 1 Consumer PX slave 2 Hash distribution Producer PX slave 1 Producer PX slave 2 HASH FUNCTION 42
43. Table rows Consumer PX slave 1 Consumer PX slave 2 Hash distribution Producer PX slave 1 Producer PX slave 2 HASH FUNCTION 43
44. Hash distribution … continued ------------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | TQ |IN-OUT| PQ Distrib | ------------------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 8 | 1475 (1)| 00:00:01 | | | | | 1 | SORT AGGREGATE | | 1 | 8 | | | | | | | 2 | PX COORDINATOR | | | | | | | | | | 3 | PX SEND QC (RANDOM) | :TQ10002 | 1 | 8 | | | Q1,02 | P->S | QC (RAND) | | 4 | SORT AGGREGATE | | 1 | 8 | | | Q1,02 | PCWP | | |* 5 | HASH JOIN | | 100K| 781K| 1475 (1)| 00:00:01 | Q1,02 | PCWP | | | 6 | PX RECEIVE | | 100K| 292K| 2 (0)| 00:00:01 | Q1,02 | PCWP | | | 7 | PX SEND HASH | :TQ10000 | 100K| 292K| 2 (0)| 00:00:01 | Q1,00 | P->P | HASH | | 8 | PX BLOCK ITERATOR | | 100K| 292K| 2 (0)| 00:00:01 | Q1,00 | PCWC | | | 9 | TABLE ACCESS FULL| T1 | 100K| 292K| 2 (0)| 00:00:01 | Q1,00 | PCWP | | | 10 | PX RECEIVE | | 1000K| 4882K| 1471 (1)| 00:00:01 | Q1,02 | PCWP | | | 11 | PX SEND HASH | :TQ10001 | 1000K| 4882K| 1471 (1)| 00:00:01 | Q1,01 | P->P | HASH | | 12 | PX BLOCK ITERATOR | | 1000K| 4882K| 1471 (1)| 00:00:01 | Q1,01 | PCWC | | | 13 | TABLE ACCESS FULL| T2 | 1000K| 4882K| 1471 (1)| 00:00:01 | Q1,01 | PCWP | | ------------------------------------------------------------------------------------------------------------------- 44
45. Hash distribution … continued ------------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | TQ |IN-OUT| PQ Distrib | ------------------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 8 | 1475 (1)| 00:00:01 | | | | | 1 | SORT AGGREGATE | | 1 | 8 | | | | | | | 2 | PX COORDINATOR | | | | | | | | | | 3 | PX SEND QC (RANDOM) | :TQ10002 | 1 | 8 | | | Q1,02 | P->S | QC (RAND) | | 4 | SORT AGGREGATE | | 1 | 8 | | | Q1,02 | PCWP | | |* 5 | HASH JOIN | | 100K| 781K| 1475 (1)| 00:00:01 | Q1,02 | PCWP | | | 6 | PX RECEIVE | | 100K| 292K| 2 (0)| 00:00:01 | Q1,02 | PCWP | | | 7 | PX SEND HASH | :TQ10000 | 100K| 292K| 2 (0)| 00:00:01 | Q1,00 | P->P | HASH | | 8 | PX BLOCK ITERATOR | | 100K| 292K| 2 (0)| 00:00:01 | Q1,00 | PCWC | | | 9 | TABLE ACCESS FULL| T1 | 100K| 292K| 2 (0)| 00:00:01 | Q1,00 | PCWP | | | 10 | PX RECEIVE | | 1000K| 4882K| 1471 (1)| 00:00:01 | Q1,02 | PCWP | | | 11 | PX SEND HASH | :TQ10001 | 1000K| 4882K| 1471 (1)| 00:00:01 | Q1,01 | P->P | HASH | | 12 | PX BLOCK ITERATOR | | 1000K| 4882K| 1471 (1)| 00:00:01 | Q1,01 | PCWC | | | 13 | TABLE ACCESS FULL| T2 | 1000K| 4882K| 1471 (1)| 00:00:01 | Q1,01 | PCWP | | ------------------------------------------------------------------------------------------------------------------- 45
46. Hash distribution … continued ------------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | TQ |IN-OUT| PQ Distrib | ------------------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 8 | 1475 (1)| 00:00:01 | | | | | 1 | SORT AGGREGATE | | 1 | 8 | | | | | | | 2 | PX COORDINATOR | | | | | | | | | | 3 | PX SEND QC (RANDOM) | :TQ10002 | 1 | 8 | | | Q1,02 | P->S | QC (RAND) | | 4 | SORT AGGREGATE | | 1 | 8 | | | Q1,02 | PCWP | | |* 5 | HASH JOIN | | 100K| 781K| 1475 (1)| 00:00:01 | Q1,02 | PCWP | | | 6 | PX RECEIVE | | 100K| 292K| 2 (0)| 00:00:01 | Q1,02 | PCWP | | | 7 | PX SEND HASH | :TQ10000 | 100K| 292K| 2 (0)| 00:00:01 | Q1,00 | P->P | HASH | | 8 | PX BLOCK ITERATOR | | 100K| 292K| 2 (0)| 00:00:01 | Q1,00 | PCWC | | | 9 | TABLE ACCESS FULL| T1 | 100K| 292K| 2 (0)| 00:00:01 | Q1,00 | PCWP | | | 10 | PX RECEIVE | | 1000K| 4882K| 1471 (1)| 00:00:01 | Q1,02 | PCWP | | | 11 | PX SEND HASH | :TQ10001 | 1000K| 4882K| 1471 (1)| 00:00:01 | Q1,01 | P->P | HASH | | 12 | PX BLOCK ITERATOR | | 1000K| 4882K| 1471 (1)| 00:00:01 | Q1,01 | PCWC | | | 13 | TABLE ACCESS FULL| T2 | 1000K| 4882K| 1471 (1)| 00:00:01 | Q1,01 | PCWP | | ------------------------------------------------------------------------------------------------------------------- 46
47. HASH distribution method and skew data Example: • Tables T1 and T2 with 2 milion rows each • Table T2 has foreign key defined on T1_ID column referencing T1(ID) • In T2 table, ¾ of the rows for T1_ID column have duplicate value (skew data) 47
48. HASH distribution method and skew data ... continued select /*+ parallel(4) */ t1.col2, t2.col2 from t1, t2 where t1.id = t2.t1_id; 48
49. HASH distribution method and skew data … continued ----------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | TQ |IN-OUT| PQ Distrib | ----------------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 250K| 12M| 484 (2)| 00:00:01 | | | | | 1 | PX COORDINATOR | | | | | | | | | | 2 | PX SEND QC (RANDOM) | :TQ10002 | 250K| 12M| 484 (2)| 00:00:01 | Q1,02 | P->S | QC (RAND) | |* 3 | HASH JOIN BUFFERED | | 250K| 12M| 484 (2)| 00:00:01 | Q1,02 | PCWP | | | 4 | PX RECEIVE | | 2000K| 49M| 421 (1)| 00:00:01 | Q1,02 | PCWP | | | 5 | PX SEND HASH | :TQ10000 | 2000K| 49M| 421 (1)| 00:00:01 | Q1,00 | P->P | HASH | | 6 | PX BLOCK ITERATOR | | 2000K| 49M| 421 (1)| 00:00:01 | Q1,00 | PCWC | | | 7 | TABLE ACCESS FULL| T1 | 2000K| 49M| 421 (1)| 00:00:01 | Q1,00 | PCWP | | | 8 | PX RECEIVE | | 250K| 6347K| 61 (2)| 00:00:01 | Q1,02 | PCWP | | | 9 | PX SEND HASH | :TQ10001 | 250K| 6347K| 61 (2)| 00:00:01 | Q1,01 | P->P | HASH | | 10 | PX BLOCK ITERATOR | | 250K| 6347K| 61 (2)| 00:00:01 | Q1,01 | PCWC | | | 11 | TABLE ACCESS FULL| T2 | 250K| 6347K| 61 (2)| 00:00:01 | Q1,01 | PCWP | | ----------------------------------------------------------------------------------------------------------------- 49
50. HASH distribution method and skew data Uniform distribution for T1 (session statistics) TQ_ID SERVER_TYP INSTANCE PROCESS NUM_ROWS PCT ---------- ---------- ---------- -------- ---------- ---------- 0 Producer 1 P004 500640 25 P005 498921 25 P006 463007 23 P007 537432 27 Consumer 1 P000 500363 25 P001 500256 25 P002 499609 25 P003 499772 25 50
51. HASH distribution method and skew data Non-uniform distribution for T2 (session statistics) TQ_ID SERVER_TYP INSTANCE PROCESS NUM_ROWS PCT ---------- ---------- ---------- -------- ---------- ---------- ... 1 Producer 1 P004 502266 25 P005 493795 25 P006 502313 25 P007 501626 25 Consumer 1 P000 62558 3 P001 62546 3 P002 62427 3 P003 1812469 91 51
52. Hybrid hash Starting with 12c • Adapts the distribution method at runtime • The source is broadcast if: 12.1 inflection point: rows < DoP*2 12.2/18c inflection point: changed, but not documented • Identified by PX SEND HYBRID HASH and STATISTICS COLLECTOR operations in the execution plan 52
53. Hybrid hash distribution method and skew data View: V$PQ_TQSTAT TQ_ID SERVER_TYP INSTANCE PROCESS NUM_ROWS ---------- ---------- ---------- -------- ---------- 0 Producer 1 P004 501273 P005 461496 P006 535080 P007 502154 ---------- 2000003 Consumer 1 P000 499616 P001 500734 P002 499522 P003 500131 Popular value is BROADCAST to all consumer PX processes 53
54. Hybrid hash distribution method and skew data … continued TQ_ID SERVER_TYP INSTANCE PROCESS NUM_ROWS ---------- ---------- ---------- -------- ---------- ... 1 Producer 1 P004 502266 P005 504034 P006 493762 P007 499938 Consumer 1 P000 499962 P001 499930 P002 499805 P003 500303 ROUND ROBIN / RANDOM Distribution for skew data rows 54
55. Hybrid hash distribution method and skew data PARAMETER_NAME PARAMETER_DESC SESS SYS ------------------------ ----------------------------------------------------------- ------ ----- _px_join_skew_handling enables skew handling for parallel joins TRUE TRUE _px_join_skew_ratio sets skew ratio for parallel joins 10 10 _px_join_skew_minfreq sets minimum frequency(%) for skewed value for parallel joins 30 30 BEGIN DBMS_STATS.GATHER_TABLE_STATS(NULL,’T2' ,METHOD_OPT=>’FOR ALL COLUMNS SIZE 1 FOR COLUMNS SIZE 254 T1_ID' ,NO_INVALIDATE=>FALSE); END; / 55
56. Bloom filters theory Probabilistic data structure • Named by Burton Howard Bloom, conceived in 1970 • Used to test whether an element is a member of a set • False positives are possible. False negatives are not possible • Composed of: • k – different hash functions (k1 , k2 ,k3 … km ) • Bit array of m – bits (b1 , b2 , b3 … bm ), initially all set to 0 56
57. Bloom filters theory How does it work ? • An element (e) is sent to all k hash functions and retrieved array indexes are set to 1 Example: m = 10, k = 3, e = 10 h1(10) = i1 h1(10) = i4 h1(10) = i6 Bit array 0 1 0 0 1 0 1 0 0 0 i0 i1 i2 i3 i4 i5 i6 i7 i8 i9 e = 2, negative h1(2) = i3 h1(2) = i9 h1(2) = i6 e = 10, positive h1(10) = i1 h1(10) = i4 h1(10) = i6 e = 8, false positive h1(8) = i1 h1(8) = i4 h1(8) = i6 57
58. Bloom filters • It reduces the overhead of data transfer between PX sets • The CBO decides whether it should use bloom filters • You can “control” bloom filter usage with a query hint: - PX_JOIN_FILTER - NO_PX_JOIN_FILTER • System or session wide control with "_bloom_filter_enabled” • Exadata smart-scan also benefits from bloom filters 58
59. Bloom filters example ----------------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| TQ |IN-OUT| PQ Distrib | ----------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 3143K| 152M| 197K (2)| | | | | 1 | PX COORDINATOR | | | | | | | | | 2 | PX SEND QC (RANDOM) | :TQ10000 | 3143K| 152M| 197K (2)| Q1,00 | P->S | QC (RAND) | |* 3 | HASH JOIN | | 3143K| 152M| 197K (2)| Q1,00 | PCWP | | | 4 | JOIN FILTER CREATE | :BF0000 | 1 | 30 | 2 (0)| Q1,00 | PCWP | | |* 5 | TABLE ACCESS FULL | PRODUCTS | 1 | 30 | 2 (0)| Q1,00 | PCWP | | | 6 | JOIN FILTER USE | :BF0000 | 424M| 8498M| 197K (1)| Q1,00 | PCWP | | | 7 | PX BLOCK ITERATOR | | 424M| 8498M| 197K (1)| Q1,00 | PCWC | | |* 8 | TABLE ACCESS FULL| SALES | 424M| 8498M| 197K (1)| Q1,00 | PCWP | | ----------------------------------------------------------------------------------------------------- 59
60. Blocking and non-blocking operations
61. Blocking and non-blocking operations • Producer and consumer model limitations • If the data has to be processed by next (different) PX Slave set it has to be “parked” • True blocking operations • Artificial blocking operations 61
62. True blocking operations (sort) :TQ10000 PX3 PX4 BUILD HASH TABLE PX1 PX2 :TQ10001 PX3 PX4 SORT AGGREGATE PX3 PX4 PROBES PX3 PX4 :TQ10002 PX SEND HASH PX SEND HASH PX RECEIVE PX RECEIVE True blocking operation T1 T2 QC PX1 PX2 62
63. True blocking operation ------------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | TQ |IN-OUT| PQ Distrib | ------------------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 52 | 901 (1)| 00:00:01 | | | | | 1 | SORT AGGREGATE | | 1 | 52 | | | | | | | 2 | PX COORDINATOR | | | | | | | | | | 3 | PX SEND QC (RANDOM) | :TQ10002 | 1 | 52 | | | Q1,02 | P->S | QC (RAND) | | 4 | SORT AGGREGATE | | 1 | 52 | | | Q1,02 | PCWP | | |* 5 | HASH JOIN | | 1000K| 49M| 901 (1)| 00:00:01 | Q1,02 | PCWP | | | 6 | PX RECEIVE | | 1000K| 24M| 416 (1)| 00:00:01 | Q1,02 | PCWP | | | 7 | PX SEND HASH | :TQ10000 | 1000K| 24M| 416 (1)| 00:00:01 | Q1,00 | P->P | HASH | | 8 | PX BLOCK ITERATOR | | 1000K| 24M| 416 (1)| 00:00:01 | Q1,00 | PCWC | | | 9 | TABLE ACCESS FULL| T1 | 1000K| 24M| 416 (1)| 00:00:01 | Q1,00 | PCWP | | | 10 | PX RECEIVE | | 1000K| 24M| 481 (1)| 00:00:01 | Q1,02 | PCWP | | | 11 | PX SEND HASH | :TQ10001 | 1000K| 24M| 481 (1)| 00:00:01 | Q1,01 | P->P | HASH | | 12 | PX BLOCK ITERATOR | | 1000K| 24M| 481 (1)| 00:00:01 | Q1,01 | PCWC | | | 13 | TABLE ACCESS FULL| T2 | 1000K| 24M| 481 (1)| 00:00:01 | Q1,01 | PCWP | | ------------------------------------------------------------------------------------------------------------------- 63
64. Artificial blocking operations PX1 PX2 :TQ10000 PX3 PX4 BUILD HASH TABLE PX1 PX2 :TQ10001 PX3 PX4 BUFFERS MATCHED ROWS PX3 PX4 :TQ10002 PX SEND HASH PX SEND HASH PX RECEIVE PX RECEIVE Artificial blocking operation T1 T2 QC PX SEND QC (RAND) PX SEND 64
65. Artificial blocking operation ----------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | TQ |IN-OUT| PQ Distrib | ----------------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1000K| 49M| 901 (1)| 00:00:01 | | | | | 1 | PX COORDINATOR | | | | | | | | | | 2 | PX SEND QC (RANDOM) | :TQ10002 | 1000K| 49M| 901 (1)| 00:00:01 | Q1,02 | P->S | QC (RAND) | |* 3 | HASH JOIN BUFFERED | | 1000K| 49M| 901 (1)| 00:00:01 | Q1,02 | PCWP | | | 4 | PX RECEIVE | | 1000K| 24M| 416 (1)| 00:00:01 | Q1,02 | PCWP | | | 5 | PX SEND HASH | :TQ10000 | 1000K| 24M| 416 (1)| 00:00:01 | Q1,00 | P->P | HASH | | 6 | PX BLOCK ITERATOR | | 1000K| 24M| 416 (1)| 00:00:01 | Q1,00 | PCWC | | | 7 | TABLE ACCESS FULL| T1 | 1000K| 24M| 416 (1)| 00:00:01 | Q1,00 | PCWP | | | 8 | PX RECEIVE | | 1000K| 24M| 481 (1)| 00:00:01 | Q1,02 | PCWP | | | 9 | PX SEND HASH | :TQ10001 | 1000K| 24M| 481 (1)| 00:00:01 | Q1,01 | P->P | HASH | | 10 | PX BLOCK ITERATOR | | 1000K| 24M| 481 (1)| 00:00:01 | Q1,01 | PCWC | | | 11 | TABLE ACCESS FULL| T2 | 1000K| 24M| 481 (1)| 00:00:01 | Q1,01 | PCWP | | ----------------------------------------------------------------------------------------------------------------- 65
66. Wrap-up Parallelism might be the cause of you performance problem when not applied properly Remember, • You must have a very large task • You must have enough resources • Focus on what gives you the most leverage 66
67. Thank you. Questions? 67