通过分析sql语句的执行计划优化sql

oracle

需积分: 10 123 浏览量更新于2023-03-03 1 收藏 254KB DOC 举报

身份认证购VIP最低享 7 折!

领优惠券(最高得80元）

资源详情

资源推荐

通过分析 SQL 语句的执行计划优化 SQL(总结)

做 DBA 快 7 年了，中间感悟很多。在 DBA 的日常工作中，调整个别性能较差的 SQL 语句

时一项富有挑战性的工作。其中的关键在于如何得到 SQL 语句的执行计划和如何从 SQL

语句的执行计划中发现问题。总是想将日常经验的点点滴滴总结一下，但是直到最近才下

定决心，总共花了 3 个周末时间，才将其整理成册，便于自己日常工作。不好意思独享，

所以将其贴出来。

第一章、第 2 章并不是很重要，是自己的一些想法，关于如何做一个稳定、高效的应用系

统的一些想法。

第三章以后都是比较重要的。

附录的内容也是比较重要的。我常用该部分的内容。

前言

本文档主要介绍与 SQL 调整有关的内容，内容涉及多个方面：SQL 语句执行的过程、

ORACLE 优化器，表之间的关联，如何得到 SQL 执行计划，如何分析执行计划等内容，从

而由浅到深的方式了解 SQL 优化的过程，使大家逐步步入 SQL 调整之门，然后你将发现

……。

该文档的不当之处，敬请指出，以便进一步改正。请将其发往我的信箱：

xu_yu_jin2000@sina.com。

如果引用本文的内容，请著名出处！

作者：徐玉金

MSN：sunny_xyj@hotmail.com

Email: xu_yu_jin2000@sina.com

l 硬件/软件管理人员必须传达系统的硬件、软件配置并提供它们的资料，使得相关人员

能有效地设计和管理系统。

简而言之，与系统涉及的每个人都在调整过程中起某些作用，当上面提及的那些人员传

达了系统的特性并提供了它们的资料，调整就能相对的容易和更快一些。

不幸的是，事实上的结果是：数据库管理员对调整负有全部或主要的责任。但是，数据

库管理员很少有合适的系统方面的资料，而且，在很多情况下，数据库管理员往往是在实

施阶段才介入数据库，这就给调整工作带来许多负面的影响，因为在设计阶段的缺陷是不

能通过 DBA 的调整而得以解决，而设计阶段的缺陷往往对数据库性能造成极大的影响。

其实，在真正成熟的开发环境下，开发人员作为纯代码编写人员时，对性能的影响最小，

此时大部分的工作应由应用设计人员完成，而且数据库管理员往往在前期的需求管理阶段

就介入，为设计人员提供必要的技术支持。

调整并不是数据库管理员的专利，相反大部分应该是设计人员和开发人员的工作，这就需

要设计人员和开发人员具体必要的数据库知识，这样才能组成一个高效的团队，然而事实

上往往并非如此。

什么时候作调整？

多数人认为当用户感觉性能差时才进行调整，这对调整过程中使用某些最有效的调整策

略来说往往是太迟了。此时，如果你不愿意重新设计应用的话，你只能通过重新分配内存

(调整 SGA)和调整 I/O 的办法或多或少地提高性能。Oracle 提供了许多特性，这些特性只有

应用到正确地设计的系统中时才能够很大地提高性能。

应用设计人员需要在设计阶段设置应用的性能期望值。然后在设计和开发期间，应用设

计人员应考虑哪些 Oracle 特性可以对系统有好处，并使用这些特性。

通过良好的系统设计，你就可以在应用的生命周期中消除性能调整的代价和挫折。图 1-1

图 1-2 说明在应用的生命周期中调整的相对代价和收益，正如你见到的，最有效的调整时

间是在设计阶段。在设计期间的调整能以最低的代价给你最大的收益。

图 1-1　在应用生命周期中调整的代价　

　　　　　　图 1-2 在应用生命周期中调整的收益

当然，即使在设计很好的系统中，也可能有性能降低。但这些性能降低应该是可控的和

可以预见的。

调整目标

不管你正在设计或维护系统，你应该建立专门的性能目标，它使你知道何时要作调整。

如果你试图胡乱地改动初始化参数或 SQl 语句，你可能会浪费调整系统的时间，而且无什

么大的收益。调整你的系统的最有效方法如下：

l 当设计系统时考虑性能

l 调整操作系统的硬件和软件

l 识别性能瓶颈

l 确定问题的原因

l 采取纠正的动作

当你设计系统时，制定专门的目标；例如，响应时间小于３秒。当应用不能满足此目标时

识别造成变慢的瓶颈（例如，I/O 竞争），确定原因，采取纠正动作。在开发期间，你应测

试应用研究，确定在采取应用之前是否满足设计的性能目标。

当你正在维护生产库系统时，有多种快速有效的方法来识别性能瓶颈。

不管怎样，调整通常是一系列开销。一旦你已确定了瓶颈，你可能要牺牲一些其它方面的

指标来达到所要的结果。例如，如果 I/O 有问题，你可能需要更多内存或磁盘。如果不可

能买，你可能要限制系统的并发性，来获取所需的性能。然而，如果你已经明确地定义了

性能的目标，那用什么来交换高性能的决策就变的很容易的，因为你已经确定了哪些方面

是最重要的，如过我的目标为高性能，可能牺牲一些空间资源。

随着应用的越来越庞大，硬件性能的提高，全面的调整应用逐渐变成代价高昂的行为，

在这样情况下，要取得最大的投入/效率之比，较好的办法是调整应用的关键部分，使其达

到比较高的性能，这样从总体上来说，整个系统的性能也是比较高的。这也就是有名的

20/80 原则，调整应用的 20%(关键部分)，能解决 80%的问题。

在设计和开发系统时作调整

良好设计的系统可以防止在应用生命周期中产生性能问题。系统设计人员和应用开发人

员必须了解 Oracle 的查询处理机制以便写出高效的 SQL 语句。“第 2 章有效的应用设计”讨

论了你的系统中各种可用的配置，以及每种配置更适合哪种类型的应用。“第 5 章优化器”

讨论了 Oracle 的查询优化器，以及如何写语句以获取最快的结果。

当设计你的系统时，使用下列优化性能的准则：

l 消除客户机／服务器应用中不必要的网络传输。-- 使用存储过程。

l 使用适合你系统的相应 Oracle 服务器选件（例如，并行查询或分布式数据库）。

l 除非你的应用有特殊的需要，否则使用缺省的 Oracle 锁。

l 利用数据库记住应用模块，以便你能以每个模块为基础来追踪性能。

l 选择你的数据块的最佳大小。 -- 原则上来说大一些的性能较好。

l 分布你的数据，使得一个节点使用的数据本地存贮在该节点中。

调整产品系统

本节描述对应用系统快速、容易地找出性能瓶颈，并决定纠正动作的方法。这种方法依

赖于对 Oracle 服务器体系结构和特性的了解程度。在试图调整你的系统前，你应熟悉

Oracle 调整的内容。

为调整你已有的系统，遵从下列步骤：

l 调整操作系统的硬件和软件

l 通过查询 V $SESSION_WAIT 视图，识别性能的瓶颈，这个动态性能视图列出了造成

会话(session)等待的事件。

l 通过分析 V $SESSION_WAIT 中的数据，决定瓶颈的原因。

l 纠正存在的问题。

监控应用系统

这主要是通过监控 oracle 的动态视图来完成。

各种有用的动态视图：如 v$session_wait, v$session_event 等。

第 2 章有效的应用设计

我们通常将最常用的应用分为 2 种类型：联机事务处理类型(OLTP)，决策支持系统

(DSS)。

联机事务处理(OLTP)

该类型的应用是高吞吐量，插入、更新、删除操作比较多的系统，这些系统以不断增

长的大容量数据为特征，它们提供给成百用户同时存取，典型的 OLTP 系统是订票系统，

银行的业务系统，订单系统。OTLP 的主要目标是可用性、速度、并发性和可恢复性。

当设计这类系统时，必须确保大量的并发用户不能干扰系统的性能。还需要避免使用

过量的索引与 cluster 表，因为这些结构会使插入和更新操作变慢。

决策支持(DSS)

该类型的应用将大量信息进行提取形成报告，协助决策者作出正确的判断。典型的情

况是：决策支持系统将 OLTP 应用收集的大量数据进行查询。典型的应用为客户行为分析

系统(超市，保险等)。

决策支持的关键目标是速度、精确性和可用性。

该种类型的设计往往与 OLTP 设计的理念背道而驰，一般建议使用数据冗余、大量索

引、cluster table、并行查询等。

近年来，该类型的应用逐渐与 OLAP、数据仓库紧密的联系在一起，形成的一个新的

应用方向。

第 3 章 SQL 语句处理的过程

在调整之前我们需要了解一些背景知识，只有知道这些背景知识，我们才能更好的

去调整 sql 语句。

本节介绍了 SQL 语句处理的基本过程，主要包括：

• 查询语句处理

• DML 语句处理(insert, update, delete)

• DDL 语句处理(create .. , drop .. , alter .. , )

• 事务控制(commit, rollback)

SQL 语句的执行过程(SQL Statement Execution)

图 3-1 概要的列出了处理和运行一个 sql 语句的需要各个重要阶段。在某些情况下，

Oracle 运行 sql 的过程可能与下面列出的各个阶段的顺序有所不同。如 DEFINE 阶段可能在

FETCH 阶段之前，这主要依赖你如何书写代码。

剩余49页未读，继续阅读

tiantom

粉丝: 6
资源: 87

会员权益专享

通过分析sql语句的执行计划优化sql

通过分析SQL语句的执行计划优化SQL(总结)

SqlServer性能优化方面的总结归纳和实战

SQLServer性能调优之执行计划第一次实践

分析postgresql sql语句执行计划

Oracle中查看SQL语句的执行计划

oracle 获取曾经执行过的SQL语句执行计划

oceanbase查看sql语句的执行计划

为什么使用SQL语句生成执行计划是可优化的

分析oraclesql语句

spark sql(11)sql语句执行流程源码

mysql的sql语句如何执行

耗时多久的SQL语句是慢SQL

oracle sql语句执行过程

mysql sql语句性能优化

springboot怎样等待mybqtis的sql语句执行完毕执行下一个sql语句

explain分析sql语句

oracle如何查看sql的执行计划

sqlyog sql语句性能分析

delphi sql 语句分析器

优化oracle sql语句详细

会员权益专享

最新资源