`
kavy
  • 浏览: 891286 次
  • 性别: Icon_minigender_1
  • 来自: 上海
社区版块
存档分类
最新评论

ORACLE-SQL优化

 
阅读更多

Oracle的优化器共有两种的优化方式,即:

  • 基于规则的优化方式(Rule-Based Optimization,RBO)
  • 基于代价的优化方式(Cost-Based Optimization,CBO)

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

     CBO方式:依词义可知,它是看语句的代价(Cost)了,这里的代价主要指CPU和内存。优化器在判断是否用这种方式时,主要参照的是表及索引的统计信息。统计信息给出表的大小 、有多少行、每行的长度等。在Oracle8及以后的版本,Oracle推荐用CBO的方式。

我们要明确一点,使用索引并不一定是最优的 ,比如一个表只有两行数据,一次IO就可以完成全表的检索,而此时使用索引时则需要两次IO,这时对整个表做全表扫描(full table scan)是最好的。

只要有足够的权限,就能在命令窗口使用命令:show parameter optimizer_mode来看当前优化器使用的是什么模式.例如:

SQL> show parameter optimizer_mode

NAME                                 TYPE        VALUE

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

optimizer_mode                       string      ALL_ROWS

optimizer_mode 参数值共有以下五个:

CHOOSE

采用这个值时,Oracle即可以采用基于规则RBO,也可以采用基于代价的CBO,到底使用那个值,取决于当前SQL的被访问的表中是不是有可以使用的统计信息。 
如果有统计信息,使用基于代价的优化方法CBO。
如果没有统计信息,Oracle就会采用基于规则的优化方法RBO。

可以使用下列的语句来改变当前的优化器模式:

alter system set optimizer_mode=CHOOSE

ALL_ROWS

不管是不是有统计信息,全部采用基于成本的优化方法CBO。

对应的优化器模式更改语句:

alter system set optimizer_mode=ALL_ROWS

FIRST_ROWS_n

不管是不是有统计信息,全部采用基于成本的优化方法CBO,并以最快的速度,返回前N行记录。

FIRST_ROWS

使用成本和试探法相结合的方法,查找一种可以最快返回前面少数行的方法;这个参数主要用于向后兼容。

RULE

这个参数正好和ALL_ROWS相反,不管是不是统计信息,全部采用基于规则的优化方法。

alter system set optimizer_mode=RULE

以上就是oracle9i的几种优化器优化方式

10g只有{ first_rows_n | first_rows | all_rows }这三种,没有rule和choose,first_rows_n中n的取值是[1 | 10 | 100 | 1000].

数据库性能优化实在是一个博大精深的技术,下面只是简单的说一说.


Oracle中SQL语句的解析步骤:

  1. 语法检测。判断一条SQL语句的语法是否符合SQL的规范,比如执行:"selet * from emp",我们就可以看出由于Select关键字少了一个“c”,这条语句就无法通过语法检验的步骤了。
  2. 语义检查。语法正确的SQL语句在解析的第二个步骤就是判断该SQL语句所访问的表及列是否准确,用户是否有权限访问或更改相应的表或列,
  3. 比如这条语句:select * from emp,如果出现类似于"table or view does not exist"这样的错误,就说明该SQL语句无法通过语义检查。
  4. Oracle根据提交的SQL语句获得最优的执行计划(sql plan),并在内存中分配一定的空间保存该语句与对应的执行计划等信息。
  5. 检查共享池中是否有相同的语句存在.当用户第二次请求或多次请求时,Oracle会自动找到先前的语句与执行计划,而不会进行硬解析,而是直接进行软解析(把语句对应的执行计划调出,然后执行),从而减少数据库的分析时间。

Oracle在执行一个SQL之前,首先要分析一下语句的执行计划,然后再按执行计划去执行。分析语句的执行计划的工作是由优化器(Optimizer)来完成的。不同的情况,一条SQL可能有多种执行计划,但在某一时点,一定只有一种执行计划是最优的,花费时间是最少的,然后把它存进共享池.

注意的是:Oracle中只有完全相同的语句,包大小写、空格、换行都要求一样时,才会重复使用以前的分析结果与执行计划。

访问Table的方式

ORACLE 采用两种访问表中记录的方式:

a.  全表扫描

全表扫描就是顺序地访问表中每条记录. ORACLE采用一次读入多个数据块(database block)的方式优化全表扫描.

b.  通过ROWID访问表

你可以采用基于ROWID的访问方式情况,提高访问表的效率, , ROWID包含了表中记录的物理位置信息.ORACLE采

用索引(INDEX)实现了数据和存放数据的物理位置(ROWID)之间的联系. 通常索引提供了快速访问ROWID的方法,

因此那些基于索引列的查询就可以得到性能上的提高.


现在来说一些能够增加效率的方法,为了方便测试,我建立了两个测试表:

IDS_EMP:image,EMPNO上有索引,表中有100W条记录image

IDS_DEPTimage,DEPTNO上有索引,表中记录:image

并且为了保证结果更加真实,在每次执行语句之前,都清空共享池:

alter system flush buffer_cache(注意:需要权限!)

 

1.选择最有效率的表名顺序(只在基于规则的优化器中有效)

首先先设置规则的优化器,使用语句:

alter system set optimizer_mode=RULE;

ORACLE的解析器按照从右到左的顺序处理FROM子句中的表名,因此FROM子句中写在最后的表(我们称它为驱动表或基础表,driving table)将被最先处理. 在FROM子句中包含多个表的情况下,你必须选择记录条数最少的表作为基础表.当ORACLE处理多个表时, 会运用排序及合并的方式连接它们.首先,扫描第一个表(FROM子句中最后的那个表)并对记录进行排序,然后扫描第二个表(FROM子句中最后第二个表),最后将所有从第二个表中检索出的记录与第一个表中合适记录进行合并.

选择记录多的DES_EMP作基础表进行表连接:

image

选择记录少的DES_DEPT作基础表进行表连接:

image

注:我是执行完之后,才把select语句选上,目的是方便大家看.

 

 

2.WHERE子句中的连接顺序

ORACLE采用自下而上的顺序解析WHERE子句,根据这个原理,表之间的连接必须写在其他WHERE条件之前, 那些可以过滤掉最大数量记录的条件必须写在WHERE子句的末尾.

高效:

image

低效:

image

 

 

3.SELECT子句中避免使用*

当你想在SELECT子句中列出所有的COLUMN时,使用动态SQL列引用 ‘*' 是一个方便的方法.不幸的是,这是一个非常低效的方法.
实际上,ORACLE在解析的过程中, 会将'*' 依次转换成所有的列名, 这个工作是通过查询数据字典完成的, 这意味着将耗费更多的时间.

 

 

4.计算记录条数

和一般的观点相反, count(*) 比count(1)稍快 , 当然如果可以通过索引检索,对索引列的计数仍旧是最快的. 例如 COUNT(EMPNO)
(在论坛中曾经对此有过相当热烈的讨论, 这个观点并不十分准确,通过实际的测试,上述三种方法并没有显著的性能差别).

设置基于规则的优化器:alter system set optimizer_mode=RULE

image

image

image

5.用WHERE子句替换HAVING子句

避免使用HAVING子句, HAVING 只会在检索出所有记录之后才对结果集进行过滤. 这个处理需要排序,总计等操作. 如果能通过WHERE子句限制记录的数目,那就能减少这方面的开销.

高效:

image

低效:

image

(HAVING 中的条件一般用于对一些集合函数的比较,如COUNT() 等等. 除此而外,一般的条件应该写在WHERE子句中)

 

6.使用表的别名(Alias)

当在SQL语句中连接多个表时, 请使用表的别名并把别名前缀于每个Column上.这样一来,就可以减少解析的时间并减少那些由Column歧义引起的语法错误.
(Column歧义指的是由于SQL中不同的表具有相同的Column名,当SQL语句中出现这个Column时,SQL解析器无法判断这个Column的归属)

 

 

7.用EXISTS替代IN

在许多基于基础表的查询中,为了满足一个条件,往往需要对另一个表进行联接.在这种情况下, 使用EXISTS(或NOT EXISTS)通常将提高查询的效率.

设置基于规则的优化器:alter system set optimizer_mode=RULE

image

image

 

 

8.用NOT EXISTS替代NOT IN

在子查询中,NOT IN子句将执行一个内部的排序和合并. 无论在哪种情况下,NOT IN都是最低效的 (因为它对子查询中的表执行了一个全表遍历). 为了避免使用NOT IN ,我们可以把它改写成外连接(Outer Joins)或NOT EXISTS.

image

image

9.用表连接替换EXISTS

通常来说 , 采用表连接的方式比EXISTS更有效率

 

10.用EXISTS替换DISTINCT

当提交一个包含一对多表信息(比如部门表和雇员表)的查询时,避免在SELECT子句中使用 DISTINCT. 一般可以考虑用EXIST替换

image

image

11.常量的计算是在语句被优化时一次性完成的,而不是在语句每次执行时

假设要检索月薪大于2000的表达式:

sal > 24000/12
sal > 2000
sal*12 > 24000

如果SQL语句包括第一种情况,优化器会简单地把它转变成第二种。
优化器不会简化跨越比较符的表达式,例如第三条语句,鉴于此,应尽量写用常量跟字段比较检索的表达式,而不要将字段置于表达式当中。否则没有办法优化,比如如果sal上有索引,第一条和第二条就可以使用(执行索引),第三条就难以使用,因为它会把(sal*12)当作一个字段,于是不识别索引

 

 

12.IN、OR子句常会使用工作表,使索引失效

如果不产生大量重复值,可以考虑把子句拆开。拆开的子句中应该包含索引。

 

 

13.消除对大型表行数据的顺序存取

在嵌套查询中,对表的顺序存取对查询效率可能产生致命的影响。比如采用顺序存取策略,一个嵌套3层的查询,如果每层都查询1000行,那么这个查询就要查询 10亿行数据。避免这种情况的主要方法就是对连接的列进行索引。例如,两个表:学生表(学号、姓名、年龄)和选课表(学号、课程号、成绩)。如果两个表要做连接,就要在“学号”这个连接字段上建立索引。
还可以使用并集来避免顺序存取。尽管在所有的检查列上都有索引,但某些形式的WHERE子句强迫优化器使用顺序存取。

使用设置基于基于成本的优化器来测试:alter system set optimizer_mode=ALL_ROWS scope=both

下面的查询将强迫对ide_emp表执行顺序操作:

image

image

注:UNION 操作符用于合并两个或多个 SELECT 语句的结果集。

UNION 内部的 SELECT 语句必须拥有相同数量的列。列也必须拥有相似的数据类型。同时,每条 SELECT 语句中的列的顺序必须相同。

 

 

14.避免相关子查询

一个列的标签同时在主查询和WHERE子句中的查询中出现,那么很可能当主查询中的列值改变之后,子查询必须重新查询一次。查询嵌套层次越多,效率越低,因此应当尽量避免子查询。如果子查询不可避免,那么要在子查询中过滤掉尽可能多的行。

 


转自:http://blog.csdn.net/zhangao0086/article/details/6250777

分享到:
评论

相关推荐

    dba-oracle-sql优化.

    Oracle SQL优化是数据库管理员(DBA)在提升系统性能、减少资源消耗方面的重要工作。SQL优化涉及多个层面,包括查询优化、索引优化、表设计优化以及存储过程和触发器的优化。以下是对"dba-oracle-sql优化"这一主题的...

    Oracle-SQL优化.doc

    ### Oracle-SQL优化知识点详解 #### 一、Oracle优化器的选择与配置 Oracle数据库提供了三种主要的优化器供用户选择: 1. **基于规则的优化器(RULE)**: - 此优化器完全依赖于预设的规则来确定查询的最佳执行...

    oracle-sql优化

    【Oracle SQL优化】是数据库管理中的重要环节,旨在提高系统的整体性能。SQL优化涉及多个方面,包括理解SQL语句的处理过程、Oracle优化器的工作原理、执行计划的获取与分析等。 1. **优化基础知识**:性能管理是...

    Oracle-SQL优化

    一些简单的oracle性能优化,对写sql很有帮助,好的sql对于性能还有效率都有很大影响

    Oracle-SQL优化.docx

    Oracle SQL 优化 Oracle SQL 优化是指对 Oracle 数据库中的 SQL 语句进行优化,以提高查询效率、减少资源占用和提高系统性能。下面是 Oracle SQL 优化的相关知识点: 一、 SQL 语句编写注意问题 1.1 IS NULL 与 ...

    ORACLE-SQL性能优化大全.pdf

    - **SQL优化机制**: - **SQL语句处理过程**:理解SQL语句在Oracle中的处理流程对于优化至关重要。 - **共享SQL区域**:Oracle会在内存的共享池中缓存已执行过的SQL语句,以便后续执行时可以直接使用而无需重新...

    Oracle-SQL优化(内部资料).docx

    Oracle SQL优化是数据库管理中的关键环节,旨在提升查询性能,减少资源消耗,从而提高整体系统效率。以下将从几个核心方面详细阐述Oracle SQL优化的基本概念和技术。 1. 选择合适的优化器 Oracle提供了三种优化器:...

    ORACLE-SQL性能优化(这个很全的)

    ORACLE-SQL性能优化(这个很全的),ORACLE SQL语句优化!

    ORACLE-SQL性能优化(这个很全的).ppt

    ORACLE-SQL性能优化(这个很全的).ppt

    Oracle-SQL.rar_oracle_oracle sql_sql

    在“Oracle-SQL.rar”这个压缩包中,包含了一份名为“Oracle SQL.ppt”的文件,这可能是一个PowerPoint演示文稿,用于详细讲解Oracle数据库的基础知识和SQL语言的使用。下面,我们将深入探讨一些Oracle SQL的基础...

    基于Oracle的SQL优化1

    基于Oracle的SQL优化

    《基于Oracle的SQL优化》PDF版本下载.txt

    根据提供的文件信息,本文将对《基于Oracle的SQL优化》这一主题进行深入解析,包括但不限于SQL优化的重要性、Oracle数据库的特点以及具体的SQL优化方法等。 ### SQL优化的重要性 SQL(Structured Query Language)...

    ORACLE-SQL性能优化(内部培训资料).docx

    ORACLE SQL 性能优化系列 在 ORACLE SQL 中,性能优化是非常重要的一方面。一个良好的性能优化可以提高数据库的运行效率,减少资源的占用和提高用户体验。下面将从多个方面对 ORACLE SQL 的性能优化进行详细的介绍...

    基于Oracle的SQL优化2

    基于Oracle的SQL优化

    OracleSQL的优化.pdf

    Oracle SQL 优化 Oracle SQL 优化是数据库性能优化的关键部分。为了提高数据库的性能,我们需要从五个方面进行调整:去掉不必要的大型表的全表扫描、缓存小型表的全表扫描、检验优化索引的使用、检验优化的连接技术...

    ORACLE-SQL性能优化

    oracle性能调优,sql优化全面讲解优化方法

    Oracle 11g-SQL-优化

    在深入讨论Oracle 11g中SQL优化的知识点之前,首先需要了解Oracle Database 11g是什么。它是甲骨文公司(Oracle Corporation)推出的一款数据库管理系统,作为Oracle数据库的第11个主要版本,具有许多新的特性和增强...

    SQL、T-SQL与PL-SQL的区别

    PL-SQL(Procedural Language-SQL)是一种增加了过程化概念的SQL语言,是Oracle对SQL的扩充。与标准SQL语言相同,PL-SQL也是Oracle客户端工具(如SQL*Plus、Developer/2000等)访问服务器的操作语言。它有标准SQL所...

Global site tag (gtag.js) - Google Analytics