Re: so ok one of the users blew out temp during production flows last night
- From: Greg Rahn <greg@xxxxxxxxxxxxxxxxxx>
- To: TESTAJ3@xxxxxxxxxxxxxx
- Date: Wed, 11 Mar 2009 12:23:50 -0700
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, <TESTAJ3@xxxxxxxxxxxxxx> 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.
--
Regards,
Greg Rahn
http://structureddata.org
--
//www.freelists.org/webpage/oracle-l
Other related posts:
