【Oracle篇】掌握SQL Tuning Advisor优化工具：从工具使用到SQL优化的全方位指南（第六篇，总共七篇）

💫《博主介绍》：✨又是一天没白过，我是奈斯，DBA一名✨

💫《擅长领域》：✌️擅长Oracle、MySQL、SQLserver、阿里云AnalyticDB for MySQL(分布式数据仓库)、Linux，也在扩展大数据方向的知识面✌️

💖💖💖大佬们都喜欢静静的看文章，并且也会默默的点赞收藏加关注💖💖💖

2024年11月6号见证历史，特朗普再次当选美国总统（原第45任美国总统，因为被弹劾所以没有连任第46任，但获取了第47任候任总统资格，并成功当选），属实意料之外。特朗普在中国被亲切的称为“川建国”同志，2017年4月12日，特朗普在接受《华尔街日报》专访时表示：“朝鲜半岛曾是中国的一部分。”这一言论正值中韩关系紧张时期，特别是萨德事件引发了中韩矛盾。这一言论使得中国网友认为特朗普是在为中国说话，甚至有人认为他是潜伏在美国的“卧底”，因此开始称他为“川建国同志”‌，“川建国”这个称呼是中国网友对特朗普的一种调侃和讽刺，既表达了对他的不满，也反映了中美关系中的复杂局面。

特朗普再次当选的时候是78岁，一个快80的人都还在去美国各大州站台拉票，我们还有什么理由不努力呢？所以我们要在有限的生命里活出无限的可能，虽然不能被世人所记得，但也要勇敢追求梦想，实现自我价值。

好啦，话不多说开始今天的内容。在这个系列之前的文章中我有介绍在大多数情况优化器是会给出最优的执行计划，但并不是所有的情况，如果执行计划不是最优的，那么就需要人工干预SQL语句的执行计划，强制让执行计划走指定的访问路径， Oracle提供了多种干预执行计划的技术，包括DBMS_STATS、SQL profiles、SQL Plan Management、参数（优化器相关），和最后的hint方式（干预执行计划博客的直通车👉【Oracle篇】精细化查询优化：如何有效使用Hint对优化器的执行计划进行干预（第五篇，总共七篇）-CSDN博客👈），但使用这些工具是需要对技术有超高要求的，不懂统计信息、优化器、执行计划、访问路径等等技术基本上不太可能把SQL优化好的，所以为了让小白或者初级技术人员也可以做好慢SQL优化，Oracle贴心的给大家提供了SQL优化工具，没有复杂的参数，只需要简单学习掌握这个工具，就可以高效的完成SQL优化，那么让我们开始今天的内容。还是老规矩为了让大家更容易消化和逐个理解，我将分成七篇文章来进行介绍，以便大家劳逸结合而不至于感觉到阅读枯燥，七篇的内容分别如下：

第一篇：统计信息和动态采样的深度剖析
第二篇：全面理解优化器和SQL语句的解析步骤(含执行计划的详细分析和四种查看方式)
第三篇：SQL执行计划之访问路径(含表级别、B树索引、位图索引、簇表四大类访问路径)
第四篇：SQL执行计划之多表连接(含内连接、外连接、半连接、反连接、笛卡尔连接五种连接方式和嵌套、哈希、排序合并三种连接算法)（当前篇）
第五篇：精细化查询优化：如何有效使用Hint对优化器的执行计划进行干预
第六篇：掌握SQL Tuning Advisor优化工具：从工具使用到SQL优化的全方位指南（当前篇）
第七篇：SQL性能优化实战案例(从15秒优化到0.08秒)

一、 SQL Tuning Advisor(STA)介绍

1.1 STA的用途

1.2 STA体系架构

STA的输入

STA的输出

自动调整优化器分析

1.3 STA的使用

自动和按需SQL调优

本地和远程SQL调优

二、管理自动SQL调优任务

2.1 关于自动SQL调优任务

自动SQL调优概念

SQL优化顾问的命令行界面

自动SQL调优的基本任务

STA优化案例：

官方文档对STA的详细介绍（12c版本）：

Analyzing SQL with SQL Tuning Advisor

