How to estimate TEMP usage for DBMS_STATS?
Date: Tue, 16 Feb 2010 11:30:15 -0600 (CST)
Entangled in my other messes is a problem in 10.1.0.5.0 where DBMS_STATS.GATHER_DATABASE_STATS is failing with ORA-1652 (unable to extend TEMP). MOS article 554871.1 helpfully addresses this by offering "Add space in the temp tablespace" as a solution.
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:
dbms_stats.gather_database_stats ( estimate_percent => 30, block_sample => FALSE, method_opt => 'FOR ALL COLUMNS SIZE AUTO', degree => 2, granularity => 'AUTO', cascade => TRUE, stattab => NULL, statid => NULL, options => 'GATHER STALE', objlist => t_objlist, statown => NULL, gather_sys => TRUE, no_invalidate => FALSE);
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.