How to estimate TEMP usage for DBMS_STATS?

From: Rich Jesse <rjoralist_at_society.servebeer.com>
Date: Tue, 16 Feb 2010 11:30:15 -0600 (CST)
Message-ID: <28db3b08943d5671d3c70473f9a973fd.squirrel_at_society.servebeer.com>



Howdy,

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.

TIA,
Rich

--
http://www.freelists.org/webpage/oracle-l
Received on Tue Feb 16 2010 - 11:30:15 CST

Original text of this message