一、 SQL Tuning Advisor(STA)介绍

使用STA可以获得高负载SQL语句性能的建议，SQL优化顾问（STA）是Oracle数据库优化包中的SQL诊断软件。可以将一个或多个SQL语句作为输入提交给指导，并接收关于如何优化语句的建议或意见，以及基本原理和预期的好处。

1.1 STA的用途

SQL优化顾问是一种用于解决与SQL语句执行不理想相关的问题的机制。使用SQL优化顾问获取提高高负载SQL语句性能的建议，并通过仅执行最佳计划来防止回归。

调整建议包括：

对象统计数据的收集
创建索引
重写SQL语句
创建SQL配置文件
创建SQL计划基线

SQL优化顾问生成的建议可帮助您实现以下具体目标：

避免劳动密集型的手动调整：即使对于专家来说，识别和调整高负载SQL语句也是一项挑战。SQL优化顾问使用优化器为您优化SQL。
自动生成建议并实施SQL配置文件：可以将自动SQL调优任务配置为在维护窗口中每晚运行。当以这种方式调用时，顾问可以生成建议，还可以自动实现SQL配置文件。
分析数据库生成的统计数据，以实现最佳计划：该数据库包含有关其自身操作的大量统计数据。SQL优化顾问可以对内部信息进行深度挖掘和分析，以改进执行计划。
使开发人员能够在测试系统而不是生产系统上调整SQL：当生产数据库上出现性能不佳的SQL语句时，开发人员可能不想直接在生产数据库上进行调查和调优。DBA可以将有问题的SQL语句传输到测试数据库，开发人员可以在那里安全地分析和调整它们。

调整多个语句时，SQL优化顾问无法识别语句之间的相互依赖关系。相反，SQL调优顾问提供了一种方便的方法来获取许多语句的调优建议。

注意：将SQL Tuning Advisor与可插拔数据库一起使用时，数据可见性和权限要求可能会有所不同。顾问可以调整当前可插拔数据库（PDB）中的查询，以及执行此查询的其他PDB中的查询。通过这种方式，容器数据库（CDB）管理员可以同时调整多个PDB中的同一查询，而PDB管理员只能调整单个PDB。

1.2 STA体系架构

自动调优优化器（Automatic Tuning Optimizer）是SQL调优顾问（SQL Tuning Advisor）使用的核心工具。STA可以从多个来源接收SQL语句作为输入，使用优化器分析这些语句，然后提出建议。

为每次硬解析调用自动调优优化器会消耗大量时间和资源。调优模式适用于对数据库性能有重大影响的复杂和高负载SQL语句。

SQL调优顾问等可管理性顾问使用一个称为顾问框架的通用基础设施。该框架提供了一个用于存储任务对象的通用模式和接口。顾问模式是一组用于存储顾问数据的表。SQL优化顾问接收优化输入，然后通过顾问框架写入顾问架构。SQL优化顾问在生成报告时从顾问架构读取数据。

下图显示了SQL调优顾问的基本架构。

  此图显示了一个带有“SQL优化顾问（SQL Tuning Advisor）”标签的人的图标。在SQL优化顾问和一个标有“优化器（Optimizer）”的框之间有一个双向箭头。此框内有一个标记为“自动优化（Automatic Tuning Optimizer）”的框，优化器框旁边有一个计算器的图标。一个箭头从SQL Tuning Advisor指向右侧，指向一个标记为“建议（Recommendations）”的框。此框内有一个标签为“SQL配置文件的实现(仅自动)（Implementation of SQL Profiles (Automatic Only)）”的框。两个图标指向SQL优化顾问：一个人图标和一个标记为“AUTOTASK”的圆柱体。SQL优化顾问左侧是ADDM、AWR、共享池和SQL优化集框。共享池框包含一个库缓存框，其中包含一个共享SQL区域框，其中包括一个虚线框，其中含有语句“SELECT*FROM employees”。SQL调优集框包含一张标有“STS”的打开的纸板箱图标。





STA的输入

SQL调优顾问的输入可以来自多个来源，包括ADDM、AWR、共享SQL区域和SQL调优集。SQL优化顾问按如下方式使用其输入源：

