> 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 | 需要为其指定隔离阈值的资源名称。 可以指定以下任一值<br>•CPU_TIME：CPU时间<br>•ELAPSED_TIME：消耗时间<br>•IO_MEGABYTES：以兆字节为单位的I/O<br>•IO_REQUESTS：物理I/O请求数<br>•IO_LOGICAL：逻辑I/O请求数<br>•ENABLED：标记以启用或禁用隔离配置,默认值为是。<br>•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