优化SQL的另一种思维

优化SQL的另一种思维目录一、性能调整综述...................................................................................................................2二、有效的应用设计...............................................................................................................2三、SQL语句处理的过程........................................................................................................3四、ORACLE的优化器.............................................................................................................9五、ORACLE的执行计划.......................................................................................................16背景知识.................................................................................................................................16执行计划之访问路径.............................................................................................................25执行计划之表之间的连接.....................................................................................................32如何产生ORACLE执行计划..................................................................................................39如何分析ORACLE执行计划..................................................................................................43如何干预ORACLE执行计划..................................................................................................53具体测试实例.........................................................................................................................58执行计划之具体案例分析.....................................................................................................65六、其它注意事项.................................................................................................................72七、整体实例分析.................................................................................................................75客户端程序优化SQL步骤.....................................................................................................75tkprof程序整体实例解说......................................................................................................78优化SQL的另一种思维系列文章是作者用了快一年的时间完成的SQL优化文章，它的原理实际上是通过分析SQL语句的执行计划来实现对整体SQL进行优化的，这是多年项目经验的总结，由于考虑到目前国内的Oracle部署还基本维持在Oracle8和Oracle9的水平上，所以里面的模拟环境都是在Oracle8.3下进行的，希望可以针对性更强一些。本文是系列连载的第五章：ORACLE的执行计划中的背景知识篇。一、性能调整综述Oracle数据库是高度可调的数据库产品。本章描述调整的过程和哪些人员应与Oracle服务器的调整有关，以及与调整相关联的操作系统硬件和软件。本章包括以下方面:谁来调整系统?什么时候调整?建立有效调整的目标在设计和开发时的调整调整产品系统监控产品系统谁来调整系统:为了有效地调整系统，若干类人员必须交换信息并牵涉到系统调整中，例如:应用设计人员必须传达应用系统的设计，使得每个人都清楚应用中的数据流动.应用开发人员必须传达他们选择的实现策略，使得语句调整的过程中能快速、容易地识别有问题的应用模块和可疑的SQL语句.数据库管理人员必须仔细地监控系统活动并提供它们的资料，使得异常的系统性能可被快速得识别和纠正.硬件/软件管理人员必须传达系统的硬件、软件配置并提供它们的资料，使得相关人员能有效地设计和管理系统。二、有效的应用设计我们通常将最常用的应用分为2种类型：联机事务处理类型(OLTP)，决策支持系统(DSS)。联机事务处理(OLTP)该类型的应用是高吞吐量，插入、更新、删除操作比较多的系统，这些系统以不断增长的大容量数据为特征，它们提供给成百用户同时存取，典型的OLTP系统是订票系统，银行的业务系统，订单系统。OTLP的主要目标是可用性、速度、并发性和可恢复性。当设计这类系统时，必须确保大量的并发用户不能干扰系统的性能。还需要避免使用过量的索引与cluster表，因为这些结构会使插入和更新操作变慢。决策支持(DSS)该类型的应用将大量信息进行提取形成报告，协助决策者作出正确的判断。典型的情况是：决策支持系统将OLTP应用收集的大量数据进行查询。典型的应用为客户行为分析系统(超市，保险等)。决策支持的关键目标是速度、精确性和可用性。该种类型的设计往往与OLTP设计的理念背道而驰，一般建议使用数据冗余、大量索引、clustertable、并行查询等。近年来，该类型的应用逐渐与OLAP、数据仓库紧密的联系在一起，形成的一个新的应用方向。三、SQL语句处理的过程在调整之前我们需要了解一些背景知识，只有知道这些背景知识，我们才能更好的去调整SQL语句。本节介绍了SQL语句处理的基本过程，主要包括：·查询语句处理·DML语句处理(insert,update,delete)·DDL语句处理(create..,drop..,alter..,)·事务控制(commit,rollback)SQL语句的执行过程(SQLStatementExecution)在某些情况下，Oracle运行sql的过程可能与下面列出的各个阶段的顺序有所不同。如DEFINE阶段可能在FETCH阶段之前，这主要依赖你如何书写代码。对许多oracle的工具来说，其中某些阶段会自动执行。绝大多数用户不需要关心各个阶段的细节问题，然而，知道执行的各个阶段还是有必要的，这会帮助你写出更高效的SQL语句来，而且还可以让你猜测出性能差的SQL语句主要是由于哪一个阶段造成的，然后我们针对这个具体的阶段，找出解决的办法。DML语句的处理本节给出一个例子来说明在DML语句处理的各个阶段到底发生了什么事情。假设你使用Pro*C程序来为指定部门的所有职员增加工资。程序已经连到正确的用户，你可以在你的程序中嵌入如下的SQL语句：var_department_id是程序变量，里面包含部门号，我们要修改该部门的职员的工资。当这个SQL语句执行时，使用该变量的值。每种类型的语句都需要如下阶段：·第1步:CreateaCursor创建游标·第2步:ParsetheStatement分析语句·第5步:BindAnyVariables绑定变量·第7步:RuntheStatement运行语句·第9步:ClosetheCursor关闭游标如果使用了并行功能，还会包含下面这个阶段：·第6步:ParallelizetheStatement并行执行语句如果是查询语句，则需要以下几个额外的步骤，如图3所示：·第3步:DescribeResultsofaQuery描述查询的结果集·第4步:DefineOutputofaQuery定义查询的输出数据·第8步:FetchRowsofaQuery取查询出来的行下面具体说一下每一步中都发生了什么事情：.第1步:创建游标(CreateaCursor)由程序接口调用创建一个游标(cursor)。任何SQL语句都会创建它，特别在运行DML语句时，都是自动创建游标的，不需要开发人员干预。多数应用中，游标的创建是自动的。然而，在预编译程序(pro*c)中游标的创建，可能是隐含的，也可能显式的创建。在存储过程中也是这样的。第2步:分析语句(ParsetheStatement)在语法分析期间，SQL语句从用户进程传送到Oracle，SQL语句经语法分析后，SQL语句本身与分析的信息都被装入到共享SQL区。在该阶段中，可以解决许多类型的错误。语法分析分别执行下列操作：翻译SQL语句，验证它是合法的语句，即书写正确实现数据字典的查找，以验证是否符合表和列的定义在所要求的对象上获取语法分析锁，使得在语句的语法分析过程中不改变这些对象的定义验证为存取所涉及的模式对象所需的权限是否满足决定此语句最佳的执行计划将它装入共享SQL区对分布的语句来说，把语句的全部或部分路由到包含所涉及数据的远程节点以上任何一步出现错误，都将导致语句报错，中止执行。只有在共享池中不存在等价SQL语句的情况下，才对SQL语句作语法分析。在这种情况下，数据库内核重新为该语句分配新的共享SQL区，并对语句进行语法分析。进行语法分析需要耗费较多的资源，所以要尽量避免进行语法分析，这是优化的技巧之一。语法分析阶段包含了不管此语句将执行多少次，而只需分析一次的处理要求。Oracle只对每个SQL语句翻译一次，在以后再次执行该语句时，只要该语句还在共享SQL区中，就可以避免对该语句重新进行语法分析，也就是此时可以直接使用其对应的执行计划对数据进行存取。这主要是通过绑定变量(bindvariable)实现的，也就是我们常说的共享SQL，后面会给出共享SQL的概念。虽然语法分析验证了SQL语句的正确性，但语法分析只能识别在SQL语句执行之前所能发现的错误(如书写错误、权限不足等)。因此，有些错误通过语法分析是抓不到的。例如，在数据转换中的错误或在数据中的错(如企图在主键中插入重复的值)以及死锁等均是只有在语句执行阶段期间才能遇到和报告的错误或情况。查询语句的处理查询与其它类型的SQL语句不同，因为在成功执行后作为结果将返回数据。其它语句只是简单地返回成功或失败，而查询则能返