自动数据库诊断监视器（ADDM）：SQL调优顾问的主要输入源是ADDM（发音为Adam）。默认情况下，ADDM每小时主动运行一次。为了识别涉及高负载SQL语句的性能问题，ADDM分析了自动工作负载存储库（AWR）在过去一小时内收集的关键统计数据。如果识别出高负载SQL语句，则ADDM建议在SQL上运行SQL优化顾问。
AWR：AWR定期对系统活动进行快照，包括按相关统计数据（如CPU消耗和等待时间）排名的高负载SQL语句。可以查看AWR并手动识别高负载SQL语句。您可以在这些语句上运行SQL优化顾问，尽管Oracle数据库会自动执行此工作，作为自动SQL优化的一部分。默认情况下，AWR保留过去八天的数据。您可以使用此技术定位和调整在AWR保留期内运行的任何高负载SQL。
共享SQL区域：数据库使用共享SQL区域来调整AWR中尚未捕获的最新SQL语句。共享SQL区域和AWR提供了从当前时间开始识别和调整高负载SQL语句的能力，最早可以追溯到AWR保留允许的时间，默认情况下至少为8天。
SQL调优集：SQL调优集（STS）是一个数据库对象，它存储SQL语句及其执行上下文。STS可以包含尚未部署的SQL语句，目的是衡量其各自的性能，或识别性能低于预期的语句。当一组SQL语句作为输入时，数据库必须首先构造和使用STS。



STA的输出

在分析完SQL语句后，SQL优化顾问会发布建议。具体来说，SQL调优顾问会生成以下类型的输出：

关于优化执行计划的建议
拟议优化的基本原理
预计性能效益
实现建议的SQL语句

每项建议的收益百分比使用以下公式计算：
abnf% = (time_old - time_new)/(time_old)
例如，假设在调整执行时间之前为100秒，在实施建议之后，新的执行时间预计为33秒。此性能改进的效益计算如下：
67% = (100 - 33)/(100)
可以选择是否接受建议以优化SQL语句。根据配置方式，自动SQL优化顾问可以实现SQL配置文件建议，在没有用户干预的情况下优化语句。当按需调用时，SQL调优顾问可以建议用户实现SQL配置文件，但永远不能自动实现。

自动调整优化器分析

在调优模式下，优化器有更多时间考虑选项并收集统计数据。例如，Automatic Tuning Optimizer可以使用动态统计和部分语句执行。下图描述了自动调整优化器（Automatic Tuning Optimizer）执行的不同类型的分析。

此图显示了SQL优化顾问图标。此图标和包含自动调整优化器框的优化器框之间有一个双向箭头。优化器框上附有计算器图标。自动调整优化器框有一个“调整模式（Tuning Mode）”标注。优化器框有一个“正常模式（Normal Mode）”标注。自动调优优化器框指向五个框：统计分析、访问路径分析、SQL结构分析、备选计划分析和SQL分析。

优化器依赖于对象统计信息来生成执行计划。如果这些统计数据过时或缺失，那么优化器可能会生成次优计划。自动调优优化器检查每个查询对象是否缺少或过时的统计信息，并在需要时建议收集新的统计信息。下图描述了统计分析的过程。

此图描绘了一个标记为“SQL优化顾问”的人的图标。一个包含文本“SELECT…”的框指向SQL优化顾问，后者又指向左侧的优化器框。优化器框包含一个自动调整优化器框。此框上附有计算器图标。“自动调整优化器”框指向书籍的“陈旧统计”图标。此图标直接指向一个包含文本“建议收集对象级统计信息”的框。自动调整优化器直接指向一本标有“缺席统计信息”字样的书的图标。这本书周围有一个带线的圆圈。每个图书图标和客户表图标之间都有一个双向箭头。

SQL分析是自动调优优化器对其自身估计的验证。通过查看执行历史记录和测试SQL，优化器可以确保它有最准确的信息来生成执行计划。SQL分析与生成SQL调优顾问建议和实现这些建议的步骤相关，但又不同。下图显示了SQL优化顾问推荐SQL配置文件并自动实现它。创建配置文件后，优化器可以在生成执行计划时将其用作额外输入。

