RE: Large Dataset - Estimated 87TB needed for TEMP - suggestions?
Date: Tue, 27 Jan 2015 13:37:11 -0500
Message-ID: <479101d03a60$44ae00f0$ce0a02d0$_at_rsiz.com>
you’d need to look at the 10053 trace (Wolfgang) to see the order of choices.
in the old one you had
8 - access(("B"."COWS_DIST_PERIOD"='20141' OR "B"."COWS_DIST_PERIOD"='20142') AND "B"."STS_PERF_PERIOD"='20141' AND
"B"."STS_PERF_SOURCE"='CB') is the new plan using a different index?
When does the new plan filter on COWS_DIST_PERIOD?
Fragmentary plans are rarely useful and create more questions than answers. Lots of things changed, including column names retrieved between these two queries.
mwf
From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Chris Taylor
Sent: Tuesday, January 27, 2015 1:00 PM
To: dmarc-noreply_at_freelists.org; oracle-l_at_freelists.org
Subject: Re: Large Dataset - Estimated 87TB needed for TEMP - suggestions?
Here's something interesting. The explain plan generated, I'm "losing" a FILTER condition against Table B.
But if I add the FILTER specifically to the query, I get the FILTER I expect. Am I missing something or is Oracle choosing to do a join elimination here or something?
See ORIGINAL plus CHANGE below it.
Original Condition:
WHERE A.STS_PERF_PERIOD = b.sts_perf_period
AND A.STS_DPS_TYPE = b.sts_dps_type
AND A.STG_GROUP_NO = b.sts_group_no
AND a.stg_group_seq_no = b.sts_group_seq_no
AND b.sts_provider = c.stp_provider
AND b.sts_program_no = c.stp_program_no
AND a.STS_PERF_PERIOD = '20141'
AND b.sts_perf_source = 'CB'
AND b.COWS_DIST_PERIOD in ('20141','20142')
AND a.PWN_P_W_IND = 'W'
AND c.stp_s03_program_cd <> '36'
GROUP BY a.STS_PERF_PERIOD,
a.PART_ACCT_NO,
a.PWN_PART_AKA_NME,
a.PWN_P_W_IND,
a.AFL_SOCIETY_NME,
b.sts_CHANNEL_TYPE,
c.stp_s03_program_cd
/
Plan hash value: 2657901994
Predicate Information (identified by operation id):
2 - access("A"."STS_PERF_PERIOD"="B"."STS_PERF_PERIOD" AND "A"."STS_DPS_TYPE"="B"."STS_DPS_TYPE" AND
"A"."STG_GROUP_NO"="B"."STS_GROUP_NO" AND "A"."STG_GROUP_SEQ_NO"="B"."STS_GROUP_SEQ_NO") 4 - access("B"."STS_PROVIDER"="C"."STP_PROVIDER" AND "B"."STS_PROGRAM_NO"="C"."STP_PROGRAM_NO")
8 - access(("B"."COWS_DIST_PERIOD"='20141' OR "B"."COWS_DIST_PERIOD"='20142') AND "B"."STS_PERF_PERIOD"='20141' AND
"B"."STS_PERF_SOURCE"='CB') 9 - filter("C"."STP_S03_PROGRAM_CD"<>'36')
11 - filter("A"."PWN_P_W_IND"='W' AND "A"."STS_PERF_PERIOD"='20141')
NEW Filter Condition:
WHERE A.STS_PERF_PERIOD = b.sts_perf_period
AND A.STS_DPS_TYPE = b.sts_dps_type
AND A.STG_GROUP_NO = b.sts_group_no
AND a.stg_group_seq_no = b.sts_group_seq_no
AND b.sts_provider = c.stp_provider
AND b.sts_program_no = c.stp_program_no
AND a.STS_PERF_PERIOD = '20133'
AND B.STS_PERF_PERIOD = '20133' /* Added this explicit condition */
AND b.sts_perf_source = 'CB'
2 - access("ITEM_4"="ITEM_4" AND "ITEM_3"="ITEM_3" AND "ITEM_2"="ITEM_2" AND "ITEM_1"="ITEM_1")
6 - filter("A"."PWN_P_W_IND"='W' AND "A"."STS_PERF_PERIOD"='20133')
9 - access("B"."STS_PROGRAM_NO"="C"."STP_PROGRAM_NO" AND "B"."STS_PROVIDER"="C"."STP_PROVIDER")
12 - access("B"."STS_PERF_PERIOD"='20133' AND "B"."STS_PERF_SOURCE"='CB')
filter("B"."STS_PERF_PERIOD"='20133' AND "B"."STS_PERF_SOURCE"='CB')
13 - filter("C"."STP_S03_PROGRAM_CD"<>'36')
Thoughts anyone? The Estimated Space changes significantly here. between these 2:
(Without the FILTER)
| Id | Operation | Name | E-Rows |E-Bytes|E-Temp | Cost (%CPU)| E-Time | Pstart| Pstop |
| 0 | SELECT STATEMENT | | 556K| 75M| | 857K (1)| 00:57:12 | | |
| 1 | HASH GROUP BY | | 556K| 75M| 2502M| 857K (1)| 00:57:12 | | |
(With the explicit FILTER)
| Id | Operation | Name | E-Rows |E-Bytes|E-Temp | Cost (%CPU)| E-Time | Pstart| Pstop |
| 0 | SELECT STATEMENT | | 97556 | 13M| | 201K (2)| 00:13:26 | | |
| 1 | HASH GROUP BY | | 97556 | 13M| 16M| 201K (2)| 00:13:26 | | |
-- http://www.freelists.org/webpage/oracle-lReceived on Tue Jan 27 2015 - 19:37:11 CET