Re: Large Dataset - Estimated 87TB needed for TEMP - suggestions?
- From: Chris Taylor <christopherdtaylor1994@xxxxxxxxx>
- To: Sayan Sergeevich Malakshinov <malakshinovss@xxxxxxxxx>
- Date: Tue, 27 Jan 2015 09:23:24 -0600
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@xxxxxxxxx> 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
Other related posts:
