Oracle SQL隔离详解

Oracle SQL Quarantine – by Firsouler

Oracle 19c开始，我们可以使用SQL隔离，来隔离语句的执行计划，如果使用了SQL隔离，消耗资源或时间超过阈值的，则不允许执行，从而防止数据库整体性能下降。

通过DBMS package进行设定，主要步骤如下：

• 1.创建资源计划
• 2.创建SQL隔离策略：可以根据已知的SQL_ID或者SQL_TEXT来创建。
• 3.修改SQL隔离参数：如cpu_time
• 4.查看隔离的详细配置(DBA_SQL_QUARANTINE)
• 5.查看隔离SQL的详细语句(v$sql)
• 5.启动和关闭SQL隔离策略：SQL隔离策略在创建之后，默认情况下是直接被启用的。

DBMS_SQLQ说明

DBMS_SQLQ程序

ALTER_QUARANTINE

语法：

DBMS_SQLQ.ALTER_QUARANTINE (
   quarantine_name   IN VARCHAR2,
   parameter_name    IN VARCHAR2,
   parameter_value   IN VARCHAR2);

举例:

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');

END;
/

CREATE_QUARANTINE_BY_SQL_ID 函数

参考：

--语法
DBMS_SQLQ.CREATE_QUARANTINE_BY_SQL_ID (
   sql_id           IN VARCHAR2,
   plan_hash_value  IN NUMBER DEFAULT NULL)
 RETURN VARCHAR2;

--例子
DECLARE
  quarantine_config VARCHAR2(30);
BEGIN
  quarantine_config := DBMS_SQLQ.CREATE_QUARANTINE_BY_SQL_ID(SQL_ID => '8vu7s907prbgr');
END;
/
--or  hash_value
DECLARE
  quarantine_config VARCHAR2(30);
BEGIN
  quarantine_config := DBMS_SQLQ.CREATE_QUARANTINE_BY_SQL_ID(SQL_ID => '8vu7s907prbgr', PLAN_HASH_VALUE => '3488063716');
END;
/

CREATE_QUARANTINE_BY_SQL_TEXT 函数

参考：

--语法
DBMS_SQLQ.CREATE_QUARANTINE_BY_SQL_TEXT (
   sql_text         IN CLOB,
   plan_hash_value  IN NUMBER DEFAULT NULL)
RETURN VARCHAR2;

--例子,也可以增加hash_value
DECLARE
  quarantine_config VARCHAR2(30);
BEGIN
  quarantine_config := DBMS_SQLQ.CREATE_QUARANTINE_BY_SQL_TEXT(SQL_TEXT => to_clob('select count(*) from emp'));
END;
/

CREATE_QUARANTINE_BY_SQL_TEXT 函数

参考：

--语法
DBMS_SQLQ.CREATE_STGTAB_QUARANTINE (
   staging_table_name   IN VARCHAR2,
   staging_table_owner  IN VARCHAR2 DEFAULT NULL,
   tablespace_name      IN VARCHAR2 DEFAULT NULL);

--例子,创建一个存储表TBL_STG_QUARANTINE。并将其表所有者设置为执行此过程的数据库用户
BEGIN
  DBMS_SQLQ.CREATE_STGTAB_QUARANTINE(STAGING_TABLE_NAME => 'TBL_STG_QUARANTINE');
END;
/

DROP_QUARANTINE程序

--quarantine_name 
BEGIN
  DBMS_SQLQ.DROP_QUARANTINE('SQL_QUARANTINE_3z0mwuq3aqsm8cfe7a0e4');
END;
/

GET_PARAM_VALUE_QUARANTINE

--quarantine_name 
DECLARE
  quarantine_config_setting_value VARCHAR2(30);
BEGIN
  quarantine_config_setting_value := DBMS_SQLQ.GET_PARAM_VALUE_QUARANTINE(
                                        QUARANTINE_NAME  =>  'SQL_QUARANTINE_3z0mwuq3aqsm8cfe7a0e4',
                                        PARAMETER_NAME   =>  'CPU_TIME');
END;
/

PACK_STGTAB_QUARANTINE

--语法
DBMS_SQLQ.PACK_STGTAB_QUARANTINE (
   staging_table_name    IN VARCHAR2,
   staging_table_owner   IN VARCHAR2 DEFAULT NULL,
   name                  IN VARCHAR2 DEFAULT '%',
   sql_text              IN VARCHAR2 DEFAULT '%',
   enabled               IN VARCHAR2 DEFAULT NULL)
RETURN NUMBER;
--eg
DECLARE
  quarantine_configs NUMBER;
BEGIN
  quarantine_configs := DBMS_SQLQ.PACK_STGTAB_QUARANTINE(
                           STAGING_TABLE_NAME => 'TBL_STG_QUARANTINE',
                           NAME => 'SQL_QUARANTINE_%');
END;
/

举例

测试环境：

SQL> select banner_full from v$version;

BANNER_FULL
--------------------------------------------------------------------------------
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.6.0.0.0

1.创建资源隔离(自动sql隔离)

注意，该特性只能在Exadata上使用,模拟该特性，需要通过设置参数来实现

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

