Re: How much space is needed for Oracle sorting?

SCOVERBY_at_DELPHI.COM (SCOVERBY_at_news.delphi.com) wrote:

: We are experiencing running out of tablespace when we are performing
: 'order by's' in select's.
 

: In my testing, I found that I need anywhere from 3-4 times the table size
: in my sort tablespace to perform the select(and inherant sort).
 

: I calculated my tablesize by multiplying average_rowlenth *
: number_of_rows
: gathered from an analyze table. I deduced the sort size by running the
: 'order by' query and monitoring the sys.dba_free_space view.
 

: Any 'real life' experiences with tablespace needed for sorts?
 

: ==========================================================================
: Scott Overby scoverby_at_delphi.com
: SDS
: Sheyboygan, WI

Scott,

I would check the default storage parameters for your temp tablespace. It may be allocating a lot more space than it needs due to poor configuration. ORACLE writes to the temp tablespace in chunks the size of your SORT_AREA_SIZE init.ora parameter (+ 1 ORACLE block, I believe). Therefore, your INITIAL and NEXT parameters should be multiples of this, otherwise you'll be wasting space. I usually set INITIAL = NEXT, and PCTINCREASE = 0, with extents big enough so that the max # of extents (121 for 2K block size). This will make the most of your temp space.

As for figuring out how much space a particular sort will actually need, god only knows...

Hope this helps,
Gary

--
Gary Kirsh
Next Extent, Inc.
Phone: (718) 380-8546
Internet: gkirsh_at_dorsai.org