Re: Large Dataset - Estimated 87TB needed for TEMP - suggestions?
Date: Tue, 27 Jan 2015 09:23:24 -0600
Message-ID: <CAP79kiSOuORyzBwoytW38wg19AHgCsDyJt_0=060JXHJRt9bEA_at_mail.gmail.com>
I'm not as familiar with extended statistics - can you help me understand why it would be STS_PROVIDER, STS_PROGRAM_NO (from table "B") that you mention?
If I look at the plan, I see the following column references from table "B":
The ones you mention:
access("B"."STS_PROGRAM_NO"="C"."STP_PROGRAM_NO" AND
"B"."STS_PROVIDER"="C"."STP_PROVIDER")
PLUS:
access("B"."STS_PERF_SOURCE"='CB')
filter("B"."STS_PERF_SOURCE"='CB')
Would I add STS_PERF_SOURCE to the extended statistics information?
I'm confused on how you identified those particular columns I guess as the ones that should have the extended statistics? I see those are access columns and doesn't include filter columns is why I ask. (So, ultimately the question is: "How do you identify the most appropriate columns for extended statistics?")
Thanks,
Chris
On Tue, Jan 27, 2015 at 9:12 AM, Sayan Sergeevich Malakshinov < malakshinovss_at_psbank.ru> wrote:
> > Also, it completed successfully for period 20142 but the explain
> > plan for that one said it would need 573 TB of TEMP space so now I
> > realize I have zero way to "guess" how much temp space it really
> > needs but that 150 GB isn't enough.
> >
> > The stats are accurate for 100% estimation so I'm not sure why the
> > estimate on the temp space is so high.
>
> How different are values of "A-Rows" from "E-rows"?
> It seems that you have to gather extended statistics on
> (STS_PROVIDER,STS_PROGRAM_NO) and on
> (STS_PERF_PERIOD,STS_DPS_TYPE,STG_GROUP_NO,STG_GROUP_SEQ_NO) column sets.
>
> --
> Best regards,
> Sayan Malakshinov
> http://orasql.org
-- http://www.freelists.org/webpage/oracle-lReceived on Tue Jan 27 2015 - 16:23:24 CET