SQL调优指南笔记1：Introduction to SQL Tuning

原创已于 2022-05-17 21:53:51 修改 · 810 阅读

1 ·

CC 4.0 BY-SA版权

文章标签：

#sql #数据库 #oracle #性能调优

于 2021-11-01 22:28:12 首次发布

数据库性能调优专栏收录该内容

47 篇文章

订阅专栏

本文介绍SQL调优的基础概念，涵盖优化器、统计信息、执行计划等，并概述SQL调优的任务与工具，包括自动与手动调优工具。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

本文为SQL Tuning Guide第1章笔记。

SQL 调优是尝试诊断和修复不符合性能标准的 SQL 语句。

重要概念

optimizer 或 query optimizer
Built-in database software that determines the most efficient way to execute a SQL statement by considering factors related to the objects referenced and the conditions specified in the statement.
内置数据库软件，通过考虑与引用的对象相关的因素和语句中指定的条件来确定执行 SQL 语句的最有效方式。
optimizer statistics
Details about the database its object used by the optimizer to select the best execution plan for each SQL statement. Categories include table statistics such as numbers of rows, index statistics such as B-tree levels, system statistics such as CPU and I/O performance, and column statistics such as number of nulls.
有关数据库的详细信息，它的对象被优化器用来为每个 SQL 语句选择最佳执行计划。类别包括诸如行数之类的表统计信息、诸如 B 树级别之类的索引统计信息、诸如 CPU 和 I/O 性能之类的系统统计信息以及诸如空值之类的列统计信息。
join condition
A condition that compares two row sources using an expression. The database combines pairs of rows, each containing one row from each row source, for which the join condition evaluates to true.
使用表达式比较两个行源的条件。数据库组合成对的行，每个行包含来自每个行源的一行，对于这些行，连接条件的计算结果为真。（所以Join一定是2个表之间的，即使多表Join也是两两之间）
driving table
The table to which other tables are joined. An analogy from programming is a for loop that contains another for loop. The outer for loop is the analog of a driving table, which is also called an outer table.
其他表连接到的表。编程的一个类比是一个包含另一个 for 循环的 for 循环。外层for循环类似于驱动表，也称为外表。（也就是说：outer table通常应为小表，例如维表；与其对应的是inner table，例如事实表。）
execution plan
The combination of steps used by the database to execute a SQL statement. Each step either retrieves rows of data physically from the database or prepares them for the session issuing the statement. You can override execution plans by using a hint.
数据库用于执行 SQL 语句的步骤组合。每个步骤要么从数据库物理检索数据行，要么为发出语句的会话准备它们。您可以使用提示覆盖执行计划。
access path
The means by which the database retrieves data from a database. For example, a query using an index and a query using a full table scan use different access paths.
数据库从数据库中检索数据的方法。例如，使用索引的查询和使用全表扫描的查询使用不同的访问路径。
selectivity
A value indicating the proportion of a row set retrieved by a predicate or combination of predicates, for example, WHERE last_name = ‘Smith’. A selectivity of 0 means that no rows pass the predicate test, whereas a value of 1 means that all rows pass the test.

The adjective selective means roughly “choosy.” Thus, a highly selective query returns a low proportion of rows (selectivity close to 0), whereas an unselective query returns a high proportion of rows (selectivity close to 1).

一个值，指示由谓词或谓词组合检索的行集的比例，例如，WHERE last_name = ‘Smith’。选择性 0 表示没有行通过谓词测试，而值 1 表示所有行都通过了测试。

形容词选择性的意思大致是“挑剔的”。因此，高选择性查询返回低比例的行（选择性接近 0），而非选择性查询返回高比例的行（选择性接近 1）。

