deciding size of temp tablespace

[Ratzie]

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.

[Oviwan]

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]

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 · Accepted solution]

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

[Eric Antunes]

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

[Pete Randall]

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 Randall]

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

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


Pete

[Yogeeraj_1]

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