Oracle 数据库 11g 新特性：SQL Access Advisor

Oracle 数据库 10g 提供了大量帮助程序（或“顾问程序”），可帮助您决定最佳操作流程。其中一个示例是 SQL Tuning Advisor，它可以提供有关查询调整以及在流程中延长整个优化过程的建议。

但请考虑以下调整案例：假设一个索引确实有助于某个查询，但该查询只执行一次。这样，即使该查询可以得益于此索引，但创建索引的成本也会超出其带来的好处。要按这种方式分析案例，您需要了解查询的访问频率和原因。

另一个顾问程序 (SQL Access Advisor) 可执行这种类型的分析。除了像在 Oracle 数据库 10g 中一样可以分析索引、物化视图等，Oracle 数据库 11g 中的 SQL Access Advisor 还可以分析表和查询以识别可能的分区策略 — 这在设计最佳模式时可以提供很大帮助。在 Oracle 数据库 11g 中，SQL Access Advisor 现在可以提供与整个负载相关的建议，包括考虑创建成本和维护访问结构。

在本文中，您将了解新的 SQL Access Advisor 如何解决常见问题。（注：出于演示目的，我们将通过一个语句演示这个功能；但是，Oracle 建议使用 SQL Access Advisor 来帮助调整整个负载，而不只是一个 SQL 语句。）

问题

下面是一个典型问题。应用程序发出了以下 SQL 语句。该查询似乎要消耗大量资源并且速度很慢。

 
select store_id, guest_id, count(1) cnt
from res r, trans t
where r.res_id between 2 and 40
and t.res_id = r.res_id
group by store_id, guest_id
/

该 SQL 涉及两个表，即 RES 和 TRANS；后者是前者的子表。您需要找到提高查询性能的解决方案 — SQL Access Advisor 正是最合适的工具。

您可以通过命令行或 Oracle 企业管理器数据库控制与顾问程序进行交互，但使用 GUI 可以提供更好的值（GUI 可让您将解决方案可视化，并将许多任务简化为简单的点击操作）。

要使用企业管理器中的 SQL Access Advisor 解决 SQL 中的问题，请遵循以下步骤。

1. 当然，第一个任务是启动企业管理器。在 Database 主页上，向下滚动到页面底部，您将在这里看到几个超链接，如下图所示：
   
2. 在该菜单中，单击 Advisor Central，这将显示一个与下图类似的屏幕。下面仅显示了该屏幕的顶部。
   
3. 单击 SQL Advisors，这将显示一个与下图类似的屏幕。
   
4. 在该屏幕中，您可以计划 SQL Access Advisor 会话，并指定其选项。顾问程序必须收集一些要使用的 SQL 语句。最简单的选项就是通过 Current and Recent SQL Activity 从共享池获取它们。选择该选项，您可以获取共享池中缓存的所有 SQL 语句来进行分析。

   但是，在某些情况下，您并不需要共享池中的所有语句；而仅需要其中的一组特定语句。为此，您需要在另一个屏幕上创建一个“SQL 调整工具集”，然后在这里（即，该屏幕中）引用集合名。

   此外，您可能希望根据理论上预期会发生的情况来运行复合负载。这些类型的 SQL 语句将不会位于共享池中，因为它们尚未处理。相反，您需要创建这些语句并将其存储在一个特殊表中。在第三个选项 (Create a Hypothetical Workload...) 中，您需要提供该表的名称以及模式名。

   对于本文，假设您希望从共享池中获取 SQL。因此，选择第一个选项（即默认选项），如屏幕所示。

5. 但是，您可能并不需要所有语句，而只需要一些关键语句。例如，您可能只希望分析用户 SCOTT（即应用程序用户）执行的 SQL。所有其他用户可能会执行即席 SQL 语句，但您希望在分析中排除它们。在这种情况下，单击 Filter Options 前面的“+”号，如下图所示。
   
6. 在该屏幕中，在要求您输入用户的文本框中输入 SCOTT，然后选择单选按钮 Include only SQL...（默认选项）。同样，您也可以排除某些用户。例如，您希望捕获数据库中的所有活动，除了用户 SYS、SYSTEM 和 SYSMAN。您可以在文本框中输入这些用户，然后单击按钮 Exclude all SQL statements...。
   
   
7. 您可以按 Module Id、Action 甚至 SQL 语句中的特定字符串来过滤语句中访问的表。其目的是确保只分析感兴趣的语句。选择整个 SQL 缓存的小型子集可以加快分析速度。在本例中，我们假设用户 SCOTT 仅执行了一个语句。如果不是这样，您可以施加额外的过滤条件，将分析集合减少到只有一个 SQL（即，原始问题语句中提到的那个 SQL）。
   
   
8. 单击 Next。这将显示以下屏幕（仅显示了顶部）：
   
