Oracle智能之SQL诊断：SQL Tuning Advisor推荐执行计划

编辑手记：在前一段，一篇智能数据库优化的论文引起广泛的关注，其实在 Oracle 数据库中，已经引入了大量自动化和智能化的方法去进行自动调节，包括在 SQL 层面的智能诊断分析和建议。

张大朋（Lunar）Oracle 资深技术专家

Lunar 拥有超过十年的 ORACLE SUPPORT 从业经验，曾经服务于ORACLE ACS部门，现就职于 ORACLE Sales Consultant 部门，负责的产品主要是 Exadata，Golden Gate，Database 等。

本文的测试目的，起因一个问题：当有hint时，并且hint跟需要绑定的执行计划有冲突，谁的优先级高？

在这个演示过程中，使用SQL Tuning Advisor来进行辅助，在 Oracle 数据库中，SQL Tuning Advisor 的智能化程度可能超过很多人的想象，应该多学习和使用。

首先创建一个测试用例：

LUNAR@lunardb>create table lunartest1 (n number );

 

Table created.

 

Elapsed: 00:00:00.08

LUNAR@lunardb>begin

  2         for i in 1 .. 10000 loop

  3             insert into lunartest1 values(i);

  4             commit;

  5         end loop;

  6        end;

  7  /

 

PL/SQL procedure successfully completed.

LUNAR@lunardb>create index idx_lunartest1_n on lunartest1(n);

Index created.

 

Elapsed: 00:00:00.04

执行查询，我们看到sql按照hint的方式没有使用索引，而是全表扫描，这是我们预期的结果：

下面我们运行SQL Tuning Advisor来生成建议报告：

查看生成的报告内容：

这里我们看到SQL Tuning Advisor提示了两个建议：

1.收集统计信息： execute dbms_stats.gather_table_stats(ownname => 'LUNAR', tabname =>'LUNARTEST1', estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE, method_opt => 'FOR ALL COLUMNS SIZE AUTO'); 2，提供了一个执行计划建议： execute dbms_sqltune.accept_sql_profile(task_name => 'Lunar_tunning_bjgduva68mbqm', task_owner => 'LUNAR', replace =>TRUE);

并且给出了这个执行计划和原始执行计划的对比，可以看到 执行效率提高了89%以上，逻辑读从23降低为2，减少了91.3%。

我们按照建议执行以上的两条命令。首先收集统计信息，再接受建议的执行计划，现在看看 SQL 执行的情况：

这里我们看到，这个执行计划中已经使用了索引，并且逻辑读从49降低为14。

现在我们查看一下这个SQL Profile的OUTLINE：

这里我们看到该SQP Profile中提供了详细的表和列的统计信息
并且有“IGNORE_OPTIM_EMBEDDED_HINTS”，也就是忽略嵌入到SQL中的hint 。

结论：虽然这个SQL的hint中指定了no index，即不使用索引，但是SQL语句仍然按照SYS_SQLPROF_015236655fb80000指定的profile使用了index。
 这说明dbms_sqltune.accept_sql_profile方式绑定的执行计划优先级高于hint指定是否使用索引的方式。

本文出自数据和云公众号，原文链接