Re: Temp space vs. table size

DerWoud wrote in message <3486203e.5548766_at_news.bna.bellsouth.net>...
>I currently have 2 temp segments, one 40 and one 70 meg, 550 extents
>at 256K each percent increase set to 0. I keep running out of temp
>space while doing a non-grouping query on a very wide (72 fields),
>very long table. This table comes in at just under 800 meg. Is there
>some sort of desired ratio between temp space and your largest table?
>Thanks in advance,
>derwoud_at_bellsouth.net

Temporary segments should be placed in their own tablespace. The default storage characteristics (initial and next) of this tablespace should be a multiple of the init.ora parameters sort_area_size plus db_block_size.

Your temporary segments are too small. Incrementally increase the size of the temporary tablespace by 100 MB until the query completes. Remember, that's just for one query that requires temporary segements, also factor in how many concurrent transactions of this type will occur and size accordingly. Don't be conservative in assigning temporary space. You may actually want to have multiple temporary tablespace with different storage characteristics for different jobs. Assign special jobs (users) to the appropriate temporary tablespace with the 'alter user' command.

Since I run 'analyze compute' I find the ratio of temporary tablespace required to the size of the largest table being analyzed is approximately 4:1.
In your case the analyze command will require close to 3 GB of temporary tablespace in order to compute statistics. I know that sounds excessive but at least that's how it works in 7.3.3.3.0.

Regards,

Craig M. Wall Sr. Oracle DBA / Manager of Oracle Technology C.I.S.A. Received on Thu Dec 04 1997 - 00:00:00 CST