Oracle 19c SQL隔离_数据库_博客_测评&博客

使用Oracle资源管理器，我们可以限制和规范CPU和I/O等资源的使用，还可以防止执行任何超过定义阈值的长时间运行的查询。

因此，我们可以“取消”或终止运行时间超过定义的阈值（例如10分钟）的SQL查询。

所有这些都是好的，但是没有什么能阻止同一个查询一次又一次的执行，每次运行10分钟，然后才被终止，每次都浪费了10分钟的资源。

Oracle 19c中的新概念是SQL隔离的新概念，如果特定SQL语句超过指定的资源限制（通过Oracle资源管理器设置），则资源管理器终止该语句的执行并“隔离”计划。

广义地说，这意味着执行计划现在被放在数据库不会执行的计划的“黑名单”上。

这个SQL隔离特性反过来又有助于提高性能，因为它可以防止将来执行代价高昂的SQL语句，而SQL语句现在已经被隔离。

但是请注意：正如我们所说，中的此功能仅在Oracle工程系统上可用（包括内部部署和ExaCS），为了测试此功能，我必须设置此下划线参数并跳出数据库：

alter system set “_exadata_feature_on”=true scope=spfile;

让我们快速了解一下这个功能是如何工作的。

我们首先创建一个使用者组和资源计划，然后添加一个计划指令，该指令将DEMO模式执行的查询的运行时间限制为20秒。请注意，plan指令最初设置为CPU时间的20秒，而不是挂钟的运行时间，它被修改为基于运行时间指定阈值。

因此，创建和配置数据库资源计划的这一部分非常标准，这里不需要更改。

begin
   dbms_resource_manager.create_pending_area();
  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 => 'ORACLE_USER',
    value => 'DEMO',
    consumer_group =>'GROUP_WITH_LIMITED_EXEC_TIME'
  );
    dbms_resource_manager.create_plan(
    PLAN=> 'LIMIT_EXEC_TIME',
    COMMENT=>'Kill statement after exceeding total execution time'
  );
   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=>30,
    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;
/

begin
  dbms_resource_manager.create_pending_area();
  dbms_resource_manager_privs.grant_switch_consumer_group('DEMO','GROUP_WITH_LIMITED_EXEC_TIME',false);
  dbms_resource_manager.set_initial_consumer_group('DEMO','GROUP_WITH_LIMITED_EXEC_TIME');
  DBMS_RESOURCE_MANAGER.VALIDATE_PENDING_AREA;
  DBMS_RESOURCE_MANAGER.SUBMIT_PENDING_AREA;
end;
/


BEGIN
   dbms_resource_manager.update_plan_directive(plan=>'LIMIT_EXEC_TIME',
   group_or_subplan=>'GROUP_WITH_LIMITED_EXEC_TIME',new_switch_elapsed_time=>20, new_switch_for_call=>TRUE,new_switch_group=>'CANCEL_SQL' );
   dbms_resource_manager.validate_pending_area();
   dbms_resource_manager.submit_pending_area;
END;
/

让我们测试一下资源计划。

我们作为演示用户连接并发出一个查询，该查询将超过资源计划中定义的20秒的运行时间阈值。

我们将看到一条错误消息，正在运行的查询将被终止。

ERROR:
ORA-56735: elapsed time limit exceeded - call aborted

300 rows selected.

Elapsed: 00:00:19.64
SQL>

我们使用DBMS_SQLQ包为需要隔离的SQL语句的执行计划创建隔离配置。我们可以通过指定要隔离的语句的SQL文本或SQL_ID来创建隔离配置-CREATE_QUARANTINE_BY_SQL_ID或CREATE_QUARANTINE_BY_SQL_TEXT。

DECLARE
  quarantine_config VARCHAR2(30);
BEGIN
  quarantine_config := DBMS_SQLQ.CREATE_QUARANTINE_BY_SQL_ID(SQL_ID => '491fa2p6qt9h6');
END;
/

在为SQL语句的执行计划创建隔离配置之后，我们然后使用DBMS_SQLQ.ALTER_QUARANTINE过程指定隔离阈值。

当资源管理器定义的任何阈值等于或小于SQL隔离配置中指定的隔离阈值时，如果SQL语句使用隔离配置中指定的相同执行计划，则不允许运行该SQL语句。

注意：隔离名可以从DBA_SQL_QUARANTINE字典表中获得。

BEGIN
  DBMS_SQLQ.ALTER_QUARANTINE(
   QUARANTINE_NAME  =>  'SQL_QUARANTINE_ca0z7uh2sqcbw',
   PARAMETER_NAME   =>  'ELAPSED_TIME',
   PARAMETER_VALUE  =>  '30');
END;
/

现在有了SQL隔离，当我们尝试发出同一条SQL语句（以前在被资源管理器终止之前，该语句运行20秒）时，它将不再执行，甚至不会启动，我们会看到一条消息，说明用于该语句的计划是隔离计划的一部分。

SQL> set timing on
SQL> select * from demo.myobjects where owner='SYS';
select * from demo.myobjects where owner='SYS'
                   *
ERROR at line 1:
ORA-56955: quarantined plan used

V$SQL视图有两个附加列，显示隔离区的名称以及隔离区已避免执行多少SQL语句。

SQL> select sql_quarantine,avoided_executions
2 from v$sql where sql_id='491fa2p6qt9h6';

SQL_QUARANTINE
--------------------------------------------------------------------------------
AVOIDED_EXECUTIONS
------------------
SQL_QUARANTINE_ca0z7uh2sqcbw
1

使用DBMS_SQLQ包子程序，我们还可以启用或禁用隔离配置、删除隔离配置，如果需要，还可以将隔离配置从一个数据库传输到另一个数据库。

SQL> BEGIN
    DBMS_SQLQ.ALTER_QUARANTINE(
       QUARANTINE_NAME => 'SQL_QUARANTINE_ca0z7uh2sqcbw',
       PARAMETER_NAME  => 'ENABLED',
       PARAMETER_VALUE => 'NO');
END;
/ 

PL/SQL procedure successfully completed.

请注意，现在由于隔离已被禁用，因此不会阻止立即执行查询，而是在与运行时间相关的资源管理器计划指令生效后取消查询。

ERROR:
ORA-56735: elapsed time limit exceeded - call aborted

300 rows selected.

Elapsed: 00:00:19.64