I agree with Stephane's response in principal as well as specifics.
Responses on this thread have fallen into one of two camps:
1. address the symptom as described
2. find out more about what is going on, because the
observed effect may be based on an anomaly in the application
In the medical arena, course #1 can be described as "allopathic" while
course #2 can be described as "homeopathic". On dictionary.com,
"homeopathy" is defined as "the method of treating disease by drugs,
given in minute doses, that would produce in a healthy person symptoms
similar to those of the disease", while "allopathy" is described as
"the method of treating disease by the use of agents that produce
effects different from those of the disease treated". In essence,
homeopathy is the more considered response (i.e. "why is symptom XYZ
happening"), while allopathy is the knee-jerk response (i.e. "treat
symptom XYZ with response ABC"). Each has their appropriate
application in medicine, but in database and application tuning, I
would argue that the homeopathic course is more effective in the
larger number of cases. Find out what is going on, why so much TEMP
space is being demanded, because the reason could be something simple
like a missing or poorly formed index.
In fact, I will gladly bet a round of drinks that the problem is a new execution plan recently adopted by the CBO or a missing index, or a poorly-conceived index, or a combination of all three, and has nothing to do with the configuration or implementation of the temporary tablespaces, nor does it have anything to do with PGA configuration.
...I'm glad to be wrong as well as right, because either outcome still results in a round of drinks...
Any takers? Quoting goran bogdanovic <goran00@xxxxxxxxx>:
Hi, Since your contention is on header block, increasing extent size from 256k to 1mb sound reasonably to me. One of the reasons why processes checks the segment header is to extend the HWM. Have you found which queries are consuming much of the temp space? Can they be optimized? regards, goran On Mon, Apr 28, 2008 at 2:36 PM, Hemant K Chitale <hkchital@xxxxxxxxxxxxxx> wrote:We'd rolled out a new system today and user connections rapidly went up to 900 sessions. OK, we are supposed to be able to handle that. However, by the afternoon, I saw 'buffer busy waits' on Block#2 of the first tempfile of the Temporary Tablespace (using a custom Temporary tablespace instead of 'TEMP', although the default 'TEMP' exists). By evening, we had 300 sessions waiting on 'buffer busy waits' on the same block (querying V$SESSION_WAIT for P1, P2). Although the tablespace has 3 tempfiles, the first file has 350 sessions against it and the other two have less than 100 sesssions put together (querying V$TEMPSEG_USAGE for SEGFILE#). These are a mix of SORT and HASH extents. How can I address this ? a. Increase PGA_AGGREGATE_TARGET (1GB for 400 concurrent users + 10-15 batch jobs) and/or switch to WORKAREA_SIZE_POLICY='MANUAL' with SORT_AREA_SIZE and HASH_AREA_SIZE. b. Add more tempfiles to the tablespace and/or rebuild the tablespace with larger Extent Sizes (1MB instead of the current value of 256K) Any other suggestions ? Hemant K Chitale http://hemantoracledba.blogspot.com "A 'No' uttered from the deepest conviction is better than a 'Yes' merely uttered to please, or worse, to avoid trouble." Mohandas Gandhi Quotes : http://www.brainyquote.com/quotes/authors/m/mohandas_gandhi.html -- //www.freelists.org/webpage/oracle-l
-- //www.freelists.org/webpage/oracle-l
