Re: Large Dataset - Estimated 87TB needed for TEMP - suggestions?
Date: Tue, 27 Jan 2015 13:07:58 -0600
Message-ID: <CAP79kiTbw8FPdHt4zi9pJavw+jzWuB99ob1Rkuk-aCkXvSGk3g_at_mail.gmail.com>
Ok - there's a few things going on. I'm gathering extended statistics while I'm playing with the query.
But, it appears the changes in the plan are only specific to changing the PERF_PERIOD. I've created 4 PasteBin pastes to illustrate. STS_PERF_PERIOD is YYYYQUARTER_ID, so 20133 is year 2013, quarter 3 and 20141 is 2014, quarter 1.
Here's the plan for 2014 quarter 1 (20141) that "loses" the additional filter against table B (prdm.cows_TCUE_STS_SCHEDULE):
Here's the plan for 2014 quarter 1 (20141) that has the explicit filter added to the query against table B (prdm.cows_TCUE_STS_SCHEDULE): http://pastebin.com/yy7tCYpk
Here's the SQL & Plan for 2013 quarter 3 (20133):
Here's the SQL & Plan for 2013 quarter 3 (20133) using the explicit filter:
Before creating these pastes, I closed my connection and reopened one window and verified I was working with the original SQL and only changing the Period ID and adding the explicit filter condition to show how the plan changes.
It appears that adding the explicit filter creates an additional HASH GROUP BY but it still seems "off" to me that the explicit filter of 20141 disappears against table B in the original.
Thanks,
Chris
On Tue, Jan 27, 2015 at 12:37 PM, Mark W. Farnham <mwf_at_rsiz.com> wrote:
> 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 - 20:07:58 CET