9. 在该屏幕中，您可以指定应该搜索哪些类型的建议。例如，在本例中，我们希望顾问程序查找潜在的索引、物化视图和分区，因此应选中这些项旁边的所有复选框。对于 Advisor Mode，您可以进行选择；默认选项 Limited Mode 仅处理高成本 SQL 语句。当然，这可以加快速度并获得更好的结果集。要分析所有 SQL，应使用 Comprehensive Mode。（在本例中，模式的选择无关紧要，因为您只有一个 SQL。）
   
   
10. 屏幕的后半部分显示了高级选项，例如，应该如何确定 SQL 语句的优先顺序、所使用的表空间等等。您可以保留默认项为标记状态（稍后将描述更多内容）。单击 Next，这将显示计划屏幕。选择 Run Immediately，并单击 Next。
11. 单击 Submit。这将创建一个 Scheduler 作业。您可以单击该屏幕中显示的作业超链接，它们位于页面顶部。作业将显示为 Running。
    
    
12. 反复单击 Refresh 直到您看到 Last Run Status 列下方的值更改为 SUCCEEDED。
    
    
13. 现在，返回 Database 主页并单击 Advisor Central，正如您在第一步中所做的那样。现在，您将看到 SQL Access Advisor 行，如下图所示：
    
14. 该屏幕表明 SQL Access Advisor 任务已经 COMPLETED。现在，单击按钮 View Result。屏幕显示如下：
    
15. 该屏幕说明了一切！SQL Access Advisor 分析了 SQL 语句，并发现某些解决方案可以将查询性能提高十倍。要查看提供了哪些具体建议，单击 Recommendations 选项卡，这将显示详细信息屏幕，如下所示。
    
16. 从较高级别看，该屏幕提供了许多很好的信息。例如，对于 ID = 1 的语句，Actions 列下方有两个建议操作。下一列 Action Types 显示了操作类型，由彩色方块表示。根据下方的图标指南，您可以了解这两个操作分别针对索引和分区。它们可以共同将性能提高几个数量级。

    要确切了解可以提高哪个 SQL 语句，单击 ID，这将显示以下屏幕。当然，该分析只有一个语句，因此这里只显示一项内容。如果您有多个语句，应该可以看到所有内容。

    
17. 在上面的屏幕上，请注意 Recommendation ID 列。单击超链接将显示详细建议，如下所示：
    
18. 该屏幕将提供非常清楚的解决方案描述。它提出了两个建议：创建分区表和使用索引。随后，它发现索引已经存在，因此建议保留该索引。

    如果您单击 Action 列下方的 PARTITION TABLE，将看到 Oracle 为使其成为分区表而生成的实际脚本。但是，在单击之前，在文本框中填入表空间名称。这将允许 SQL Access Advisor 在构建该脚本时使用该表空间：

    Rem 
    Rem Repartitioning table "SCOTT"."TRANS"
    Rem 
    
    SET SERVEROUTPUT ON
    SET ECHO ON
    
    Rem 
    Rem Creating new partitioned table
    Rem 
    CREATE TABLE "SCOTT"."TRANS1" 
    (    "TRANS_ID" NUMBER, 
        "RES_ID" NUMBER, 
        "TRANS_DATE" DATE, 
        "AMT" NUMBER, 
        "STORE_ID" NUMBER(3,0)
    ) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
    TABLESPACE "USERS" 
    PARTITION BY RANGE ("RES_ID") INTERVAL( 3000) ( PARTITION VALUES LESS THAN (3000)
    );
    
    begin
    dbms_stats.gather_table_stats('"SCOTT"', '"TRANS1"', NULL, dbms_stats.auto_sample_size);
    end;
    /
    
    Rem 
    Rem Copying constraints to new partitioned table
    Rem 
    ALTER TABLE "SCOTT"."TRANS1" MODIFY ("TRANS_ID" NOT NULL ENABLE);
    
    Rem 
    Rem Copying referential constraints to new partitioned table
    Rem 
    ALTER TABLE "SCOTT"."TRANS1" ADD CONSTRAINT "FK_TRANS_011" FOREIGN KEY ("RES_ID")
         REFERENCES "SCOTT"."RES" ("RES_ID") ENABLE;
    
    Rem 
    Rem Populating new partitioned table with data from original table
    Rem 
    INSERT /*+ APPEND */ INTO "SCOTT"."TRANS1"
    SELECT * FROM "SCOTT"."TRANS";
    COMMIT;
    
    Rem 
    Rem Renaming tables to give new partitioned table the original table name
    Rem 
    ALTER TABLE "SCOTT"."TRANS" RENAME TO "TRANS11";
    ALTER TABLE "SCOTT"."TRANS1" RENAME TO "TRANS";
    

    脚本实际上将构建一个新表，然后将其重命名以匹配原始表。
    
    
