手把手教你Oracle Database 19c中的新功能：SQL隔离

Oracle Database 19c引入了Runaway Query Management的扩展，称为SQL隔离。

取消失控查询有助于防止浪费系统资源，但是如果重复运行该问题查询，仍可能导致相当数量的资源浪费。SQL隔离通过隔离已取消的SQL语句来解决此问题，因此它们不能多次运行。

文章摘要：

• 先决条件
• 自动SQL隔离
• Views
• DBMS_SQLQ：SQL隔离管理
• 传输隔离区定义

先决条件

此功能目前仅限于企业版的工程系统，像数据库云服务器和Exadata云服务，如所描述这里。通过启用“ _exadata_feature_on”初始化参数，可以找到一种解决方法。不要在实际实例上使用它，否则您将违反许可协议。

export ORACLE_SID=cdb1
export ORAENV_ASK=NO
. oraenv
export ORAENV_ASK=YES

sqlplus / as sysdba <<EOF

alter system set "_exadata_feature_on"=true scope=spfile;
shutdown immediate;
startup;

exit;
EOF
复制

测试完SQL隔离区后，请记住要重置此参数。

export ORACLE_SID=cdb1
export ORAENV_ASK=NO
. oraenv
export ORAENV_ASK=YES

sqlplus / as sysdba <<EOF

alter system reset "_exadata_feature_on" scope=spfile;
shutdown immediate;
startup;

exit;
EOF
复制

自动SQL隔离

默认情况下，SQL隔离是可用的，因此我们不必执行任何特殊操作即可开始使用它。用于Runaway Query Management的常规资源计划将触发它。我们将使用此处 12c Runaway Query Management文章中使用的示例来演示这一点。

我们创建一个测试用户。

CONN sys/SysPassword1@//localhost:1521/pdb1 AS SYSDBA

--DROP USER testuser1 CASCADE;
CREATE USER testuser1 IDENTIFIED BY testuser1 QUOTA UNLIMITED ON users;
GRANT CREATE SESSION, CREATE PROCEDURE TO testuser1;
复制

在下面的示例中，我们有一个资源计划，其中包含一个NORMAL_CG用于常规会话的消费者组。与NORMAL_CG使用方组的任何会话使用CPU进行一次调用超过60秒都会取消该SQL。

BEGIN
  DBMS_RESOURCE_MANAGER.clear_pending_area;
  DBMS_RESOURCE_MANAGER.create_pending_area;

  -- Create plan
  DBMS_RESOURCE_MANAGER.create_plan(
    plan    => 'long_running_query_plan',
    comment => 'Plan to handle long running queries.');

  -- Create consumer groups
  DBMS_RESOURCE_MANAGER.create_consumer_group(
    consumer_group => 'normal_cg',
    comment        => 'Consumer group for normal sessions.');

  -- Assign consumer groups to plan and define priorities
  DBMS_RESOURCE_MANAGER.create_plan_directive (
    plan                 => 'long_running_query_plan',
    group_or_subplan     => 'normal_cg',
    comment              => 'Normal Priority',
    mgmt_p1              => 100,
    switch_group         => 'CANCEL_SQL',
    switch_time          => 60,
    switch_for_call      => TRUE);

  DBMS_RESOURCE_MANAGER.create_plan_directive(
    plan             => 'long_running_query_plan',
    group_or_subplan => 'OTHER_GROUPS',
    comment          => 'Default',
    mgmt_p2          => 100);

  DBMS_RESOURCE_MANAGER.validate_pending_area;
  DBMS_RESOURCE_MANAGER.submit_pending_area;
END;
/
复制

我们允许TESTUSER1用户切换到NORMAL_CG消费者组，然后将TESTUSER1用户的初始消费者组设置为NORMAL_CG。

BEGIN
  DBMS_RESOURCE_MANAGER_PRIVS.grant_switch_consumer_group(
    grantee_name   => 'testuser1',
    consumer_group => 'normal_cg',
    grant_option   => FALSE);

  DBMS_RESOURCE_MANAGER.set_initial_consumer_group('testuser1', 'normal_cg');
END;
/
复制

最后，我们激活该计划。

ALTER SYSTEM SET RESOURCE_MANAGER_PLAN ='long_running_query_plan';
复制

