发布于

如何使用查询重写插件在 MySQL 数据库中调整 SQL II？

MySQL 中的查询重写插件是一个组件，允许您在执行之前修改传入的 SQL 查询。它提供了根据特定需求转换、路由、过滤或扩展查询的功能。

如果您已安装了该插件，可以使用以下 SQL 语句来定义您的 SQL 替换规则和错误消息处理.

INSERT INTO query_rewrite.rewrite_rules (message, pattern, replacement)
VALUES(Unique_ID, Original_SQL, Rewrite_SQL);

MySQL 中的 query_rewrite.rewrite_rules 表存储了 Query Rewriter 插件用于重写 SQL 查询的规则。该表包含两列：

Pattern – 此列代表触发 SQL 查询重写的模式或条件。它定义了要匹配的特定查询或查询模式。

Replacement – 此列指定应该应用于匹配的查询或查询模式的替换或转换。

当执行 SQL 查询时，Query Rewriter 插件会检查 query_rewrite.rewrite_rules 表以查找匹配的模式。如果模式与执行的查询匹配，插件将使用相应的替换重写查询。这使您能够根据特定的模式或条件修改查询结构、优化查询或添加自定义逻辑。
以下是一个包含硬编码字面量的 SQL 语句示例，执行该 SQL 需要 2.1 秒，并附有以下查询计划。

SELECT   *
FROM     employee
WHERE    emp_id in (SELECT emp_id id
                    FROM   emp_subsidiary
                    where  emp_dept < ‘D’)
ORDER BY emp_id LIMIT 1;

以下屏幕显示了通过 Tosska DB Ace SQL 调优工具识别出的性能显著提升的替代 SQL 语句。由于注入 JOIN ORDER 提示，此优化后的 SQL 查询比原始 SQL 快了300多倍.

现在，我们需要将这个优化后的 SQL 与原始 SQL 一起部署到数据库中。然而，一个挑战出现在条件“emp_dept < 'D'”上，当使用一个带有不同硬编码字面量的 SQL 语句，比如“emp_dept < 'E'”时，它与 SQL 文本不匹配。因此，我们必须使用数值占位符Placeholder来替换硬编码字面量，将其替换为占位符“？”，如下所示。

INSERT INTO query_rewrite.rewrite_rules (message, pattern, replacement)
VALUES(Unique_ID, Original_SQL, Rewrite_SQL);

Original_SQL

SELECT   *
FROM     employee
WHERE    emp_id in (SELECT emp_id id
                    FROM   emp_subsidiary
                    where  emp_dept < ?)
ORDER BY emp_id LIMIT 1

Rewrite_SQL

select   /*+ QB_NAME(QB1) JOIN_ORDER(`employee`@QB1, `emp_subsidiary`@QB2) */ *
from     employee
where    emp_id in (select /*+ QB_NAME(QB2) */ emp_id id
                    from   emp_subsidiary
                    where  emp_dept < ?)
order by emp_id limit 1

Tosska DB Ace for MySQL通过自动化发现优化SQL替代方案和部署具有数值占位符替换的SQL，简化了这一过程。

查询重写插件功能强大且易于使用。最具挑战性的方面是为性能较差的SQL语句找到替代SQL。Tosska DB Ace Enterprise for MySQL可以帮助您自动化这一过程，从识别性能较差的SQL语句到重写SQL语法并部署替代规则。

Tosska DB Ace Enterprise for MySQL – Tosska Technologies Limited

DBAM Tune Rewriter demo – YouTube

发布于

如何使用MySQL数据库中的Query Rewriter插件来调优SQL？

MySQL中的Query Rewriter插件是一个组件，允许您在执行SQL之前修改传入的SQL查询。它提供了根据特定需求转换、路由、过滤或扩展查询的能力。该插件在SQL层操作，并可用于优化查询性能、强制执行安全策略、实施数据分区策略或向查询添加附加业务逻辑。通过Query Rewriter插件，您可以自定义和塑造SQL查询，以满足特定需求，在MySQL服务器内灵活控制查询执行。

