RE: Best Extent Size for Locally Managed Temporary Tablespaces

Thanks for the primer on locally managed tablespaces, but all that having been said, I'm looking for tuning ideas.

I'm thinking of uniform extents. It would be good to develop some sort performance benchmarks. We could test a few permutations of dictionary managed vs various size permutations of uniform local vs system-managed in relation to permutations of SORT_AREA_SIZE and sort_area_retained_size. Any other ideas? Any white papers?

We could rank the results. It could be "a sorted affair." :-)

Steve Orr
Maybe I'll work on this before someone beats me to it and win a free trip to Disney.

-----Original Message-----

From: Davenport, Linda [mailto:LDavenport_at_umpublishing.org] Sent: Friday, June 16, 2000 11:38 AM
To: steve_at_arzoo.com; Oracledba; Oracle-L Subject: RE: Best Extent Size for Locally Managed Temporary Tablespaces

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.

For system-managed extents, you can specify the size of the initial extent and
Oracle determines the optimal size of additional extents, with a minimum extent
size of 64 KB. This is the default for permanent tablespaces.

For uniform extents, you can specify an extent size or use the default size, which is 1 MB. Temporary tablespaces that manage their extents locally can only
use this type of allocation.

The storage parameters NEXT, PCTINCREASE, MINEXTENTS, MAXEXTENTS, and DEFAULT
STORAGE are not valid for extents that are managed locally.

The above is from Note:93771.1.

Hope this helps! :)
Linda

-----Original Message-----

From: Steve Orr [mailto:sorr_at_arzoo.com]
Sent: Friday, June 16, 2000 12:47 PM
To: Oracledba; Oracle-L
Subject: Best Extent Size for Locally Managed Temporary Tablespaces

In the old Oracle7 world the recommended practice was to make initial and next extent sizes to be a multiple of SORT_AREA_SIZE plus 1 DB block. I guess the thinking was that if a sort needed to move from memory to disk then it could fit neatly into the next extent.

So lets say:
SORT_AREA_SIZE = 512K and
sort_area_retained_size = 64K

Any ideas for deriving the best extent size for a locally managed temporary tablespace? Or do you think it no longer makes a difference? Received on Fri Jun 16 2000 - 14:27:35 CDT