SEO

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

On Feb 1, 12:05 pm, "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

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

sybrandb wrote:
> On Feb 1, 12:05 pm, "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
>
> 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

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

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

Ana Ribeiro 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

As Sybrand says look at creating a PROFILE for those users:
http://www.psoug.org/reference/profiles.html
Also look at the possibility of leveraging Resource Management:
http://www.psoug.org/reference/dbms_res_mgr.html
--
Daniel A. Morgan
University of Washington
damorgan@x.washington.edu
(replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org

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.