Re: Temporary tablespace and uniform extents

On May 14, 9:59 am, Mladen Gogala <mgog..._at_yahoo.com> wrote:
> 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 Gogalahttp://mgogala.freehostia.com

The Oracle Performance Tuning Guide for 10g R2 and 11g R1 seem to indicate that Oracle favors 1MB extent sizes, likely as this matches the recommended (tested to be best performance) stripe width for Oracle's SAME configuration. From:
http://download.oracle.com/docs/cd/B28359_01/server.111/b28274/build_db.htm "Properly configuring the temporary tablespace helps optimize disk sort performance. Temporary tablespaces can be dictionary-managed or locally managed. Oracle recommends the use of locally managed temporary tablespaces with a UNIFORM extent size of 1 MB.

You should monitor temporary tablespace activity to check how many extents are being allocated for the temporary segment. If an application extensively uses temporary tables, as in a situation when many users are concurrently using temporary tables, the extent size could be set smaller, such as 256K, because every usage requires at least one extent. The EXTENT MANAGEMENT LOCAL clause is optional for temporary tablespaces because all temporary tablespaces are created with locally managed extents of a uniform size. The default for SIZE is 1M."

Charles Hooper
IT Manager/Oracle DBA
K&M Machine-Fabricating, Inc. Received on Wed May 14 2008 - 12:06:27 CDT