Re: parallel query and direct path read
- From: Ls Cheng <exriscer@xxxxxxxxx>
- To: Jonathan Lewis <jonathan@xxxxxxxxxxxxxxxxxx>
- Date: Tue, 2 Jul 2013 10:02:31 +0200
Hi
The plans are as following:
NO DIRECT PATH READS (or very little):
select /*+ parallel(t 8) parallel(h 8) */
t.*, h.id_hora
from STG_VALHOR_AGRE t,
DM_HORA h
where t.fx_period = h.ds_hour_end
and t.nu_period_intgr = h.id_period_day
and t.fx_period >= to_date('20110101 000000', 'yyyymmdd hh24miss')
and t.fx_period < to_date('20120101 000000', 'yyyymmdd hh24miss')
Plan hash value: 3992848562
------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost
(%CPU)| Time | Pstart| Pstop | TQ |IN-OUT| PQ Distrib |
------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 58577
(100)| | | | | | |
| 1 | PX COORDINATOR | | |
| | | | | | | |
| 2 | PX SEND QC (RANDOM) | :TQ10001 | 11M| 801M|
58577 (2)| 00:11:43 | | | Q1,01 | P->S | QC (RAND) |
|* 3 | HASH JOIN | | 11M| 801M|
58577 (2)| 00:11:43 | | | Q1,01 | PCWP | |
| 4 | PART JOIN FILTER CREATE| :BF0000 | 8761 | 162K|
53 (2)| 00:00:01 | | | Q1,01 | PCWP | |
| 5 | PX RECEIVE | | 8761 | 162K|
53 (2)| 00:00:01 | | | Q1,01 | PCWP | |
| 6 | PX SEND BROADCAST | :TQ10000 | 8761 | 162K|
53 (2)| 00:00:01 | | | Q1,00 | P->P | BROADCAST |
| 7 | PX BLOCK ITERATOR | | 8761 | 162K|
53 (2)| 00:00:01 | | | Q1,00 | PCWC | |
|* 8 | TABLE ACCESS FULL | DM_HORA | 8761 | 162K|
53 (2)| 00:00:01 | | | Q1,00 | PCWP | |
| 9 | PX BLOCK ITERATOR | | 181M| 9540M|
58393 (2)| 00:11:41 |KEY(AP)|KEY(AP)| Q1,01 | PCWC | |
|* 10 | TABLE ACCESS FULL | STG_VALHOR_AGRE| 181M| 9540M|
58393 (2)| 00:11:41 |KEY(AP)|KEY(AP)| Q1,01 | PCWP | |
------------------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("T"."FX_PERIOD"="H"."ds_hour_end" AND
"T"."NU_PERIOD_INTGR"="H"."id_period_day")
8 - access(:Z>=:Z AND :Z<=:Z)
filter(("H"."DS_HOUR_END">=TO_DATE(' 2011-01-01 00:00:00',
'syyyy-mm-dd hh24:mi:ss') AND "H"."DS_HOUR_END"<TO_DATE('
2012-01-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss')))
10 - access(:Z>=:Z AND :Z<=:Z)
filter(("T"."FX_PERIOD"<TO_DATE(' 2012-01-01 00:00:00', 'syyyy-mm-dd
hh24:mi:ss') AND "T"."FX_PERIOD">=TO_DATE('
2011-01-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND
SYS_OP_BLOOM_FILTER(:BF0000,"T"."FX_PERIOD","T"."NU_PERIOD_INTGR")))
FAST DIRECT PATH READS:
select count(*)
from (select /*+ parallel(t 8) parallel(h 8) */
t.*, rownum rn
from stg_valhor_agre t,
lu_hora h
where t.fx_period = h.ds_hour_end
and t.nu_period_intgr = h.id_period_day
and t.fx_period >= to_date('20110101 000000', 'yyyymmdd
hh24miss')
and t.fx_period < to_date('20120101 000000', 'yyyymmdd
hh24miss')
);
Plan hash value: 360743418
----------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |
Cost (%CPU)| Time | Pstart| Pstop | TQ |IN-OUT| PQ Distrib |
----------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | |
58298 (100)| | | | | | |
| 1 | SORT AGGREGATE | | 1 |
| | | | | | | |
| 2 | VIEW | | 11M| |
58298 (1)| 00:11:40 | | | | | |
| 3 | COUNT | | |
| | | | | | | |
| 4 | PX COORDINATOR | | |
| | | | | | | |
| 5 | PX SEND QC (RANDOM) | :TQ10001 | 11M| 238M|
58298 (1)| 00:11:40 | | | Q1,01 | P->S | QC (RAND) |
|* 6 | HASH JOIN | | 11M| 238M|
58298 (1)| 00:11:40 | | | Q1,01 | PCWP | |
| 7 | BUFFER SORT | | |
| | | | | Q1,01 | PCWC | |
| 8 | PART JOIN FILTER CREATE| :BF0000 | 8761 | 96371
| 36 (0)| 00:00:01 | | | Q1,01 | PCWP | |
| 9 | PX RECEIVE | | 8761 | 96371
| 36 (0)| 00:00:01 | | | Q1,01 | PCWP | |
| 10 | PX SEND BROADCAST | :TQ10000 | 8761 | 96371
| 36 (0)| 00:00:01 | | | | S->P | BROADCAST |
|* 11 | INDEX RANGE SCAN | XAK1DM_HOUR | 8761 | 96371
| 36 (0)| 00:00:01 | | | | | |
| 12 | PX BLOCK ITERATOR | | 181M| 1908M|
58130 (1)| 00:11:38 |KEY(AP)|KEY(AP)| Q1,01 | PCWC | |
|* 13 | TABLE ACCESS FULL | STG_VALHOR_AGRE| 181M| 1908M|
58130 (1)| 00:11:38 |KEY(AP)|KEY(AP)| Q1,01 | PCWP | |
----------------------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
6 - access("T"."FX_PERIOD"="H"."DS_HOUR_END" AND
"T"."NU_PERIOD_INTGR"="H"."id_period_day")
11 - ACCESS("H"."DS_HOUR_END">=TO_DATE(' 2011-01-01 00:00:00',
'SYYYY-MM-DD HH24:MI:SS') AND "H"."DS_HOUR_END"<TO_DATE(' 2012-01-01
00:00:00', 'SYYYY-MM-DD HH24:MI:SS'))
13 - ACCESS(:Z>=:Z AND :Z<=:Z)
FILTER(("T"."FX_PERIOD"<TO_DATE(' 2012-01-01 00:00:00', 'SYYYY-MM-DD
HH24:MI:SS') AND "T"."FX_PERIOD">=TO_DATE(' 2011-01-01
00:00:00', 'SYYYY-MM-DD HH24:MI:SS') AND
SYS_OP_BLOOM_FILTER(:BF0000,"T"."FX_PERIOD","T"."NU_PERIOD_INTGR")))
--
//www.freelists.org/webpage/oracle-l
Other related posts:
