Re: How to estimate TEMP usage for DBMS_STATS?
From: Greg Rahn <greg_at_structureddata.org>
Date: Tue, 16 Feb 2010 10:11:15 -0800
Message-ID: <a9c093441002161011o1570a892sf62f206a9a25a8fc_at_mail.gmail.com>
Temp is used for the NDV calculation because it does a sort distinct so the larger the number of distinct values the more space it needs. This changes in 11.1 with the new "fast NDV" that was introduced for dbms_stats.auto_sample_size (no temp is used - a hash based algo makes this possible). So worst case scenario I'd say, temp would need to be as large as your largest segment.
Date: Tue, 16 Feb 2010 10:11:15 -0800
Message-ID: <a9c093441002161011o1570a892sf62f206a9a25a8fc_at_mail.gmail.com>
Temp is used for the NDV calculation because it does a sort distinct so the larger the number of distinct values the more space it needs. This changes in 11.1 with the new "fast NDV" that was introduced for dbms_stats.auto_sample_size (no temp is used - a hash based algo makes this possible). So worst case scenario I'd say, temp would need to be as large as your largest segment.
References:
http://structureddata.org/2007/09/17/oracle-11g-enhancements-to-dbms_stats/
http://www.oracle.com/technology/products/bi/db/11g/pdf/twp_bidw_dss_perf_11gr1.pdf
On Tue, Feb 16, 2010 at 9:30 AM, Rich Jesse
<rjoralist_at_society.servebeer.com> wrote:
> The question is: how can I predict the TEMP space usage? Here's the call
> I'm using in the PL/SQL to gather the stats:
>
> The table that's being reported in the ORA-1652 error has a PK and 27
> additional indexes (yes, I know). It's about 15M rows and 25GB. Indexes
> are from ~900MB to ~4GB. The TEMP tablespace is 2GB.
>
> So, instead of guessing how big to make TEMP, I'd like to know how to
> predict it.
-- Regards, Greg Rahn http://structureddata.org -- http://www.freelists.org/webpage/oracle-lReceived on Tue Feb 16 2010 - 12:11:15 CST