Oracle SQL-查询时间限制 [英] Oracle SQL - Query Time Limit

问题描述

以类似的方式

select * from mytable where rownum <= 1000;

将为我提供查询结果的前1000行，有没有办法

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

select * from mytable where runtime <= 1000;

将返回在运行查询的前1000个<时间单位>中获得的结果?

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

推荐答案

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

一个博客我发现能够限制用户在特定资源组中的执行时间.他们为所说的用户创建了一个特殊的组，然后他们为该用户定义了一个称为LIMIT_EXEC_TIME的资源计划.他们的代码如下供参考:

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;

部分结果

查询可以返回部分结果，但查询还会引发异常"ORA-00040:超出活动时间限制-呼叫中止"，客户端必须忽略该异常.

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.

这可以通过执行大量CPU工作的函数来模拟:

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

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> 

请注意，本例中的经过时间为8秒.我将超时设置为5秒，这表明很难获得良好的精度.

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时间，未经过的时间

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

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.

例如，创建此功能:

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.

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;

这篇关于Oracle SQL-查询时间限制的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！