Home » RDBMS Server » Server Administration » GATHER_TABLE_STATS - temp tablespace getting full
GATHER_TABLE_STATS - temp tablespace getting full [message #243080] Wed, 06 June 2007 00:23 Go to next message
Brayan
Messages: 315
Registered: June 2002
Senior Member
Hi,

I'm execting DBMS_STATS.GATHER_TABLE_STATS for a specific table.

Begin
DBMS_STATS.GATHER_TABLE_STATS(ownname =>'BOIDW', 
			      tabname=> 'FEM_SAVINGS',
				  cascade=>TRUE,
				  degree =>16		  
			      );
End;




While gathering the temp tablespace is getting full. The temp tablespace is 36GB. Earlier this was working fine with just 24 GB temp space. There hasn't much inserts recently.

Recently I have done
alter database default temporary tablespace temp
Also, I have partioned above mentioned table.

Is it a normal behavior and simply increase TEMP tablespace.

Brayan.
Re: GATHER_TABLE_STATS - temp tablespace getting full [message #243087 is a reply to message #243080] Wed, 06 June 2007 00:45 Go to previous messageGo to next message
Frank Naude
Messages: 4502
Registered: April 1998
Senior Member
It may be that the space is allocated, but not used. To verify, do a select from V_$SORT_SEGMENT.
Re: GATHER_TABLE_STATS - temp tablespace getting full [message #243088 is a reply to message #243080] Wed, 06 June 2007 00:46 Go to previous messageGo to next message
Michel Cadot
Messages: 64122
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
If you partitioned the table then there is much more work to do to gather statistics.

Regards
Michel
Re: GATHER_TABLE_STATS - temp tablespace getting full [message #243098 is a reply to message #243080] Wed, 06 June 2007 01:24 Go to previous messageGo to next message
Brayan
Messages: 315
Registered: June 2002
Senior Member
Hi Frank and Michel,
1.
Quote:
It may be that the space is allocated, but not used. To verify, do a select from V_$SORT_SEGMENT.



Do you mean to say that, allocated/used/and still occupied in temp tablespce.
If so, is there a way i can coalesce?
Which columns in V_$SORT_SEGMENT states this?

2.
sort_area_size=64K, Version 9.2.0.7

and I create the tablespace using
CREATE TEMPORARY TABLESPACE temp
TEMPFILE '/db01/oradata/data/temp01.dbf' SIZE 6144M
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 8M


will it be ok?

Thankfully,
Brayan
Re: GATHER_TABLE_STATS - temp tablespace getting full [message #243113 is a reply to message #243098] Wed, 06 June 2007 02:32 Go to previous messageGo to next message
Frank Naude
Messages: 4502
Registered: April 1998
Senior Member
Something like this should do:

select tablespace_name "Tablespace", current_users "Active Users",
       extent_size "Extent Size (blks)", total_extents "Total Extents",
       used_extents "Used Extents", free_extents "Free Extents",
       max_sort_size "Max Sort Size (extents)"
from   sys.v_$sort_segment;


PS: Any reason for still using "sort_area_size"? See if you can switch to pga_aggregate_target.
Re: GATHER_TABLE_STATS - temp tablespace getting full [message #243144 is a reply to message #243080] Wed, 06 June 2007 04:17 Go to previous message
Brayan
Messages: 315
Registered: June 2002
Senior Member
Hi Frank,

Actually I have not thought of moving to "pga_aggregate_target", Let me evaluate the pro's and con's.

Extent size should be multiple of sort_area_size, will this apply to uniform extent also.

In this case for one table stats gather is 36GB of temp tablespace(Its too much to digest). Anyway I do not have any benchmark to prove. Now I'm doing it partition by partition.

Thank you very much Frank.

Regards,
Brayan.
Previous Topic: System account keeps locking
Next Topic: Online Cloning
Goto Forum:
  


Current Time: Wed Dec 07 04:49:27 CST 2016

Total time taken to generate the page: 0.09188 seconds