Focus On Oracle

Installing, Backup & Recovery, Performance Tuning,
Troubleshooting, Upgrading, Patching

Oracle Engineered System


当前位置: 首页 » 技术文章 » Exadata

19c新特性之SQL语句隔离

通过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 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 '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;


4.再次运行测试语句
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;


5.查询隔离的sql
通过查询视图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.


Reference

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


关键词:sql-quarantine 19c 

相关文章

Oracle Database 19c with UCP(Universal Connection Pool)
19c中新增dbms_xplan.compare_plans
Oracle 19c新特性之Automatic Flashback
Install Oracle RAC Database 19c Step by Step
19c新特性之Automatic In-memory
19c新特性之SQL语句隔离
19c新特性之实时统计信息收集
19c新特性之自动索引
Oracle事务卫士(Transaction Guard)和应用连续性(Application Continuity)
在Oracle RAC ADG中通过opatchauto应用补丁(19.5.0-->19.5.1)
Upgrade to Oracle Database 19c Hands-on
Install Oracle Database 19c on X86
Top