Query Rewriter查询转换功能使您能够将原始查询重写或转换为等效或更高效的形式。这对于优化性能、简化复杂查询或强制使用特定查询计划非常有用。

在使用此功能之前，您必须安装Query Rewriter插件。Query Rewriter的概念很简单，它是一组预定义的SQL语句，用于替换从应用程序程序触发的特定SQL语句模式。

如果您已安装了该插件，以下SQL语句可用于定义您的SQL替换规则和错误消息处理。

INSERT INTO query_rewrite.rewrite_rules (message, pattern, replacement)
VALUES(Unique_ID, Original_SQL, Rewrite_SQL);

在MySQL中，query_rewrite.rewrite_rules表存储了Query Rewriter插件用于重写SQL查询的规则。该表具有两列：
Pattern – 此列表示触发SQL查询重写的模式或条件。它定义了要匹配的特定查询或查询模式。

Replacement – 此列指定应应用于匹配的查询或查询模式的替换或转换。

当执行SQL查询时，Query Rewriter插件会检查query_rewrite.rewrite_rules表以查找匹配的模式。如果某个模式与执行的查询匹配，插件将使用相应的替换来重写查询。这使您能够根据特定的模式或条件修改查询结构、优化查询或添加自定义逻辑。
我利用message列来定义SQL替换规则的临时唯一标识，这样可以使用以下SQL提取实际的规则ID。

SELECT id into :SID FROM query_rewrite.rewrite_rules where message= Unique_ID;

当您对query_rewrite.rewrite_rules表中的查询重写规则进行更改时，这些更改不会立即生效。相反，MySQL会将规则缓存在内存中以提高性能。然而，如果您希望确保更新后的规则立即生效，可以调用query_rewrite.flush_rewrite_rules()函数。

CALL query_rewrite.flush_rewrite_rules();

如果发生加载错误，插件还会将Rewriter_reload_error状态变量设置为ON，并将错误消息存储在Message列中。

SELECT message FROM query_rewrite.rewrite_rules where id=:SID;

实际上，Query Rewriter插件功能强大且易于使用。最具挑战性的方面是为性能不佳的SQL语句找到替代的SQL语句。Tosska DB Ace Enterprise for MySQL可以帮助您自动化这个过程，从识别性能不佳的SQL语句到重写SQL语法和部署替代规则。

Tosska DB Ace Enterprise for MySQL – Tosska Technologies Limited

DBAM Tune Rewriter demo – YouTube

发布于

如何使用计划指南调优 SQL Server 中的临时 SQL？

在 MS SQL Server 中使用计划指南来调优第三方应用程序的 SQL 可以是一种有用的技术，当您需要优化应用程序生成的特定查询或查询集的性能，而无需更改应用程序源代码时。
以下是在 MS SQL Server 中使用计划指南来调优第三方应用程序的 SQL 的步骤，而无需更改源代码：

  • 鉴定导致应用程序性能问题的查询或查询集。您可以使用 SQL Server Profiler 或扩展事件来捕获和分析应用程序生成的 SQL 语句。
  • 创建计划指南，为已鉴定的查询或查询集提供优化的执行计划。这可以涉及修改查询文本或提供查询提示以影响优化器的决策。
  • 测试计划指南，确保它提供了期望的性能改进，并且不会引起任何意外的副作用。
  • 将计划指南部署到生产环境，并监控应用程序的性能，确保计划指南被使用，并且提供了期望的性能改进。

在优化应用程序源代码中的临时 SQL 语句之前，了解 SQL 语句如何与计划指南中指定的语句匹配是至关重要的，包括空格和注释。此外，还需要匹配 SQL 语句的来源。

以下是用于创建计划指南的系统存储过程：

