通过Oracle资源管理器，我们可以限制和规范CPU和IO等资源的使用，还可以防止任何超过定义阈值的长时间运行的查询执行。比如有一个非常耗费资源的SQL一直不停的在执行，假定这个阈值是30分钟，那么他就会一直执行，直到30分钟后被kill，但她会一直不停的重复执行，没办法阻止。
从Oracle Database 19c开始，您可以使用SQL Quarantine来隔离SQL语句的执行计划，这些SQL语句由于消耗Oracle数据库中过多的系统资源而被资源管理器终止。单个SQL语句可能有多个执行计划，如果它试图使用被隔离的执行计划，则不允许该SQL语句运行，从而防止数据库性能下降。
0.由于该特性只能在Exadata上使用，要模拟Exadata的特性，我们可以通过设置参数来实现。请不要在非Exadta生产环境上使用，切记，切记！！！
alter system set "_exadata_feature_on"=true scope=spfile;1.创建资源管理计划
以下代码通过使用dbms_resource_manager包子程序创建复杂的资源计划，主要做了下面的操作：
1.创建消费组TEST_RUNAWAY_GROUP.2.将用户OHSDBA指定到消费组TEST_RUNAWAY_GROUP
3.创建一个资源计划LIMIT_RESOURCE，用于终止超过5秒的SQL语句
4.然后将这个资源计划LIMIT_RESOURCE指定到消费组TEST_RUNAWAY_GROUP
connect / as sysdba
begin
-- Create a pending areadbms_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 'OHSDBA' to the consumer group 'TEST_RUNAWAY_GROUP'
dbms_resource_manager.set_consumer_group_mapping(
attribute => DBMS_RESOURCE_MANAGER.ORACLE_USER,
value => 'OHSDBA',
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 5 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 5 seconds',
switch_group => 'CANCEL_SQL',
switch_time => 5,
switch_estimate => false
);
-- Allocate 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 'OHSDBA' user to switch to the 'TEST_RUNAWAY_GROUP'
-- consumer group
dbms_resource_manager_privs.grant_switch_consumer_group('OHSDBA',
'TEST_RUNAWAY_GROUP',
false);
-- Set the initial consumer group of the 'OHSDBA' user to 'TEST_RUNAWAY_GROUP'
dbms_resource_manager.set_initial_consumer_group('OHSDBA',
'TEST_RUNAWAY_GROUP');
end;
/
2.修改初始化参数RESOURCE_MANAGER_PLAN，并指定为LIMIT_RESOURCE
-- Set the 'LIMIT_RESOURCE' plan as the top plan for the Resource Manager
alter system set RESOURCE_MANAGER_PLAN = 'LIMIT_RESOURCE' scope = memory;
-- Flush the shared pool
alter system flush shared_pool;
3.创建测试数据并测试
SQL> create table ohsdba_objects as select * from dba_objects;
Table created.
SQL>
select count(*)
from ohsdba_objects ohs1, ohsdba_objects ohs2,
ohsdba_objects ohs3, ohsdba_objects ohs4,
ohsdba_objects ohs5, ohsdba_objects ohs6,
ohsdba_objects ohs7, ohsdba_objects ohs8,
ohsdba_objects ohs9, ohsdba_objects ohs10
where rownum <= 100000000;
select count(*) from ohsdba_objects ohs1, ohsdba_objects ohs2,
ohsdba_objects ohs3, ohsdba_objects ohs4,
ohsdba_objects ohs5, ohsdba_objects ohs6,
ohsdba_objects ohs7, ohsdba_objects ohs8,
ohsdba_objects ohs9, ohsdba_objects ohs10
where rownum <= 100000000;
通过查询视图v$SQL和dba_sql_quarantine，查看SQL语句隔离执行计划的详细信息。
select sql_text, plan_hash_value, avoided_executions, sql_quarantine
from v$sql where sql_quarantine is not null;
select sql_text, name, plan_hash_value, last_executed, enabled from dba_sql_quarantine;
到此，实验结束
6.还原测试环境
删除隔离的sql及相关信息
begin
for quarantineObj in (select name from dba_sql_quarantine) loop
sys.dbms_sqlq.drop_quarantine(quarantineObj.name);
end loop;
end;
/
删除资源隔离计划
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();
除了以上常用的。我们还可以手动添加要隔离的SQL，有兴趣的同学，可以查看白皮书
declare
sqlq varchar2(30);
begin
sqlq:= dbms_sqlq.create_quarantine_by_sql_id(sql_id => '4xj398u70q0av');
end;
/
select sql_text, name, plan_hash_value from dba_sql_quarantine;
exec
dbms_sqlq.alter_quarantine(quarantine_name =>
'SQL_QUARANTINE_cdhrf6jm7ttbc',parameter_name =>
'ELAPSED_TIME',parameter_value => '3');
下面语句将禁用这个隔离配置SQL_QUARANTINE_cdhrf6jm7ttbc
exec
dbms_sqlq.alter_quarantine(quarantine_name =>
'SQL_QUARANTINE_cdhrf6jm7ttbc',parameter_name =>
'ENABLED',parameter_value => 'NO');
查询修改隐含参数，请参考
set lines 180 pages 200
col parameter for a50
col "default value" for a20
col "session value" for a20
col "instance value" for a20
col is_session_modifiable for a10
col is_system_modifiable for a10
select a.ksppinm "parameter", b.ksppstdf "default value",
b.ksppstvl "session value",
c.ksppstvl "instance value",
decode(bitand(a.ksppiflg/256,1),1,'true','false') is_session_modifiable,
decode(bitand(a.ksppiflg/65536,3),1,'immediate',2,'deferred',3,'immediate','false') is_system_modifiable
from x$ksppi a,
x$ksppcv b,
x$ksppsv c
where a.indx = b.indx
and a.indx = c.indx
and a.ksppinm like '%&a%'；
如何要查看所有隐含参数，将最后一行替换为and a.ksppinm LIKE '/_%' escape '/' ;
SQL> alter system set "<_parameter-of-choice>" scope=spfile and bounce the database.
https://docs.oracle.com/en/database/oracle/oracle-database/19/admin/diagnosing-and-resolving-problems.html#GUID-1CF7E2B7-1BF8-4907-889E-1107CAA83E51
https://docs.oracle.com/en/database/oracle/oracle-database/19/arpls/DBMS_SQLQ.html#GUID-C9CEBBEA-9543-42B2-B318-465BAA4F832F
https://docs.oracle.com/en/database/oracle/oracle-database/19/admin/diagnosing-and-resolving-problems.html#GUID-138E9175-A847-441E-9C3C-DB919BE594B1
https://docs.oracle.com/en/database/oracle/oracle-database/19/admin/managing-resources-with-oracle-database-resource-manager.html#GUID-2BEF5482-CF97-4A85-BD90-9195E41E74EF
