怎么调优Oracle SQL

本篇内容介绍了“怎么调优oracle sql”的有关知识，在实际案例的操作过程中，不少人都会遇到这样的困境，接下来就让小编带领大家学习一下如何处理这些情况吧！希望大家仔细阅读，能够学有所成！

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

1.1 关于SQL调优

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

SQL调优意味着修复已部署正在运行的应用程序中的问题。

1.2 SQL调优的目的

当SQL语句无法按照预定和可测量的标准执行时，它就成为一个问题。

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

• 减少用户响应时间，减少用户发出语句和收到响应之间的时间

• 提高吞吐量，处理语句访问所有行需要的最少量资源

1.3 SQL调优的前提

SQL性能调优需要数据库知识的基础。

假定您具有下表中显示的知识和技能。

表1-1 所需知识

1.4 SQL调优的任务和工具

在确定调优会话的目标后，例如，将用户响应时间从三分钟缩短到不到一秒，问题就变成了如何实现此目标。

调优会话的细节取决于许多因素，包括您是主动调优还是被动调优。

在主动SQL调优中，您经常使用SQL Tuning Advisor来确定是否可以使SQL语句更好地执行。 在被动SQL调优中，您可以更正用户遇到的与SQL相关的问题。

无论您是主动，还是被动地进行调优，典型的SQL调优会话都涉及以下所有或大部分任务：

查看过去的执行历史记录，以查找负责大量应用程序工作负载和系统资源的语句。

优化程序统计信息对SQL调优至关重要。 如果这些统计信息不存在或不再准确，则优化程序无法生成最佳执行计划。 与SQL性能相关的其他数据包括语句访问的表和视图的结构，以及语句可用的索引的定义。

通常，SQL性能问题的原因包括：

• 设计效率低下的SQL语句
  如果编写SQL语句以便执行不必要的工作，那么优化器无法提高其性能。 低效设计的例子包括:

• 忽略添加Join条件，这会导致笛卡尔连接

• 使用hint将大表指定为连接中的驱动表

• 指定UNION而不是UNioN ALL

• 为外部查询中的每一行执行子查询

• 次优的执行计划
  查询优化器（也称为优化器）是内部软件，用于确定哪个执行计划最有效。 有时，优化器会选择具有次优访问路径的计划，这是数据库从数据库中检索数据的方法。 例如，具有低选择性的查询谓词的计划，可以在大表而不是索引上使用全表扫描。

您可以将执行最佳SQL语句的执行计划与次优的计划进行比较。 这种比较以及诸如数据量变化之类的信息可以帮助确定性能下降的原因。

• 缺少SQL访问结构
  缺少SQL访问结构（例如，索引和物化视图）是SQL性能欠佳的典型原因。 最佳访问结构集可以将SQL性能提高几个数量级。

• 过时的优化程序统计信息
  当统计维护操作（自动或手动）无法跟上DML引起的对表数据的更改时，DBMS_STATS收集的统计信息可能会变得陈旧。 由于表上的陈旧统计信息无法准确反映表数据，因此优化程序可能会根据错误信息做出决策并生成次优执行计划。

• 硬件问题
  次优性能可能与内存、I/O和CPU问题有关。

解决方案的范围必须与问题的范围相匹配。需要考虑数据库级别的问题和语句级别的问题。例如，共享池太小，这会导致游标快速老化，从而导致许多硬解析。使用初始化参数来增加共享池大小可以修复数据库级别的问题并提高所有会话的性能。但是，如果单个SQL语句未使用有用的索引，则更改整个数据库的优化程序初始化参数可能会损害整体性能。如果单个SQL语句出现问题，那么适当范围的解决方案只能通过此语句解决此问题。

这些行为因环境而异。例如，您可以重写SQL语句以提高效率，通过重写语句以使用绑定变量来避免不必要的硬解析。 您还可以使用equijoins，从WHERE子句中删除函数，并将复杂的SQL语句分解为多个简单语句。

