sql - Oracle SQL - 查询时间限制

Question

以类似的方式

select * from mytable where rownum <= 1000;

会给我查询的前 1000 行结果，有没有办法

select * from mytable where runtime <= 1000;

哪个将返回在运行查询的前 1000 个 中获得的结果？

Answer 1

Oracle does not support this ，至少不像你的例子那样简单。

我发现 One blog 能够限制用户在他们创建的某个资源组中的执行时间。他们为上述用户创建了一个特殊组，然后他们为该用户定义了一个名为 LIMIT_EXEC_TIME 的资源计划。他们的代码如下供引用:

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;

部分结果

查询可以返回部分结果，但查询也会抛出异常“ORA-00040:超出事件时间限制 - 调用中止”，客户端必须忽略该异常。

这可以用一个执行大量 CPU 工作的函数来模拟:

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 可以演示客户端能够读取部分结果:

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> 

注意本例中的 Elapsed time 是 8 秒。我将超时设置为 5 秒，这表明很难获得良好的精度。

CPU 时间，未耗时

资源管理器只计算 CPU 时间，而不是耗时。尽管文档是这样说的。 Pythian 文章中的一条评论表明可以通过 ALTER SYSTEM SET EVENT = '10720 trace name context forever, level 16384' scope=spfile;(和重新启动)更改此行为，但这对我不起作用。

例如，创建这个函数:

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;
/

这个 SELECT 将运行整整 100 秒，因为它没有使用 100 秒的 CPU。

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