full table scan
A scan of table data in which the database sequentially reads all rows from a table and filters out those that do not meet the selection criteria. All data blocks under the high water mark are scanned.
对表数据的扫描，其中数据库顺序读取表中的所有行并过滤掉那些不符合选择条件的行。 高水位线下的所有数据块都将被扫描。
cardinality
The number of rows that is expected to be or is returned by an operation in an execution plan.
执行计划中的操作预期或实际返回的行数。
SQL profile
A set of auxiliary information built during automatic tuning of a SQL statement. A SQL profile is to a SQL statement what statistics are to a table. The optimizer can use SQL profiles to improve cardinality and selectivity estimates, which in turn leads the optimizer to select better plans.
在自动调优 SQL 语句期间构建的一组辅助信息。 SQL 配置文件之于 SQL 语句就像统计信息之于表一样。优化器可以使用 SQL 配置文件来改进基数和选择性估计，从而引导优化器选择更好的计划。
SQL plan baseline
A set of one or more accepted plans for a repeatable SQL statement. Each accepted plan contains a set of hints, a plan hash value, and other plan-related information. SQL plan management uses SQL plan baselines to record and evaluate the execution plans of SQL statements over time.
用于可重复 SQL 语句的一组一个或多个接受的计划。每个接受的计划都包含一组提示、计划哈希值和其他计划相关信息。 SQL 计划管理使用 SQL 计划基线来记录和评估 SQL 语句在一段时间内的执行计划。

1.1 Changes in Oracle Database Release 21c for SQL Tuning Guide

略

1.2 About SQL Tuning

SQL 调优是提高 SQL 语句性能以满足特定、可衡量和可实现的目标的迭代过程。

SQL 调优意味着修复已部署应用程序中的问题。相比之下，应用程序设计在部署应用程序之前设定安全和性能目标。

1.3 Purpose of SQL Tuning

当 SQL 语句未能按照预定的和可衡量的标准执行时，它即成为一个问题。

确定问题后，典型的调优具有以下目标之一：

减少用户响应时间，这意味着减少用户发出SQL语句和收到响应之间的时间
提高吞吐量，这意味着使用最少的资源来处理语句访问的所有行

1.4 Prerequisites for SQL Tuning

数据库架构：数据库架构不仅仅是管理员的领域。作为开发人员，您希望在最短的时间内针对 Oracle 数据库开发应用程序，这需要利用数据库架构和特性。例如，不了解 Oracle 数据库并发控制和多版本读取一致性可能会使应用程序破坏数据的完整性、运行缓慢并降低可扩展性。详见Oracle Database Concepts。
SQL和PL/SQL
不了解 SQL 就不可能调优应用程序或数据库。
SQL调优工具：数据库生成性能统计信息，并提供解释这些统计信息的 SQL 调优工具。详见Oracle Database 2 Day + Performance Tuning Guide

1.5 Tasks and Tools for SQL Tuning

1.5.1 SQL Tuning Tasks

SQL调优分为主动和被动两种方式。主动 SQL 调优指定期使用 SQL Tuning Advisor 来确定是否可以使 SQL 语句执行得更好。
无论哪种方式，典型的调优过程包含以下步骤：

识别重载 SQL 语句
查看过去的执行历史以查找占比大部分应用程序工作负载和系统资源的语句。
收集性能相关数据
优化器统计信息对于 SQL 调优至关重要。如果这些统计信息不存在或不再准确，则优化器无法生成最佳计划。其他与 SQL 性能相关的数据包括语句访问的表和视图的结构，以及索引定义。
确定问题的原因
通常造成性能问题的原因包括：
1）设计效率低下的 SQL 语句
2）次优执行计划：查询优化器（也称为优化器）是确定哪个执行计划最有效的内部软件。有时优化器会选择具有次优访问路径（access path，即从数据库中检索数据的方式）的计划。例如，具有低选择性（selectivity）的查询谓词的计划可能会在大表上使用全表扫描而不是索引。
3）缺少 SQL 访问结构：缺少 SQL 访问结构（例如索引和物化视图）是 SQL 性能欠佳的典型原因。最佳访问结构可以将 SQL 性能提高几个数量级。
4）陈旧的优化器统计信息：当统计信息维护操作（自动或手动）跟不上 DML 导致的表数据更改时，DBMS_STATS 收集的统计信息可能会过时。由于表上的陈旧统计信息不能准确反映表数据，优化器会根据错误信息做出决策并生成次优执行计划。
5）硬件问题：如内存，CPU和存储
定义问题的范围：解决方案的范围必须与问题的范围相匹配。否则可能会产生副作用，伤及无辜。
为执行不理想的 SQL 语句实施纠正措施：如重写SQL，重构schema对象（创建索引，调整索引列顺序，建立分区，视图）或改变数据库设计。
防止 SQL 性能退化：为确保最佳 SQL 性能，请验证执行计划是否继续提供最佳性能，并始终选择更好的计划。您可以使用优化器统计信息、SQL 配置文件和 SQL 计划基线来实现这些目标。