在某些情况下，您不是通过重写语句而是通过重构模式对象来提高SQL性能。例如，您可以对表进行分区，引入派生值，甚至更改数据库设计。

要确保最佳SQL性能，请验证执行计划是否继续提供最佳性能，并选择更好的计划（如果可用）。您可以使用优化程序统计信息，SQL配置文件和SQL计划基准来实现这些目标。

SQL调优工具是自动或手动的。

在某种情况下，如果数据库本身可以提供诊断，建议或纠正措施，则工具是自动化的。手动工具要求您执行所有这些操作。

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

Oracle数据库提供了几个与SQL调优相关的顾问程序。

此外，SQL计划管理是一种可以防止性能回归的机制，还可以帮助您提高SQL性能。

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

ADDM是Oracle数据库内置的自诊断软件。

ADDM可以自动定位性能问题的根本原因，提供纠正建议，并量化预期收益。 ADDM还可识别无需采取任何措施的区域。

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

例如，您可以将ADDM配置为每晚运行。在早上，您可以检查最新的ADDM报告，以查看可能导致问题的原因以及是否存在建议的修复。该报告可能会显示特定的SELECT语句占用了大量CPU，并建议您运行SQL调优顾问。

SQL调优顾问是内部诊断软件，可识别有问题的SQL语句，并建议如何提高语句性能。

在数据库维护窗口期间作为自动维护任务运行时，SQL调优顾问称为自动SQL调整顾问。

SQL调优顾问将一个或多个SQL语句作为输入，并调用自动调整优化器对语句执行SQL调优。 顾问执行以下类型的分析：

• 检查无效或过时的统计信息

• 构建SQL profile
  SQL profile是一组特定于SQL语句的辅助信息。SQL profile包含在自动SQL调整期间发现的次优优化程序估计的更正。此信息可以改进基数的优化器估计，基数是执行计划中的操作估计或实际返回的行数，以及选择性。 这些改进的估计导致优化器选择更好的计划。

• 探讨不同的访问路径是否可以显着提高性能

• 标识适合于次优计划的SQL语句

产出的形式是报告或建议，以及每项建议的理由及其预期收益。该建议涉及对象统计信息的集合，新索引的创建，SQL语句的重构或SQL profile的创建。 您可以选择接受建议以完成SQL语句的调整。

SQL访问顾问是内部诊断软件，它建议创建，删除或保留哪些物化视图，索引和物化视图日志。

SQL访问顾问将实际工作负载作为输入，或者顾问程序可以从模式中获取假设的工作负载。SQL访问顾问会考虑空间使用和查询性能之间的权衡，并建议对新的和现有的物化视图和索引进行最具成本效益的配置。 顾问还提出有关分区的建议。

Oracle数据库可以持续监控应用程序工作负载，自动创建和管理索引。

手动创建索引需要深入了解数据模型，应用程序和数据分布。 DBA通常会选择创建哪些索引，然后从不修改他们的选择。 结果，失去了改进的机会，不必要的索引，可能会成为性能瓶颈。自动索引管理通过执行以下任务解决了此问题：

• 不断监控工作负载

• 创建新索引

• 重建索引，然后将其标记为不可用或不可见

• 删除索引

• 检查自动索引管理对性能的影响

索引功能实现是自动任务，以固定间隔在后台运行。 在每次迭代时，该任务执行以下基本步骤：

• 根据列和列组使用情况标识候选索引。

• 在不可用和不可见模式下创建一组候选索引。这些索引不占用存储空间。

• 查询先前执行的语句的优先级列表，以确定候选索引是否值得重建。

• 编译语句以确定优化程序是否会选择新索引，然后重建优化程序选择的索引。

• 执行使用自动索引的语句

• 执行以下任一操作：

• 当语句显着改善其性能时，将索引标记为可见。只有在验证并将索引标记为可见之后，数据库才会更改工作负载中语句的计划。在此之前，数据库不会使游标无效并继续使用旧执行计划。