今天的重点将放在使用计划指南来调优临时 SQL (@type = N’SQL’) 上。SQL 有两种类型：独立的 SQL (@module_or_batch = NULL) 和批处理文本中的 SQL (@module_or_batch = N’batch_text’)。例如，如果一个应用程序发送了以下 SQL，并且它独立执行而没有其他代码，那么它属于独立的 SQL。
select top 10 * from employee;
下面的示例展示了一个批处理文本，其中包含了上述列出的 SQL 语句之一，需要通过计划指南进行优化。这个 SQL 语句位于批处理文本的中间。由于相同的 SQL 语句可能来自批处理文本，我们必须通过使用变量 @module_or_batch = N’batch_text’ 来指定具体的批处理文本。因此，必须为同一个 SQL 语句创建两个计划指南，一个用于临时 SQL，一个用于批处理文本。为了准确地确定临时 SQL 的来源，建议使用 SQL Profiler 来捕获需要通过计划指南进行优化的 SQL 语句。

select count(*) from employee;
select top 10 * from  employee;
where emp_id in (select emp_id id
                             from emp_subsidiary
                             where emp_dept<‘h’)
order by emp_name;

Microsoft SQL Server Management Studio提供了一个有用的工具，可以帮助用户创建计划指南，而无需手动执行系统存储过程。然而，了解被优化的SQL语句的类型以及需要输入的相应参数的含义是至关重要的。

尽管对于初学者来说，创建计划指南的步骤可能看起来很复杂，但它们对于在不修改源代码或没有修改权限的情况下改善SQL性能是值得的。然而，最具挑战性和耗时的方面是找到SQL语句的最佳查询提示（@hints = N’OPTION(query_hint [ ,…n ])）。除非您对SQL调优技术有深入的了解并且有足够的时间进行实验，否则您可能需要一个能够从捕获SQL、识别SQL来源类型、自动调优查询提示并便于部署计划指南的产品来简化这个过程。

Tosska DB Ace Enterprise for SQL Server – Tosska Technologies Limited
DBAS Tune SQL PG Standalone – YouTube
DBAS Tune SQL PG Batch – YouTube

发布于

如何使用80/20法则来调优数据库应用程序 II ？

之前的文章“如何使用80/20法则来调优数据库应用程序 I”演示了如何应用80/20法则来评估数据库中SQL工作负载的整体性能。在本例中，展示了从Oracle SGA检索到的一组90个SQL语句的图表，每个语句按照其资源使用情况以降序排列列出，最具资源密集型的SQL在左侧。分析显示，大约14.44％的SQL语句占用了80％的总经过时间，而21.11％的SQL语句占用了80％的总CPU时间，表明SQL工作负载分布符合80/20法则。因此，调整SQL可能并不必要，因为这不太可能带来显著的性能改进。

然而，为了更加成本有效地进一步优化数据库性能，建议对高工作量SQL语句的前20％进行深入调查。这将揭示资源利用在前几个SQL语句中急剧下降，使它们成为优化的最关键候选项。


让我们将目标的总资源消耗比例从80％降低到60％，并检查负责利用这些资源的SQL语句。结果很有趣，显示出3个SQL语句占用了60％的经过时间，6个SQL语句占用了60％的CPU时间，而仅有一个SQL语句占用了60％的磁盘读取。通过专注于这些SQL语句，可以提高数据库工作负载高达60％。例如，如果数据库遇到IO瓶颈，专注于一个SQL语句可以节省高达60％的磁盘读取。




您可以利用Excel来进行上述80/20法则分析的模拟，提供SQL工作负载分布的全面概述。这种方法有助于快速评估数据库SQL性能的整体状况，以及优化高负载SQL语句的成本和效益。更进一步的SQL资源频谱分析已集成到我们的Tosska DB Ace for Oracle软件中。

Tosska DB Ace Enterprise for Oracle – Tosska Technologies Limited

DBAO Inspect SQL – YouTube

发布于

如何为SQL Server调优子查询中带有OR条件的SQL语句？

下面示例显示带有EXISTS子查询的SQL语句。如果在DEPARTMENT表的子查询中满足OR条件，SQL会对EMPLOYEE表中的记录进行计数。

select countn(*) from employee a where
exists (select ‘x’ from department b
   where a.emp_id=b.dpt_manager or a.emp_salary=b.dpt_avg_salary
    )

