Oracle SQL - Query timed out

Oracle doesn't support this , at least not as simple as your example.

One blog I found that I was able to limit the execution time of users in a specific resource group they created. They created a dedicated group for the specified users, and then they defined a resource plan that they named LIMIT_EXEC_TIME

for that user. Their code looks like this:

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 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 generate an ORA-00040: time limit exceeded - call aborted exception, which should be ignored by the client.

This can be modeled 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 that the client can 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 shows that it is difficult to get good accuracy.

CPU time not elapsed

The resource manager only counts processor time, not elapsed time. This is despite what the documentation says. One of the comments in the Pythian article suggests that this behavior can be changed with ALTER SYSTEM SET EVENT = '10720 trace name context forever, level 16384' scope=spfile;

(and rebooting), 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 100 seconds since it doesn't use 100 seconds of CPU.

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