此图描绘了两个单独的框。顶部是SQL优化顾问图标。一个显示SQL指向优化器（调优模式）框的页面。箭头标记为“提交”。优化器框旁边有一个计算器图标。该框指向一个显示“SQL配置文件”的页面。箭头标记为“创建”。箭头从SQL配置文件图标向下指向下框，下框包含一个标记为“优化器（正常模式）”的框。箭头标记的是“使用”。优化器框右指标记为“调优计划”的框，箭头标记的为“输出”。标记为“SQL”的页面右指优化器，标记为“无应用程序代码更改”。SQL页面左侧是一组标记为“数据库用户”的用户图标

1.3 STA的使用

可以自动或按需运行STA，还可以在本地或远程数据库上运行STA。

自动和按需SQL调优

将SQL优化顾问配置为使用DBMS_AUTO_SQLTUNE自动运行，或根据需要使用DBMS_SQLTUNE运行。调用方法不同如下：

自动：您可以将SQL Tuning Advisor配置为在夜间系统维护窗口期间运行。当由AUTOTASK运行时，该顾问被称为自动SQL调优顾问，并执行自动SQL调优。
按需：在按需SQL调优中，您可以在发现SQL相关的性能问题后手动调用SQL调优顾问来诊断和修复这些问题。Oracle Enterprise Manager云控制（Cloud Control）是按需调优SQL的首选接口，但您也可以使用DBMS_SQLTUNE PL/SQL包。

SQL调优顾问使用自动调优优化器来执行其分析。这种优化是“自动的”，因为优化器分析SQL而不是用户。不要将自动调优优化器与自动SQL调优混淆，在本文档中，自动SQL调优仅指由自动SQL调优任务执行的工作。

本地和远程SQL调优

SQL优化顾问可以分析本地数据库或远程数据库上的工作负载。在最简单的情况下，SQL调优顾问接受输入、执行并将结果存储在单个数据库中。本地模式适用于SQL调优顾问执行的性能开销可接受的数据库。

在远程调优中，启动调优任务的数据库与执行调优过程或存储结果的数据库不同。例如，备用数据库可以有自己的查询工作负载，其中一些可能需要调优。您可以在备用数据库上发出SQL Tuning Advisor语句。备用数据库到主数据库的链接使DBMS_SQLTUNE能够向主数据库写入数据和从主数据库读取数据。该链接是必要的，因为只读的备用数据库无法写入SQL优化数据。

下图说明了调整主数据库上的备用数据库工作负载的一般设置。此技术需要备用数据库到主数据库的链接。

要调整主数据库上的备用工作负载，请在DBMS_SQLTUNE过程中指定database_link_To参数。默认情况下，database_link_to参数为null，这意味着调优是本地的。

database_link_to参数必须指定私有数据库链接。此链接必须归SYS所有，并由默认特权用户SYS$UMF访问。以下示例语句创建了一个名为lnk_to_pri的链接：
CREATE DATABASE LINK lnk_to_pri CONNECT TO SYS$UMF IDENTIFIED BY password USING 'inst1';

二、管理自动SQL调优任务

当您的目标是主动识别SQL性能问题时，将SQL Tuning Advisor配置为自动化任务是一个简单的解决方案。该任务处理从AWR中选择的高负载SQL语句，这些语句符合调优候选条件。

2.1 关于自动SQL调优任务

默认情况下，自动SQL调优任务在夜间维护窗口中运行。配置自动SQL调优而不是手动调优可以降低成本并提高可管理性

许多DBA没有足够的时间进行SQL调优所需的深入分析。即使这样做，SQL调优也涉及几个手动步骤。因为在任何一天，几个不同的SQL语句都可能负载很高，DBA可能不得不花费大量精力来监视和调整它们。自动SQL调优任务不处理以下类型的SQL：

临时SQL语句或一周内不重复的SQL语句
并行查询
SQL分析后运行时间过长的查询，因此SQL优化顾问测试执行是不切实际的
递归SQL

您可以根据需要运行SQL优化顾问来优化前面类型的SQL语句。



自动SQL调优概念

Oracle Scheduler使用自动化维护任务基础架构（称为AutoTask）来安排任务自动运行。

