Re: temp extent size

From: joel garry <joel-garry_at_home.com>
Date: 18 Jan 2007 16:36:16 -0800
Message-ID: <1169166976.708274.275680@38g2000cwa.googlegroups.com>

viny wrote:
> My sort area size is 8MB and db_block_size is 8KB
>
> I have created locally managed temporary tablespace. What should be
> ideal value for INITIAL, NEXT extent size for the locally managed
> temporary tablespace??
>
> I am using 8MB+8KB extent size. Is it higher/lower? Need the suggestion
> asap.
>
> thanks in advance !!
>
> regards,
> vinay

Does the syntax for creating a temporary tablespace even allow you to set initial and next? (Depends on version, which you should state.)

from metalink Note:102339.1 Temporary Segments: What Happens When a Sort Occurs

Locally-managed tablespace (extent management by the tablespace; Oracle8.1+).

A tablespace that manages its own extents, maintains a bitmap in each

datafile to keep track of the free or used status of blocks in that datafile.Each bit in the bitmap corresponds to a block or a group of blocks. When an extent is allocated or freed for reuse, Oracle changes
the bitmap values to show the new status of the blocks. A tablespace that manages its extents locally can have either uniform extent sizes

or variable extent sizes that are determined automatically by the system. When you create the tablespace, the UNIFORM or AUTOALLOCATE (system-managed) option specifies the type of allocation. The storage parameters NEXT, PCTINCREASE, MINEXTENTS, MAXEXTENTS, and DEFAULT STORAGE are not valid parameters for extents that are managed locally.

So of course, if you use uniform, that would imply the initial. I would set it much higher, dependent on how much data you intend to be putting through temp. You should also figure out what goes into temp, while you are at it (it's in the docs). Somewhere is told the limited number of segment sizes for LMT.

Oddly enough, looking at some script (hacked from an old wizard, probably) that created my 9.2 db, it has this clause in the create database statement:

...
EXTENT MANAGEMENT LOCAL
DEFAULT TEMPORARY TABLESPACE TEMP TEMPFILE '/oradata/xxxx/temp01.dbf' SIZE 40M REUSE AUTOEXTEND ON NEXT 640K MAXSIZE 10000M ...

But of course, since it is LMT, it's 1M:

1 select * from dba_tablespaces
2* where tablespace_name like 'TEM%'
SQL> /

TABLESPACE_NAME                BLOCK_SIZE INITIAL_EXTENT NEXT_EXTENT

MIN_EXTENTS

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

MAX_EXTENTS PCT_INCREASE MIN_EXTLEN STATUS CONTENTS LOGGING FOR EXTENT_MAN
----------- ------------ ---------- --------- --------- --------- ---

ALLOCATIO PLU SEGMEN DEF_TAB_
--------- --- ------ --------

TEMP                                 8192        1048576     1048576
       1
                       0    1048576 ONLINE    TEMPORARY NOLOGGING NO

LOCAL
UNIFORM NO MANUAL DISABLED That would be because the NEXT is ignored with LMT. At least, I think that's what happened, it's hard to know if an old create statement is the same as the temp that is there. Anyways, it works good for my environment. You need to test what is good for your environment.

-- 
@home.com is bogus.
http://www.medilexicon.com/medicalnews.php

Received on Thu Jan 18 2007 - 18:36:16 CST