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

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


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

张大朋(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的方式没有使用索引,而是全表扫描,这是我们预期的结果:


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


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


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


查看生成的报告内容:


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

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

Oracle智能之SQL诊断: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 执行的情况:


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


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


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


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


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

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


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



上一篇:思维导图学 Linux Shell攻略之干货篇 mysql数据库脚本管理系统


下一篇:oracle 如何稳定执行计划