我们可以使用以下查询监视当前的消费者组分配。

COLUMN username FORMAT A30
COLUMN resource_consumer_group FORMAT A30

SELECT username, resource_consumer_group
FROM   v$session
WHERE  username = 'TESTUSER1';
复制

让此会话保持打开状态，以便我们可以监视进度。

在一个单独的会话中，我们连接到测试用户，创建一个仅在CPU上放置指定分钟数的函数，然后查询该函数。

CONN testuser1/testuser1@//localhost:1521/pdb1

CREATE OR REPLACE FUNCTION burn_cpu (p_mins IN NUMBER)
  RETURN NUMBER
AS
  l_start_time DATE;
  l_number     NUMBER := 1;
BEGIN
  l_start_time := SYSDATE;
  LOOP
    EXIT WHEN SYSDATE - l_start_time > (p_mins/24/60);
    l_number := l_number + 1;
  END LOOP;
  RETURN 0;
END;
/

SELECT burn_cpu (5) FROM dual;
复制

大约60秒钟后，调用该BURN_CPU函数的查询已取消。

SQL> SELECT burn_cpu (5) FROM dual;

Error starting at line : 1 in command -
SELECT burn_cpu (5) FROM dual
Error report -
ORA-00040: active time limit exceeded - call aborted

SQL>
复制

文档会让您相信已取消的呼叫会立即被隔离，但实际上可能要花很长时间才能被注意到。在某些情况下，我等了15分钟以上才对已取消的语句进行隔离。显然，有些人对此感到困惑，并试图将其记录为错误。响应只是等待。

19c新功能SQL隔离不起作用（文档ID 2634990.1）

如果等待了足够长的时间，将隔离导致取消调用的执行计划，并且随后尝试使用相同的执行计划运行语句将导致隔离消息。

SQL> SELECT burn_cpu (5) FROM dual;

Error starting at line : 1 in command -
SELECT burn_cpu (5) FROM dual
Error report -
ORA-56955: quarantined plan used

SQL>
复制

Views

我们可以在V$SQL和DBA_SQL_QUARANTINE视图中看到隔离操作的结果。

该V$SQL视图包括SQL_QUARANTINE和AVOIDED_EXECUTIONS列。我们看到的两个条目SQL_ID，一个显示AVOIDED_EXECUTIONS为0，一个显示为1。

COLUMN sql_text FORMAT A30
COLUMN sql_quarantine FORMAT A40

SELECT sql_text, sql_id, plan_hash_value, child_number, sql_quarantine, avoided_executions 
FROM   v$sql
WHERE  sql_quarantine IS NOT NULL;

SQL_TEXT                       SQL_ID        PLAN_HASH_VALUE CHILD_NUMBER SQL_QUARANTINE                           AVOIDED_EXECUTIONS
------------------------------ ------------- --------------- ------------ ---------------------------------------- ------------------
SELECT burn_cpu (5) FROM dual  gs59hr0xtjrf8      1388734953            0 SQL_QUARANTINE_8zpc9pwdmb8vr125daea2                      1
SELECT burn_cpu (5) FROM dual  gs59hr0xtjrf8      1388734953            1 SQL_QUARANTINE_8zpc9pwdmb8vr125daea2                      0

SQL>
复制

该DBA_SQL_QUARANTINE视图向我们显示了SQL隔离定义的详细信息。

COLUMN sql_text FORMAT A30
COLUMN name FORMAT A40

SELECT sql_text, name, plan_hash_value, enabled
FROM   dba_sql_quarantine;

SQL_TEXT                       NAME                                     PLAN_HASH_VALUE ENA
------------------------------ ---------------------------------------- --------------- ---
SELECT burn_cpu (5) FROM dual  SQL_QUARANTINE_8zpc9pwdmb8vr125daea2           308129442 YES

SQL>


COLUMN sql_text FORMAT A30
COLUMN cpu_time FORMAT A10
COLUMN io_megabytes FORMAT A10
COLUMN io_requests FORMAT A10
COLUMN elapsed_time FORMAT A10
COLUMN io_logical FORMAT A10

SELECT sql_text, cpu_time, io_megabytes, io_requests, elapsed_time, io_logical
FROM   dba_sql_quarantine;

