Preventing runaway SQL in Oracle -

in existing application there several dynamically generated sql statements being executed. of slow in performance , block ui.

without changing code generates sql wondering if there way prematurely stop oracle/sql statement exceeds a) execution time threshold b) number of result rows

while b) sounds easy, not easy in application infrastructure dealing with, because not recordset can iterate on sql being executed. guess in ways huge number of result rows trigger time threshold.

i read using oracle resource manager, wasn't sure if can address a) , b) , if easiest way solve this. hoping there session/connection options me.

thanks in advance!

create profile limit cpu time on single sql call. assign profile application user.

--create profile limits cpu per call 1 second. create profile temp_profile limit cpu_per_call 100;  --create user, assign profile. create user profile_test_user identified "asdf1234!"; alter user profile_test_user profile temp_profile; grant connect profile_test_user; 

that user errors this:

profile_test_user@somedb> select count(*) user_objects;    count(*) ----------          0  profile_test_user@somedb> select count(*) all_objects; select count(*) all_objects * error @ line 1: ora-02393: exceeded call limit on cpu usage 

in general approach should last resort. it's better spend time tuning queries , databases.