--1.创建组TEST_RUNAWAY_GROUP
--2.用户HR指定到组TEST_RUNAWAY_GROUP
--3.创建一个资源计划LIMIT_RESOURCE
--4.将LIMIT_RESOURCE指定到组TEST_RUNAWAY_GROUP
begin
  --Create a pending area
  dbms_resource_manager.create_pending_area();

  --Create a consumer group 'TEST_RUNAWAY_GROUP'
  dbms_resource_manager.create_consumer_group (
    consumer_group => 'TEST_RUNAWAY_GROUP',
    comment        => 'This consumer group limits execution time for SQL statements'
  );

  --Map the sessions of the user 'HR' to the consumer group 'TEST_RUNAWAY_GROUP' 
  dbms_resource_manager.set_consumer_group_mapping(
    attribute      => DBMS_RESOURCE_MANAGER.ORACLE_USER,
    value          => 'HR',
    consumer_group => 'TEST_RUNAWAY_GROUP'
  );

  --Create a resource plan 'LIMIT_RESOURCE'
  dbms_resource_manager.create_plan(
    plan    => 'LIMIT_RESOURCE',
    comment => 'Terminate SQL statements after exceeding total execution time'
  );

  --Create a resource plan directive by assigning the 'LIMIT_RESOURCE' plan to 
  --the 'TEST_RUNAWAY_GROUP' consumer group
  --Specify the execution time limit of 3 seconds for SQL statements belonging to 
  --the 'TEST_RUNAWAY_GROUP' group
  dbms_resource_manager.create_plan_directive(
    plan             => 'LIMIT_RESOURCE',
    group_or_subplan => 'TEST_RUNAWAY_GROUP',
    comment          => 'Terminate SQL statements when they exceed the' || 
                        'execution time of 3 seconds',
    switch_group     => 'CANCEL_SQL',
    switch_time      => 3,
    switch_estimate  => false
  );

  --llocate resources to the sessions not covered by the currently active plan 
  --according to the OTHER_GROUPS directive
  dbms_resource_Manager.create_plan_directive(
    plan              => 'LIMIT_RESOURCE',
    group_or_subplan  => 'OTHER_GROUPS',
    comment           => 'Ignore'
  );

  --Validate and submit the pending area
  dbms_resource_manager.validate_pending_area();
  dbms_resource_manager.submit_pending_area();

  --Grant switch privilege to the 'HR' user to switch to the 'TEST_RUNAWAY_GROUP' 
  --consumer group
  dbms_resource_manager_privs.grant_switch_consumer_group('HR','TEST_RUNAWAY_GROUP',false);
  
  --Set the initial consumer group of the 'HR' user to 'TEST_RUNAWAY_GROUP'
  dbms_resource_manager.set_initial_consumer_group('HR','TEST_RUNAWAY_GROUP');
end;
/

--Set the 'LIMIT_RESOURCE' plan as the top plan for the Resource Manager
alter system set RESOURCE_MANAGER_PLAN='LIMIT_RESOURCE' scope = memory;

--Unlock the HR user and assign it the DBA role
alter user hr identified by oracle account unlock;
grant dba to hr;

--Flush the shared pool
alter system flush shared_pool;

--查看资源管理计划
set lines 200 pages 999
col plan for a20
col status for a10
col comments for a60
col GROUP_OR_SUBPLAN for a18
select plan,type,GROUP_OR_SUBPLAN,switch_time,MAX_EST_EXEC_TIME,status,comments from DBA_RSRC_PLAN_DIRECTIVES
where plan='LIMIT_RESOURCE';

--删除资源隔离
删除资源隔离计划
alter system set RESOURCE_MANAGER_PLAN = '' scope = memory;
execute dbms_resource_manager.clear_pending_area();
execute dbms_resource_manager.create_pending_area();
execute dbms_resource_manager.delete_plan('LIMIT_RESOURCE');
execute dbms_resource_manager.delete_consumer_group('TEST_RUNAWAY_GROUP');
execute dbms_resource_manager.validate_pending_area();
execute dbms_resource_manager.submit_pending_area();

2.执行相关SQL

第一次执行，首先使用资源管理中限制，第二次会自动使用sql隔离相关限制

set timing on
select count(*) from hr.employees emp1, hr.employees emp2,hr.employees emp3, hr.employees emp4,
hr.employees emp5, hr.employees emp6, hr.employees emp7, hr.employees emp8,hr.employees emp9, 
hr.employees emp10 where rownum <= 100000000;

--报错示例
ERROR at line 1:
ORA-00040: active time limit exceeded - call aborted
--or
ORA-56955: quarantined plan used

3.手动创建资源隔离

set lines 200
col sql_text for a60
col SQL_QUARANTINE for a20
select sql_text,sql_id, plan_hash_value, avoided_executions, sql_quarantine 
from v$sql where sql_text like '%employees%';
--添加sql
declare
  quarantine_config varchar2(30);
begin
  quarantine_config := DBMS_SQLQ.CREATE_QUARANTINE_BY_SQL_TEXT(sql_text => to_clob('&sql_text'));
end;
/

--检查设置
set lines 200 pages 999
col name for a30
col sql_text for a60
col elapsed_time for a10
col cpu_time for a10
col LAST_EXECUTED for a10
select name,sql_text,elapsed_time,cpu_time,last_executed,enabled from dba_sql_quarantine;

--设置超时3秒
begin
  DBMS_SQLQ.ALTER_QUARANTINE(
    QUARANTINE_NAME => '',
    PARAMETER_NAME =>'ELAPSED_TIME',
    PARAMETER_VALUE =>'5');
END;
/

begin
  DBMS_SQLQ.ALTER_QUARANTINE(
    QUARANTINE_NAME => '',
    PARAMETER_NAME =>'CPU_TIME',
    PARAMETER_VALUE =>'5');
END;
/

--删除
BEGIN
  DBMS_SQLQ.DROP_QUARANTINE('SQL_QUARANTINE_355nsmzubt1r4');
END;
/

参考

• https://docs.oracle.com/en/database/oracle/oracle-database/19/arpls/DBMS_SQLQ.html#GUID-C07258CB-4706-4722-AE5B-85184173B70B
• https://docs.oracle.com/en/database/oracle/oracle-database/19/admin/diagnosing-and-resolving-problems.html#GUID-9B3EC88E-E330-4B7E-BF98-617803A24092