下面是Tosska专有的树结构执行计划，它需要4分29秒才能完成。

该执行计划显示了从EMPLOYEE到全表扫描DEPARTMENT表的嵌套循环，这是整个执行计划的主要问题。因为 SQL Server 无法通过其他连接操作来解决这个OR条件“a.emp_id=b.dpt_manager or a.emp_salary=b.dpt_avg_salary”。

下面我将子查询中的OR条件改写为UNION ALL子查询，子查询中UNION ALL的第一部分表示“a.emp_id=b.dpt_manager”条件，第二部分表示“a.emp_salary=b.dpt_avg_salary”条件，但排除已经满足第一个条件的数据。

select count(*)
from  employee a
where exists ( select ‘x’
        from   department b
        where a.emp_id = b.dpt_manager
        union all
        select  ‘x’
        from   department b
        where ( not ( a.emp_id = b.dpt_manager )
           or b.dpt_manager is null )
           and a.emp_salary = b.dpt_avg_salary )

下面是改写后SQL的执行计划，看起来有点复杂，但现在性能很好，只需要0.447秒。有两个散列匹配连接用于替换原来从EMPLOYEE表到全表扫描DEPARTMENT表的嵌套循环。

虽然最终改写的步骤有点复杂，但这种改写可以由Tosska SQL Tuning Expert for SQL Server自动完成，这表明该改写SQL比原始SQL快了600多倍。

Tosska SQL Tuning Expert (TSES™) for SQL Server® – Tosska Technologies Limited

发布于

如何让SQL语句运行得更慢…但让用户的体验更好(Oracle)？

您的最终用户可能会不断抱怨他们的数据库应用程序的某些功能运行缓慢，但您可能会发现，在当前的Oracle和硬件配置中，这些SQL语句已经达到了它们的最大速度。除非您愿意升级硬件，否则可能没有办法改进SQL。有时，为了让用户感觉更好，您不必调优SQL以使其运行得更快，而只需调优SQL以使某些应用程序的SQL语句运行得更慢。

这是一个示例，该SQL用于显示表Emp_sal_hist和表Employee在满足某些条件后的信息。该SQL是在线查询执行的，用户必须等待至少5秒，才能在单击鼠标后在屏幕上显示任何数据。

select * from employee a,emp_sal_hist c
where a.emp_name like ‘A%’
 and a.emp_id=c.sal_emp_id
 and c.sal_salary<1800000
order by c.sal_emp_id

下面是SQL的执行计划和执行统计，提取全部79374条记录花费了10.41秒，第一条记录返回的反应时间为5.72秒。该查询显示了EMPLOYEE和EMP_SAL_HIST表的MERGE JOIN，在将其合并到最终结果之前，对两个相对应的表进行排序操作。这就是用户必须等待至少5秒才能看到屏幕上显示的内容的原因。

作为条件“a.emp_id = c.sal_emp_id”，我们知道“ORDER BY c.sal_emp_id”和“ORDER BY a.emp_id”是等效的，因为SQL语法改写不能强制对该SQL的执行计划进行指定的操作，所以我添加了一个优化器提示/*+ INDEX(@SEL$1 A EMPLOYEE_PK) */来减少“order by a.emp_id”的排序时间。

SELECT   /*+ INDEX(@SEL$1 A EMPLOYEE_PK) */ *
FROM   employee a,
     emp_sal_hist c
WHERE  a.emp_name LIKE ‘A%’
   AND a.emp_id = c.sal_emp_id
   AND c.sal_salary < 1800000
ORDER BY c.sal_emp_id

虽然在新的执行计划中，总体运行时间增加了3秒，但响应时间从5.72秒减少到1.16秒，因此用户可以更快的看到屏幕上第一页信息。我相信大多数用户不在乎是否还有3秒可以返回所有79374条记录。这就是为什么在管理用户期望时，SQL调优更像一门艺术而不是科学。
这种改写可以由Tosska SQL Tuning Expert Pro for Oracle自动实现。

