Path: news.f.de.plusline.net!news-fra1.dfn.de!newsfeed.hanau.net!news.tiscali.de!tiscali!newsfeed1.ip.tiscali.net!proxad.net!216.239.36.134.MISMATCH!postnews.google.com!p10g2000cwp.googlegroups.com!not-for-mail
From: "Vince" <vinnyop@yahoo.com>
Newsgroups: comp.databases.oracle.server
Subject: Re: Under performing queries can have their execution avoided by an application?
Date: 2 Feb 2007 12:04:35 -0800
Organization: http://groups.google.com
Lines: 60
Message-ID: <1170446675.563419.59410@p10g2000cwp.googlegroups.com>
References: <1170327917.914801.230660@k78g2000cwa.googlegroups.com>
NNTP-Posting-Host: 198.31.83.50
Mime-Version: 1.0
Content-Type: text/plain; charset="iso-8859-1"
X-Trace: posting.google.com 1170446680 19674 127.0.0.1 (2 Feb 2007 20:04:40 GMT)
X-Complaints-To: groups-abuse@google.com
NNTP-Posting-Date: Fri, 2 Feb 2007 20:04:40 +0000 (UTC)
In-Reply-To: <1170327917.914801.230660@k78g2000cwa.googlegroups.com>
User-Agent: G2/1.0
X-HTTP-UserAgent: Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.1; SV1; .NET CLR 1.1.4322; InfoPath.1; .NET CLR 2.0.50727),gzip(gfe),gzip(gfe)
Complaints-To: groups-abuse@google.com
Injection-Info: p10g2000cwp.googlegroups.com; posting-host=198.31.83.50;
   posting-account=oOaxRAwAAABpgk-tSFyXemtXGYGcaZ1v
Xref: news.f.de.plusline.net comp.databases.oracle.server:193523

On Feb 1, 3:05 am, "Ana Ribeiro" <ana.ribe...@reflective.com> wrote:
> Hello,
> We have an application which gives the users the option to build on-
> line queries which will retrieve data from the Oracle 9i database in
> ANY way they want.
>
> The obvious problem we have is that some queries executes full table
> scans in very big tables, all sorts of bad queries have been issued by
> the users - and the system performance is currently very bad!
>
> My question is: is there a way to prevent the users to run very bad
> queries? I mean, if the application can check that the query doesn't
> have "enough arguments" or has a very high cost and return a warning
> message to the user, telling him that this can not be executed.
>
> Another idea would be configure something inside oracle to prevent
> "very bad" queries to be executed ...
>
> Does anyone have any ideas about it?
>
> Many thanks in advance!
> Ana

Here is roughly the code i used to get the cost of the sql statement.
In our case, we also did not wish for the sql to be executed if it's
plan was bad. That way we avoided the resource consumption to get to
the point where the limits would kick in.

function get_cost( p_statement in varchar2, p_statement_id in
plant_table.statement_id%type)
return number is
   pragma autonomous transaction;

   l_cost number;

begin

   delete plan_table where statement_id = p_statement_id;

   execute immdiate 'explain plan set statement_id = ' ||
p_statement_id || ' for ' || p_statement';
   commit;

   select cost into l_cost
   from plan_table
   where statement_id = p_statement_id
   and operation like '%STATEMENT%';

   return l_cost;

end get_cost;

Another check could be inspecting the plan_table for full table scans
on certain tables, or too many full table scans, etc.

the calling program then compared against a limit to issue a warning
and prevented the sql from running if it was over. We were sucessful
in eliminating most really bad queries this way. Maybe use the
resource_limit, etc as a backup.