RE: Two questions about resource profiles

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_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org]On Behalf Of Ethan Post   Sent: Tuesday, March 14, 2006 2:06 PM
  To: gints.plivna_at_gmail.com
  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_at_gmail.com> wrote:     Hello!

    I'm investigating resource profiles to prevent users from running     unreasonably long time queries.

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