Re: so ok one of the users blew out temp during production flows last night

From: Greg Rahn <>
Date: Wed, 11 Mar 2009 12:23:50 -0700
Message-ID: <>

Limiting CPU, etc, will not impact temp space usage.

I've seen many "clever" people try and limit impact by limiting resources without thinking it through. For example, on a warehouse that allows parallel query one such person thought it would be best to limit the DOP to 2 to users instead of a much higher number like 16 or 32. This caused the execution plans to change as more PGA memory can be allocated the more PQ slaves that a query uses, so using less PQ slaves caused more to spill to TEMP and thus increasing not only the elapsed times of the queries, but the TEMP usage and the writes associated with that usage. And "clever" users thought, well, if I get less resources for a single query, then I will just run multiple sessions at the same time. The moral of the story is that water generally seeks its own level, and IT people need to in touch with the business needs, not just preventing hard failures. Sometimes it is best to give a few people lots of resources so their queries finish fast and just limit the number of active sessions and queue them. That way you dont get the slow running query that has run for 2 hours, then a user cancels it thus wasting 2 hours of his/her time as well as consuming 2 hours of wall time (and who knows how much DB time) all for nothing.

On Wed, Mar 11, 2009 at 9:28 AM, <> wrote:


> the own ts thing i brought up as an option but was shot down as a secondary
> thing to try after putting in oracle profiles and limiting things like cpu.
Greg Rahn
Received on Wed Mar 11 2009 - 14:23:50 CDT

Original text of this message