SQL_TEXT                       CPU_TIME   IO_MEGABYT IO_REQUEST ELAPSED_TI IO_LOGICAL
------------------------------ ---------- ---------- ---------- ---------- ----------
SELECT burn_cpu (5) FROM dual  60

SQL>
复制

请注意，最后一个查询显示的阈值被打破，导致该查询被取消，因此首先被隔离。在这种情况下，CPU时间为60秒。

DBMS_SQLQ：SQL隔离管理

该DBMS_SQLQ软件包提供了用于管理SQL隔离的API。

我们可以基于SQL_ID或手动隔离语句SQL_TEXT。两种方法都接受一个PLAN_HASH_VALUE参数，这使我们能够隔离单个执行计划。如果未指定，则隔离该语句的所有执行计划。

以下是一些手动隔离语句的示例。

SET SERVEROUTPUT ON

-- Quarantine all execution plans for a SQL statement.
DECLARE
  l_sql_quarantine  VARCHAR2(100);
BEGIN
  l_sql_quarantine := sys.DBMS_SQLQ.create_quarantine_by_sql_text(
                        sql_text => TO_CLOB('SELECT burn_cpu (5) FROM dual')
                      );
  DBMS_OUTPUT.put_line('l_sql_quarantine=' || l_sql_quarantine);
END;
/

-- Quarantine a specific execution plan for a SQL statement.
DECLARE
  l_sql_quarantine  VARCHAR2(100);
BEGIN
  l_sql_quarantine := sys.DBMS_SQLQ.create_quarantine_by_sql_text(
                        sql_text        => TO_CLOB('SELECT burn_cpu (5) FROM dual'),
                        plan_hash_value => '1388734953'
                      );
  DBMS_OUTPUT.put_line('l_sql_quarantine=' || l_sql_quarantine);
END;
/

-- Quarantine all execution plans for a SQL_ID.
DECLARE
  l_sql_quarantine  VARCHAR2(100);
BEGIN
  l_sql_quarantine := sys.DBMS_SQLQ.create_quarantine_by_sql_id(
                        sql_id => 'gs59hr0xtjrf8'
                      );
  DBMS_OUTPUT.put_line('l_sql_quarantine=' || l_sql_quarantine);
END;
/

-- Quarantine a specific execution plan for a SQL_ID.
DECLARE
  l_sql_quarantine  VARCHAR2(100);
BEGIN
  l_sql_quarantine := sys.DBMS_SQLQ.create_quarantine_by_sql_id(
                        sql_id          => 'gs59hr0xtjrf8',
                        plan_hash_value => '1388734953'
                      );
  DBMS_OUTPUT.put_line('l_sql_quarantine=' || l_sql_quarantine);
END;
/
复制

手动创建的隔离区的所有阈值均设置为ALWAYS。

SELECT sql_text, name, plan_hash_value, cpu_time, io_megabytes, io_requests, elapsed_time, io_logical
FROM   dba_sql_quarantine;

SQL_TEXT                       NAME                                     PLAN_HASH_VALUE CPU_TIME   IO_MEGABYT IO_REQUEST ELAPSED_TI IO_LOGICAL
------------------------------ ---------------------------------------- --------------- ---------- ---------- ---------- ---------- ----------
SELECT burn_cpu (5) FROM dual  SQL_QUARANTINE_8zpc9pwdmb8vr                             ALWAYS     ALWAYS     ALWAYS     ALWAYS     ALWAYS
SELECT burn_cpu (5) FROM dual  SQL_QUARANTINE_8zpc9pwdmb8vr125daea2           308129442 60                            
SELECT burn_cpu (5) FROM dual  SQL_QUARANTINE_8zpc9pwdmb8vr52c669e9          1388734953 ALWAYS     ALWAYS     ALWAYS     ALWAYS     ALWAYS

SQL>
复制

该ALTER_QUARANTINE程序使我们可以更改阈值，使它们看起来更像自动生成的隔离区。我们可以使用该过程来更改以下参数。

• CPU_TIME
• ELAPSED_TIME
• IO_MEGABYTES
• IO_REQUESTS
• IO_LOGICAL
• ENABLED
• AUTOPURGE

这是CPU_TIME为手动创建的隔离区设置阈值的示例。

