Oracle 19c 新特性详解：失控的 SQL 执行计划隔离（Quarantine SQL Plans）

2019年2月20日

盖国强

2848

我们经常遇到的一种情况是，数据库中运行了一条非常消耗资源的 SQL，一而再再而三的消耗了宝贵的资源，往往我们不能及时施加控制。

在 Oracle 19c 中，增加了一个新特性，失控 SQL 的隔离。这个特性是通过资源管理器实现的，只支持在 Oracle 一体机中实现。但是这不妨碍我们了解一下这个特性的工作原理。

Oracle Resource Manager 允许指定用于定义失控查询的条件，任何超出指定限制的SQL语句都将自动终止。但是以前，很难阻止最终用户不断发布这样的SQL，浪费宝贵的系统资源。从19c开始，由于过度消耗CPU或I / O资源而被资源管理器终止的失控SQL计划将被自动隔离。这将阻止这些计划再次执行。

V$SQL中名为QUARANTINED的新列指示哪个SQL语句的计划已放置在隔离区中。虽然AVOIDED_EXECUTION列表示计划被隔离后尝试的执行次数。您可以使用新的DBMS_SQLQ包或DBMS_RESOUCE_MANAGER来控制SQL计划的隔离。您还可以使用DBA_SQL_QUARANTINE查看当前配置。

对于这些被隔离的计划，可以尝试手动调整语句，以便生成新计划。如果该SQL有新计划，则允许其再次执行，并且只有在新计划超出指定限制时才会重新进入隔离状态。或者您可以利用自动索引，来改善和生成新的执行计划。

DBMS_SQLQ 包中包含了如下一个系列的存储过程用于进行控制：

Procedure	Description
ALTER_QUARANTINE Procedure	Configures quarantine settings for a SQL statement and its execution plans
CREATE_QUARANTINE_BY_SQL_ID Function	Creates a quarantine configuration for a SQL statement and its execution plans using SQL ID
CREATE_QUARANTINE_BY_SQL_TEXT Function	Creates a quarantine configuration for a SQL statement and its execution plans using SQL text
CREATE_STGTAB_QUARANTINE Procedure	Creates a staging table to store quarantine configurations
DROP_QUARANTINE Procedure	Deletes a quarantine configuration
GET_PARAM_VALUE_QUARANTINE Function	Returns the value for a quarantine configuration setting for a SQL statement and its execution plans
PACK_STGTAB_QUARANTINE Function	Adds one or more quarantine configurations into a staging table
UNPACK_STGTAB_QUARANTINE Function	Creates quarantine configurations in a database from a staging table

在以下示例中，为隔离配置名称为SQL_QUARANTINE_3z0mwuq3aqsm8cfe7a0e4的SQL语句配置了隔离设置，因此，如果SQL语句占用CPU时间5秒或经过10秒的时间，则SQL语句将终止并隔离。

BEGIN

  DBMS_SQLQ.ALTER_QUARANTINE(
   QUARANTINE_NAME  =>  'SQL_QUARANTINE_3z0mwuq3aqsm8cfe7a0e4',
   PARAMETER_NAME   =>  'CPU_TIME',
   PARAMETER_VALUE  =>  '5');

  DBMS_SQLQ.ALTER_QUARANTINE(
   QUARANTINE_NAME  =>  'SQL_QUARANTINE_3z0mwuq3aqsm8cfe7a0e4',
   PARAMETER_NAME   =>  'ELAPSED_TIME',
   PARAMETER_VALUE  =>  '10');

  DBMS_SQLQ.ALTER_QUARANTINE(
   QUARANTINE_NAME  =>  'SQL_QUARANTINE_3z0mwuq3aqsm8cfe7a0e4',
   PARAMETER_NAME   =>  'ENABLED',
   PARAMETER_VALUE  =>  'YES');

END;
/

以下示例为SQL语句为8vu7s907prbgr的SQL语句创建隔离配置。隔离配置适用于SQL语句的所有执行计划。

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

详细信息参考官网链接。