Re: Under performing queries can have their execution avoided by an application?

On Feb 1, 3:05 am, "Ana Ribeiro" <ana.ribe..._at_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. Received on Fri Feb 02 2007 - 14:04:35 CST