共计 6422 个字符，预计需要花费 17 分钟才能阅读完成。
本篇内容介绍了“怎么调优 Oracle SQL”的有关知识，在实际案例的操作过程中，不少人都会遇到这样的困境，接下来就让丸趣 TV 小编带领大家学习一下如何处理这些情况吧！希望大家仔细阅读，能够学有所成！
1 SQL 调优简介
SQL 调优是尝试诊断和修复不符合性能标准的 SQL 语句。
1.1 关于 SQL 调优
SQL 调优是提高 SQL 语句性能以满足特定、可衡量和可实现目标的迭代过程。
SQL 调优意味着修复已部署正在运行的应用程序中的问题。
1.2 SQL 调优的目的
当 SQL 语句无法按照预定和可测量的标准执行时，它就成为一个问题。
确定问题后，典型的调优会话具有以下目标之一：
减少用户响应时间，减少用户发出语句和收到响应之间的时间
提高吞吐量，处理语句访问所有行需要的最少量资源
1.3 SQL 调优的前提
SQL 性能调优需要数据库知识的基础。
假定您具有下表中显示的知识和技能。
表 1 -1 所需知识
所需知识说明数据库架构数据库体系结构不仅仅是管理员所要了解的内容。作为开发人员，您希望在最少的时间内针对 Oracle 数据库开发应用程序，这需要利用数据库体系结构和特性。例如，不理解 Oracle 数据库并发控制和多版本读取一致性，可能会使应用程序破坏数据的完整性，运行缓慢并降低可扩展性。SQL 和 PL/SQL 由于存在基于 GUI 的工具，因此可以在不知道 SQL 的情况下创建应用程序和管理数据库。但是，如果不了解 SQL，就无法调整应用程序或数据库。SQL 调优工具数据库生成性能统计信息，并提供解释这些统计信息的 SQL 调优工具。1.4 SQL 调优的任务和工具
在确定调优会话的目标后，例如，将用户响应时间从三分钟缩短到不到一秒，问题就变成了如何实现此目标。
1.4.1 SQL 调优任务
调优会话的细节取决于许多因素，包括您是主动调优还是被动调优。
在主动 SQL 调优中，您经常使用 SQL Tuning Advisor 来确定是否可以使 SQL 语句更好地执行。在被动 SQL 调优中，您可以更正用户遇到的与 SQL 相关的问题。
无论您是主动，还是被动地进行调优，典型的 SQL 调优会话都涉及以下所有或大部分任务：
1. 识别高负载 SQL 语句
查看过去的执行历史记录，以查找负责大量应用程序工作负载和系统资源的语句。
2. 收集与性能相关的数据
优化程序统计信息对 SQL 调优至关重要。如果这些统计信息不存在或不再准确，则优化程序无法生成最佳执行计划。与 SQL 性能相关的其他数据包括语句访问的表和视图的结构，以及语句可用的索引的定义。
3. 确定问题的原因
通常，SQL 性能问题的原因包括：
设计效率低下的 SQL 语句
如果编写 SQL 语句以便执行不必要的工作，那么优化器无法提高其性能。低效设计的例子包括:
忽略添加 Join 条件，这会导致笛卡尔连接
使用 hint 将大表指定为连接中的驱动表
指定 UNION 而不是 UNION ALL
为外部查询中的每一行执行子查询
次优的执行计划
查询优化器（也称为优化器）是内部软件，用于确定哪个执行计划最有效。有时，优化器会选择具有次优访问路径的计划，这是数据库从数据库中检索数据的方法。例如，具有低选择性的查询谓词的计划，可以在大表而不是索引上使用全表扫描。
您可以将执行最佳 SQL 语句的执行计划与次优的计划进行比较。这种比较以及诸如数据量变化之类的信息可以帮助确定性能下降的原因。
缺少 SQL 访问结构
缺少 SQL 访问结构（例如，索引和物化视图）是 SQL 性能欠佳的典型原因。最佳访问结构集可以将 SQL 性能提高几个数量级。
过时的优化程序统计信息
当统计维护操作（自动或手动）无法跟上 DML 引起的对表数据的更改时，DBMS_STATS 收集的统计信息可能会变得陈旧。由于表上的陈旧统计信息无法准确反映表数据，因此优化程序可能会根据错误信息做出决策并生成次优执行计划。
硬件问题
次优性能可能与内存、I/ O 和 CPU 问题有关。
4. 定义问题的范围
解决方案的范围必须与问题的范围相匹配。需要考虑数据库级别的问题和语句级别的问题。例如，共享池太小，这会导致游标快速老化，从而导致许多硬解析。使用初始化参数来增加共享池大小可以修复数据库级别的问题并提高所有会话的性能。但是，如果单个 SQL 语句未使用有用的索引，则更改整个数据库的优化程序初始化参数可能会损害整体性能。如果单个 SQL 语句出现问题，那么适当范围的解决方案只能通过此语句解决此问题。
5. 为次优执行 SQL 语句实施更正操作
这些行为因环境而异。例如，您可以重写 SQL 语句以提高效率，通过重写语句以使用绑定变量来避免不必要的硬解析。您还可以使用 equijoins，从 WHERE 子句中删除函数，并将复杂的 SQL 语句分解为多个简单语句。
在某些情况下，您不是通过重写语句而是通过重构模式对象来提高 SQL 性能。例如，您可以对表进行分区，引入派生值，甚至更改数据库设计。
6. 防止 SQL 性能回归
要确保最佳 SQL 性能，请验证执行计划是否继续提供最佳性能，并选择更好的计划（如果可用）。您可以使用优化程序统计信息，SQL 配置文件和 SQL 计划基准来实现这些目标。
1.4.2 SQL 调优工具
SQL 调优工具是自动或手动的。
在某种情况下，如果数据库本身可以提供诊断，建议或纠正措施，则工具是自动化的。手动工具要求您执行所有这些操作。
所有调优工具都依赖于数据库实例收集的动态性能视图，统计信息和度量标准的基本工具。数据库本身包含调整 SQL 语句所需的数据和元数据。
1.4.2.1 自动 SQL 调优工具
Oracle 数据库提供了几个与 SQL 调优相关的顾问程序。
此外，SQL 计划管理是一种可以防止性能回归的机制，还可以帮助您提高 SQL 性能。
所有自动 SQL 调优工具都可以使用 SQL 调优集作为输入。SQL 调优集（STS）是一个数据库对象，包括一个或多个 SQL 语句及其执行统计信息和执行上下文。
1.4.2.1.1 自动数据库诊断监视器（ADDM）
ADDM 是 Oracle 数据库内置的自诊断软件。
ADDM 可以自动定位性能问题的根本原因，提供纠正建议，并量化预期收益。ADDM 还可识别无需采取任何措施的区域。
ADDM 和其他顾问使用自动工作负载存储库（AWR），它是一种为数据库组件提供服务以收集，维护和使用统计信息的基础结构。ADDM 检查并分析 AWR 中的统计信息，以确定可能的性能问题，包括高负载 SQL。
例如，您可以将 ADDM 配置为每晚运行。在早上，您可以检查最新的 ADDM 报告，以查看可能导致问题的原因以及是否存在建议的修复。该报告可能会显示特定的 SELECT 语句占用了大量 CPU，并建议您运行 SQL 调优顾问。
1.4.2.1.2 SQL 调优顾问
SQL 调优顾问是内部诊断软件，可识别有问题的 SQL 语句，并建议如何提高语句性能。
在数据库维护窗口期间作为自动维护任务运行时，SQL 调优顾问称为自动 SQL 调整顾问。
SQL 调优顾问将一个或多个 SQL 语句作为输入，并调用自动调整优化器对语句执行 SQL 调优。顾问执行以下类型的分析：
检查无效或过时的统计信息
构建 SQL profile
SQL profile 是一组特定于 SQL 语句的辅助信息。SQL profile 包含在自动 SQL 调整期间发现的次优优化程序估计的更正。此信息可以改进基数的优化器估计，基数是执行计划中的操作估计或实际返回的行数，以及选择性。这些改进的估计导致优化器选择更好的计划。
探讨不同的访问路径是否可以显着提高性能
标识适合于次优计划的 SQL 语句
产出的形式是报告或建议，以及每项建议的理由及其预期收益。该建议涉及对象统计信息的集合，新索引的创建，SQL 语句的重构或 SQL profile 的创建。您可以选择接受建议以完成 SQL 语句的调整。
1.4.2.1.3 SQL 访问顾问
SQL 访问顾问是内部诊断软件，它建议创建，删除或保留哪些物化视图，索引和物化视图日志。
SQL 访问顾问将实际工作负载作为输入，或者顾问程序可以从模式中获取假设的工作负载。SQL 访问顾问会考虑空间使用和查询性能之间的权衡，并建议对新的和现有的物化视图和索引进行最具成本效益的配置。顾问还提出有关分区的建议。
1.4.2.1.4 自动索引
Oracle 数据库可以持续监控应用程序工作负载，自动创建和管理索引。
手动创建索引需要深入了解数据模型，应用程序和数据分布。DBA 通常会选择创建哪些索引，然后从不修改他们的选择。结果，失去了改进的机会，不必要的索引，可能会成为性能瓶颈。自动索引管理通过执行以下任务解决了此问题：
不断监控工作负载
创建新索引
重建索引，然后将其标记为不可用或不可见
删除索引
检查自动索引管理对性能的影响
索引功能实现是自动任务，以固定间隔在后台运行。在每次迭代时，该任务执行以下基本步骤：
根据列和列组使用情况标识候选索引。
在不可用和不可见模式下创建一组候选索引。这些索引不占用存储空间。
查询先前执行的语句的优先级列表，以确定候选索引是否值得重建。
编译语句以确定优化程序是否会选择新索引，然后重建优化程序选择的索引。
执行使用自动索引的语句
执行以下任一操作：
当语句显着改善其性能时，将索引标记为可见。只有在验证并将索引标记为可见之后，数据库才会更改工作负载中语句的计划。在此之前，数据库不会使游标无效并继续使用旧执行计划。
标记索引在提供不足的性能优势时无法使用。当使用其他的索引的概率较低或存在空间压力时，此操作以延迟方式发生。
使用 SQL 计划管理避免回归。索引可能会使一个语句受益，但会导致第二个语句中的性能下降。在这种情况下，数据库通过将索引标记为可见来优化第一个语句。为了防止第二个语句的下降，数据库使用 SQL 计划管理来保护它。
删除长时间未使用的索引。
您可以通过在 DBMS_AUTO_INDEX 包中执行以下过程来启用自动索引：
EXEC DBMS_AUTO_INDEX.CONFIGURE(AUTO_INDEX_MODE , IMPLEMENT)您还可以使用 DBMS_AUTO_INDEX 包报告自动任务并配置设置，例如保留未使用的索引的时间。
1.4.2.1.5 SQL 计划管理
SQL 计划管理是一种预防机制，使优化程序能够自动管理执行计划，确保数据库仅使用已知或已验证的计划。
此机制可以构建 SQL 计划基准，该基准包含每个 SQL 语句的一个或多个接受的计划。通过使用基线，SQL 计划管理可以防止计划回归环境变化，同时允许优化程序发现和使用更好的计划。
1.4.2.1.6 SQL 性能分析器
SQL 性能分析器通过识别每个 SQL 语句的性能差异来确定更改对 SQL 工作负载的影响。
系统更改（如升级数据库或添加索引）可能会导致执行计划发生更改，从而影响 SQL 性能。通过使用 SQL 性能分析器，您可以准确地预测系统更改对 SQL 性能的影响。使用此信息，您可以在 SQL 性能下降时调整数据库，或在 SQL 性能提高时验证和测量增益。
1.4.2.2 手动 SQL 调优工具
在某些情况下，除了自动化工具之外，您可能还需要运行手动工具。或者，您可能无法访问自动化工具。
1.4.2.2.1 执行计划
执行计划是手动 SQL 调优的主要诊断工具。例如，您可以查看计划以确定优化程序是选择预期的计划，还是确定在表上创建索引的效果。
您可以通过多种方式显示执行计划。以下工具是最常用的：
DBMS_XPLAN
您可以使用 DBMS_XPLAN 包方法显示 EXPLAIN PLAN 命令生成的执行计划以及 V$SQL_PLAN 的查询。
EXPLAIN PLAN
通过此 SQL 语句，您可以查看优化程序在不实际执行语句的情况下用于执行 SQL 语句的执行计划。
V$SQL_PLAN 和相关视图
这些视图包含有关已执行的 SQL 语句及其执行计划的信息，这些信息仍在共享池中。
AUTOTRACE
SQL * Plus 中的 AUTOTRACE 命令生成有关查询性能的执行计划和统计信息。此命令提供磁盘读取和内存读取等统计信息。
1.4.2.2.2 实时 SQL 监控和实时数据库操作
Oracle 数据库的实时 SQL 监视功能使您可以在执行时监视 SQL 语句的性能。默认情况下，当一个语句并行运行，或者在一次执行中消耗了至少 5 秒的 CPU 或 I / O 时间时，SQL 监视会自动启动。
数据库操作是由最终用户或应用程序代码定义的一组数据库任务，例如，批处理作业或提取，转换和加载（ETL）处理。您可以定义，监视和报告数据库操作。实时数据库操作提供自动监视复合操作的功能。执行开始后，数据库会自动监视并行查询，DML 和 DDL 语句。
Oracle Enterprise Manager Cloud Control（云控制）提供易于使用的 SQL 监控页面。或者，您可以使用 V$SQL_MONITOR 和 V$SQL_PLAN_MONITOR 视图监视与 SQL 相关的统计信息。您可以将这些视图与以下视图一起使用，以获取有关正在监视的执行的更多信息：
V$ACTIVE_SESSION_HISTORY
V$SESSION
V$SESSION_LONGOPS
V$SQL
V$SQL_PLAN
1.4.2.2.3 应用程序跟踪
SQL 跟踪文件提供有关各个 SQL 语句的性能信息：解析计数，物理和逻辑读取，库高速缓存上的未命中等。
跟踪文件有时可用于诊断 SQL 性能问题。您可以使用 DBMS_MONITOR 或 DBMS_SESSION 包为特定会话启用和禁用 SQL 跟踪。当您启用跟踪机制时，Oracle 数据库通过为每个服务器进程生成跟踪文件来实现跟踪。
Oracle 数据库提供以下命令行工具来分析跟踪文件：
TKPROF
此实用程序接受 SQL 跟踪工具生成的跟踪文件作为输入，然后生成格式化的输出文件。
trcsess
此实用程序根据会话 ID，客户端 ID 和服务 ID 等条件合并来自多个跟踪文件的跟踪输出。在 trcsess 将跟踪信息合并到单个输出文件后，您可以使用 TKPROF 格式化输出文件。 trcsess 对于合并特定会话的跟踪以用于性能或调试目的非常有用。
端到端应用程序跟踪简化了诊断多层环境中性能问题的过程。在这些环境中，中间层将请求从最终客户端路由到不同的数据库会话，从而难以跨数据库会话跟踪客户端。端到端应用程序跟踪使用客户端 ID 通过数据库的所有层唯一地跟踪特定的最终客户端。
1.4.2.2.4 优化器 Hint
Hint 是通过 SQL 语句中的注释传递给优化程序的指令。
Hint 使您可以通常由优化程序自动做出决策。在测试或开发环境中，Hint 对于测试特定访问路径的性能很有用。例如，您可能知道特定索引对某些查询更具选择性。在这种情况下，您可以使用 Hint 来指示优化器使用更好的执行计划，如以下示例所示：
SELECT /*+ INDEX (employees emp_department_ix) */
employee_id, department_id
FROM employeesWHERE department_id 50;有时，由于拼写错误，无效参数，冲突提示以及通过转换无效的提示，数据库可能不会使用 hint。从 Oracle Database 19c 开始，您可以生成有关在计划生成期间使用或未使用哪些 hint 的报告。
1.4.3 SQL 调优工具的用户界面
Cloud Control 是一种系统管理工具，可以对数据库环境进行集中管理。Cloud Control 提供对大多数调优工具的访问。
通过结合图形控制台，Oracle 管理服务器，Oracle 智能代理，通用服务和管理工具，Cloud Control 提供了一个全面的系统管理平台。
您可以使用命令行界面访问所有 SQL 调优工具。例如，DBMS_SQLTUNE 包是 SQL 调优顾问的命令行界面。
Oracle 建议将 Cloud Control 作为数据库管理和调优的最佳界面。但是如果命令行界面能够更好地说明特定的概念或任务，我们的示例也将使用命令行。
“怎么调优 Oracle SQL”的内容就介绍到这里了，感谢大家的阅读。如果想了解更多行业相关的知识可以关注丸趣 TV 网站，丸趣 TV 小编将为大家输出更多高质量的实用文章！