https://www.tosska.cn/tosska-sql-tuning-expert-pro-tse-pro-for-oracle-zh/

发布于

如何调优SQL Server性能不好并且设置返回行数的SQL语句？

在设置返回行数或者使用Top关键字后，某些SQL语句会运行的非常慢。设置返回行数和使用Top关键字会告诉SQL Server从SQL语句中选择特定行数而不是提取所有记录。没有多少人知道在设置返回行数或者使用Top关键字后SQL Server会尝试重新优化您的SQL语句，重新优化后的SQL语句产生的执行计划通常可以更快的检索前几条记录，但是也可能适得其反。

设置返回行数重新优化查询的好示例
下面是一个示例，显示了该SQL从数据库中检索217500行记录需要6.78秒。这是一个好的执行计划，因为它对[DEPARTMENT]和[EMPLOYEE]的两个表扫描进行哈希匹配。

下面屏幕显示了设置返回行数为1后新的执行计划把哈希匹配改为嵌套循环。嵌套循环操作通常提供更快的前几条记录检索时间，但在某些情况下可能不利于整体记录的提取。很高兴看到SQL Server提取此SQL的第一行记录仅用了0.013秒。

设置返回行数重新优化查询的坏示例
让我们来看一个坏示例，它展示了在使用设置返回行数为1后，SQL Server如何将一个好的执行计划变成一个糟糕的执行计划。下面一个示例显示，该SQL从数据库中检索1613行记录花费了0.118秒，虽然该执行计划有点复杂，但对于检索完所有行来说是一个很好的执行计划。

下面屏幕显示了设置返回行数为1后生成的新执行计划，该执行计划现在更改为带有两次表扫描的嵌套循环。新的执行计划提取第一条记录需要1.312秒，比从数据库提取所有行所用的0.118秒还要慢。

如何解决这个问题呢？
我们可以使用提示注入或者SQL语法改写来影响SQL Server为设置返回行数或者Top关键字操作改回原始的执行计划或者生成更好的执行计划。下面是提示注入生成的一个很好的执行计划，它比设置返回行数为1的原始SQL快了90倍。

Tosska SQL Tuning Expert (TSES™) for SQL Server® – Tosska Technologies Limited

发布于

如何调优SQL Server的冷缓存SQL语句？

对于不经常执行的SQL语句，相关的数据可能不在缓存区，冷缓存会显著的影响这类SQL语句的性能。 用于热缓存的高性能SQL语句在冷缓存环境中可能表现的不好。经验丰富的开发人员会调优他们的SQL, 使其在两种情况下都能得到良好运行。

下面是一个SQL示例：
select * from
EMPLOYEE A
 where A.EMP_ID IN (SELECT B.EMP_ID from EMP_SUBSIDIARY B
     where B.EMP_DEPT < ‘D’)

下面是Tosska的树结构执行计划，第一次执行因为缓存延迟需要8.024秒，第二次执行无需缓存延迟只需要3.7秒。

根据执行计划，您可能发现IO消耗最多的是[EMPLOYEE]表的表扫描。为了模拟冷缓存环境，我们可以在每次执行SQL语句前使用DBCC DROPCLEANBUFFERS命令来清除数据缓存。

让我为这条SQL添加OPTION(LOOP JOIN)提示,并尝试将执行计划的哈希匹配更改为嵌套循环连接。因此，将使用[EMPLOYEE]的EMP_ID(EMPLOYEE_PK)和RID查找，而不是使用表扫描。我希望RID查找可以从硬盘中选择较少的数据，同时在[EMPLOYEE]和[EMP_SUBSIDIARY]中匹配EMP_ID。

select *
from   EMPLOYEE A
where  A.EMP_ID in (select  B.EMP_ID
          from   EMP_SUBSIDIARY B
          where  B.EMP_DEPT < ‘D’) OPTION(LOOP JOIN)

