ORACLE-SQL性能优化(4）

优化 Tools **

SQL 语句的执行步骤

  语法分析 ，分析语句的语法是否符合规范，衡量语句中各表达式的意义。

  语义分析 ，检查语句中涉及的所有数据库对象是否存在，且用户

有相应的权限。

  视图转换，将涉及视图的查询语句转换为相应的对基表查询语句。  表达式转换， 将复杂的 SQL 表达式转换为较简单的等效连接表达

式。

  选择优化器，不同的优化器一般产生不同的“执行计划”

  选择连接方式， ORACLE 有三种连接方式，对多表连接 ORACLE

可选择适当的连接方式。

  选择连接顺序， 对多表连接 ORACLE 选择哪一对表先连接，选择

这两表中哪个表做为源数据表。

  选择数据的搜索路径， 根据以上条件选择合适的数据搜索路径，如是选用全表搜索还是利用索引或是其他的方式。

  运行“执行计划”

优化器与执行计划

Oracle在执行一个SQL之前,首先要分析一下语句的执行计划,然后再按执行计划去执行。分析语句的执行计划的工作是由优化器(Optimizer)来完成的

Oracle的优化器共有两种的优化方式,即基于规则的优化方式(Rule-Based Optimization,简称为RBO)和基于代价的优化方式(Cost-Based Optimization,简称为CBO)。

A、RBO方式：优化器在分析SQL语句时,所遵循的是Oracle内部预定的一些规则。比如我们常见的,当一个where子句中的一列有索引时去走

索引。

B、CBO方式：是看语句的代价(Cost)了,这里的代价主要指Cpu和内存。优化器在判断是否用这种方式时,主要参照的是表及索引的统计信息，

很多的时侯过期统计信息会令优化器做出一个错误的执行计划在Oracle8及以后的版本,Oracle推荐用CBO的方式。

在Oracle10g中,取消了RBO的支持。

优化器与执行计划

Rule:即走基于规则的方式

Choose:默认的情况下Oracle用的便是这种方式。当一个表或或索引有统计信息,则走CBO的方式,如果表或索引没统计信息,表又不是特别的小,而且相应的列有索引时,那么就走索引,走RBO的方式

First Rows:它与Choose方式是类似的,所不同的是当一个表有统计信息时,它将是以最快的方式返回查询的最先的几行,从总体上减少了响应时

间

All Rows:all_rows是oracle优化器默认的模式,它将选择一种在最短时间内返回所有数据的执行计划,它将基于整体成本的考虑.

first_rows_n:first_rows_n是根据成本而不是基于硬编码的规则来选择执行计划.n可以是1,10,100,1000或者直接用first_rows(n) hint指定任意正数.这里的n是我们想获取结果集的前n条记录,这种需求在很多分页语句的需求中会碰到.

用EXPLAIN PLAN 分析SQL语句

EXPLAIN PLAN 是一个很好的分析SQL语句的工具,它甚至可以在不执行SQL的情况下分析语句. 通过分析,我们就可以知道ORACLE是怎么样连接表,使用什么方式扫描表(索引扫描或全表扫描)以及使用到的索引名称.

你需要按照从里到外,从上到下的次序解读分析的结果. EXPLAIN PLAN分析的结果是用缩进的格式排列的, 最内部

的操作将被最先解读, 如果两个操作处于同一层中,带有最小操作号的将被首先执行.

NESTED LOOP是少数不按照上述规则处理的操作, 正确的执行路径是检查对NESTED LOOP提供数据的操作,其中操作号最小的将被最先处理.

Autotrace 解读

Current mode:     对于修改的数据从数据段中读

Read-consistent mode:   读一致性模式

Physical block:    物理块（如8192字节）

Recursive calls:   嵌套调用次数

使用TKPROF 工具

SQL trace 工具收集正在执行的SQL的性能状态数据并记录到一个跟踪文件中. 这个跟踪文件提供了许多有用的信息,例如解析次数.执行次数,CPU使用时间等.这些数据将可以用来优化你的系统.

设置SQL TRACE在会话级别: 有效

ALTER SESSION SET SQL_TRACE TRUE

设置SQL TRACE 在整个数据库有效, 你必须将SQL_TRACE参数在init.ora中设为TRUE, USER_DUMP_DEST参数说明了

生成跟踪文件的目录

再使用TKPROF对TRACE文件进行分析分析结果更加准确、清楚

在SQLPLUS 配置AUTOTRACE

AUTOTRACE 参数	解       释
SET AUTOTRACE OFF	不能获得AUTOTRACE报告. 这是默认的.
SET AUTOTRACE ON EXPLAIN	仅仅显示优化器执行计划的AUTOTRACE 报告
SET AUTOTRACE ON STATISTICS	仅仅显示SQL语句执行的统计结果的AUTOTRACE报告
SET AUTOTRACE ON	包括上面两项内容的AUTOTRACE报告
SET AUTOTRACE TRACEONLY	与SET AUTOTRACE ON类似,所有的统计 和数 据都在，但不可以打印

在SQLPLUS 配置AUTOTRACE

1、 首先创建PLUSTRACE角色并且赋给public：

Sql> @$ORACLE_HOME/sqlplus/admin/plustrce.sql

2、 赋权限给用户

Sql> grant plustrace to public（预赋权的用户名）; 3、以SYSTEM用户创建PLAN_TABLE表

Sql> @$ORACLE_HOME/rdbms/admin/utlxplan.sql Sql> create public synonym plan_table for

plan_table;

Sql> grant all on plan_table to public;

在每个用户下设置AUTOTRACE可显示其执行计划。

优化器与执行计划

SQL> select ename,dname   from emp, dept  where emp.deptno=dept.deptno   and dept.dname in ('ACCOUNTING','RESEARCH','SALES','OPERATIONS ');

Execution Plan

----------------------------------------------------------

1. SELECT STATEMENT Optimizer=CHOOSE
2. 0  NESTED LOOPS
3. 1   TABLE ACCESS (FULL) OF 'EMP'
4. 1   TABLE ACCESS (BY INDEX ROWID) OF 'DEPT'
5. 3     INDEX (UNIQUE SCAN) OF 'PK_DEPT' (UNIQUE)

最起码要解决全表扫描问题

改变where条件的次序一般没有用

目录

1. 优化基础知识
2. 性能调整综述
3. 有效的应用设计
4. SQL语句的处理过程
5. Oracle的优化器
6. Oracle的执行计划

1. 注意事项

性能调整综述

 谁来调整系统? 什么时候调整?

 建立有效调整的目标

 在设计和开发时的调整

谁来调整系统

 应用设计人员必须传达应用系统的设计，使得每个人都清楚应用中的数据流动.

 应用开发人员必须传达他们选择的实现策略，使得语句调整的过程中能快速、容易地识别有问题的应用模块和可疑的SQL语句.

 数据库管理人员必须仔细地监控系统活动并提供它们的资料，使得异常的系统性能可被快速得识别和纠正.

 硬件/软件管理人员必须传达系统的硬件、软件配置并提供它们的资料，使得相关人员能有效地设计和管理系统。

谁来调整系统

  与系统涉及的每个人都在调整过程中起某些作用，当上面提及的那些人员传达了系统的特性并提供了它们的资料，调整就能相对的容易和更快一些。

  事实上的结果是：数据库管理员对调整负有全部或主要的责任。但是，数据库管理员很少有合适的系统方面的资料，而且，在很多情况下，数据库管理员往往是在实施阶段才介入数据库，这就给调整工作带来许多负面的影响，因为在设计阶段的缺陷是不能通过DBA的调整而得以解决，而设计阶段的缺陷往往对数据库性能造成极大的影响。

  在真正成熟的开发环境下，开发人员作为纯代码编写人员时，对性能的影响最小，此时大部分的工作应由应用设计人员完成，而且数据库管理员往往在前期的需求管理阶段就介入，为设计人员提供必要的技术支持。

  调整并不是数据库管理员的专利，相反大部分应该是设计人员和开发人员的工作，这就需要设计人员和开发人员具体必要的数据库知识，这样才能组成一个高效的团队，然而事实上往往并非如此。

什么时候调整系统

 多数人认为当用户感觉性能差时才进行调整，这对调整过

程中使用某些最有效的调整策略来说往往是太迟了。此时，如果你不愿意重新设计应用的话，你只能通过重新分配内

存(调整SGA)和调整I/O的办法或多或少地提高性能。Oracle提供了许多特性，这些特性只有应用到正确地设计的系统中时才能够很大地提高性能。

 应用设计人员需要在设计阶段设置应用的性能期望值。然后在设计和开发期间，应用设计人员应考虑哪些Oracle 特性可以对系统有好处，并使用这些特性。

 通过良好的系统设计，你就可以在应用的生命周期中消除性能调整的代价和挫折。下图说明在应用的生命周期中调整的相对代价和收益，最有效的调整 时间是在设计阶段。在设计期间的调整能以最低的代价给你最大的收益。

什么时候调整系统

 图: 在应用生命周期中调整的代价

什么时候调整系统

图: 在应用生命周期中调整的收益

调整的目标

  不管正在设计或维护系统，应该建立专门的性能目标，它使你知道何时要作调整。调整你的系统的最有效方法如下：

    当设计系统时考虑性能

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

    识别性能瓶颈

    确定问题的原因

    采取纠正的动作

当你设计系统时，制定专门的目标；例如，响应时间小于３秒。当应用不能满足此目标时，识别造成变慢的瓶颈（例如，I/O竞争），确定原因，采取纠正动作。在开发期间，你应测试应用研究，确定在采取应用之前是否满足设计的性能目标。

调整的目标

 调整通常是一系列开销。一旦确定了瓶颈，可能要牺牲一些其它方面的指标来达到所要的结果。例如，如果I/O有问题，你可能需要更多内存或磁盘。如果不可能买，你可能要限制系统的并发性，来获取所需的性能。如果你已经明确地定义了性能的目标，那用什么来交换高性能的决策就变的很容易的，因为已经确定了哪些方面是最重要的，如果我的目标为高性能，可能牺牲一些空间资源。

 随着应用的越来越庞大，硬件性能的提高，全面的调整应用逐渐变成代价高昂的行为，在这样情况下，要取得最大的投入/效率之比，较好的办法是调整应用的关键部分，使其达到比较高的性能，这样从总体上来说，整个系统的性能也是比较高的。这也就是有名的20/80原则，调整应用的20%(关键部分)，能解决80%的问题。

SQL 调整的目标

1. 去掉不必要的大型表的全表扫描。
2. 缓存小型表的全表扫描。
3. 校验优化索引的使用。
4. 检验优化的连接技术。

以上目标任务将占据SQL调整90%以上的工作。

在设计和开发时调整

 良好设计的系统可防止在应用生命周期中产生性能问题。

 系统设计人员和应用开发人员必须了解Oracle的查询处理机制以便写出高效的SQL语句。

 “有效的应用设计”讨论了你的系统中各种可用的配置，以及每种配置更适合哪种类型的应用。

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

在设计和开发时调整

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

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

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

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

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

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

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

目录

1. 优化基础知识
2. 性能调整综述
3. 有效的应用设计
4. SQL语句的处理过程
5. Oracle的优化器
6. Oracle的执行计划

1. 注意事项

有效的应用设计

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

 联机事务处理(OLTP)

 该类型的应用是高吞吐量，插入、更新、删除操作比较多的系统，这些系统以不断增长的大容量数据为特征，它们提供给成百用户同时存取，典型的OLTP系统是订票系统，银行的业务系统，订单系统。OTLP的主要目标是可用性、速度、并发性和可恢复性。

 当设计这类系统时，必须确保大量的并发用户不能干扰系统的性能。还需要避免使用过量的索引与cluster表，因为这些结构会使插入和更新操作变慢。

有效的应用设计

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

 决策支持(DSS)

 该类型的应用将大量信息进行提取形成报告，协助决策者作出正确的判断。典型情况是：决策支持系统将OLTP应用收集的大量数据进行查询。典型的应用为客户行为分析系统(超市，保险等)。

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

 该种类型的设计往往与OLTP设计的理念背道而驰，一般建议使用数据冗余、大量索引、cluster table、并行查询等。

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

目录

1. 优化基础知识
2. 性能调整综述
3. 有效的应用设计
4. SQL语句的处理过程
5. Oracle的优化器
6. Oracle的执行计划

1. 注意事项

SQL语句的处理过程

1. 查询语句处理
2. DML语句处理(insert, update,

delete)

1. DDL 语句处理(create .. , drop .. , alter .. , )
2. 事务控制(commit, rollback)

SQL语句执行过程

如图：

列出了处理和运行一个sql语句的需要各个重

要阶段。在某些情况下，Oracle运行sql的过程

可能与下面列出的各个阶段的顺序有所不同。如DEFINE阶段可能在FETCH阶段之前，这主要依赖你如何书写代码

DML 语句的处理

 假设你使用Pro*C程序来为指定部门的所有职员增加工资。程序已经连到正确的用户，你可以在你的程序中嵌入如下的SQL语句：

EXEC SQL UPDATE employees SET salary = 1.10 * salary

WHERE department_id = :var_department_id;

var_department_id是程序变量，里面包含部门号，我们要修改该部门的职员的工资。当这个SQL语句执行时，使用该

变量的值。

DML 语句的处理

 每种类型的语句都需要如下阶段：

• 第1步: Create a Cursor 创建游标

• 第2步: Parse the Statement 分析语句• 第5步: Bind Any Variables 绑定变量• 第7步: Run the Statement 运行语句

• 第9步: Close the Cursor 关闭游标

 如果使用了并行功能，还会包含下面这个阶段：

• 第6步: Parallelize the Statement 并行执行语句

以上语句处理步骤解释

 第1步: 创建游标(Create a Cursor)

 由程序接口调用创建一个游标（cursor）。任何SQL

语句都会创建它，特别在运行DML语句时，都是自动创建游标的，不需要开发人员干预。多数应用中，游

标的创建是自动的。然而，在预编译程序(pro*c)中游标的创建，可能是隐含的，也可能显式的创建。在存储过程中也是这样的。

 第2步:分析语句(Parse the Statement)

 在语法分析期间，SQL语句从用户进程传送到Oracle，SQL语句经语法分析后，SQL语句本身与分析的信息

都被装入到共享SQL区。在该阶段中，可以解决许多类型的错误。

以上语句处理步骤解释

语法分析分别执行下列操作：

 翻译SQL语句，验证它是合法的语句，即书写正确

 实现数据字典的查找，以验证是否符合表和列的定义

 在所要求的对象上获取语法分析锁，使得在语句的语法分析过程中不改变这些对象的定义

 验证为存取所涉及的模式对象所需的权限是否满足

 决定此语句最佳的执行计划 将它装入共享SQL区

 对分布的语句来说，把语句的全部或部分路由到包含所涉及数据的远程节点

* 以上任何一步出错误，都将导致语句报错，中止执行。

以上语句处理步骤解释

  只有在共享池中不存在等价SQL语句的情况下，才对SQL语句作语法分析。在这种情况下，数据库内核重新为该语句分配新的共享SQL区，并对语句进行语法分析。进行语法分析需要耗费较多的资源，所以要

尽量避免进行语法分析，这是优化的技巧之一。

  语法分析阶段包含了不管此语句将执行多少次，而只需分析一次的处理要求。Oracle只对每个SQL语句翻译一次，在以后再次执行该语句时，只要该语句还在共享SQL区中，就可以避免对该语句重新进行语

法分析，也就是此时可以直接使用其对应的执行计划对数据进行存取。这主要是通过绑定变量(bind variable)实现的，也就是我们常说的共

享SQL，后面会给出共享SQL的概念。

  虽然语法分析验证了SQL语句的正确性，但语法分析只能识别在SQL语句执行之前所能发现的错误(如书写错误、权限不足等)。因此，有些错误通过语法分析是抓不到的。例如，在数据转换中的错误或在数据中的错（如企图在主键中插入重复的值）以及死锁等均是只有在语句执行阶段期间才能遇到和报告的错误或情况。

查询 语句的处理

查询与其它类型的SQL语句不同，因为在成功执行后作为结果将返回数据。

第3步: 描述查询结果(Describe Results of a Query)

描述阶段只有在查询结果的各个列是未知时才需要；例如，当查询由用户交互地输入需要输出的列名。在这种情况要用描述阶段来决定查询结果的特征（数据类型，长度和名字）。

第4步: 定义查询的输出数据(Define Output of a Query)

在查询的定义阶段，你指定与查询出的列值对应的接收变量的位置、大小和数据类型，这样我们通过接收变量就可以得到查询结果。如果必要的话，Oracle会自动实现数据类型的转换。这是将接收变量的类型与对应的列类型相比较决定的。

查询 语句的处理

  第5步: 绑定变量(Bind Any Variables)

 Oracle知道了SQL语句的意思，但仍没有足够的信息用于执行该

语句。Oracle 需要得到在语句中列出的所有变量的值。在该例中，Oracle需要得到对department_id列进行限定的值。得到这个值

的过程就叫绑定变量(binding variables)

 此过程称之为将变量值捆绑进来。程序必须指出可以找到该数值的变量名（该变量被称为捆绑变量，变量名实质上是一个内存地址，相当于指针）。应用的最终用户可能并没有发觉他们正在指定捆绑变量，因为Oracle 的程序可能只是简单地指示他们输入新的值，其实这一切都在程序中自动做了。

 因为你指定了变量名，在你再次执行之前无须重新捆绑变量。你可以改变绑定变量的值，而Oracle在每次执行时，仅仅使用内存地址来查找此值。

 如果Oracle 需要实现自动数据类型转换的话（除非它们是隐含的或缺省的），你还必须对每个值指定数据类型和长度。关于这些信息可以参考oracle的相关文档，如Oracle Call Interface Programmer's Guide

查询 语句的处理

  第6步: 并行执行语句(Parallelize the Statement )

 ORACLE 可以在SELECTs, INSERTs, UPDATEs, MERGEs, DELETEs语句中执行相应并行查询操作，对某些DDL操作，如创建索引、用子查询创建表、在分区表上的操作，可以执行并行操作。并行化可导致多个服务器进程(oracle server processes)为同一个SQL语句工作，使该SQL语句可以快速完成，但是会耗

费更多的资源，所以除非很有必要，否则不要使用并行查询。  第7步: 执行语句(Run the Statement)

 此时，Oracle拥有所有需要的信息与资源，可以真正运行SQL语句了。如果该语句为SELECT查询或INSERT语句，则不需要锁定任

何行，因没有数据需要被改变。如果语句为UPDATE或DELETE语句，则该语句影响的所有行都被锁定，防止该用户提交或回滚之前，

别的用户对这些数据进行修改。这保证了数据的一致性。

 对于某些语句，你可以指定执行的次数，这称为批处理(array processing)。指定执行N次，则绑定变量与定义变量被定义为大小为N的数组的开始位置，这种方法可以减少网络开销，也是优化的技巧之一。

查询 语句的处理

第8步: 取出查询的行(Fetch Rows of

a Query)

在fetch阶段，行数据被取出来，每个后续的存取操作检索结果集中的下一行数据，直到最后一行被取出来。上面提到过，批量的fetch是优化的技巧之一。

第9步: 关闭游标(Close the Cursor)

SQL语句处理的最后一个阶段就是关闭游标。

DDL 语句的处理

 DDL语句的执行不同与DML语句和查询语句的执行，这是因为DDL语句执行成功后需要对数据字典数据进行修改。对于DDL语句，语句的分析阶段包括：分析、查找数据字典信息和执行。

 事务管理语句、会话管理语句、系统管理语句只有分析与执行阶段，为了重新执行该语句，会重新分析与执行该语句。

事务控制

 必须定义事务，这样在一个逻辑单元中的所有工作可以同时被提交或回滚，保证了数据的一致性。一个事务应该由逻辑单元中的所有必须部分组成，不应该多一个，也不应该少一个。

 在事务开始和结束的这段时间内，所有被引用表中的

数据都应该在一致的状态(或可以被回溯到一致的状态) 事务应该只包含可以对数据进行一致更改(one

consistent change to the data)的SQL语句

 Eg:在两个帐号之间的转帐(这是一个事务或逻辑工作单

元)，应该包含从一个帐号中借钱(由一个SQL完成)，然后将借的钱存入另一个帐号(由另一个SQL完成)。这2个操作作为一个逻辑单元，应该同时成功或同时失败。其它不相关的操作，如向一个帐户中存钱，不应该包含在这个转帐事务中。

 在设计应用时，除需要决定哪种类型的操作组成一个事务外，还需要决定使用BEGIN_DISCRETE_TRANSACTIO存储过程是否对提高小的、非分布式的事务的性能有作用。