Home » RDBMS Server » Server Administration » Temporary Tablespace size out of Control
Temporary Tablespace size out of Control [message #55205] Fri, 10 January 2003 10:23 Go to next message
Ron Utsinger
Messages: 4
Registered: January 2003
Junior Member
All,

Using 8.1.7.4, 64 bit on HP-UX 11.0 cluster. We have locally managed temporary tablespace created with the command:

create temporary tablespace temp tempfile '/z1pb/tabs/z1pb_temp_1.dbs' size 1000M reuse autoextend on next 20M maxsize 2000M extent management local uniform size 2M uniform size 2M;

alter tablespace temp add tempfile '/z1pb/tabs/z1pb_temp_2.dbs' size 1000M autoextend on next 20M maxsize 2000M;

and so on......

We are constantly having to add datafiles due to getting:

ORA-1652 .....unable to extend temp segment by 256 in tablespace temp;

The temp tablespace is of type temp, locally managed and has total of 32G of space. I know it is released when bounce database, but this is high availability and only gets shut down for colds on Sunday at 12:00 AM. How can we control the size of the temp? I have checked, and sorts (disk) in v$sysstat is < 5% of sorts (memory) (is actually like .08%, so don't believe the sort_area_size is cause. Any help would be appreciated before we have to get dedicated EMC storage box to hold the temp :->

Thanks,

Ron

Report message to a moderator

Re: Temporary Tablespace size out of Control [message #55207 is a reply to message #55205] Fri, 10 January 2003 11:20 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10707
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
 
make sure 
a. PCTINCREASE is set to Zero. 
b. INITIAL and NEXTEXTENT are equal( this may not be that important ...anyhow set this also)

----------------------------------------------------------------------

sql> alter tablespace temp default storage(pctincrease 0) 

----------------------------------------------------------------------

pls try the above and post your results.
if its is not helping you, we should fix a workaround.

Report message to a moderator

Re: Temporary Tablespace size out of Control [message #55210 is a reply to message #55205] Fri, 10 January 2003 12:49 Go to previous messageGo to next message
Ron Utsinger
Messages: 4
Registered: January 2003
Junior Member
Mahesh,

The Initial and next extent are both set to 2M. Querying dba_tablespaces returns the following:

SQL> select * from dba_tablespaces
2 where tablespace_name ='TEMP';

TABLESPACE_NAME INITIAL_EXTENT NEXT_EXTENT MIN_EXTENTS
------------------------------ -------------- ----------- -----------
MAX_EXTENTS PCT_INCREASE MIN_EXTLEN STATUS CONTENTS LOGGING EXTENT_MAN
----------- ------------ ---------- --------- --------- --------- ----------
ALLOCATIO PLU
--------- ---
TEMP 2097152 2097152 1
0 2097152 ONLINE TEMPORARY NOLOGGING LOCAL
UNIFORM NO

I checked to see what pctincrease was earlier,and it was already set to 0, however at that time, I had attempted to run the statement that you are suggesting, with the following results:

SQL> alter tablespace temp default storage (pctincrease 0);
alter tablespace temp default storage (pctincrease 0)
*
ERROR at line 1:
ORA-03217: invalid option for alter of TEMPORARY TABLESPACE

Really rather unsure where to look from here, as the other DBA's in my group indicate this is a rather isolated, relatively recent occurence having to add the temp files. We have +/- 80 instances running on an HP Cluster, and with the exception of this and one or two others, haven't experienced any issues with the temp tablespaces.

Report message to a moderator

Re: Temporary Tablespace size out of Control [message #55214 is a reply to message #55205] Fri, 10 January 2003 21:39 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10707
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
sort_area_size=2m is kind of OK with medium sized databases.
if the nature of business is of batch process, increase it significantly
say to 10m

Report message to a moderator

Re: Temporary Tablespace size out of Control [message #55240 is a reply to message #55205] Mon, 13 January 2003 08:31 Go to previous message
Ron Utsinger
Messages: 4
Registered: January 2003
Junior Member
I am unsure how you would classify as small,medium,large. Currently there are 381 active connections to the instance, with +/- 200GB of active datafiles allocated to index and objects, so would definitely classify this as large.

However, I believe the nature of usage is not so much one of batch processing as much as it is of large sort operations. However, when looking at statistics on the instance, something like 99.92 percent of sorts are currently being done in memory. However, our instance bounced over the weekend, and less than 24 hours, all 36gb of temp files have been used, and "unable to extend" messages showed up in alert file. We do compute statistics on Sunday nite, but do not believe that the space would be used and not released.

Also, with this many users, if we were to substantially increase the sort_area_size, we would also run the risk of nearly 4g of memory being used by the instance just for sorts, not including the memory requirements for all other operations in the instance. While we have that available on the server, we are in the process of migrating to 32-bit Oracle running on IBM Regatta servers running AIX 5L OS. As such, with the 32 bit software, we have an instance limit on the memory of something like 2.5G, so will have a problem I believe.

Thanks for the input thus far.......any ideas?

Report message to a moderator

Previous Topic: ORACLE 9I INSTALLATION ON LINUX
Next Topic: ora01093
Goto Forum:
  

[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Fri Sep 20 05:52:00 CDT 2024
.:: Forum Home :: Blogger Home :: Wiki Home :: Contact :: Privacy ::.