性能优化培训文档

Oracle项目部专题知识总结提炼性能优化培训文档Author:Wenfeng.ZhangCreationDate:September16,2019LastUpdated:September16,2019September16,2019DocumentRef:Version:DRAFT1AApprovals:第2页共56页DocumentControlChangeRecord3日期作者版本变更说明ZHASeptember16,2019Zhangwenfeng1.0NoPreviousDocumentReviewers姓名职位Distribution拷贝编号姓名位置/岗位1234第3页共56页第4页共56页性能优化简介性能优化简介本文档主要介绍与SQL调整有关的内容，内容涉及多个方面：SQL语句执行的过程、ORACLE优化器，表之间的关联，如何得到SQL执行计划，如何分析执行计划等内容，从而由浅到深的方式了解SQL优化的过程，使大家逐步步入SQL调整之门，然后你将发现……第1章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语句主要是由于哪一个阶段造成的，然后我们针对这个具体的阶段，找出解决的办法。第5页共56页7第6页共56页图3-1SQL语句处理的各个阶段DML语句的处理本节给出一个例子来说明在DML语句处理的各个阶段到底发生了什么事情。假设你使用Pro*C程序来为指定部门的所有职员增加工资。程序已经连到正确的用户，你可以在你的程序中嵌入如下的SQL语句：EXECSQLUPDATEemployeesSETsalary=1.10*salaryWHEREdepartment_id=:var_department_id;8第7页共56页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区。在该阶段中，可以解决许多类型的错误。语法分析分别执行下列操作：第8页共56页翻译SQL语句，验证它是合法的语句，即书写正确实现数据字典的查找，以验证是否符合表和列的定义在所要求的对象上获取语法分析锁，使得在语句的语法分析过程中不改变这些对象的定义验证为存取所涉及的模式对象所需的权限是否满足9第9页共56页决定此语句最佳的执行计划将它装入共享SQL区对分布的语句来说，把语句的全部或部分路由到包含所涉及数据的远程节点以上任何一步出现错误，都将导致语句报错，中止执行。只有在共享池中不存在等价SQL语句的情况下，才对SQL语句作语法分析。在这种情况下，数据库内核重新为该语句分配新的共享SQL区，并对语句进行语法分析。进行语法分析需要耗费较多的资源，所以要尽量避免进行语法分析，这是优化的技巧之一。语法分析阶段包含了不管此语句将执行多少次，而只需分析一次的处理要求。Oracle只对每个SQL语句翻译一次，在以后再次执行该语句时，只要该语句还在共享SQL区中，就可以避免对该语句重新进行语法分析，也就是此时可以直接使用其对应的执行计划对数据进行存取。这主要是通过绑定变量(bindvariable)实现的，也就是我们常说的共享SQL，后面会给出共享SQL的概念。虽然语法分析验证了SQL语句的正确性，但语法分析只能识别在SQL语句执行之前所能发现的错误(如书写错误、权限不足等)。因此，有些错误通过语法分析是抓不到的。例如，在数据转换中的错误或在数据中的错（如企图在主键中插入重复的值）以及死锁等均是只有在语句执行阶段期间才能遇到和报告的错误或情况。查询语句的处理查询与其它类型的SQL语句不同，因为在成功执行后作为结果将返回数据。其它语句只是简单地返回成功或失败，而查询则能返回一行或许多行数据。查询的结果均采用表格形式，结果行被一次一行或者批量地被检索出来。从这里我们可以得知批量的fetch数据可以降低网络开销，所以批量的fetch也是优化的技巧之一。有些问题只与查询处理相关，查询不仅仅指SELECT语句，同样也包括在其它SQL语句中的隐含查询。例如，下面的每个语句都需要把查询作为它执行的一部分：INSERTINTOtableSELECT...UPDATEtableSETx=yWHERE...DELETEFROMtableWHERE...CREATEtableASSELECT...具体来说，查询•要求读一致性•可能使用回滚段作中间处理第10页共56页•可能要求SQL语句处理描述、定义和取数据阶段第3步:描述查询结果(DescribeResultsofaQuery)描述阶段只有在查询结果的各个列是未知时才需要；例如，当查询由用户交互地输入需要输出的列名。在这种情况要用描述阶段来决定查询结果的特征（数据类型，长度和名字）。第4步:定义查询的输出数据(DefineOutputofaQuery)在查询的定义阶段，你指定与查询出的列值对应的接收变量的位置、大小和数据类型，这样我们通过接收变量就可以得到查询结果。如果必要的话，Oracle会自动实现数据类型的转换。这是将接收变量的类型与对应的列类型相比较决定的。10第11页共56页第5步:绑定变量(BindAnyVariables)此时，Oracle知道了SQL语句的意思，但仍没有足够的信息用于执行该语句。Oracle需要得到在语句中列出的所有变量的值。在该例中，Oracle需要得到对department_id列进行限定的值。得到这个值的过程就叫绑定变量(bindingvariables)此过程称之为将变量值捆绑进来。程序必须指出可以找到该数值的变量名（该变量被称为捆绑变量，变量名实质上是一个内存地址，相当于指针）。应用的最终用户可能并没有发觉他们正在指定捆绑变量，因为Oracle的程序可能只是简单地指示他们输入新的值，其实这一切都在程序中自动做了。因为你指定了变量名，在你再次执行之前无须重新捆绑变量。你可以改变绑定变量的值，而Oracle在每次执行时，仅仅使用内存地址来查找此值。如果Oracle需要实现自动数据类型转换的话（除非它们是隐含的或缺省的），你还必须对每个值指定数据类型和长度。关于这些信息可以参考oracle的相关文档，如OracleCallInterfaceProgrammer'sGuide第6步:并行执行语句(ParallelizetheStatement)ORACLE可以在SELECTs,INSERTs,UPDATEs,MERGEs,DELETEs语句中执行相应并行查询操作，对于某些DDL操作，如创建索引、用子查询创建表、在分区表上的操作，也可以执行并行操作。并行化可以导致多个服务器进程(oracleserverprocesses)为同一个SQL语句工作，使该SQL语句可以快速完成，但是会耗费更多的资源，所以除非很有必要，否则不要使用并行查询。第7步:执行语句(RuntheStatement)到了现在这个时候，Oracle拥有所有需要的信息与资源，因此可以真正运行SQL语句了。如果该语句为SELECT查询或INSERT语句，则不需要锁定任何行，因为没有数据需要被改变。然而，如果语句为UPDATE或DELETE语句，则该语句影响的所有行都被锁定，防止该用户提交或回滚之前，别的用户对这些数据进行修改。这保证了数据的一致性。对于某些语句，你可以指定执行的次数，这称为批处理(arrayprocessing)。指定执行N次，则绑定变量与定义变量被定义为大小为N的数组的开始位置，这种方法可以减少网络开销，也是优化的技巧之一。第8步:取出查询的行(FetchRowsofaQuery)在fetch阶段，行数据被取出来，每个后续的存取操作检索结果集中的下一行数据，直到最后一行被取出来。上面提到过，批量的fetch是优化的技巧之一。第9步:关闭游标(ClosetheCursor)SQL语句处理的最后一个阶段就是关闭游标DDL语句的处理(DDLStatementProcessing)11第12页共56页DDL语句的执行不同与DML语句和查询语句的执行，这是因为DDL语句执行成功后需要对数据字典数据进行修改。对于DDL语句，语句的分析阶段实际上包括分析、查找数据字典信息和执行。事务管理语句、会话管理语句、系统管理语句只有分析与执行阶段，为了重新执行该语句，会重新分析与执行该语句。事务控制(ControlofTransactions)一般来说，只有使用ORACLE编程接口的应用设计人员才关心操作的类型，并把相关的操作组织在一起，形成一个事务。一般来说，我门必须定义事务，这样在一个逻辑单元中的所有工作可以同时被提交或回滚，保证了数据的一致性。一个事务应该由逻辑单元中的所有必须部分组成，不应该多一个，也不应该少一个。•在事务开始和结束的这段时间内，所有被引用表中的数据都应该在一致的状态(或可以被回溯到一致的状态)•事务应该只包含可以对数据进行一致更改(oneconsistentchangetothedata)的SQL语句例如，在两个帐号之间的转帐(这是一个事务或逻辑工作单元)，应该包含从一个帐号中借钱(由一个SQL完成)，然后将借的钱存入另一个帐号(由另一个SQL完成)。这2个操作作为一个逻辑单元，应该同时成功或同时失败。其它不相关的操作，如向一个帐户中存钱，不应该包含在这个转帐事务中。在设计应用时，除了需要决定哪种类型的操作组成一个事务外，还需要决定使用BEGIN_DISCRETE_TRANSACTIO存储过程是否对提高小的、非分布式的事务的性能有作用。第2章ORACLE的优化器优化器有时也被称为查询优化器，这是因为查询是影响数据库性能最主要的部分，不要以为只有SELECT语句是查询。实际上，带有任何WHE