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参数说明了

生成跟踪文件的目录