Simpler Navigation for Servers and Operating Systems
Completed: a much simpler Servers and Operating Systems section of the Community. We combined many of the older boards, so you won't have to click through so many levels to get at the information you need. Check the consolidated boards here as many sub-forums are now single boards.
cancel
Showing results for 
Search instead for 
Did you mean: 

deciding size of temp tablespace

SOLVED
Go to solution
Ratzie
Super Advisor

deciding size of temp tablespace

We are having issues with our temp tablespace. This is used for reporting so we having users that may not be very good at creating queries and have basically created a run away query that has basically crashed our system.

With a database at about 140 GB what size should you have a temp tablespace at?
Should we create temp tablespaces for each particular user as to avoid this issue.

We have had a temp space as big as 30GB which I think is huge, but one of our users has millions of lines in a table (he has now partitioned it) but still for a week or month of data, there are still millions. Is there anything that can determine the size of what temp should be?

Also, how do we clean this up, quickly and easy, besides creating another tablespace and they renaming to old. (with out bring down the instance)
I guess I am looking at how temp actually works.

Appreciate the help.
7 REPLIES
Oviwan
Honored Contributor

Re: deciding size of temp tablespace

Hey

we have two temp tablespaces. one for the application and one for the users for their sql.

check this thread for other opinions:
http://forums.oracle.com/forums/thread.jspa?threadID=524966&tstart=15

Hope this helps...

Regards
Hein van den Heuvel
Honored Contributor

Re: deciding size of temp tablespace

Hello Laura

You never need to bring down a DB to resize TEMP, but you may have to take down users which may be much the same as taking down the DB.

If you are so lucky, the simple solution will be:
alter database tempfile 'x.dbf' resize NNNm;

However, this is likely to file with 'space in use' on an active system.

You many also need to create an extra temp tabelspace before clearing the original.
This may become a four step thing if you like to get back to the original names/locations:
- add temporary temp
- drop temp
- re-create temp as desired
- drop temporary temp

I sometimes use this to temoprary put temp on a file system for file-system backups and then swithc batch to raw devices for production usage.

See:

http://www.idevelopment.info/data/Oracle/DBA_tips/Tablespaces/TBS_3.shtml

and...

http://sysdba.wordpress.com/2006/04/28/how-to-adjust-the-high-watermark-in-oracle-10g-alter-table-shrink/

and google "oracle shrink temp" for many more...

Cheers,
Hein.
Ben Dehner
Trusted Contributor
Solution

Re: deciding size of temp tablespace

The determining factor of the temp tablespace really isn't the data size, but the types of queries that are hitting the database. Cartesian products or lots of subqueries can use lots of temporary sorts.

That being said, I think 30 GB is rather excessive for temp tablespace. The largest I've ever seen is about 2 GB or so on a 100+ GB database. And though I find the autoextend feature on datafiles useful, I *never* enable autoextend on tempfiles or rollback/undo tablespaces, so some bad query or update doesn't fill up the disk. Also, any query that uses this much temp space is probably going to run very slowly because there is going to be a lot of disk IO for the temporary sort segments.

Most optimization begins by looking at the SQL; what is possible depends on your database architecture. Here are some options:

1) fix the SQL so that it runs more efficiently

2) add an index or two so that the query can run better

3) use materialized views to maintain intermediate results, so the query has to do less sorting work at runtime
Trust me, I know what I'm doing
Eric Antunes
Honored Contributor

Re: deciding size of temp tablespace

Hi LHradowy,

There are 2 parameters you should look at before concerning temp tablespace:

pga_aggregate_target
workarea_size_policy

workarea_size_policy should be set to AUTO and pga_aggregate_target to 1024Mb is a good start for an OLTP database.

After this, check if you have statistics on this particular tables.

Finally, if this don't get better, sql is the issue...

The normal size is something between 2-4Gb

Best Regards,

Eric Antunes
Each and every day is a good day to learn.
Pete Randall
Outstanding Contributor

Re: deciding size of temp tablespace

We assign temp tblspace randomly to each user when they log in, using this bit of code in /etc/profile:

# Following code set to randomly assign dbspace
NUMBER=`date +%S | cut -c2,2`
set +A LINE_ARRAY `cat /etc/dbspace`
DBSPACETEMP=${LINE_ARRAY[$NUMBER]}
export DBSPACETEMP
#############################################


Pete

Pete
Pete Randall
Outstanding Contributor

Re: deciding size of temp tablespace

I guess the contents of /etc/dbspace might be helpful:

#cat /etc/dbspace
dbs14 dbs13 dbs12 dbs11 dbs09 dbs10 dbs14 dbs13 dbs12


Pete

Pete
Yogeeraj_1
Honored Contributor

Re: deciding size of temp tablespace

Hi,

If you can categorise your users in several groups you can create several temp tablespaces with extent management of 64k, 128k, 256k, 512k, 1024k.

e.g. (nb. i use ASM so it would be a bit different for you when specifying the value for tempfile)

CREATE TEMPORARY TABLESPACE TEMP01 TEMPFILE
'+DG_DATA/mydb/datafile/temp01.dbf' SIZE 1024M AUTOEXTEND ON NEXT 640K MAXSIZE
1024M
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 128K
BLOCKSIZE 8K
/


--
-- TEMP02 (Tablespace)
--
CREATE TEMPORARY TABLESPACE TEMP02 TEMPFILE
'+DG_DATA/mydb/datafile/temp02.dbf' SIZE 1024M AUTOEXTEND ON NEXT 640K MAXSIZE
1024M
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 256K
BLOCKSIZE 8K
/

--
CREATE TEMPORARY TABLESPACE TEMP03 TEMPFILE
'+DG_DATA/mydb/datafile/temp03.dbf' SIZE 1024M AUTOEXTEND ON NEXT 640K MAXSIZE
1024M
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 512K
BLOCKSIZE 8K
/


--
-- TEMP04 (Tablespace)
--
CREATE TEMPORARY TABLESPACE TEMP04 TEMPFILE
'+DG_DATA/mydb/datafile/temp04.dbf' SIZE 1024M AUTOEXTEND ON NEXT 640K MAXSIZE
1024M
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M
BLOCKSIZE 8K
/


After you have created these temporary tablespaces, you change the user profile using sql command as follows:

alter user temporary tablespace ;

You can do it without any downtimes and as soon as the user creates a new session, the change is immediately applicable.

If you need any further assistance, please let us know.

good luck!

kind regards
yogeeraj
No person was ever honoured for what he received. Honour has been the reward for what he gave (clavin coolidge)