Oracle 12c动态SQL执行超时设置方案咨询

为什么PLSQL_TIMEOUT无效 #

PLSQL_TIMEOUT是Oracle 12.2版本才引入的会话级参数，你的12.1.0.2.0版本不支持，所以执行ALTER SESSION SET PLSQL_TIMEOUT会报错。

可行解决方案 #

1. 资源管理器（Resource Manager）精准管控

通过资源管理器创建带执行时间限制的消费组，在执行动态SQL前切换到该消费组，完成后切回原组：

• 创建消费组：

  BEGIN
    DBMS_RESOURCE_MANAGER.CREATE_CONSUMER_GROUP(
      CONSUMER_GROUP_NAME => 'QUERY_TIMEOUT_GROUP',
      COMMENT => 'Group for queries with execution time limit'
    );
  END;
  /
  

• 创建资源计划并设置执行时间限制：

  BEGIN
    DBMS_RESOURCE_MANAGER.CREATE_PLAN(
      PLAN_NAME => 'QUERY_TIMEOUT_PLAN',
      COMMENT => 'Plan with max execution time'
    );
    DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE(
      PLAN_NAME => 'QUERY_TIMEOUT_PLAN',
      GROUP_OR_SUBPLAN => 'QUERY_TIMEOUT_GROUP',
      COMMENT => 'Limit max execution time to 30s',
      MAX_EXECUTION_TIME => 30 -- 单位：秒
    );
    DBMS_RESOURCE_MANAGER.VALIDATE_PLAN(PLAN_NAME => 'QUERY_TIMEOUT_PLAN');
    DBMS_RESOURCE_MANAGER.SET_PLAN(PLAN_NAME => 'QUERY_TIMEOUT_PLAN');
  END;
  /
  

• 在程序包中切换消费组执行动态SQL：

  DECLARE
    l_sid NUMBER := SYS_CONTEXT('USERENV', 'SID');
    l_serial# NUMBER := SYS_CONTEXT('USERENV', 'SERIAL#');
    l_cursor SYS_REFCURSOR;
    l_dynamic_sql VARCHAR2(4000) := '你的动态SQL语句';
  BEGIN
    -- 切换到超时管控消费组
    DBMS_RESOURCE_MANAGER.SWITCH_CONSUMER_GROUP_FOR_SESSION(
      SID => l_sid,
      SERIAL# => l_serial#,
      CONSUMER_GROUP => 'QUERY_TIMEOUT_GROUP'
    );
    
    -- 执行动态SQL并返回游标
    OPEN l_cursor FOR l_dynamic_sql;
    
    -- 切回原消费组
    DBMS_RESOURCE_MANAGER.SWITCH_CONSUMER_GROUP_FOR_SESSION(
      SID => l_sid,
      SERIAL# => l_serial#,
      CONSUMER_GROUP => 'DEFAULT_CONSUMER_GROUP'
    );
    
    RETURN l_cursor;
  END;
  /
  

2. 自主事务+定时中断会话

通过启动一个独立的自主事务进程，监控主SQL的执行时间，超时后中断当前会话：

• 先创建一个用于中断会话的存储过程：

  CREATE OR REPLACE PROCEDURE INTERRUPT_QUERY(p_sid NUMBER, p_serial# NUMBER, p_timeout NUMBER) AS
    PRAGMA AUTONOMOUS_TRANSACTION;
  BEGIN
    -- 等待指定超时时间
    DBMS_LOCK.SLEEP(p_timeout);
    
    -- 中断会话中的当前SQL
    EXECUTE IMMEDIATE 'ALTER SYSTEM KILL SESSION ''' || p_sid || ',' || p_serial# || ''' IMMEDIATE';
    COMMIT; -- 自主事务需要提交
  END;
  /
  

• 在程序包中调用该过程并执行动态SQL：

  DECLARE
    l_sid NUMBER := SYS_CONTEXT('USERENV', 'SID');
    l_serial# NUMBER := SYS_CONTEXT('USERENV', 'SERIAL#');
    l_cursor SYS_REFCURSOR;
    l_dynamic_sql VARCHAR2(4000) := '你的动态SQL语句';
    l_timeout NUMBER := 30; -- 超时时间，单位秒
  BEGIN
    -- 启动中断监控进程
    INTERRUPT_QUERY(l_sid, l_serial#, l_timeout);
    
    -- 执行动态SQL并返回游标
    OPEN l_cursor FOR l_dynamic_sql;
    
    RETURN l_cursor;
  END;
  /
  

注意：这种方法需要当前用户拥有ALTER SYSTEM权限，且中断会话会导致应用端收到ORA-00028错误，需做好异常处理。

方案选择建议 #

• 如果需要精细化管控不同类型的SQL超时，优先选择资源管理器方案，它不会直接杀死会话，而是在SQL执行超时后自动终止查询，会话仍可继续使用。
• 如果资源管理器配置复杂，可选择自主事务中断方案，但要注意权限和异常处理逻辑。

内容的提问来源于stack exchange，提问作者scharrua