默认情况下，自动SQL调优任务在夜间维护窗口中最多运行一小时。您可以自定义维护窗口的属性，包括开始和结束时间、频率和一周中的天数。





SQL优化顾问的命令行界面

在命令行上，您可以使用PL/SQL包来执行SQL调优任务。下表描述了最相关的软件包。

包描述
DBMS_AUTO_SQLTUNE 使您可以运行SQL优化指导、管理SQL概要文件、管理SQL优化集以及执行实时SQL性能监视。要使用此API，您必须拥有ADVISOR权限。
DBMS_AUTO_TASK_ADMIN 为自动任务提供接口。您可以使用此界面来启用和禁用自动SQL优化任务。

自动SQL调优的基本任务

本节介绍将SQL Tuning Advisor作为自动任务运行的基本任务。下图显示了基本工作流程。

STA优化案例：

生产SQL执行耗时31秒：

SQL>
select b.logtime, a.name, a.cardid, b.amount, b.goods, b.score
from itpux_member a,itpux_sales b
where a.region = '四川省'
    and a.recommend = '刘老师'
    and b.logtime > to_date('2016-12-15', 'yyyy-mm-dd')
and a.cardid = b.cardid
order by b.logtime;

（1）创建STA优化任务

SQL> 
grant  advisor  to  itpux;
  
DECLARE
    my_task_name  VARCHAR2(30);
    my_sqltext     CLOB;
BEGIN
    my_sqltext := 'select b.logtime, a.name, a.cardid, b.amount, b.goods, b.score
from itpux_member a,itpux_sales b
where a.region = ''四川省''
    and a.recommend = ''刘老师''
    and b.logtime > to_date(''2016-12-15'', ''yyyy-mm-dd'')
and a.cardid = b.cardid
order by b.logtime';       ###指定优化的sql语句。Sql中条件用' '两个单引，不能用'单引。不然报PLS-00103。并且sql后面不能有; ，不然在查看STA优化结果时报 ORA-00911: 无效字符
    my_task_name := DBMS_SQLTUNE.CREATE_TUNING_TASK(sql_text    => my_sqltext,
                                                    user_name  => 'ITPUX',
                                                    scope      =>'COMPREHENSIVE',
                                                    time_limit  => 60,
                                                    task_name  =>'tuning_sql01_itpux',
                                                    description => 'Task to tune a query on a specified table');
    DBMS_SQLTUNE.EXECUTE_TUNING_TASK(task_name =>'tuning_sql01_itpux'); 
END;
/

（2）检查STA优化任务状态

SQL> select * from dba_advisor_tasks where task_name = 'tuning_sql01_itpux';    ### task_name同上面的优化任务。COMPLETED为完成优化这个任务完成

（3）查看STA优化结果

SQL> select dbms_sqltune.report_tuning_task(task_name=>'tuning_sql01_itpux') from dual; ### task_name同上面的优化任务

GENERAL INFORMATION SECTION      ###-一般信息部分
##############################################################################-
Tuning Task Name   : tuning_sql40_itpux
Tuning Task Owner  : ITPUX
Workload Type      : Single SQL Statement
Scope              : COMPREHENSIVE
Time Limit(seconds): 60
Completion Status  : COMPLETED
Started at         : 11/17/2020 15:38:47
Completed at       : 11/17/2020 15:39:34

Schema Name: ITPUX
SQL ID     : 0cg9k5gkbhcjs
SQL Text   : select b.logtime, a.name, a.cardid, b.amount, b.goods, b.score
             from itpux_member a,itpux_sales b
             where a.region = '四川省'
                 and a.recommend = '刘老师'
                 and b.logtime > to_date('2016-12-15', 'yyyy-mm-dd')
             and a.cardid = b.cardid
             order by b.logtime

FINDINGS SECTION (1 finding)     ###调查结果（优化建议）