19. 最后一个选项卡 Details 将显示有关任务的某些有趣的详细信息。尽管它们对于分析并不重要，但可以提供有关顾问程序如何得出这些结论的有价值线索，从而有助于您自己的思考过程。该屏幕分为两部分，第一个部分是 Workload and Task Options，如下所示。
    
20. 屏幕的后半部分显示任务的运行日志。有时，顾问程序无法处理所有 SQL 语句。如果某些 SQL 语句被舍弃，就会在这里显示，并计入 Invalid SQL String:Statements discarded 计数。如果您不明白为什么只分析了数个 SQL 语句，下面就是原因。
    

高级选项

在上面的第 10 步中，我使用了一个对高级设置的引用。我们来看看这些设置的作用。

单击 Advanced Options 左侧的加号，这将显示一个屏幕，如下所示：

该屏幕允许您输入将在其中创建索引的表空间的名称、索引的创建模式等。对于分区建议，您可以指定实现分区的表空间等。

看来，最重要的元素是 Consider access structures creation costs recommendations 复选框。如果您选中该复选框，SQL Access Advisor 将考虑索引本身的创建成本。例如，是否应该创建 10 个新索引，相关成本可能会导致 SQL Access Advisor 建议不创建它们。

您还可以在该屏幕中指定索引的最大大小。

与 SQL Tuning Advisor 的差异

在简介中，我只简单描述了该工具与 SQL Tuning Advisor 的不同，下面我们来详细说明它们之间的差异。一个简单演示可以最好地说明这些差异。

从 SQL Advisors 屏幕中，选择 SQL Tuning Advisor 并运行。完成后，下面是显示结果的屏幕部分：

现在，如果您单击 View 查看建议，将显示一个如下所示的屏幕：

仔细查看建议：它将根据 RES_ID 列上的 TRANS 创建一个索引。但是，SQL Access Advisor 没有执行该建议。相反，它建议将表分区，原因如下：根据访问模式和可用数据，SQL Access Advisor 确定分区比在列上构建索引更加高效。与 SQL Tuning Advisor 提供的建议相比，这是一个更“实际”的建议。

SQL Tuning Advisor 提出的建议只对应以下四个目标之一：

• 为统计信息丢失或失效的对象收集统计信息
• 考虑优化器的任何数据偏差、复杂谓词或失效的统计信息
• 重新构建 SQL 以优化性能
• 提出新索引建议

这些建议仅与单个语句（而非整个负载）相关。因此，只能将 SQL Tuning Advisor 偶尔用于高负载或关键业务查询。注意，与 SQL Access Advisor 相比（其标准更加宽松），该顾问程序只建议能够提供重大性能改进的索引。当然，SQL Tuning Advisor 没有分区顾问程序。

用例

SQL Access Advisor 对于调整模式（而不仅仅是查询）很有用。作为一个最佳实践，您可以使用该策略来开发高效的 SQL 调整计划：

1. 搜索高成本 SQL 语句，或者（更好的是）评估整个负载。
2. 将可疑语句放入 SQL 调整工具集。
3. 使用 SQL Tuning Advisor 和 SQL Access Advisor 对其进行分析。
4. 得到分析结果；记录建议。
5. 将建议插入 SQL Performance Analyzer（参见）。
6. 在 SQL Performance Analyzer 中检查更改前后的情况，并得出最佳解决方案。
7. 重复上述操作，直到获得最佳模式设计。
8. 获得最佳模式设计之后，您可能希望使用 SQL 计划管理基准锁定该计划（如所述）。

结论

调整数据库结构是最费时费力的棘手任务之一，同时也是最有成效的任务之一。同样，分区是一个非常有效的调整工具，但分区的选择很难轻松决定。SQL Access Advisor 在这些过程中提供了一个非常有用的帮助。