Oracle sql性能的调整-总结

前言本文档主要介绍与SQL调整有关的内容，内容涉及多个方面：SQL语句执行的过程、ORACLE优化器，表之间的关联，如何得到SQL执行计划，如何分析执行计划等内容，从而由浅到深的方式了解SQL优化的过程，使大家逐步步入SQL调整之门，然后你将发现……。该文档的不当之处，敬请指出，以便进一步改正。请将其发往我的信箱：[e-mail]。如果引用本文的内容，请著名出处！作者：徐玉金MSN：[e-mail]:[e-mail]日期：2005.12.12活跃于:的执行计划访问路径(方法)--accesspath表之间的连接如何产生执行计划如何分析执行计划如何干预执行计划--使用hints提示具体案例分析第6章其它注意事项附录2第1章性能调整综述Oracle数据库是高度可调的数据库产品。本章描述调整的过程和那些人员应与Oracle服务器的调整有关，以及与调整相关联的操作系统硬件和软件。本章包括以下方面:谁来调整系统?什么时候调整?建立有效调整的目标在设计和开发时的调整调整产品系统监控产品系统谁来调整系统:为了有效地调整系统，若干类人员必须交换信息并牵涉到系统调整中，例如:应用设计人员必须传达应用系统的设计，使得每个人都清楚应用中的数据流动.应用开发人员必须传达他们选择的实现策略，使得语句调整的过程中能快速、容易地识别有问题的应用模块和可疑的SQL语句.数据库管理人员必须仔细地监控系统活动并提供它们的资料，使得异常的系统性能可被快速得识别和纠正.硬件/软件管理人员必须传达系统的硬件、软件配置并提供它们的资料，使得相关人员能有效地设计和管理系统。简而言之，与系统涉及的每个人都在调整过程中起某些作用，当上面提及的那些人员传达了系统的特性并提供了它们的资料，调整就能相对的容易和更快一些。不幸的是，事实上的结果是：数据库管理员对调整负有全部或主要的责任。但是，数据库管理员很少有合适的系统方面的资料，而且，在很多情况下，数据库管理员往往是在实施阶段才介入数据库，这就给调整工作带来许多负面的影响，因为在设计阶段的缺陷是不能通过DBA的调整而得以解决，而设计阶段的缺陷往往对数据库性能造成极大的影响。其实，在真正成熟的开发环境下，开发人员作为纯代码编写人员时，对性能的影响昀小，此时大部分的工作应由应用设计人员完成，而且数据库管理员往往在前期的需求管理阶段就介入，为设计人员提供必要的技术支持。调整并不是数据库管理员的专利，相反大部分应该是设计人员和开发人员的工作，这就需要设计人员和开发人员具体必要的数据库知识，这样才能组成一个高效的团队，然而事实上往往并非如此。什么时候作调整？多数人认为当用户感觉性能差时才进行调整，这对调整过程中使用某些昀有效的调整策略来说往往是太迟了。此时，如果你不愿意重新设计应用的话，你只能通过重新分配内存(调整SGA)和调整I/O的办法或多或少地提高性能。Oracle提供了许多特性，这些特性只有应用到正确地设计的系统中时才能够很大地提高性能。应用设计人员需要在设计阶段设置应用的性能期望值。然后在设计和开发期间，应用设计人员应考虑哪些Oracle特性可以对系统有好处，并使用这些特性。通过良好的系统设计，你就可以在应用的生命周期中消除性能调整的代价和挫折。图1-1图1-2说明在应用的生命周期中调整的相对代价和收益，正如你见到的，昀有效的调整3时间是在设计阶段。在设计期间的调整能以昀低的代价给你昀大的收益。ProductionDevelopmentDesignCOSTTIME图1-1在应用生命周期中调整的代价ProductionDevelopmentDesignBenefitTime图1-2在应用生命周期中调整的收益当然，即使在设计很好的系统中，也可能有性能降低。但这些性能降低应该是可控的和可以预见的。调整目标不管你正在设计或维护系统，你应该建立专门的性能目标，它使你知道何时要作调整。如果你试图胡乱地改动初始化参数或SQl语句，你可能会浪费调整系统的时间，而且无什么大的收益。调整你的系统的昀有效方法如下：当设计系统时考虑性能调整操作系统的硬件和软件识别性能瓶颈4确定问题的原因采取纠正的动作当你设计系统时，制定专门的目标；例如，响应时间小于３秒。当应用不能满足此目标时，识别造成变慢的瓶颈（例如，I/O竞争），确定原因，采取纠正动作。在开发期间，你应测试应用研究，确定在采取应用之前是否满足设计的性能目标。当你正在维护生产库系统时，有多种快速有效的方法来识别性能瓶颈。不管怎样，调整通常是一系列开销。一旦你已确定了瓶颈，你可能要牺牲一些其它方面的指标来达到所要的结果。例如，如果I/O有问题，你可能需要更多内存或磁盘。如果不可能买，你可能要限制系统的并发性，来获取所需的性能。然而，如果你已经明确地定义了性能的目标，那用什么来交换高性能的决策就变的很容易的，因为你已经确定了哪些方面是昀重要的，如过我的目标为高性能，可能牺牲一些空间资源。随着应用的越来越庞大，硬件性能的提高，全面的调整应用逐渐变成代价高昂的行为，在这样情况下，要取得昀大的投入/效率之比，较好的办法是调整应用的关键部分，使其达到比较高的性能，这样从总体上来说，整个系统的性能也是比较高的。这也就是有名的20/80原则，调整应用的20%(关键部分)，能解决80%的问题。在设计和开发系统时作调整良好设计的系统可以防止在应用生命周期中产生性能问题。系统设计人员和应用开发人员必须了解Oracle的查询处理机制以便写出高效的SQL语句。“第2章有效的应用设计”讨论了你的系统中各种可用的配置，以及每种配置更适合哪种类型的应用。“第5章优化器”讨论了Oracle的查询优化器，以及如何写语句以获取昀快的结果。当设计你的系统时，使用下列优化性能的准则：消除客户机／服务器应用中不必要的网络传输。--使用存储过程。使用适合你系统的相应Oracle服务器选件（例如，并行查询或分布式数据库）。除非你的应用有特殊的需要，否则使用缺省的Oracle锁。利用数据库记住应用模块，以便你能以每个模块为基础来追踪性能。选择你的数据块的昀佳大小。--原则上来说大一些的性能较好。分布你的数据，使得一个节点使用的数据本地存贮在该节点中。调整产品系统本节描述对应用系统快速、容易地找出性能瓶颈，并决定纠正动作的方法。这种方法依赖于对Oracle服务器体系结构和特性的了解程度。在试图调整你的系统前，你应熟悉Oracle调整的内容。为调整你已有的系统，遵从下列步骤：调整操作系统的硬件和软件通过查询V$SESSION_WAIT视图，识别性能的瓶颈，这个动态性能视图列出了造成会话(session)等待的事件。通过分析V$SESSION_WAIT中的数据，决定瓶颈的原因。纠正存在的问题。监控应用系统这主要是通过监控oracle的动态视图来完成。各种有用的动态视图：如v$session_wait,v$session_event等。5第2章有效的应用设计我们通常将昀常用的应用分为2种类型：联机事务处理类型(OLTP)，决策支持系统(DSS)。联机事务处理(OLTP)该类型的应用是高吞吐量，插入、更新、删除操作比较多的系统，这些系统以不断增长的大容量数据为特征，它们提供给成百用户同时存取，典型的OLTP系统是订票系统，银行的业务系统，订单系统。OTLP的主要目标是可用性、速度、并发性和可恢复性。当设计这类系统时，必须确保大量的并发用户不能干扰系统的性能。还需要避免使用过量的索引与cluster表，因为这些结构会使插入和更新操作变慢。决策支持(DSS)该类型的应用将大量信息进行提取形成报告，协助决策者作出正确的判断。典型的情况是：决策支持系统将OLTP应用收集的大量数据进行查询。典型的应用为客户行为分析系统(超市，保险等)。决策支持的关键目标是速度、精确性和可用性。该种类型的设计往往与OLTP设计的理念背道而驰，一般建议使用数据冗余、大量索引、clustertable、并行查询等。近年来，该类型的应用逐渐与OLAP、数据仓库紧密的联系在一起，形成的一个新的应用方向。6第3章SQL语句处理的过程在调整之前我们需要了解一些背景知识，只有知道这些背景知识，我们才能更好的去调整sql语句。本节介绍了SQL语句处理的基本过程，主要包括：•查询语句处理•DML语句处理(insert,update,delete)•DDL语句处理(create..,drop..,alter..,)•事务控制(commit,rollback)SQL语句的执行过程(SQLStatementExecution)图3-1概要的列出了处理和运行一个sql语句的需要各个重要阶段。在某些情况下，Oracle运行sql的过程可能与下面列出的各个阶段的顺序有所不同。如DEFINE阶段可能在FETCH阶段之前，这主要依赖你如何书写代码。对许多oracle的工具来说，其中某些阶段会自动执行。绝大多数用户不需要关心各个阶段的细节问题，然而，知道执行的各个阶段还是有必要的，这会帮助你写出更高效的SQL语句来，而且还可以让你猜测出性能差的SQL语句主要是由于哪一个阶段造成的，然后我们针对这个具体的阶段，找出解决的办法。7图3-1SQL语句处理的各个阶段DML语句的处理本节给出一个例子来说明在DML语句处理的各个阶段到底发生了什么事情。假设你使用Pro*C程序来为指定部门的所有职员增加工资。程序已经连到正确的用户，你可以在你的程序中嵌入如下的SQL语句：EXECSQLUPDATEemployeesSETsalary=1.10*salaryWHEREdepartment_id=:var_department_id;8var_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语句，验证它是合法的语句，即书写正确实现数据字典的查找，以验证是否符合表和列的定义在所要求的对象上获取语法分析锁，使得在语句的语法分析过程中不改变这些对象的定义验证为存取所涉及的模式对象所需的权限是否满足9决定此语句昀佳的执行计划将它装入共享SQL区对分布的语句来说，把语句的全部或部分路由到包含所涉及数据的远程节点以上任何一步出现错误，都将导致语句报错，中止执行。只有在共享池中不存在等价SQL语句的情