1.5.2 SQL Tuning Tools

SQL 调优工具分为自动和手动两种。

如果数据库本身可以提供诊断、建议或纠正措施，那么工具就是自动化的。手动工具要求您执行所有这些操作。

所有调优工具都依赖于数据库实例收集的动态性能视图、统计信息和指标的基本工具。数据库本身包含调整 SQL 语句所需的数据和元数据。

1.5.2.1 Automated SQL Tuning Tools

主要是指各种Advisor。
此外，SQL 计划管理是一种可以防止性能倒退并帮助您提高 SQL 性能的机制。

所有自动化 SQL 调优工具都可以使用 SQL 调优集作为输入。 SQL 调整集 (STS) 是一个数据库对象，包括一个或多个 SQL 语句及其执行统计信息和执行上下文。

1.5.2.1.1 Automatic Database Diagnostic Monitor (ADDM)

ADDM 是内置于 Oracle 数据库中的自我诊断软件。可以自动定位性能问题的根本原因，提供更正建议，并量化预期收益。 ADDM 还确定不需要采取行动的区域。ADDM使用AWR中的信息。

ADDM 和其他顾问使用自动工作负载存储库 (AWR)，这是一种为数据库组件提供服务以收集、维护和使用统计信息的基础设施。 ADDM 检查和分析 AWR 中的统计数据以确定可能的性能问题，包括高负载 SQL。

例如，您可以将 ADDM 配置为每晚运行。早上，您可以检查最新的 ADDM 报告，了解可能导致问题的原因以及是否有推荐的修复方法。该报告可能会显示特定的 SELECT 语句消耗了大量 CPU，并建议您运行 SQL Tuning Advisor。

1.5.2.1.2 SQL Tuning Advisor

SQL Tuning Advisor 是内部诊断软件，可识别有问题的 SQL 语句并建议如何提高语句性能。在数据库维护窗口期间作为自动维护任务运行时，SQL Tuning Advisor 称为 Automatic SQL Tuning Advisor。

SQL Tuning Advisor 将一个或多个 SQL 语句作为输入，并调用 Automatic Tuning Optimizer 对语句执行 SQL 调优。顾问执行以下类型的分析：

检查缺失或过时的统计数据
构建 SQL 配置文件 (SQL Profile)
SQL 配置文件是一组特定于 SQL 语句的辅助信息。 SQL 配置文件包含对自动 SQL 调优期间发现的次优优化器估计的更正。此信息可以改进优化器对基数 (cardinality) 的估计，基数是执行计划中的操作估计返回或实际返回的行数，以及选择性。这些改进的估计导致优化器选择更好的计划。
探索不同的访问路径是否可以显着提高性能
识别适合次优计划的 SQL 语句

输出采用建议或建议的形式，以及每个建议的基本原理及其预期收益。该建议涉及对象统计信息的收集、新索引的创建、SQL 语句的重组或 SQL 配置文件的创建。您可以选择接受建议以完成 SQL 语句的调优。

1.5.2.1.3 SQL Access Advisor