根据下面的执行计划，携带数据缓存开销的执行时间从8.024秒减少到1.565秒，物理读也从190,621减少到39,044。如果您用SQL Server的EstimateIO乘以EstimiateExecutions来得到IO估计值，这是错误的。

下面的人工智能调优工具还提供了其它更好的调优解决方案：

Tosska SQL Tuning Expert (TSES™) for SQL Server® – Tosska Technologies Limited

下面带有提示的SQL语句生成一个更复杂的执行计划，最好的执行时间为0.7秒。该SQL是在冷缓存下进行调优的，每次执行SQL语句之前都会清除缓存数据。

发布于

如何调优SQL Server数据库包含EXISTS子查询的SQL语句（二）？

在我上一篇文章中，带有Exists子查询的SQL语句通过以下改写执行速度快了90倍。

SELECT *
  FROM DEPARTMENT
   where exists (select ‘x’
        from employee
        where emp_id > 2700000
        and emp_dept=DPT_ID)

执行计划：

改写的SQL语句：

select *
from   DEPARTMENT
where DPT_ID in (select  isnull(emp_dept,emp_dept)
         from    employee
         where  emp_id > 2700000)
         group by emp_dept)
执行计划：

语法改写的解决方案
DBA或者开发人员通常使用语法改写技术来改进SQL语句，尤其是对于Oracle或者MySQL数据库，但对于MS SQL Server或者IBM Db2 LUW的用户来说语法改写并不容易使用。原因是MS SQL Server和IBM Db2 LUW在它们的SQL优化器中有一个强大的内部改写引擎。这个内部SQL改写引擎会尝试将SQL语法改写为它们的内部规范语法。这意味着，无论您如何改写您的SQL语句，MS SQL Server或者IBM Db2 LUW都会尝试将SQL重新改写回它们内部假定的最优语法。所以如果假定的最优语法不好，就很难调优SQL。因为用户不容易通过简单的SQL改写来影响数据库SQL优化器生成更好的执行计划。

查询提示解决方案
为了解决这个问题，SQL Server为用户提供了查询提示功能，以帮助它的SQL优化器生成更好的执行计划。 它不像SQL改写方法，经验丰富的开发人员可能会说出改写SQL的最终执行计划是什么。查询提示是一个精确的解决方案，它通常应用于整个执行计划中的特定步骤，但对特定步骤的更改将会对整个执行计划的其他步骤产生多米诺效应。因此MS SQL Server必须调整其它步骤以实现用户对SQL语句中查询提示的预期。所以最终的执行计划并不容易被用户预测，尤其是对复杂的SQL语句。

以下由Tosska SQL Tuning Expert生成带提示的SQL比原来的SQL快了4倍左右，耗时0.639秒。

select  *
from    DEPARTMENT
where    exists (select  ‘x’
        from   employee
        where emp_id > 2700000
           and emp_dept = DPT_ID) OPTION(LOOP JOIN,HASH GROUP)

还有一个带提示的SQL,它比原始的SQL快了50倍左右，只需0.055秒。这个执行计划非常接近我上一篇文章中的改写调优。

select  *
from    DEPARTMENT
where    exists (select  ‘x’
        from   employee WITH(INDEX(EMPS_DPT_INX))
        where emp_id > 2700000
           and emp_dept = DPT_ID)

语法改写加提示的解决方案
对于一些SQL语句，语法改写或者提示方法可能无法单独解决一个复杂的SQL性能问题，有些人可能会想，是否有可能同时改写和应用提示来改进一条SQL语句？是的，这在Tosska SQL Tuning Expert人工智能引擎中是可能的，这种技术可以解决更多的SQL性能问题。稍后我将在我的博客中讨论这项技术。

Tosska SQL Tuning Expert (TSES™) for SQL Server® – Tosska Technologies Limited

下面显示Tosska SQL Tuning Expert在考察300种加提示的SQL写法后挑选出178条等价SQL，这远远超出了一个专家在10分钟内所能达到的水平。MS SQL Server是市场上对提示注入最敏感的数据库，SQL Server的查询提示通常能够影响SQL优化器生成特定的执行计划，因此MS SQL Server的SQL调优远比其他数据库更具挑战性。

