oracle 19c 自动 SQL 执行管理与 SQL 隔离

手动隔离-为SQL语句的执行计划创建隔离配置

SQL只要执行计划不变，每次都会被直接终止

使用以下任一包函数为SQL语句的执行计划创建隔离配置：DBMS_SQLQ.CREATE_QUARANTINE_BY_SQL_IDDBMS_SQLQ .CREATE_QUARANTINE_BY_SQL_TEX

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

DECLARE
quarantine_config VARCHAR2(30);
BEGIN

quarantine_config:= DBMS_SQLQ.CREATE_QUARANTINE_BY_SQL_ID( SQL_ID => '152sukb473gsk'); 

END;
/
DECLARE

quarantine_config VARCHAR2(30); 

BEGIN
quarantine_config:= DBMS_SQLQ.CREATE_QUARANTINE_BY_SQL_TEXT( SQL_TEXT => to_clob('select count(*) from emp'));
END;

手动隔离-在隔离配置中指定隔离阈值
可指定如下隔离阈值：
-CPU time
-Elapsed time
-I/O in megabytes
-Number of physical I/O requests
-Number of logical I/O requests

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;