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

Ana,

I once wrote a similar application to allow users to construct queries (probably a bit more structured than what you mention). In the process the sql was constrctured dynamically and then executed using a wrapper on dbms_sql. In that code, if it were a new query/report, I would analyze the sql first, then inspect the plan table. If I found details that showed a "bad" query, a message would be presented back to the user.

Vince Received on Thu Feb 01 2007 - 11:53:13 CST