如何为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语句之前都会清除缓存数据。

如何在不修改源代码的情况下调优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/