• 标记索引在提供不足的性能优势时无法使用。当使用其他的索引的概率较低或存在空间压力时，此操作以延迟方式发生。

• 使用SQL计划管理避免回归。索引可能会使一个语句受益，但会导致第二个语句中的性能下降。在这种情况下，数据库通过将索引标记为可见来优化第一个语句。 为了防止第二个语句的下降，数据库使用SQL计划管理来保护它。

• 删除长时间未使用的索引。

您可以通过在 DBMS_AUTO_INDEX 包中执行以下过程来启用自动索引：

EXEC DBMS_AUTO_INDEX.CONFIGURE('AUTO_INDEX_MODE','IMPLEMENT')

您还可以使用 DBMS_AUTO_INDEX 包报告自动任务并配置设置，例如保留未使用的索引的时间。

SQL计划管理是一种预防机制，使优化程序能够自动管理执行计划，确保数据库仅使用已知或已验证的计划。

此机制可以构建SQL计划基准，该基准包含每个SQL语句的一个或多个接受的计划。 通过使用基线，SQL计划管理可以防止计划回归环境变化，同时允许优化程序发现和使用更好的计划。

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

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

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

执行计划是手动SQL调优的主要诊断工具。 例如，您可以查看计划以确定优化程序是选择预期的计划，还是确定在表上创建索引的效果。

您可以通过多种方式显示执行计划。 以下工具是最常用的：

• DBMS_XPLAN
  您可以使用 DBMS_XPLAN 包方法显示 EXPLaiN PLAN 命令生成的执行计划以及 V$SQL_PLAN 的查询。

• EXPLAIN PLAN
  通过此SQL语句，您可以查看优化程序在不实际执行语句的情况下用于执行SQL语句的执行计划。

• V$SQL_PLAN 和相关视图
  这些视图包含有关已执行的SQL语句及其执行计划的信息，这些信息仍在共享池中。

• AUTOTRACE
  SQL * Plus中的 AUTOTRACE 命令生成有关查询性能的执行计划和统计信息。此命令提供磁盘读取和内存读取等统计信息。

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

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

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

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

• TKPROF

此实用程序接受SQL跟踪工具生成的跟踪文件作为输入，然后生成格式化的输出文件。

• trcsess

此实用程序根据会话ID，客户端ID和服务ID等条件合并来自多个跟踪文件的跟踪输出。在 trcsess 将跟踪信息合并到单个输出文件后，您可以使用TKPROF格式化输出文件。 trcsess 对于合并特定会话的跟踪以用于性能或调试目的非常有用。

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

Hint是通过SQL语句中的注释传递给优化程序的指令。

Hint使您可以通常由优化程序自动做出决策。 在测试或开发环境中，Hint对于测试特定访问路径的性能很有用。例如，您可能知道特定索引对某些查询更具选择性。 在这种情况下，您可以使用Hint来指示优化器使用更好的执行计划，如以下示例所示：

SELECT  
       employee_id, department_id 
FROM   employeesWHERE  department_id > 50;

有时，由于拼写错误，无效参数，冲突提示以及通过转换无效的提示，数据库可能不会使用hint。 从Oracle Database 19c开始，您可以生成有关在计划生成期间使用或未使用哪些hint的报告。

Cloud Control是一种系统管理工具，可以对数据库环境进行集中管理。 Cloud Control提供对大多数调优工具的访问。

通过结合图形控制台，Oracle管理服务器，Oracle智能代理，通用服务和管理工具，Cloud Control提供了一个全面的系统管理平台。

您可以使用命令行界面访问所有SQL调优工具。 例如，DBMS_SQLTUNE 包是SQL调优顾问的命令行界面。

Oracle建议将Cloud Control作为数据库管理和调优的最佳界面。但是如果命令行界面能够更好地说明特定的概念或任务，我们的示例也将使用命令行。

“怎么调优Oracle SQL”的内容就介绍到这里了，感谢大家的阅读。如果想了解更多行业相关的知识可以关注编程网网站，小编将为大家输出更多高质量的实用文章！