如何调优 Oracle SQL系列文章：SQL性能方法论

如何调优 oracle sql系列文章 第二篇： SQL性能方法论。

2 SQL性能方法论

2.1 应用程序设计指南

获得良好SQL性能的关键是在设计应用程序时考虑性能。

2.1.1 数据建模指南

数据建模对于成功的应用程序设计非常重要。

你必须以根据实际的业务需求进行数据建模。在这个过程中，对于什么样的模型是正确的数据模型可能会出现不同的争议。重要的是将最大的建模工作应用于受最频繁的业务事务影响的实体。

在建模阶段，很有可能花费太多时间来建模非核心数据元素，这会导致开发前置时间的增加。使用建模工具可以快速生成模式定义，并且在需要快速原型时非常有用。

2.1.2 编写有效的应用指南

在系统开发的设计和架构阶段，确保应用程序开发人员了解SQL执行效率。
为实现此目标，开发环境必须支持以下特征：

• 良好的数据库连接管理
  连接到数据库是一项昂贵的操作，并且无法扩展。因此，最佳做法是最小化与数据库的并发连接数。一个简单的系统，用户在应用程序初始化时连接，是比较理想的。但是，在基于WEB或多层应用程序中，这种方法可能很困难。使用这些类型的应用程序，一般是使用数据库连接池，而不是为每个用户请求重新建立连接。

• 良好的游标使用和管理
  维护用户连接对于最小化系统上的解析活动同样重要。解析是解释SQL语句并为其创建执行计划的过程。此过程有许多阶段，包括语法检查，安全检查，执行计划生成以及将共享结构加载到共享池中。有两种类型的解析操作：

  首次提交SQL语句，并且在共享池中找不到匹配项。硬解析是资源最密集且不可扩展的，因为它们执行解析中涉及的所有操作。

  首次提交SQL语句，并在共享池中找到匹配项。匹配可以是其他用户先前执行的结果。共享SQL语句，这对性能来说是最佳的。但是，软解析并不是最理想的，因为它们仍然需要语法和安全检查，这会消耗系统资源。

• 软解析

• 硬解析

因为解析应该尽可能地最小化，所以应用程序开发人员应该设计他们的应用程序来解析一次SQL语句并多次执行它们。这是通过游标完成的。经验丰富的SQL程序员应该熟悉打开和重新执行游标的概念。

• 有效使用绑定变量

应用程序开发人员还必须确保在共享池中所共享SQL语句。为了实现这一目标，使用绑定变量来改造查询。如果不这样做，则SQL语句可能会被解析一次，并且永远不会被其他用户重用。要确保共享SQL，不要将字符串文字与SQL语句一起使用。例如：

带字符串文字的语句:

SELECT
 * 
FROM
 employees 
WHERE
 last_name 
LIKE 'KING';

绑定变量的语句：

SELECT
 * 
FROM
 employees 
WHERE
 last_name 
LIKE :1;

下面的例子展示了一个简单的OLTP应用程序的一些测试结果:

这些测试是在4颗CPU计算机上进行的。随着系统上CPU数量的增加，差异也会增加。

2.2 部署应用程序指南

要实现最佳性能，部署应用程序时要像设计应用程序时一样精心。

2.2.1 在测试环境中部署指南

测试过程主要包括功能测试和稳定性测试。在这个过程的某个时候，您必须执行性能测试。

以下列表描述了对应用程序进行性能测试的简单规则。如果记录正确，则此列表在应用程序上线后为生产应用程序和容量规划过程提供重要信息。

• 使用自动数据库诊断监视器（ADDM）和SQL Tuning Advisor进行设计验证。

• 使用实际数据量和分布进行测试。

所有测试必须使用完全填充的表完成。测试数据库应包含代表生产系统的数据，包括表之间的数据量和基数。应构建所有生产索引，并正确填充模式统计信息。

• 使用正确的优化程序模式。

使用您计划在生产中使用的优化程序模式执行所有测试。

• 测试单个用户性能。

在空闲或轻度使用的数据库上测试单个用户以获得可接受的性能。如果单个用户在理想条件下无法达到可接受的性能，则多个用户在实际条件下无法实现可接受的性能。

• 获取并记录所有SQL语句的计划。

获取每个SQL语句的执行计划。使用此过程验证优化器是否获得了最佳执行计划，并且可以根据CPU时间和物理I/O来理解SQL语句的相对成本。此过程有助于识别将来最需要调优和性能工作的大量事务。

• 尝试多用户测试。

此过程难以准确执行，因为用户工作负载和配置文件可能无法完全量化。但是，应测试执行DML语句的事务以确保不存在锁定冲突或序列化问题。

• 使用正确的硬件配置进行测试。

使用尽可能靠近生产系统的配置进行测试。使用真实的系统对于网络延迟，I/O子系统带宽以及处理器类型和速度尤为重要。如果不使用此方法，可能会导致对潜在性能问题的错误分析。

• 测量稳态性能。

在基准测试时，对稳态条件下的性能进行测量是非常重要的。每个基准测试运行都应该有一个上升阶段，在这个阶段，用户连接到应用程序，并逐渐开始对应用程序执行工作。这个过程允许将频繁缓存的数据初始化到缓存中，并在稳态条件之前完成单个执行操作(例如解析)。同样，在基准测试运行之后，有一段下降期也是有用的，这样系统就可以释放资源，用户就可以停止工作并断开连接。

2.2.2 应用程序部署指南

当新应用程序推出时，通常采用两种策略:Big Bang方法(即所有用户同时迁移到新系统)和Trickle方法(即用户缓慢地从现有系统迁移到新系统)。

这两种方法都有优点和缺点。 Big Bang方法依赖于以所需规模对应用程序进行可靠测试，但具有最小化数据转换和与旧系统同步的优势，因为它只是被关闭。 Trickle方法允许在工作负载增加时调试可伸缩性问题，但可能意味着必须在转换发生时将数据迁移到遗留系统和从遗留系统迁移。

很难推荐一种方法，而不选另一种方法，因为每种技术都存在相关风险，可能会在转换发生时导致系统中断。当然，Trickle方法允许在实际用户被引入新应用程序时对他们进行分析，并且允许在只影响迁移用户的情况下重新配置系统。这种方法会影响早期采用者，但限制了支持服务的负载。因此，计划外停机只影响一小部分用户。

关于如何推出新应用程序的决定是针对每个业务的。任何采用的方法都有其独特的压力和应力。您从测试过程中获得的测试和知识越多，就越能认识到什么时机最适合推出新应用。