> 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程式

Procedure | Description
---|---
ALTER_QUARANTINE Procedure| 在隔離配置中為SQL語句的執行計劃指定隔離閾值
CREATE_QUARANTINE_BY_SQL_ID Function|使用SQL ID為SQL語句的執行計劃建立隔離配置
CREATE_QUARANTINE_BY_SQL_TEXT Function|使用SQL語句為SQL語句的執行計劃建立隔離配置
CREATE_STGTAB_QUARANTINE Procedure|建立臨時表儲存隔離配置
DROP_QUARANTINE Procedure|刪除隔離配置
GET_PARAM_VALUE_QUARANTINE Function|檢視在隔離配置中指定的隔離閾值的值
PACK_STGTAB_QUARANTINE Function|將一個或多個隔離配置新增到儲存表
UNPACK_STGTAB_QUARANTINE Function|根據儲存表在資料庫中建立隔離配置

#### ALTER_QUARANTINE

語法：
``` sql
DBMS_SQLQ.ALTER_QUARANTINE (
   quarantine_name   IN VARCHAR2,
   parameter_name    IN VARCHAR2,
   parameter_value   IN VARCHAR2);
```

引數|說明 |
---|--- |
quarantine_name | 隔離配置的名字 |
parameter_name | 需要為其指定隔離閾值的資源名稱。 可以指定以下任一值 •CPU_TIME：CPU時間 •ELAPSED_TIME：消耗時間 •IO_MEGABYTES：以兆位元組為單位的I/O •IO_REQUESTS：物理I/O請求數 •IO_LOGICAL：邏輯I/O請求數 •ENABLED：標記以啟用或禁用隔離配置,預設值為是。 •AUTOPURGE：標記以啟用或禁用隔離配置的自動清除.如果設定為'是'，則在53周後（如果未使用）會自動清除隔離配置。如果將其設定為NO，則不會清除隔離配置。 預設值為是.
parameter_value | 指定的資源的隔離閾值

舉例:
``` 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');

END;
/
```

#### CREATE_QUARANTINE_BY_SQL_ID 函式

參考：
``` sql
--語法
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  函式

參考：
``` sql
--語法
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  函式

參考：
``` sql
--語法
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程式
``` sql
--quarantine_name 
BEGIN
  DBMS_SQLQ.DROP_QUARANTINE('SQL_QUARANTINE_3z0mwuq3aqsm8cfe7a0e4');
END;
/
```
#### GET_PARAM_VALUE_QUARANTINE 
``` sql
--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 
``` sql
--語法
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
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;

``` sql
--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隔離相關限制

``` 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.手動建立資源隔離
``` sql
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

【SQL】Oracle 19c SQL隔離詳解(SQL Quarantine)

相關文章