ITPub博客

首页 > Linux操作系统 > Linux操作系统 > ORACLE-SQL性能调整

ORACLE-SQL性能调整

原创 Linux操作系统 作者:keungyu 时间:2013-06-25 13:32:32 0 删除 编辑

ORACLE-SQL性能调整

ORACLE的优化器

ORACLE的发展过程中,一共开发过2种类型的优化器:基于规则的优化器和基于代价的优化器。这2种优化器的不同之处关键在于:取得代价的方法与衡量代价的大小不同。

基于规则的优化器 -- Rule Based (Heuristic) Optimization(简称RBO)

比较显著的缺点:在where子句中如果条件列有索引,就直接运用索引,而不会去判断是否用索引更优化等

 

基于代价的优化器 -- Cost Based Optimization(简称CBO)

Oracle把一个代价引擎(Cost Engine)集成到数据库内核中,用来估计每个执行计划需要的代价,一个查询耗费的资源可分为3部分:I/O代价,CPU代价,network代价。

 

1.1   I/O代价是将数据从磁盘读入内存所需的代价。访问数据包括将数据文件中数据块的内容读入到SGA的数据高速缓存中,在一般情况下,该代价是处理一个查询所需要的最主要代价,所以我们在优化时,一个基本原则就是降低查询所产生的I/O总次数。

 

1.2   CPU代价是处理在内存中数据所需要的代价,如一旦数据被读入内存,则我们在识别出我们需要的数据后,在这些数据上执行排序(sort)或连接(join)操作,这需要耗费CPU资源。

 

1.3   对于需要访问跨节点(即通常说的服务器)数据库上数据的查询来说,存在network代价,用来量化传输操作耗费的资源。查询远程表的查询或执行分布式连接的查询会在network代价方面花费比较大。

 

判断当前数据库使用何种优化器:

主要是由optimizer_mode初始化参数决定的。该参数可能的取值为:first_rows_[1 | 10 | 100 | 1000] | first_rows | all_rows | choose | rule。具体解释如下:

(1)RULE为使用RBO优化器。

(2)CHOOSE则是根据实际情况,如果数据字典中包含被引用的表的统计数据,即引用的对象已经被分析,则就使用CBO优化器,否则为RBO优化器。

(3)ALL_ROWSCBO优化器使用的第一种具体的优化方法,是以数据的吞吐量为主要目标,以便可以使用最少的资源完成语句。

(3)FIRST_ROWS为优化器使用的第二种具体的优化方法,是以数据的响应时间为主要目标,以便快速查询出开始的几行数据。

(4)FIRST_ROWS_[1 | 10 | 100 | 1000] 为优化器使用的第三种具体的优化方法,让优化器选择一个能够把响应时间减到最小的查询执行计划,以迅速产生查询结果的前 n 行。该参数为ORACLE 9I新引入的。

ORACLE V7以来,optimizer_mode参数的缺省设置应是"choose",即如果对已分析的表查询的话选择CBO,否则选择RBO。在此种设置中,如果采用了CBO,则缺省为CBO中的all_rows模式。

 

 

 

 

ORACLE的执行计划

 

查找某占用资源较多的SQL

SELECT ADDRESS,

       substr(SQL_TEXT, 1, 20) Text,

       buffer_gets,

       executions,

       buffer_gets / executions AVG

  FROM v$sqlarea

 WHERE executions > 0

   AND buffer_gets > 100000

 ORDER BY 5;

 

(1)指示优化器的方法与目标的hints

ALL_ROWS -- 基于代价的优化器,以吞吐量为目标

FIRST_ROWS(n) -- 基于代价的优化器,以响应时间为目标

CHOOSE -- 根据是否有统计信息,选择不同的优化器

RULE -- 使用基于规则的优化器

例子:

SELECT /*+ FIRST_ROWS(10) */ employee_id, last_name, salary, job_id

FROM employees

WHERE department_id = 20;

 

SELECT /*+ CHOOSE */ employee_id, last_name, salary, job_id

FROM employees