1- Index Finding (see explain plans section below)
################################################--
  通过创建一个或多个索引可以改进此语句的执行计划。
  Recommendation (estimated benefit: 98.95%)
  ##########################################
  - 考虑运行可以改进物理方案设计的访问指导或者创建推荐的索引。
    create index ITPUX.IDX$$_03520001 on ITPUX.ITPUX_MEMBER("REGION","RECOMMEND
    ","CARDID","NAME");
  Rationale
  #########
    创建推荐的索引可以显著地改进此语句的执行计划。但是, 使用典型的 SQL 工作量运行 "访问指导"
可能比单个语句更可取。通过这种方法可以获得全面的索引建议案, 包括计算索引维护的开销和附加的空间消耗。

EXPLAIN PLANS SECTION    ###解释执行计划部分
##############################################################################-

1- Original   ###原执行计划（原sql的执行计划）
#########--
Plan hash value: 1796535899

####################################################################################
| Id  | Operation                | Name          | Rows  | Bytes | Cost (%CPU)  |   Time   |
####################################################################################
|   0 | SELECT STATEMENT    |                |  921  | 61707 |  72113   (1)|  00:14:26 |
|   1 |  SORT ORDER BY      |                 |  921  | 61707 |  72113   (1)|  00:14:26 |
|*  2 |   HASH JOIN          |                 |  921  | 61707 |  72112   (1)|  00:14:26 |
|*  3 |    TABLE ACCESS FULL| ITPUX_SALES   |  921  | 28551 |   396   (1) |  00:00:05 |
|*  4 |    TABLE ACCESS FULL| ITPUX_MEMBER |  47619 | 1674K|  71715   (1)|  00:14:21 |
####################################################################################
Predicate Information (identified by operation id):
###################################################
 
   2 - access("A"."CARDID"="B"."CARDID")
   3 - filter("B"."LOGTIME">TO_DATE(' 2016-12-15 00:00:00', 'syyyy-mm-dd 
              hh24:mi:ss'))
   4 - filter("A"."REGION"='四川省' AND "A"."RECOMMEND"='刘老师')


2- Using New Indices   ###使用优化建议后的执行计划
##################--
Plan hash value: 632050092

####################################################################################--
| Id  | Operation              | Name           | Rows   |  Bytes | Cost (%CPU)|  Time   |
####################################################################################--
|   0 | SELECT STATEMENT    |                |   921  | 61707 |   750   (1)| 00:00:10 |
|   1 |  SORT ORDER BY      |                |   921  | 61707 |   750   (1)| 00:00:10 |
|*  2 |   HASH JOIN          |                |   921  | 61707 |   749   (1) | 00:00:09 |
|*  3 |    TABLE ACCESS FULL| ITPUX_SALES  |   921  | 28551 |   396   (1) | 00:00:05 |
|*  4 |    INDEX RANGE SCAN | IDX$$_03520001 |  47619 | 1674K|   353   (1) | 00:00:05 |
####################################################################################--
Predicate Information (identified by operation id):
###################################################
 
   2 - access("A"."CARDID"="B"."CARDID")
   3 - filter("B"."LOGTIME">TO_DATE(' 2016-12-15 00:00:00', 'syyyy-mm-dd 
              hh24:mi:ss'))
   4 - access("A"."REGION"='四川省' AND "A"."RECOMMEND"='刘老师')

（4）按照建议加索引

SQL> create index ITPUX.IDX$$_03520001 on ITPUX.ITPUX_MEMBER("REGION","RECOMMEND","CARDID","NAME");

（5）查询对比性能

SQL> 
select b.logtime, a.name, a.cardid, b.amount, b.goods, b.score
from itpux_member a,itpux_sales b
where a.region = '四川省'
    and a.recommend = '刘老师'
    and b.logtime > to_date('2016-12-15', 'yyyy-mm-dd')
and a.cardid = b.cardid
order by b.logtime;                  ###使用STA优化建议后查询时间为0.071秒，提高多倍

（6）删除STA优化任务

SQL>
begin
DBMS_SQLTUNE.drop_TUNING_TASK(task_name => 'tuning_sql01_itpux');
end;
/

呼！今天的内容到这里就结束了，又是一篇万字博客，这篇博客对于优化方向不那么了解的小伙伴来说是非常有帮助的，技术上的不足确实可以通过巧妙的工具使用来弥补，这也是技术进步和问题解决的一种重要途径😆 ，那么see you~