Re: Sort tablespace usage

"Vincent Ventrone" <vav_at_brandeis.edu> wrote in message news:9gl5p3$l33$1_at_new-news.cc.brandeis.edu...
>
> > Don't know, I haven't looked at that yet. I'd probably check CREATE
> > TABLESPACE in the SQL Reference, and related material in Oracle 8i
> > Concepts.
>
> Look for "CREATE TEMPORARY TABLESPACE..." (8i syntax). Note: you will
 need
> to use temporary files & you should (IMHO) make it a locally-managed
> tablespace. You probably also want to use uniform extent sizes.
>
> Here's an example to get you started:
>
> CREATE TEMPORARY TABLESPACE TEMP
>
> TEMPFILE '/r01/oradata/catdev/temp_01.dbf' SIZE 100M
>
> AUTOEXTEND ON NEXT 25M MAXSIZE 500M
>
> EXTENT MANAGEMENT LOCAL UNIFORM SIZE 10M;
>
>
>

Bloody hell! What size sort_area_size do you have on that database!!!?? I only ask, because the 'uniform size' ought to be the sort_area_size (or multiples thereof, and the multiples idea was only ever invented to avoid the creation of thousands of extents, which aren't a problem in locally managed tablespace anyway), so 10M extents sounds awfully large.

If a sort requires 2M of temp space, and assuming a sort_area_size of 1M, you'll be wasting 8M, where 1M extents would have done quite nicely.

It's also the case that with a 100M datafile, and 10M extents, you'll be wasting 9.9something M of space at the end of the tablespace, since the first 64K of the tablespace is taken up with the tablespace bitmap, and hence there isn't 100M of useable space in that tablespace.

A better one would have been ...SIZE 10064K...uniform size 10M.

But I'm still left wondering how big your sort_area_size is, and how big your sorts are....

Regards
HJR Received on Tue Jun 19 2001 - 04:12:35 CDT