| GATHER_TABLE_STATS - temp tablespace getting full [message #243080] |
Wed, 06 June 2007 00:23  |
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 #243098 is a reply to message #243080] |
Wed, 06 June 2007 01:24  |
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 |
Frank Naude
Messages: 4580
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 |
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.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
