Oracle固定执行计划之SQL PROFILE概要文件详解

下面我就为您详细讲解一下“Oracle固定执行计划之SQL PROFILE概要文件详解”的完整攻略。

什么是SQL PROFILE？

SQL PROFILE 是一种将 SQL 语句的执行计划持久存储在数据库中的机制。它可以被看作是 Oracle 中固定查询计划的一种技术解决方案，它会将最佳的执行计划与 SQL 语句绑定在一起，从而确保每次执行 SQL 语句时都使用相同的执行计划。

创建 SQL PROFILE

使用 SQL PROFILE 的第一步是创建它，可以采用以下方式：

DECLARE
  my_plan  SYS.SQLPROF_ATTR;
  my_hint  VARCHAR2(100);
BEGIN
  my_hint := 'GATHER_PLAN_STATISTICS';
  my_plan := SYS.SQLPROF_ATTR(
    --
    -- Specify the hints to be used.
    -- These may be any valid hints; this example gathers plan
    -- statistics.
    --
    hint_text => my_hint,
    level     => 'DEFAULT',
    --
    -- Set the value of statistics_level as it should be when this
    -- profile is in use. This supports Oracle Database feature usage
    -- tracking and SQL plan directives. Tuning Oracle Database
    -- recommends AUTOTASK for managing SQL directives and SQL patches.
    --
    attr1     => 'STATISTICS_LEVEL=TYPICAL',
    --
    -- Set parameterized SQL text and bind type to NONE. The following
    -- SQL text matches a simple SQL query with no bind variables.
    --
    attr2     => 'SELECT ENAME, EMPNO FROM EMP WHERE EMPNO = 7788',
    bind_data => NULL,
    --
    -- Set the format of the SQL text.
    -- In this example, the format is lowercase and no formatting.
    --
    attr3     => 'NATIVE:o=LOWER',
    --
    -- This example assumes that my_prof is the name of a user-defined
    -- SQL profile. This step saves the profile to the database.
    --
    attr4     => 'NAME:my_prof',
    --
    -- Specify "DEFAULT" to indicate that this profile should be used
    -- when no user-defined profile or other hints are in use.
    -- Set the force_match parameter to TRUE to ensure that this profile
    -- will be used whenever possible.
    --
    attr5     => 'DEFAULT',
    force_match => TRUE
  );
  DBMS_SQLTUNE.CREATE_SQL_PROFILE(sql_text       => my_plan.attr2,
                                   profile        => my_plan,
                                   name           => my_plan.attr4,
                                   description    => 'My SQL Profile',
                                   category       => 'DEFAULT',
                                   validate       => TRUE,
                                   replace        => TRUE);
END;

上述示例代码中展示了如何创建一个名为“my_prof”的 SQL PROFILE，它包含了一个最合适的执行计划。

应用 SQL PROFILE

创建 SQL PROFILE 后，可以通过以下方式将其应用到 SQL 语句上：

DECLARE
  my_sql_id     VARCHAR2(13) := '5ftrg9fvdgf8d';
  my_plan_name  VARCHAR2(128) := 'my_prof';
BEGIN
  DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE(SQL_ID => my_sql_id);
  DBMS_SPM.ALTER_SQL_PROFILE(plan_name      => my_plan_name,
                             attribute_name => 'CATEGORY',
                             attribute_value => 'CUSTOM',
                             force_match     => TRUE);
END;

上述示例代码中展示了如何将 SQL PROFILE 应用到名为“5ftrg9fvdgf8d”的 SQL 语句上。

示例说明：

下面提供两条示例说明，以帮助更好地理解 SQL PROFILE 的使用方法。

示例一

假设我们有以下 SQL 语句：

select /*+ index(emp emp_email) */ * from emp where email = :email

这个 SQL 语句包含了一个子查询，但是 Oracle 会在执行时先查询主表，然后再执行子查询。

如果我们想要让 Oracle 直接执行子查询，可以使用以下方式创建一个 SQL PROFILE：

DECLARE
  my_plan  SYS.SQLPROF_ATTR;
  my_hint  VARCHAR2(100);
BEGIN
  my_hint := 'LEADING(emp_email emp)';
  my_plan := SYS.SQLPROF_ATTR(
    hint_text => my_hint,
    level     => 'DEFAULT',
    attr1     => 'STATISTICS_LEVEL=TYPICAL',
    attr2     => 'SELECT * FROM (SELECT /*+ no_unnest */ * FROM emp_email) WHERE email = :email',
    bind_data => NULL,
    attr3     => 'NATIVE:o=LOWER',
    attr4     => 'NAME:my_prof',
    attr5     => 'DEFAULT',
    force_match => TRUE
  );
  DBMS_SQLTUNE.CREATE_SQL_PROFILE(sql_text       => my_plan.attr2,
                                   profile        => my_plan,
                                   name           => my_plan.attr4,
                                   description    => 'My SQL Profile',
                                   category       => 'DEFAULT',
                                   validate       => TRUE,
                                   replace        => TRUE);
END;

上述示例代码中的 SQL PROFILE 将运行计划更改为在子查询上执行主表，在大多数情况下会比默认的显式连接更优。

示例二

假设我们有以下 SQL 语句：

SELECT /*+ ORDERED USE_HASH(a b) */ *
FROM a, b
WHERE a.id=b.id and a.id in (1,2,3);

这个 SQL 语句需要联结两个表，Oracle 默认的执行计划（使用嵌套循环连接）的性能并不理想。

如果我们希望Oracle使用HASH连接，可以使用以下方式创建一个SQL PROFILE：

DECLARE
  my_plan  SYS.SQLPROF_ATTR;
  my_hint  VARCHAR2(100);
BEGIN
  my_hint := 'USE_HASH(a b)';
  my_plan := SYS.SQLPROF_ATTR(
    hint_text => my_hint,
    level     => 'DEFAULT',
    attr1     => 'STATISTICS_LEVEL=TYPICAL',
    attr2     => 'SELECT /*+ USE_HASH(a b) */ *
              FROM a, b
             WHERE a.id=b.id and a.id in (1,2,3)',
    bind_data => NULL,
    attr3     => 'NATIVE:o=LOWER',
    attr4     => 'NAME:my_prof',
    attr5     => 'DEFAULT',
    force_match => TRUE
  );
  DBMS_SQLTUNE.CREATE_SQL_PROFILE(sql_text       => my_plan.attr2,
                                   profile        => my_plan,
                                   name           => my_plan.attr4,
                                   description    => 'My SQL Profile',
                                   category       => 'DEFAULT',
                                   validate       => TRUE,
                                   replace        => TRUE);
END;

上述示例代码中的 SQL PROFILE 将运行计划更改为使用 HASH 连接，可以获得更好的性能。

希望这两个示例可以帮助您更好地理解如何使用 SQL PROFILE。