select *from v$version;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit Production
PL/SQL Release 11.1.0.7.0 - Production
CORE 11.1.0.7.0 Production
TNS for Linux: Version 11.1.0.7.0 - Production
NLSRTL Version 11.1.0.7.0 - Production
I am trying to understand PQ_DISTRIBUTION method. I am doing some test and stuck in interpreting execution plan when it comes to PQ_DISTRIBUTION.
CASE-1 Without any PQ_DISTRIBUTE hint
AE and JEL tables are monthly partitioned on ACCOUNTING_DATE column.
set autot traceonly
select *
from journal_entry_lines jel,
acctg_events ae
where ae.acctg_event_id = jel.acctg_event_id
and ae.client_code = 'CAMPS'
and jel.accounting_date >= to_date('06/30/2010', 'mm/dd/yyyy')
and jel.accounting_date < to_date('06/30/2010', 'mm/dd/yyyy') + 1
and jel.set_of_books = '222'
and jel.je_line_detail_table = 2
and jel.gl_batch_id <> -10
/
870650 rows selected.
Elapsed: 00:13:26.73
Plan hash value: 3439450812
--------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop | TQ |IN-OUT| PQ Distrib |
--------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 842K| 271M| 524K (2)| 01:44:56 | | | | | |
| 1 | PX COORDINATOR | | | | | | | | | | |
| 2 | PX SEND QC (RANDOM) | :TQ10002 | 842K| 271M| 524K (2)| 01:44:56 | | | Q1,02 | P->S | QC (RAND) |
|* 3 | HASH JOIN BUFFERED | | 842K| 271M| 524K (2)| 01:44:56 | | | Q1,02 | PCWP | |
| 4 | PX RECEIVE | | 839K| 175M| 7032 (1)| 00:01:25 | | | Q1,02 | PCWP | |
| 5 | PX SEND HASH | :TQ10000 | 839K| 175M| 7032 (1)| 00:01:25 | | | Q1,00 | P->P | HASH |
| 6 | PX BLOCK ITERATOR | | 839K| 175M| 7032 (1)| 00:01:25 | 129 | 129 | Q1,00 | PCWC | |
|* 7 | TABLE ACCESS FULL| JOURNAL_ENTRY_LINES | 839K| 175M| 7032 (1)| 00:01:25 | 129 | 129 | Q1,00 | PCWP | |
| 8 | PX RECEIVE | | 751M| 83G| 516K (2)| 01:43:24 | | | Q1,02 | PCWP | |
| 9 | PX SEND HASH | :TQ10001 | 751M| 83G| 516K (2)| 01:43:24 | | | Q1,01 | P->P | HASH |
| 10 | PX BLOCK ITERATOR | | 751M| 83G| 516K (2)| 01:43:24 | 1 | 80 | Q1,01 | PCWC | |
|* 11 | TABLE ACCESS FULL| ACCTG_EVENTS | 751M| 83G| 516K (2)| 01:43:24 | 1 | 80 | Q1,01 | PCWP | |
--------------------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("AE"."ACCTG_EVENT_ID"="JEL"."ACCTG_EVENT_ID")
7 - filter("JEL"."JE_LINE_DETAIL_TABLE"=2 AND "JEL"."SET_OF_BOOKS"='222' AND "JEL"."GL_BATCH_ID"<>(-10))
11 - filter("AE"."CLIENT_CODE"='CAMPS')
Statistics
----------------------------------------------------------
77 recursive calls
3 db block gets
19578304 consistent gets
19436090 physical reads
636 redo size
228785499 bytes sent via SQL*Net to client
2252 bytes received via SQL*Net from client
176 SQL*Net roundtrips to/from client
80 sorts (memory)
0 sorts (disk)
870650 rows processed
set autot off
SELECT dfo_number, tq_id, server_type, process, num_rows, bytes
FROM v$pq_tqstat
ORDER BY dfo_number DESC, tq_id, server_type DESC, process;
DFO_NUMBER TQ_ID SERVER_TYPE PROCESS NUM_ROWS BYTES
---------- ---------- ------------------------------ ------------------------------ ---------- ----------
1 0 Producer P008 118401 34188427
Producer P009 89973 26044552
Producer P010 104927 30341606
Producer P011 150535 43642705
Producer P012 110959 31951898
Producer P013 110489 32026211
Producer P014 69530 20011267
Producer P015 115836 33428684
Consumer P000 108546 31366540
Consumer P001 108490 31355412
Consumer P002 108846 31461078
Consumer P003 109236 31569624
Consumer P004 108250 31282750
Consumer P005 108718 31422268
Consumer P006 109394 31619070
Consumer P007 109170 31558608
1 Producer P008 91947187 1489054585
Producer P009 89830033 1605194494
Producer P010 91781613 1738341746
Producer P011 90123858 1335132558
Producer P012 112915823 3097474621
Producer P013 92374010 1941680072
Producer P014 85984550 996237091
Producer P015 84574176 1469162033
Consumer P000 92437241 1708225118
Consumer P001 92451080 1710462005
Consumer P002 92430918 1707653178
Consumer P003 92455013 1711555216
Consumer P004 92442948 1709292497
Consumer P005 92453211 1710595887
Consumer P006 92438263 1708654596
Consumer P007 92422576 1705838703
2 Producer P000 108546 52478106
Producer P001 108490 52461404
Producer P002 108846 52638700
Producer P003 109236 52818826
Producer P004 108250 52338072
Producer P005 108718 52572952
Producer P006 109394 52903004
Producer P007 109170 52802748
Consumer QC 870650 421013812
CASE-2 With pq_distribute(ae,BROADCAST,NONE) hint (Elapsed time is almost reduce to half)
set autot traceonly
select /*+ pq_distribute(ae,BROADCAST,NONE) */ *
from journal_entry_lines jel,
acctg_events ae
where ae.acctg_event_id = jel.acctg_event_id
and ae.client_code = 'CAMPS'
and jel.accounting_date >= to_date('06/30/2010', 'mm/dd/yyyy')
and jel.accounting_date < to_date('06/30/2010', 'mm/dd/yyyy') + 1
and jel.set_of_books = '222'
and jel.je_line_detail_table = 2
and jel.gl_batch_id <> -10
/
870650 rows selected.
Elapsed: 00:06:59.82
Plan hash value: 810058038
--------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop | TQ |IN-OUT| PQ Distrib |
--------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 842K| 271M| 524K (2)| 01:44:56 | | | | | |
| 1 | PX COORDINATOR | | | | | | | | | | |
| 2 | PX SEND QC (RANDOM) | :TQ10001 | 842K| 271M| 524K (2)| 01:44:56 | | | Q1,01 | P->S | QC (RAND) |
|* 3 | HASH JOIN | | 842K| 271M| 524K (2)| 01:44:56 | | | Q1,01 | PCWP | |
| 4 | PX RECEIVE | | 839K| 175M| 7032 (1)| 00:01:25 | | | Q1,01 | PCWP | |
| 5 | PX SEND BROADCAST | :TQ10000 | 839K| 175M| 7032 (1)| 00:01:25 | | | Q1,00 | P->P | BROADCAST |
| 6 | PX BLOCK ITERATOR | | 839K| 175M| 7032 (1)| 00:01:25 | 129 | 129 | Q1,00 | PCWC | |
|* 7 | TABLE ACCESS FULL| JOURNAL_ENTRY_LINES | 839K| 175M| 7032 (1)| 00:01:25 | 129 | 129 | Q1,00 | PCWP | |
| 8 | PX BLOCK ITERATOR | | 751M| 83G| 516K (2)| 01:43:24 | 1 | 80 | Q1,01 | PCWC | |
|* 9 | TABLE ACCESS FULL | ACCTG_EVENTS | 751M| 83G| 516K (2)| 01:43:24 | 1 | 80 | Q1,01 | PCWP | |
--------------------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("AE"."ACCTG_EVENT_ID"="JEL"."ACCTG_EVENT_ID")
7 - filter("JEL"."JE_LINE_DETAIL_TABLE"=2 AND "JEL"."SET_OF_BOOKS"='222' AND "JEL"."GL_BATCH_ID"<>(-10))
9 - filter("AE"."CLIENT_CODE"='CAMPS')
Elapsed: 00:06:59.82
Statistics
----------------------------------------------------------
70 recursive calls
3 db block gets
19575169 consistent gets
19447517 physical reads
592 redo size
220287857 bytes sent via SQL*Net to client
2252 bytes received via SQL*Net from client
176 SQL*Net roundtrips to/from client
80 sorts (memory)
0 sorts (disk)
870650 rows processed
set autot off
SELECT dfo_number, tq_id, server_type, process, num_rows, bytes
FROM v$pq_tqstat
ORDER BY dfo_number DESC, tq_id, server_type DESC, process;
DFO_NUMBER TQ_ID SERVER_TYPE PROCESS NUM_ROWS BYTES
---------- ---------- ------------------------------ ------------------------------ ---------- ----------
1 0 Producer P042 937293 270163229
Producer P072 985236 284172630
Producer P073 819154 236780264
Producer P074 812707 236511618
Producer P075 840084 242875885
Producer P076 841967 243613230
Producer P077 858109 247325386
Consumer P034 870650 251634606
Consumer P035 870650 251634606
Consumer P036 870650 251634606
Consumer P037 870650 251634606
Consumer P039 870650 251634606
Consumer P040 870650 251634606
Consumer P041 870650 251634606
1 Producer P034 0 24
Producer P035 456252 220576660
Producer P036 0 24
Producer P037 2174 1098406
Producer P039 323218 156364802
Producer P040 89006 42974232
Producer P041 0 24
Consumer QC 870650 421014172
Can i ask why there is a drastic difference in elapsed time just by changing pq distribution method to BROADCAST ? Is there any way to know which slave set is scanning which table ?
Edited by: OraDBA02 on Jul 13, 2010 3:33 AM
Edited by: OraDBA02 on Jul 13, 2010 3:35 AM