Is it possible to limit how long an Oracle SQL query runs for and return the results it gathered in the time limit?

Question

In a similar fashion to how

select * from mytable where rownum <= 1000;

will give me the first 1000 rows of results of a query, is there a way to

select * from mytable where runtime <= 1000;

which would return the results obtained in the first 1000 <time units> of running the query?

Answer 1

Oracle does not support this, at least not in an easy sense like your example.

One blog I found was able to limit the execution time of users in a certain resource group that they made. They created a special group for said users, and then they defined a resource plan that they called LIMIT_EXEC_TIME for that user. Their code is as follows for reference:

set serverout on size 5555
--
-- first remove an existing active plan
ALTER SYSTEM SET RESOURCE_MANAGER_PLAN ='';
--
-- delete any existing plan or group
-- we have to create a pending area first
exec dbms_resource_manager.clear_pending_area();
exec dbms_resource_manager.create_pending_area();
exec dbms_resource_manager.DELETE_PLAN ('LIMIT_EXEC_TIME');
exec dbms_resource_manager.DELETE_CONSUMER_GROUP ('GROUP_WITH_LIMITED_EXEC_TIME');
exec DBMS_RESOURCE_MANAGER.VALIDATE_PENDING_AREA;

exec DBMS_RESOURCE_MANAGER.SUBMIT_PENDING_AREA();
begin
  dbms_resource_manager.create_pending_area();
  --
  -- we need a consumer group that maps to the desired oracle user:
  dbms_resource_manager.create_consumer_group(
    CONSUMER_GROUP=>'GROUP_WITH_LIMITED_EXEC_TIME',
    COMMENT=>'This is the consumer group that has limited execution time per statement'
    );
  dbms_resource_manager.set_consumer_group_mapping(
    attribute => DBMS_RESOURCE_MANAGER.ORACLE_USER,
    value => 'PYTHIAN',
    consumer_group =>'GROUP_WITH_LIMITED_EXEC_TIME'
  );

  -- and we need a resource plan:
  dbms_resource_manager.create_plan(
    PLAN=> 'LIMIT_EXEC_TIME',
    COMMENT=>'Kill statement after exceeding total execution time'
  );

  -- now let's create a plan directive for that special user group
  -- the plan will cancel the current SQL if it runs for more than 120 sec
  dbms_resource_manager.create_plan_directive(
    PLAN=> 'LIMIT_EXEC_TIME',
    GROUP_OR_SUBPLAN=>'GROUP_WITH_LIMITED_EXEC_TIME',
    COMMENT=>'Kill statement after exceeding total execution time',
    SWITCH_GROUP=>'CANCEL_SQL',
    SWITCH_TIME=>15,
    SWITCH_ESTIMATE=>false
  );

  dbms_resource_manager.create_plan_directive(
    PLAN=> 'LIMIT_EXEC_TIME',
    GROUP_OR_SUBPLAN=>'OTHER_GROUPS',
    COMMENT=>'leave others alone',
    CPU_P1=>100
  );

  DBMS_RESOURCE_MANAGER.VALIDATE_PENDING_AREA;

  DBMS_RESOURCE_MANAGER.SUBMIT_PENDING_AREA();

end;
/

exec dbms_resource_manager_privs.grant_switch_consumer_group('PYTHIAN','GROUP_WITH_LIMITED_EXEC_TIME',false);

exec dbms_resource_manager.set_initial_consumer_group('PYTHIAN','GROUP_WITH_LIMITED_EXEC_TIME');

select * from DBA_RSRC_CONSUMER_GROUPS;
select * from DBA_RSRC_GROUP_MAPPINGS;
select * from DBA_RSRC_PLANS;
select * from DBA_RSRC_PLAN_DIRECTIVES;

-- to enable it:
ALTER SYSTEM SET RESOURCE_MANAGER_PLAN ='LIMIT_EXEC_TIME';

SELECT se.sid sess_id, co.name consumer_group,
 se.state, se.consumed_cpu_time cpu_time, se.cpu_wait_time, se.queued_time
 FROM v$rsrc_session_info se, v$rsrc_consumer_group co
 WHERE se.current_consumer_group_id = co.id;

select username,resource_CONSUMER_GROUP,count(*) from v$session group by username,resource_CONSUMER_GROUP;

Partial Results

Queries can return partial results but the query will also throw exception "ORA-00040: active time limit exceeded - call aborted" that must be ignored by the client.

This can be simulated with a function that does a lot of CPU work:

create or replace function sleep_cpu return number authid current_user is
    v_loop number := 0;
begin
    for i in 1 .. 10000000 loop
        v_loop := v_loop + 1;
    end loop;
    
    return v_loop;
end;
/

SQL*Plus can demonstrate a client able to read partial results:

SQL> set timing on
SQL> select sleep_cpu()
  2  from dual
  3  connect by level <= 100;

SLEEP_CPU()
-----------
   10000000
   10000000
   10000000
   10000000
   10000000
   10000000
   10000000
   10000000
   10000000
   10000000
   10000000
   10000000
   10000000
   10000000
   10000000
ERROR:
ORA-00040: active time limit exceeded - call aborted


15 rows selected.

Elapsed: 00:00:08.52
SQL> 

Note the Elapsed time in this example is 8 seconds. I set the timeout to 5 seconds, this demonstrates that it's hard to get good precision.

CPU Time, Not Elapsed Time

Resource manager only counts CPU time, not elapsed time. This is despite what the documentation says. One of the comments in the Pythian article suggests this behavior can be changed with an ALTER SYSTEM SET EVENT = '10720 trace name context forever, level 16384' scope=spfile; (and a restart), but that didn't work for me.

For example, create this function:

create or replace function sleep_no_cpu return number authid current_user is
begin
    execute immediate 'begin dbms_lock.sleep(1); end;';
    return 1;
end;
/

This SELECT will run for the whole 100 seconds because it's not using 100 seconds of CPU.

select sleep_cpu()
from dual
connect by level <= 100;