SQL Access Advisor 是内部诊断软件，它建议创建、删除或保留哪些物化视图、索引和物化视图日志。这里的Access指的就是access path。

SQL Access Advisor 将实际工作负载作为输入，或者顾问可以从schema中导出假设的工作负载。 SQL Access Advisor 考虑空间使用和查询性能之间的权衡，并推荐新的和现有的物化视图和索引的最具成本效益的配置。该顾问还提出了有关分区的建议。

1.5.2.1.4 Automatic Indexing

Oracle 数据库可以持续监控应用程序负载，自动创建和管理索引。手动创建索引需要对数据模型、应用程序和数据分布有深入的了解。 DBA 通常会选择创建哪些索引，然后从不修改他们的选择。结果，失去了改进的机会，不必要的索引可能成为性能负担。（19c开始有此特性，只支持Exadata，详见这里）

1.5.2.1.5 SQL Plan Management

SQL 计划管理（SPM）是一种预防机制，它使优化器能够自动管理执行计划，确保数据库仅使用已知或经过验证的计划。相关概念包括SQL plan baseline。

这种机制可以构建一个 SQL 计划基线，其中包含每个 SQL 语句的一个或多个已接受的计划。通过使用基线，SQL 计划管理可以防止计划因环境变化而退化，同时允许优化器发现和使用更好的计划。

1.5.2.1.6 SQL Performance Analyzer

SQL 性能分析器（SPA）通过识别每个 SQL 语句的性能差异来确定更改对 SQL 工作负载的影响。

升级数据库或添加索引等系统更改可能会导致执行计划发生更改，从而影响 SQL 性能。通过使用 SQL Performance Analyzer，您可以准确预测系统变化对 SQL 性能的影响。使用此信息，您可以在 SQL 性能下降时调整数据库，或者在 SQL 性能提高时验证和测量增益。

1.5.2.2 Manual SQL Tuning Tools

在某些情况下，除了自动化工具之外，您可能还想运行手动工具。或者，您可能无法访问自动化工具。

1.5.2.2.1 Execution Plans

执行计划是手动 SQL 调优中的主要诊断工具。例如，您可以查看计划以确定优化器是否选择了您期望的计划，或者确定在表上创建索引的效果。

最常用的查看执行计划的方法包括：DBMS_XPLAN，EXPLAIN PLAN，V$SQL_PLAN等视图，AUTOTRACE

1.5.2.2.2 Real-Time SQL Monitoring and Real-Time Database Operations

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.5.2.2.3 Application Tracing

SQL 跟踪文件（Trace file）提供有关单个 SQL 语句的性能信息：解析计数、物理和逻辑读取等信息。相关工具为TKPROF和trcsess

SQL 跟踪文件提供有关各个 SQL 语句的性能信息：解析计数、物理和逻辑读取、库缓存未命中等。

跟踪文件有时可用于诊断 SQL 性能问题。您可以使用 DBMS_MONITOR 或 DBMS_SESSION 包为特定会话启用和禁用 SQL 跟踪。当您启用跟踪机制时，Oracle 数据库通过为每个服务器进程生成一个跟踪文件来实现跟踪。

Oracle 数据库提供了以下用于分析跟踪文件的命令行工具：

TKPROF
此实用程序接受 SQL 跟踪工具生成的跟踪文件作为输入，然后生成格式化的输出文件。
trcsess
此实用程序根据会话 ID、客户端 ID 和服务 ID 等条件整合来自多个跟踪文件的跟踪输出。 trcsess 将跟踪信息合并为单个输出文件后，您可以使用 TKPROF 格式化输出文件。 trcsess 可用于合并特定会话的跟踪以实现性能或调试目的。

端到端应用程序跟踪简化了在多层环境中诊断性能问题的过程。在这些环境中，中间层将来自终端客户端的请求路由到不同的数据库会话，因此很难跨数据库会话跟踪客户端。端到端应用程序跟踪使用客户端 ID 通过所有层唯一地跟踪特定的最终客户端到数据库。