WHERE employee_id = 7566;

SELECT /*+ RULE */ employee_id, last_name, salary, job_id

FROM employees

WHERE employee_id = 7566;

 

(2)指示存储路径的hints

FULL /*+ FULL ( table ) */

指定该表使用全表扫描

ROWID /*+ ROWID ( table ) */

指定对该表使用rowid存取方法,该提示用的较少

INDEX /*+ INDEX ( table [index]) */

使用该表上指定的索引对表进行索引扫描

INDEX_FFS /*+ INDEX_FFS ( table [index]) */

使用快速全表扫描

NO_INDEX /*+ NO_INDEX ( table [index]) */

不使用该表上指定的索引进行存取,仍然可以使用其它的索引进行索引扫描

SELECT /*+ FULL(e) */ employee_id, last_name

FROM employees e

WHERE last_name LIKE :b1;

SELECT /*+ROWID(employees)*/ *

FROM employees

WHERE rowid > 'AAAAtkAABAAAFNTAAA' AND employee_id = 155;

SELECT /*+ INDEX(A sex_index) use sex_index because there are few

male patients */ A.name, A.height, A.weight

FROM patients A

WHERE A.sex = ’m’;

SELECT /*+NO_INDEX(employees emp_empid)*/ employee_id

FROM employees

WHERE employee_id > 200;

 

(3)指示连接顺序的hints:

ORDERED /*+ ORDERED */

from 字句中表的顺序从左到右的连接

STAR /*+ STAR */

指示优化器使用星型查询

 

SELECT /*+ORDERED */ o.order_id, c.customer_id, l.unit_price * l.quantity

FROM customers c, order_items l, orders o

WHERE c.cust_last_name = :b1

AND o.customer_id = c.customer_id

AND o.order_id = l.order_id;

/*+ ORDERED USE_NL(FACTS) INDEX(facts fact_concat) */

 

(4)指示连接类型的hints

USE_NL /*+ USE_NL ( table [,table, ...] ) */

使用嵌套连接

USE_MERGE /*+ USE_MERGE ( table [,table, ...]) */

使用排序- -合并连接

USE_HASH /*+ USE_HASH ( table [,table, ...]) */

使用HASH连接

注意:如果表有alias(别名),则上面的table指的是表的别名,而不是真实的表名

 

优化的总结:

1) CBO选择了一个次优化的执行计划时, 不要同CBO过意不去, 先采取如下措施:

         a) 检查是否在表与索引上又最新的统计数据

         analyze table a compute statistics;

analyze index inx_col12A compute statistics;

         b) 对所有的数据进行分析,而不是只分析一部分数据

         c) 检查是否引用的数据字典表,在oracle 10G之前,缺省情况下是不对数据字典表进行分析的。

         d) 试试RBO优化器,看语句执行的效率如何,有时RBO能比CBO产生的更好的执行计划

         e) 如果还不行,跟踪该语句的执行,生成trace信息,然后用tkprof格式化trace信息,这样可以得到全面的供优化的信息。

2) 假如利用附录的方法对另一个会话进行trace,则该会话应该为专用连接

3) 不要认为绑定变量(bind variables)的缺点只有书写麻烦,而优点多多,实际上使用绑定

变量虽然避免了重复parse,但是它导致优化器不能使用数据库中的列统计,从而选择了较差的执行计划。而使用硬编码的SQL则可以使用列统计。当然随着CBO功能的越来越强,这种情况会得到改善。目前就已经实现了在第一次运行绑定变量的sql语句时,考虑列统计。

4) 如果一个row source 超过10000行数据,则可以被认为大row source

5) (+)的表不是driving table,注意:如果有外联接,而且order hint指定的顺序与外联结决定的顺序冲突,则忽略order hint

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/25269462/viewspace-764768/,如需转载,请注明出处,否则将追究法律责任。

上一篇: 表监控 -- dbms_fga
请登录后发表评论 登录
全部评论

注册时间:2012-08-31

  • 博文量
    15
  • 访问量
    41080