ORACLE-SQL性能调整

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_ROWS为CBO优化器使用的第一种具体的优化方法，是以数据的吞吐量为主要目标，以便可以使用最少的资源完成语句。

(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/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/25269462/viewspace-764768/