Re: How to estimate TEMP usage for DBMS_STATS?

From: Greg Rahn <>
Date: Tue, 16 Feb 2010 10:11:15 -0800
Message-ID: <>

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.


On Tue, Feb 16, 2010 at 9:30 AM, Rich Jesse <> 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.

Greg Rahn
Received on Tue Feb 16 2010 - 12:11:15 CST

Original text of this message