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

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, <TESTAJ3_at_nationwide.com> wrote:

>

-- 
Regards,
Greg Rahn
http://structureddata.org
--
http://www.freelists.org/webpage/oracle-l