TEMP - why pctincrease 0 ?

From: Cunningham, Gerald <Gerald.Cunningham_at_usi.net>
Date: Mon, 4 Dec 2000 11:27:10 -0500
Message-Id: <10700.123536@fatcity.com>

Hi there,

Why does Oracle recommend that PCTINCREASE be set to 0?

Just wondering because I am seeing some unruly PeopleSoft queries using bad execution plans and running out of sort space. When I kill the process/query the temp segments don't seem to be released immediately. I was checking out this tip:

http://www.oracle.com/oramag/code/tips2000/Tip18.html

which says the fastest way to free up temp segments is to alter the TEMP tablespace and set pctincrease so that smon will wake up and free the extents so they may be used again.

On a related note, does anybody have a script that will show which process has allocated temp segements (and how much space)? Or is this even possible?

Thanks! Received on Mon Dec 04 2000 - 10:27:10 CST