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

通过分析SQL语句的执行计划优化SQL
http://www.itpub.net/thread-478999-1-1.html

在DBA的日常工作中，调整个别性能较差的SQL语句时一项富有挑战性的工作。其中的关键在于如何得到SQL语句的执行计划和如何从SQL语句的执行计划中发现问题。
  本文档主要介绍与SQL调整有关的内容，内容涉及多个方面：SQL语句执行的过程、ORACLE优化器，表之间的关联，如何得到SQL执行计划，如何分析执行计划等内容，从而由浅到深的方式了解SQL优化的过程，使大家逐步步入SQL调整之门，

第1章 性能调整综述
第2章 有效的应用设计
第3章 SQL语句处理的过程
第4章 ORACLE的优化器
第5章 ORACLE的执行计划
        访问路径(方法) – access path
        表之间的连接
        如何产生执行计划
        如何分析执行计划
        如何干预执行计划 - - 使用hints提示
        具体案例分析
第6章 其它注意事项
附录
语句, 计划, 优化, 总结, 性能

第1章 性能调整综述

前期阶段（设计、开发阶段）的应用设计应该多考虑性能问题。
应用设计人员需要在设计阶段设置应用的性能期望值。然后在设计和开发期间，应用设计人员应考虑哪些Oracle 特性可以对系统有好处，并使用这些特性。
通过良好的系统设计，你就可以在应用的生命周期中消除性能调整的代价和挫折

[调整目标]
整你的系统的最有效方法如下：
l        当设计系统时考虑性能
l        调整操作系统的硬件和软件
l        识别性能瓶颈
l        确定问题的原因
l        采取纠正的动作

当你正在维护生产库系统时，有多种快速有效的方法来识别性能瓶颈。
不管怎样，调整通常是一系列开销。一旦你已确定了瓶颈，你可能要牺牲一些其它方面的指标来达到所要的结果。例如，如果I/O有问题，你可能需要更多内存或 磁盘。如果不可能买，你可能要限制系统的并发性，来获取所需的性能。然而，如果你已经明确地定义了性能的目标，那用什么来交换高性能的决策就变的很容易 的，因为你已经确定了哪些方面是最重要的，如果目标为高性能，可能牺牲一些空间资源。
要取得最大的投入/效率之比，调整应用的关键部分，使其达到比较高的性能，这样从总体上来说，整个系统的性能也是比较高的。这也就是有名的20/80原则，调整应用的20%(关键部分)，能解决80%的问题。

[在设计和开发系统是作调整]
在设计你的系统时，使用下列优化性能的准则：
l        消除客户机／服务器应用中不必要的网络传输。-- 使用存储过程。
l        使用适合你系统的相应Oracle服务器选件（例如，并行查询或分布式数据库）。
l        除非你的应用有特殊的需要，否则使用缺省的Oracle锁。
l        利用数据库记住应用模块，以便你能以每个模块为基础来追踪性能。
l        选择你的数据块的最佳大小。  – 原则上来说大一些的性能较好。
l        分布你的数据，使得一个节点使用的数据本地存贮在该节点中。

[调整产品系统]
为调整已有的系统，遵从下列步骤：
l        调整操作系统的硬件和软件
l        通过查询V $SESSION_WAIT视图，识别性能的瓶颈，这个动态性能视图列出了造成会话(session)等待的事件。
l        通过分析V $SESSION_WAIT中的数据，决定瓶颈的原因。
l        纠正存在的问题。

【监控应用系统】

这主要是通过监控oracle的动态视图来完成。各种有用的动态视图：如v$sessio{n}_{w}ait,v$session_event等。
第2章 有效的应用设计
我们通常将最常用的应用分为2种类型：联机事务处理类型(OLTP)，决策支持系统(DSS)。
联机事务处理(OLTP)
        该类型的应用是高吞吐量，插入、更新、删除操作比较多的系统，这些系统以不断增长的大容量数据为特征，它们提供给成百用户同时存取，典型的OLTP系统是订票系统，银行的业务系统，订单系统。OTLP的主要目标是可用性、速度、并发性和可恢复性。
        当设计这类系统时，必须确保大量的并发用户不能干扰系统的性能。 还需要避免使用过量的索引与cluster 表，因为这些结构会使插入和更新操作变慢。

决策支持(DSS)
        该类型的应用将大量信息进行提取形成报告，协助决策者作出正确的判断。典型的情况是：决策支持系统将OLTP应用收集的大量数据进行查询。典型的应用为客户行为分析系统(超市，保险等)。
        决策支持的关键目标是速度、精确性和可用性。
        该种类型的设计往往与OLTP设计的理念背道而驰，一般建议使用数据冗余、大量索引、cluster table、并行查询等。
        近年来，该类型的应用逐渐与OLAP、数据仓库紧密的联系在一起，形成的一个新的应用方向。

第3章  SQL语句处理的过程
SQL语句处理的基本过程，主要包括：
·        查询语句处理
·        DML语句处理(insert, update, delete)
·        DDL 语句处理(create … , drop … , alter … , )
·        事务控制(commit, rollback)

SQL 语句的执行过程(SQL Statement Execution)
知道执行的各个阶段还是有必要的，这会帮助你写出更高效的SQL语句来，而且还可以让你猜测出性能差的SQL语句主要是由于哪一个阶段造成的，然后我们针对这个具体的阶段，找出解决的办法。

SQL语句处理的各个阶段
DML语句的处理