发布于

如何在不修改源代码的情况下调优Oracle应用程序包的SQL

介绍

应用程序包软件是为授权给第三方组织而开发的软件程序的集合。虽然软件包软件可以通过参数或表来满足用户的特定需求，但软件本身并不是专门为组织开发的。因此，用户不能拥有源代码，也没有方法修改嵌入式SQL语句来进行性能调优。有很多基于Oracle RDBMS的应用程序包，如Siebel、PeopleSoft JD Edwards、SAP等。为了帮助应用程序包的用户，Oracle提供了一些特性来帮助用户调优SQL语句，且不需要更改源代码。

SQL profile

它是由Oracle SQL Tuning Advisor生成的概要文件。SQL概要文件包含对错误估计的统计信息和辅助信息的更正。因此，SQL profile只是将优化器引导到一个更好的计划，但它们并不保证每次解析语句时都使用相同的计划。对于某些SQL语句，无论统计数据有多好，Oracle SQL optimizer仍然无法在特定的环境中生成更好的计划。对于这些类型的SQL语句，人工干预是必要的，但是对于开发人员来说，SQL profile不是一个方便的工具，在不更改程序源代码的情况下，不能强迫Oracle执行新的计划。

SQL plan baselines and stored outlines

由于Oracle环境的变化或Oracle数据库版本升级，它可能会针对Oracle SQL优化器为某些SQL语句生成新的计划。如果它不好，我们需要一些东西来保存旧的计划，以适应新的环境。为了实现SQL计划的稳定性，stored outlines是Oracle数据库早期版本中的主要工具。Oracle数据库11g仍然支持该特性;但是，在将来的版本中，它可能会贬值，并被SQL计划管理所替代。SQL Plan Baselines的机制是保持指定SQL语句的性能，而不管数据库环境的变化或版本升级。此外，还可以手动为SQL语句创建Plan Baselines，并且该技术可以帮助开发人员指导Oracle SQL优化器为性能较差的SQL语句生成特定的计划。因此，当Oracle SQL optimizer下次接收到相同的SQL语句时，将根据数据库中存储的新计划基线组成更好的性能计划。不需要更改源程序中的SQL语句。
例如，如果您想要调优一条执行plan-A计划的SQL(目前Oracle SQL optimizer在您的数据库中使用这条SQL)，并且您想要调优这条SQL(带有提示)，使Oracle SQL optimizer生成一个新的计划plan-B。你需要做的是用执行调优后的SQL（带新的提示），并使用Oracle提供的以下方法:

使用优化后带提示的SQL执行，并在SGA中缓存plan B。

SET SERVEROUTPUT ON
DECLARE
    My_Plan  PLS_INTEGER;
BEGIN
     My_Plan := DBMS_SPM.load_plans_from_cursor_cache(
              sql_id          => 'Plan-B SQL_ID',
              plan_hash_value => 'Plan-B plan_hash_value’,
              sql_handle      => 'Original SQL’s sql_handle');
      DBMS_OUTPUT.put_line('Plan Loaded=> ' || My_plan);
END;

要启用调优计划，手动将调优后的计划更改为固定计划，方法是将其FIXED属性设置为YES。
要启用SQL plan baselines，请确保optimizer_use_sql_plan_baslines初始化参数设置为TRUE。

使用SQL Plan baselines进行SQL调优的缺点

由于SQL Plan baselines的设计目的是为了在以下环境发生变化后保持SQL语句的性能:

  • 新的优化器版本
  • 更改优化器统计信息和优化器参数
  • 更改schema和元数据定义
  • 更改系统设置
  • SQL profile 创建

您可以看到，它不是为手动SQL调优而设计的。还有一些额外的限制，比如SQL Plan Baselines不支持Parallel Hints，您不能为你的SQL加载一个应用Parallel Hints的Plan-B，而原始计划Plan-A的性能很差。 有时候, Parallel Hints 在Oracle SQL optimizer中往往能生成更好的计划。

