Temporary tablespace and uniform extents

From: Mladen Gogala <mgogala_at_yahoo.com>
Date: 14 May 2008 13:59:05 GMT
Message-ID: <482af029$0$30633$834e42db@reader.greatnowhere.com>


Once upon a time, in an Oracle version far, far away, there existed a link between SORT_AREA_SIZE and the size of uniform extent in the default temporary tablespace. Then came the automatic memory management, first in 9i, then in 10g and, the latest iteration, memory management in 11g. Temporary tablespace and creation scripts from DBCA, however, still live in 8i times. Here is the creation script from my 11g:

 CREATE TEMPORARY TABLESPACE "TEMP" TEMPFILE   '/oracle/oradata/test11/temp01.dbf' SIZE 188743680   AUTOEXTEND ON NEXT 655360 MAXSIZE 32767M   EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1048576;   I tried creating an "autoallocate" version but it failed:

SQL> create temporary tablespace temp1
  2 tempfile '/oracle/oradata/test11/temp_test.dbf' size 1024M   3 extent management local autoallocate; extent management local autoallocate

                        *

ERROR at line 3:
ORA-25139: invalid option for CREATE TEMPORARY TABLESPACE

Now, how can I determine an optimal extent size in a situation where SORT_AREA_SIZE is dynamic? I only have PGA_AGGREGATE_TARGET, no SORT_AREA_SIZE any more. Extents in the temporary tablespace are still fixed length. Once upon a time, common wisdom for sizing the extent of the temporary tablespace was SORT_AREA_SIZE + 2 blocks of overhead. What do we do now?

-- 
Mladen Gogala
http://mgogala.freehostia.com
Received on Wed May 14 2008 - 08:59:05 CDT

Original text of this message