使用Oracle資源管理器，我們可以限制和規(guī)范CPU和I/O等資源的使用，還可以防止執(zhí)行任何超過定義閾值的長時間運(yùn)行的查詢。

因此，我們可以“取消”或終止運(yùn)行時間超過定義的閾值（例如10分鐘）的SQL查詢。

所有這些都是好的，但是沒有什么能阻止同一個查詢一次又一次的執(zhí)行，每次運(yùn)行10分鐘，然后才被終止，每次都浪費(fèi)了10分鐘的資源。

Oracle 19c中的新概念是SQL隔離的新概念，如果特定SQL語句超過指定的資源限制（通過Oracle資源管理器設(shè)置），則資源管理器終止該語句的執(zhí)行并“隔離”計劃。

廣義地說，這意味著執(zhí)行計劃現(xiàn)在被放在數(shù)據(jù)庫不會執(zhí)行的計劃的“黑名單”上。

這個SQL隔離特性反過來又有助于提高性能，因?yàn)樗梢苑乐箤韴?zhí)行代價高昂的SQL語句，而SQL語句現(xiàn)在已經(jīng)被隔離。

但是請注意：正如我們所說，中的此功能僅在Oracle工程系統(tǒng)上可用（包括內(nèi)部部署和ExaCS），為了測試此功能，我必須設(shè)置此下劃線參數(shù)并跳出數(shù)據(jù)庫：

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

讓我們快速了解一下這個功能是如何工作的。

我們首先創(chuàng)建一個使用者組和資源計劃，然后添加一個計劃指令，該指令將DEMO模式執(zhí)行的查詢的運(yùn)行時間限制為20秒。請注意，plan指令最初設(shè)置為CPU時間的20秒，而不是掛鐘的運(yùn)行時間，它被修改為基于運(yùn)行時間指定閾值。

因此，創(chuàng)建和配置數(shù)據(jù)庫資源計劃的這一部分非常標(biāo)準(zhǔn)，這里不需要更改。

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;
/

讓我們測試一下資源計劃。

我們作為演示用戶連接并發(fā)出一個查詢，該查詢將超過資源計劃中定義的20秒的運(yùn)行時間閾值。

我們將看到一條錯誤消息，正在運(yùn)行的查詢將被終止。

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

300 rows selected.

Elapsed: 00:00:19.64
SQL> 

我們使用DBMS_SQLQ包為需要隔離的SQL語句的執(zhí)行計劃創(chuàng)建隔離配置。我們可以通過指定要隔離的語句的SQL文本或SQL_ID來創(chuàng)建隔離配置-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語句的執(zhí)行計劃創(chuàng)建隔離配置之后，我們?nèi)缓笫褂肈BMS_SQLQ.ALTER_QUARANTINE過程指定隔離閾值。

當(dāng)資源管理器定義的任何閾值等于或小于SQL隔離配置中指定的隔離閾值時，如果SQL語句使用隔離配置中指定的相同執(zhí)行計劃，則不允許運(yùn)行該SQL語句。

注意：隔離名可以從DBA_SQL_QUARANTINE字典表中獲得。

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

現(xiàn)在有了SQL隔離，當(dāng)我們嘗試發(fā)出同一條SQL語句（以前在被資源管理器終止之前，該語句運(yùn)行20秒）時，它將不再執(zhí)行，甚至不會啟動，我們會看到一條消息，說明用于該語句的計劃是隔離計劃的一部分。

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視圖有兩個附加列，顯示隔離區(qū)的名稱以及隔離區(qū)已避免執(zhí)行多少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包子程序，我們還可以啟用或禁用隔離配置、刪除隔離配置，如果需要，還可以將隔離配置從一個數(shù)據(jù)庫傳輸?shù)搅硪粋€數(shù)據(jù)庫。

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

PL/SQL procedure successfully completed.

請注意，現(xiàn)在由于隔離已被禁用，因此不會阻止立即執(zhí)行查詢，而是在與運(yùn)行時間相關(guān)的資源管理器計劃指令生效后取消查詢。

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

300 rows selected.

Elapsed: 00:00:19.64