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

sybrandb wrote:
> On Feb 1, 12:05 pm, "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
>
> Please look up the CREATE PROFILE statement, where you can set limits
> for various categories.
> The init.ora parameter resource_limit must be set to TRUE for this to
> work.
> Also you could use the Resource Manager provided in Oracle to make
> sure these processes run at lower priority.
> Undoubtedly, profiles are discussed in the Concepts Manual.
> In order to avoid redundant questions, reading the Concepts Manual is
> compulsory.
>
>
> --
> Sybrand Bakker
> Senior Oracle DBA

I second that and just want to point you to CPU_PER_CALL and LOGICAL_READS_PER_CALL. Just experiment and set appropriate values. If query will not give any row back within thresholds of CPU_PER_CALL and LOGICAL_READS_PER_CALL then bang! it is stopped and error is issued which you can easily trap and say that this was bad query. We used this technique with good results for a dynamic search that included a bunch of optional parameters across many tables. As select was built in a dynamic way we were not able to test all combinations, so as a quick and dirty way (just in the beggining until we tuned the possible combinations) we used these parameters.

Gints Plivna
http://www.gplivna.eu Received on Thu Feb 01 2007 - 11:27:38 CST