SQL Patches

SQL Patches是SQL Repair Advisor提供的特性之一，用于修复SQL语句的关键故障，比如返回错误的结果。 SQL Repair Advisor分析有问题的语句，并在许多情况下建议使用SQL patch来修复该语句。SQL patch将影响Oracle SQL优化器为将来的执行选择一个替代的执行计划，而不是使用原来有问题的执行计划。在Oracle Database 12c Release 2之后,提供了一个公共API来手动创建SQL patch。DBMS_SQLDIAG.CREATE_SQL_PATCH包可以帮助用户为特定的SQL语句创建用于SQL调优的SQL patch。你可以改变一个性能不好的SQL语句的执行计划，而不需要修改程序源代码, 如下:

DECLARE
    Patch_name  VARCHAR2(32767);
BEGIN
    Patch_name := SYS.DBMS_SQLDIAG.create_sql_patch(
        sql_text  => 'SELECT *
                      FROM   employees
                      WHERE  emps_dept IN
                     (SELECT dpts_id
                      FROM   departments
                      WHERE  dpts_avg_salary <200000)', hint_text => 'INDEX(@SEL$1 EMPLOYEES) INDEX(@SEL$2 DEPARTMENTS)',
        name      => 'my_sql_patch_name');
END;

如果数据库版本在Oracle database 12c Release 2之前，则必须使用DBMS_SQLDIAG_INTERNAL.i_create_patch包代替。SQL文本和SQL ID都可以用于SQL提示注入。为SQL注入的提示应该放在hint_text输入参数中。只有一行 Hints 文本可以用于SQL，而且无法为任何子查询块定义自己的查询块名称。因此，如果您的SQL有多个子查询，并且希望指示Oracle在子查询的块中执行某些操作，则必须在注入的 Hints 文本中使用Oracle默认的查询块名称。
hint_text => ‘ INDEX(@SEL$1 EMPLOYEES) INDEX(@SEL$2 DEPARTMENTS) ‘
上面示例中的提示文本显示@SEL$1和@SEL$2是Oracle在SQL执行计划中提供的默认查询块名称。提示告诉Oracle使用索引搜索查询块@SEL$1中的EMPLOYEES表，同时使用索引搜索查询块@SEL$2中的DEPARTMENTS表。

使用SQL Patches来调优SQL的优缺点

没有SQL Plan Baselines的限制，SQL patch更灵活地接受提示指令，带parallel操作的复杂提示通常能被SQL patch接受。在创建 SQL Patches之后，不需要额外的维护工作来告诉Oracle使用 SQL Patches。 Oracle将使用存储的提示优化任何具有相同SQL ID或SQL文本的SQL，并生成更好的性能执行计划。此外,您还可以使用 SQL Patches 来禁用已在包应用程序中写入的破坏性提示的 SQL,甚至使用它来控制一个具有绑定感知的SQL的执行行为。
由于注入的提示文本必须放在一个文本行中，并且只使用默认的查询块名称，因此手工编写一个预期的提示来改进SQL语句，对于大多数SQL开发人员来说是一项困难的任务，尤其是对于包含许多子查询的复杂SQL语句。

一个自动创建Hints和SQL Patches的工具

到目前为止，市场上只有一个工具能够生成更好的提示且完全自动化的方式创建SQL Patch。
Tosska SQL Tuning Expert Pro 是一个用户可以在不接触其程序源代码的情况下提高 SQL 性能的工具。用户可以为不同大小的生产数据库部署不同的性能查询计划，而不需要保存程序源代码的多个版本，并且特别适合于不拥有其应用程序源代码的包应用程序用户。该工具将尝试最有用的提示组合来调优性能较差的SQL语句，最好的提示组合SQL性能将与原始SQL并排进行基准测试。用户将得到准确的性能改进，没有任何猜测或不确定的成本评估。

您可以访问我们的网站了解产品详情
https://www.tosska.cn/tosska-sql-tuning-expert-pro-tse-pro-for-oracle-zh/