BEGIN
  sys.DBMS_SQLQ.alter_quarantine(
    quarantine_name  =>  'SQL_QUARANTINE_8zpc9pwdmb8vr',
    parameter_name   =>  'CPU_TIME',
    parameter_value  =>  '60');

  sys.DBMS_SQLQ.alter_quarantine(
    quarantine_name  =>  'SQL_QUARANTINE_8zpc9pwdmb8vr52c669e9',
    parameter_name   =>  'CPU_TIME',
    parameter_value  =>  '60');
END;
/

SELECT sql_text, plan_hash_value, cpu_time, io_megabytes, io_requests, elapsed_time, io_logical
FROM   dba_sql_quarantine;

SQL_TEXT                       NAME                                     PLAN_HASH_VALUE CPU_TIME   IO_MEGABYT IO_REQUEST ELAPSED_TI IO_LOGICAL
------------------------------ ---------------------------------------- --------------- ---------- ---------- ---------- ---------- ----------
SELECT burn_cpu (5) FROM dual  SQL_QUARANTINE_8zpc9pwdmb8vr                             60            
SELECT burn_cpu (5) FROM dual  SQL_QUARANTINE_8zpc9pwdmb8vr125daea2           308129442 60            
SELECT burn_cpu (5) FROM dual  SQL_QUARANTINE_8zpc9pwdmb8vr52c669e9          1388734953 60            

SQL>
复制

删除隔离定义之前，您可能需要复制它们，如以下部分所述。

我们使用该DROP_QUARANTINE过程删除隔离区。

BEGIN
  sys.DBMS_SQLQ.drop_quarantine('SQL_QUARANTINE_8zpc9pwdmb8vr');
  sys.DBMS_SQLQ.drop_quarantine('SQL_QUARANTINE_8zpc9pwdmb8vr125daea2');
  sys.DBMS_SQLQ.drop_quarantine('SQL_QUARANTINE_8zpc9pwdmb8vr52c669e9');
END;
/
复制

传输隔离区定义

该DBMS_SQLQ软件包使我们能够在数据库之间传输SQL隔离定义。

我们使用该CREATE_STGTAB_QUARANTINE过程创建一个临时表。在STAGING_TABLE_OWNER和TABLESPACE_NAME参数默认为空，这意味着该表将在目前的方案中创建，并用于该模式的默认表空间。

BEGIN
  sys.DBMS_SQLQ.create_stgtab_quarantine(staging_table_name  => 'my_quarantine_defs',
                                         staging_table_owner => 'testuser1',
                                         tablespace_name     => NULL);
END;
/
复制

我们使用PACK_STGTAB_QUARANTINE函数将SQL隔离定义转移到登台表。默认情况下，它捕获所有定义。我们可以使用这个限制NAME，SQL_TEST并且ENABLED参数。在下面的示例中，我们使用默认值。我们可以省略这三个参数。

SET SERVEROUTPUT ON
DECLARE
  l_number  NUMBER;
BEGIN
  l_number  := sys.DBMS_SQLQ.pack_stgtab_quarantine (
    staging_table_name  => 'my_quarantine_defs',
    staging_table_owner => 'testuser1',
    name                => '%',
    sql_text            => '%',
    enabled             => NULL);
  DBMS_OUTPUT.put_line('l_number=' || l_number);
END;
/
l_number=3


PL/SQL procedure successfully completed.

SQL>
复制

可以使用表级export和import将表转移到另一个数据库。

将表转移到新数据库后，可以使用以下UNPACK_STGTAB_QUARANTINE过程来加载定义。参数及其作用与PACK_STGTAB_QUARANTINE功能相似。

SET SERVEROUTPUT ON
DECLARE
  l_number  NUMBER;
BEGIN
  l_number  := sys.DBMS_SQLQ.unpack_stgtab_quarantine (
    staging_table_name  => 'my_quarantine_defs',
    staging_table_owner => 'testuser1',
    name                => '%',
    sql_text            => '%',
    enabled             => NULL);
  DBMS_OUTPUT.put_line('l_number=' || l_number);
END;
/
l_number=3


PL/SQL procedure successfully completed.

SQL>
复制

文章来源：https://oracle-base.com/articles/19c/sql-quarantine-19c