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 中的问题,请遵循以下步骤。
- 当然,第一个任务是启动企业管理器。在 Database 主页上,向下滚动到页面底部,您将在这里看到几个超链接,如下图所示:
- 在该菜单中,单击 Advisor Central,这将显示一个与下图类似的屏幕。下面仅显示了该屏幕的顶部。
- 单击 SQL Advisors,这将显示一个与下图类似的屏幕。
- 在该屏幕中,您可以计划 SQL Access Advisor 会话,并指定其选项。顾问程序必须收集一些要使用的 SQL 语句。最简单的选项就是通过 Current and Recent SQL Activity 从共享池获取它们。选择该选项,您可以获取共享池中缓存的所有 SQL 语句来进行分析。
但是,在某些情况下,您并不需要共享池中的所有语句;而仅需要其中的一组特定语句。为此,您需要在另一个屏幕上创建一个“SQL 调整工具集”,然后在这里(即,该屏幕中)引用集合名。
此外,您可能希望根据理论上预期会发生的情况来运行复合负载。这些类型的 SQL 语句将不会位于共享池中,因为它们尚未处理。相反,您需要创建这些语句并将其存储在一个特殊表中。在第三个选项 (Create a Hypothetical Workload...) 中,您需要提供该表的名称以及模式名。
对于本文,假设您希望从共享池中获取 SQL。因此,选择第一个选项(即默认选项),如屏幕所示。
- 但是,您可能并不需要所有语句,而只需要一些关键语句。例如,您可能只希望分析用户 SCOTT(即应用程序用户)执行的 SQL。所有其他用户可能会执行即席 SQL 语句,但您希望在分析中排除它们。在这种情况下,单击 Filter Options 前面的“+”号,如下图所示。
- 在该屏幕中,在要求您输入用户的文本框中输入 SCOTT,然后选择单选按钮 Include only SQL...(默认选项)。同样,您也可以排除某些用户。例如,您希望捕获数据库中的所有活动,除了用户 SYS、SYSTEM 和 SYSMAN。您可以在文本框中输入这些用户,然后单击按钮 Exclude all SQL statements...。
- 您可以按 Module Id、Action 甚至 SQL 语句中的特定字符串来过滤语句中访问的表。其目的是确保只分析感兴趣的语句。选择整个 SQL 缓存的小型子集可以加快分析速度。在本例中,我们假设用户 SCOTT 仅执行了一个语句。如果不是这样,您可以施加额外的过滤条件,将分析集合减少到只有一个 SQL(即,原始问题语句中提到的那个 SQL)。
- 单击 Next。这将显示以下屏幕(仅显示了顶部):
- 在该屏幕中,您可以指定应该搜索哪些类型的建议。例如,在本例中,我们希望顾问程序查找潜在的索引、物化视图和分区,因此应选中这些项旁边的所有复选框。对于 Advisor Mode,您可以进行选择;默认选项 Limited Mode 仅处理高成本 SQL 语句。当然,这可以加快速度并获得更好的结果集。要分析所有 SQL,应使用 Comprehensive Mode。(在本例中,模式的选择无关紧要,因为您只有一个 SQL。)
- 屏幕的后半部分显示了高级选项,例如,应该如何确定 SQL 语句的优先顺序、所使用的表空间等等。您可以保留默认项为标记状态(稍后将描述更多内容)。单击 Next,这将显示计划屏幕。选择 Run Immediately,并单击 Next。
- 单击 Submit。这将创建一个 Scheduler 作业。您可以单击该屏幕中显示的作业超链接,它们位于页面顶部。作业将显示为 Running。
- 反复单击 Refresh 直到您看到 Last Run Status 列下方的值更改为 SUCCEEDED。
- 现在,返回 Database 主页并单击 Advisor Central,正如您在第一步中所做的那样。现在,您将看到 SQL Access Advisor 行,如下图所示:
- 该屏幕表明 SQL Access Advisor 任务已经 COMPLETED。现在,单击按钮 View Result。屏幕显示如下:
- 该屏幕说明了一切!SQL Access Advisor 分析了 SQL 语句,并发现某些解决方案可以将查询性能提高十倍。要查看提供了哪些具体建议,单击 Recommendations 选项卡,这将显示详细信息屏幕,如下所示。
- 从较高级别看,该屏幕提供了许多很好的信息。例如,对于 ID = 1 的语句,Actions 列下方有两个建议操作。下一列 Action Types 显示了操作类型,由彩色方块表示。根据下方的图标指南,您可以了解这两个操作分别针对索引和分区。它们可以共同将性能提高几个数量级。
要确切了解可以提高哪个 SQL 语句,单击 ID,这将显示以下屏幕。当然,该分析只有一个语句,因此这里只显示一项内容。如果您有多个语句,应该可以看到所有内容。
- 在上面的屏幕上,请注意 Recommendation ID 列。单击超链接将显示详细建议,如下所示:
- 该屏幕将提供非常清楚的解决方案描述。它提出了两个建议:创建分区表和使用索引。随后,它发现索引已经存在,因此建议保留该索引。
如果您单击 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";
脚本实际上将构建一个新表,然后将其重命名以匹配原始表。 - 最后一个选项卡 Details 将显示有关任务的某些有趣的详细信息。尽管它们对于分析并不重要,但可以提供有关顾问程序如何得出这些结论的有价值线索,从而有助于您自己的思考过程。该屏幕分为两部分,第一个部分是 Workload and Task Options,如下所示。
- 屏幕的后半部分显示任务的运行日志。有时,顾问程序无法处理所有 SQL 语句。如果某些 SQL 语句被舍弃,就会在这里显示,并计入 Invalid SQL String:Statements discarded 计数。如果您不明白为什么只分析了数个 SQL 语句,下面就是原因。
高级选项
在上面的第 10 步中,我使用了一个对高级设置的引用。我们来看看这些设置的作用。
单击 Advanced Options 左侧的加号,这将显示一个屏幕,如下所示:
![图 13](http://www.oracle.com/technology/pub/images/11g-saa-f13.jpg)
该屏幕允许您输入将在其中创建索引的表空间的名称、索引的创建模式等。对于分区建议,您可以指定实现分区的表空间等。
看来,最重要的元素是 Consider access structures creation costs recommendations 复选框。如果您选中该复选框,SQL Access Advisor 将考虑索引本身的创建成本。例如,是否应该创建 10 个新索引,相关成本可能会导致 SQL Access Advisor 建议不创建它们。
您还可以在该屏幕中指定索引的最大大小。
与 SQL Tuning Advisor 的差异
在简介中,我只简单描述了该工具与 SQL Tuning Advisor 的不同,下面我们来详细说明它们之间的差异。一个简单演示可以最好地说明这些差异。
从 SQL Advisors 屏幕中,选择 SQL Tuning Advisor 并运行。完成后,下面是显示结果的屏幕部分:
![图 14](http://www.oracle.com/technology/pub/images/11g-saa-f14.jpg)
现在,如果您单击 View 查看建议,将显示一个如下所示的屏幕:
![图 15](http://www.oracle.com/technology/pub/images/11g-saa-f15.jpg)
仔细查看建议:它将根据 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 调整计划:
- 搜索高成本 SQL 语句,或者(更好的是)评估整个负载。
- 将可疑语句放入 SQL 调整工具集。
- 使用 SQL Tuning Advisor 和 SQL Access Advisor 对其进行分析。
- 得到分析结果;记录建议。
- 将建议插入 SQL Performance Analyzer(参见本文)。
- 在 SQL Performance Analyzer 中检查更改前后的情况,并得出最佳解决方案。
- 重复上述操作,直到获得最佳模式设计。
- 获得最佳模式设计之后,您可能希望使用 SQL 计划管理基准锁定该计划(如本文所述)。
结论
调整数据库结构是最费时费力的棘手任务之一,同时也是最有成效的任务之一。同样,分区是一个非常有效的调整工具,但分区的选择很难轻松决定。SQL Access Advisor 在这些过程中提供了一个非常有用的帮助。