SQL Access Advisor的使用

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

<span>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 /                        
 
您可以通过命令行或 Oracle 企业管理器数据库控制与顾问程序进行交互，但<strong>使用</strong> GUI 可以提供更好的值（GUI 可让您将解决方案可视化，并将许多任务简化为简单的点击操作）。
</span><p><span>要<strong>使用</strong>企业管理器中的 SQL Access Advisor 解决 SQL 中的问题，请遵循以下步骤。</span></p><ol>
<li>
<span>当然，第一个任务是启动企业管理器。在 Database 主页上，向下滚动到页面底部，您将在这里看到几个超链接，如下图所示：          </span><p><span><img  src="/inc/test.jsp?url=http%3A%2F%2Fwww.oracle.com%2Focom%2Fgroups%2Fpublic%2F%40otn%2Fdocuments%2Fdigitalasset%2F114311.jpg&refer=http%3A%2F%2Fblog.csdn.net%2Fhaungyuncheng%2Farticle%2Fdetails%2F23039705" alt="SQL Access Advisor的使用" ></span></p>
</li>
<li>
<span>在该菜单中，单击 <strong>Advisor Central</strong>，这将显示一个与下图类似的屏幕。下面仅显示了该屏幕的顶部。         </span><p><span><img  src="/inc/test.jsp?url=http%3A%2F%2Fwww.oracle.com%2Focom%2Fgroups%2Fpublic%2F%40otn%2Fdocuments%2Fdigitalasset%2F117605.jpg&refer=http%3A%2F%2Fblog.csdn.net%2Fhaungyuncheng%2Farticle%2Fdetails%2F23039705" alt="SQL Access Advisor的使用" ></span></p>
</li>
<li>
<span>单击 <strong>SQL Advisors</strong>，这将显示一个与下图类似的屏幕。  </span><p><span><img  src="/inc/test.jsp?url=http%3A%2F%2Fwww.oracle.com%2Focom%2Fgroups%2Fpublic%2F%40otn%2Fdocuments%2Fdigitalasset%2F123806.jpg&refer=http%3A%2F%2Fblog.csdn.net%2Fhaungyuncheng%2Farticle%2Fdetails%2F23039705" alt="SQL Access Advisor的使用" ></span></p>
</li>
<li>
<span>在该屏幕中，您可以计划 SQL Access Advisor 会话，并指定其选项。顾问程序必须收集一些要<strong>使用</strong>的 SQL 语句。最简单的选项就是通过 Current and Recent SQL Activity 从共享池获取它们。选择该选项，您可以获取共享池中缓存的所有 SQL 语句来进行分析。 </span><p><span>但是，在某些情况下，您并不需要共享池中的所有语句；而仅需要其中的一组特定语句。为此，您需要在另一个屏幕上创建一个“SQL 调整工具集”，然后在这里（即，该屏幕中）引用集合名。 </span></p>
<p><span>此外，您可能希望根据理论上预期会发生的情况来运行复合负载。这些类型的 SQL 语句将不会位于共享池中，因为它们尚未处理。相反，您需要创建这些语句并将其存储在一个特殊表中。在第三个选项 (<strong>Create a Hypothetical Workload...</strong>) 中，您需要提供该表的名称以及模式名。    </span></p>
<p><span>对于本文，假设您希望从共享池中获取 SQL。因此，选择第一个选项（即默认选项），如屏幕所示。 
</span></p>
</li>
<li>
<span>但是，您可能并不需要所有语句，而只需要一些关键语句。例如，您可能只希望分析用户 SCOTT（即应用程序用户）执行的 SQL。所有其他用户可能会执行即席 SQL 语句，但您希望在分析中排除它们。在这种情况下，单击 <strong>Filter Options</strong> 前面的“+”号，如下图所示。      </span><p><span><img  src="/inc/test.jsp?url=http%3A%2F%2Fwww.oracle.com%2Focom%2Fgroups%2Fpublic%2F%40otn%2Fdocuments%2Fdigitalasset%2F104252.jpg&refer=http%3A%2F%2Fblog.csdn.net%2Fhaungyuncheng%2Farticle%2Fdetails%2F23039705" alt="SQL Access Advisor的使用" ></span></p>
</li>
<li><span>在该屏幕中，在要求您输入用户的文本框中输入 SCOTT，然后选择单选按钮 <strong>Include only SQL...</strong>（默认选项）。同样，您也可以排除某些用户。例如，您希望捕获数据库中的所有活动，除了用户 SYS、SYSTEM 和 SYSMAN。您可以在文本框中输入这些用户，然后单击按钮 <strong>Exclude all SQL statements...</strong>。 </span></li>
<li><span>您可以按 Module Id、Action 甚至 SQL 语句中的特定字符串来过滤语句中访问的表。其目的是确保只分析感兴趣的语句。选择整个 SQL 缓存的小型子集可以加快分析速度。在本例中，我们假设用户 SCOTT 仅执行了一个语句。如果不是这样，您可以施加额外的过滤条件，将分析集合减少到只有一个 SQL（即，原始问题语句中提到的那个 SQL）。 </span></li>
<li>
<span>单击 <strong>Next</strong>。这将显示以下屏幕（仅显示了顶部）：     </span><p><span><img  src="/inc/test.jsp?url=http%3A%2F%2Fwww.oracle.com%2Focom%2Fgroups%2Fpublic%2F%40otn%2Fdocuments%2Fdigitalasset%2F104207.jpg&refer=http%3A%2F%2Fblog.csdn.net%2Fhaungyuncheng%2Farticle%2Fdetails%2F23039705" alt="SQL Access Advisor的使用" ></span></p>
</li>
<li><span>在该屏幕中，您可以指定应该搜索哪些类型的建议。例如，在本例中，我们希望顾问程序查找潜在的索引、物化视图和分区，因此应选中这些项旁边的所有复选框。对于 Advisor Mode，您可以进行选择；默认选项 Limited Mode 仅处理高成本 SQL 语句。当然，这可以加快速度并获得更好的结果集。要分析所有 SQL，应<strong>使用</strong> Comprehensive Mode。（在本例中，模式的选择无关紧要，因为您只有一个 SQL。） </span></li>
<li><span>屏幕的后半部分显示了高级选项，例如，应该如何确定 SQL 语句的优先顺序、所<strong>使用</strong>的表空间等等。您可以保留默认项为标记状态（稍后将描述更多内容）。单击 <strong>Next</strong>，这将显示计划屏幕。选择 <strong>Run Immediately</strong>，并单击 <strong>Next</strong>。 </span></li>
<li><span>单击 <strong>Submit</strong>。这将创建一个 Scheduler 作业。您可以单击该屏幕中显示的作业超链接，它们位于页面顶部。作业将显示为 <strong>Running</strong>。 </span></li>
<li><span>反复单击 <strong>Refresh</strong> 直到您看到 <strong>Last Run Status</strong> 列下方的值更改为 <strong>SUCCEEDED</strong>。 </span></li>
<li>
<span>现在，返回 Database 主页并单击 <strong>Advisor Central</strong>，正如您在第一步中所做的那样。现在，您将看到 <strong>SQL Access Advisor</strong> 行，如下图所示：  </span><p><span><img  src="/inc/test.jsp?url=http%3A%2F%2Fwww.oracle.com%2Focom%2Fgroups%2Fpublic%2F%40otn%2Fdocuments%2Fdigitalasset%2F123123.jpg&refer=http%3A%2F%2Fblog.csdn.net%2Fhaungyuncheng%2Farticle%2Fdetails%2F23039705" alt="SQL Access Advisor的使用" ></span></p>
</li>
<li>
<span>该屏幕表明 SQL Access Advisor 任务已经 <strong>COMPLETED</strong>。现在，单击按钮 <strong>View Result</strong>。屏幕显示如下：     </span><p><span><img  src="/inc/test.jsp?url=http%3A%2F%2Fwww.oracle.com%2Focom%2Fgroups%2Fpublic%2F%40otn%2Fdocuments%2Fdigitalasset%2F119058.jpg&refer=http%3A%2F%2Fblog.csdn.net%2Fhaungyuncheng%2Farticle%2Fdetails%2F23039705" alt="SQL Access Advisor的使用" ></span></p>
</li>
<li>
<span>该屏幕说明了一切！SQL Access Advisor 分析了 SQL 语句，并发现某些解决方案可以将查询性能提高十倍。要查看提供了哪些具体建议，单击 <strong>Recommendations</strong> 选项卡，这将显示详细信息屏幕，如下所示。       </span><p><span><img  src="/inc/test.jsp?url=http%3A%2F%2Fwww.oracle.com%2Focom%2Fgroups%2Fpublic%2F%40otn%2Fdocuments%2Fdigitalasset%2F116660.jpg&refer=http%3A%2F%2Fblog.csdn.net%2Fhaungyuncheng%2Farticle%2Fdetails%2F23039705" alt="SQL Access Advisor的使用" ></span></p>
</li>
<li>
<span>从较高级别看，该屏幕提供了许多很好的信息。例如，对于 ID = 1 的语句，Actions 列下方有两个建议操作。下一列 Action Types 显示了操作类型，由彩色方块表示。根据下方的图标指南，您可以了解这两个操作分别针对索引和分区。它们可以共同将性能提高几个数量级。 </span><p><span>要确切了解可以提高哪个 SQL 语句，单击 ID，这将显示以下屏幕。当然，该分析只有一个语句，因此这里只显示一项内容。如果您有多个语句，应该可以看到所有内容。     </span></p>
<p><span><img  src="/inc/test.jsp?url=http%3A%2F%2Fwww.oracle.com%2Focom%2Fgroups%2Fpublic%2F%40otn%2Fdocuments%2Fdigitalasset%2F104210.jpg&refer=http%3A%2F%2Fblog.csdn.net%2Fhaungyuncheng%2Farticle%2Fdetails%2F23039705" alt="SQL Access Advisor的使用" ></span></p>
</li>
<li>
<span>在上面的屏幕上，请注意 Recommendation ID 列。单击超链接将显示详细建议，如下所示：      </span><p><span><img  src="/inc/test.jsp?url=http%3A%2F%2Fwww.oracle.com%2Focom%2Fgroups%2Fpublic%2F%40otn%2Fdocuments%2Fdigitalasset%2F116329.jpg&refer=http%3A%2F%2Fblog.csdn.net%2Fhaungyuncheng%2Farticle%2Fdetails%2F23039705" alt="SQL Access Advisor的使用" ></span></p>
</li>
<li>
<span>该屏幕将提供非常清楚的解决方案描述。它提出了两个建议：创建分区表和<strong>使用</strong>索引。随后，它发现索引已经存在，因此建议保留该索引。 </span><p><span>如果您单击 Action 列下方的 <strong>PARTITION TABLE</strong>，将看到 Oracle 为使其成为分区表而生成的实际脚本。但是，在单击之前，在文本框中填入表空间名称。这将允许 SQL Access Advisor 在构建该脚本时<strong>使用</strong>该表空间：</span></p>
<pre class="brush:php;toolbar:false"><span>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";              </span>

脚本实际上将构建一个新表，然后将其重命名以匹配原始表。

最后一个选项卡 Details 将显示有关任务的某些有趣的详细信息。尽管它们对于分析并不重要，但可以提供有关顾问程序如何得出这些结论的有价值线索，从而有助于您自己的思考过程。该屏幕分为两部分，第一个部分是 Workload and Task Options，如下所示。

屏幕的后半部分显示任务的运行日志。有时，顾问程序无法处理所有 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 查看建议，将显示一个如下所示的屏幕：

SQL Tuning 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 在这些过程中提供了一个非常有用的帮助。