RE: Two questions about resource profiles
- From: "Mark W. Farnham" <mwf@xxxxxxxx>
- To: <post.ethan@xxxxxxxxx>, <gints.plivna@xxxxxxxxx>
- Date: Tue, 14 Mar 2006 16:20:34 -0500
I think Ethan makes an excellent point, and there is more: While long
running, expensive queries are the only ones worth looking at to determine
whether the query is "bad," that does NOT mean that every long running query
IS "bad." Some very expensive queries are also optimal and absolutely
required.
Flash back 25 years. The default timesharing quota control by user was "if a
user's job consumes more than x in resources, kill it." "x" varied by user.
As data accumulated for any time sharing user, job costs also tended to
increase. So eventually some routine job would eventually exceed the
"quotas" for that user for a single job and the job would get killed. Since
most software of that vintage did not support the idea of savepoints and
continuing a job with some of the work completed, that meant the customer
paid for the job nearly twice. No one ended up happy. Of course no one
wanted to pay for an infinite loop, either. In an Oracle environment you'll
get even more extra cost and delay while the rollback executes before you
can re-run the job. Of course if the job is not a monolith you may only have
to roll back to some point in the process and be able to continue from
there, but controlling the costs of resource control is not an issue to
ignore.
If your shop has job naming conventions, then you *may* have a way to let
you know that job x by user y should be allowed to run, or set some string
in the job envionment that lets you know that the job is a production job
rather than an ad hoc query. Anyway good luck.
-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx]On Behalf Of Ethan Post
Sent: Tuesday, March 14, 2006 2:06 PM
To: gints.plivna@xxxxxxxxx
Cc: Oracle Discussion List
Subject: Re: Two questions about resource profiles
Be aware you are more likely going to come across as an unreasonable DBA.
Better to monitor for long running queries and when you find one find out
who, what, when and why before you shoot.
On 3/13/06, Gints Plivna <gints.plivna@xxxxxxxxx> wrote:
Hello!
I'm investigating resource profiles to prevent users from running
unreasonably long time queries.
Other related posts:
