Update on 'buffer busy waits' on Header Block (#2) of Tempfile

Tim and Mark both lost me after 3 lines on the medical analogies.

However, Mark is absolutely right when he says
"This is a new system, so the existing plan is the only plan, not a new plan."

Behaviour that did not manifest in testing and SQL statements that did not come to the fore then are now the problem.

Here's how I "treated" the issue today :

1. Had the DBA rebuild the TemporaryTablespace with 1MB extents.
2. Tuned a couple of queries
3. Allowed the longest running SQL Query to fail on TempTablespace -- asked the DBA to build the TempTablespace with fixed sized files and NOT autoextend. (The SQL had a "cost" of 50G but a "TempSpace" requirement of 100M fetching about 2T intermediate rows for a Hash Join --- quite obviously the Explain Plan is very wrong in it's cardinality estimates. This needs some tuning, eh !).
4. Got rid of Parallelism on a table where ParallelQuery slaves themselves were contending on the Tempfile as more sessions fired concurrent queries. We changed the queries to run in Serial mode and take 10minutes instead of
   "4minutes to 30minutes" (as concurrency went up, response time went down)
   and eliminated waits on the Tempfile. Here's the question : How did the 10g automatic default setting of PARALLEL_MAX_SERVERS get to be "80" when CPU_COUNT is 2 and PARALLEL_THREADS_PER_CPU is 2. (don't ask -- but, no, I wasn't the one who set a Degree of 4 on the table in the first place).
5. Today we had only 310 concurrent sessions, not the peak of 1350 yesterday. My guess is that we had three problems
6. Many users wanted to login to the new system
7. As more users came in and got a slow response, they kept retrying
8. The Apps team had increased the number of Application Sessions permitted to allow the "expected surge" in user count. However, I hadn't been informed of this and we hadn't anticipated that connection pooling would virtually be replaced by dedicated sessions per user !

More "tuning" to be done in the next two days.

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

--
http://www.freelists.org/webpage/oracle-l