<b>30个Oracle语句优化法则详解-性能调优</b>[Oracle防范]

1.选用合适的Oracle优化器

Oracle的优化器共有3种:

a.RULE(基于法则)

b.COST(基于本钱)

c.CHOOSE(挑选性)

设置缺省的优化器,可以通过对init.ora文件中OPTIMIZER_MODE参数的各种声明,如RULE、COST、CHOOSE、ALL_ROWS、FIRST_ROWS.你当然也在SQL句级或是会话(session)级对其举行覆盖.

为了利用基于本钱的优化器(CBO,Cost-Based Optimizer),你必须常常运行analyze号令,以增添数据库中的对象统计信息(object statistics)的精确性.

假如数据库的优化器情势设置为挑选性(CHOOSE),那么实际的优化器情势将和能否运行过analyze号令有关.假如table已经被analyze过,优化器情势将自动成为CBO,反之,数据库将采取RULE情势的优化器.

在缺省情形下,Oracle采取CHOOSE优化器,为了避免那些不必要的全表扫描(full table scan),你必须尽大概避免利用CHOOSE优化器,而直接采取基于法则大概基于本钱的优化器.

2.拜候Table的方法Oracle采取两种拜候表中记录的方法:

a.全表扫描

全表扫描就是次序地拜候表中每条记录.Oracle采取一次读入多个数据块(database block)的方法优化全表扫描.

b. 通过ROWID拜候表

你可以采取基于ROWID的拜候方法情形,提高拜候表的效率,ROWID包含了表中记录的物理位置信息……Oracle采取索引(INDEX)实现了数据和存放数据的物理位置(ROWID)之间的接洽.普通索引供应了快速拜候ROWID的办法,因此那些基于索引列的查询便可以得到性能上的提高.

3.同享SQL语句

为了不反复解析相同的SQL语句,在第一次解析之后,Oracle将SQL语句存放在内存中.这块位于系统全局区域SGA(system global area)的同享池(shared buffer pool)中的内存可以被全部的数据库用户同享.因此,当你履行一个SQL语句(有时被称为一个游标)时,假如它和之前的履行过的语句完好相同,Oracle就可以很快得到已经被解析的语句以及最好的履行途径.Oracle的这个功效大大地提高了SQL的履行性能并节俭了内存的利用.

惋惜的是Oracle只对简单的表供应高速缓冲(cache buffering) ,这个功效并不实用于多表衔接查询.

数据库管理员必须在init.ora中为这个区域设置符合的参数,当这个内存区域越大,便可以保存更多的语句,当然被同享的大概性也就越大了.

当你向Oracle提交一个SQL语句,Oracle会首先在这块内存中查找相同的语句.

这里需求注明的是,Oracle对二者采纳的是一种严峻匹配,要达成同享,SQL语句必须完好相同(包含空格,换行等).

同享的语句必须满意三个条件:

A.字符级的对比:

当前被履行的语句和同享池中的语句必须完好相同.

比方:

SELECT * FROM EMP;

和下列每一个都差别

SELECT * from EMP;
Select * From Emp;
SELECT * FROM EMP; 

B.两个语句所指的对象必须完好相同:

比方:

用户对象名若何拜候

Jack sal_limit private synonym
Work_city public synonym
Plant_detail public synonym
Jill sal_limit private synonym
Work_city public synonym
Plant_detail table owner 

考虑一下下列SQL语句可否在这两个用户之间同享.

SQL可否同享缘由

select max(sal_cap) from sal_limit;不能每个用户都有一个private synonym - sal_limit,它们是差别的对象

select count(*0 from work_city where sdesc like 'NEW%';能两个用户拜候相同的对象public synonym - work_city

select a.sdesc,b.location from work_city a,plant_detail b where a.city_id = b.city_id不能用户jack通过private synonym拜候plant_detail而jill是表的全部者,对象差别.

C.两个SQL语句中必须利用相同的名字的绑定变量(bind variables)

比方:第一组的两个SQL语句是相同的(可以同享),而第二组中的两个语句是差别的(即便在运行时,赋于差别的绑定变量相同的值)

a.　
select pin ,name from people where pin = :blk1.pin;
select pin ,name from people where pin = :blk1.pin; 

b.
select pin ,name from people where pin = :blk1.ot_ind;
select pin ,name from people where pin = :blk1.ov_ind; 

4.挑选最有效率的表名次序(只在基于法则的优化器中有效)

Oracle的解析器按照从右到左的次序处理FROM子句中的表名,因此FROM子句中写在最后的表(底子表 driving table)将被最早处理.在FROM子句中包含多个表的情形下,你必须挑选记录条数最少的表作为底子表.当Oracle处理多个表时,会应用排序及归并的方法衔接它们.首先,扫描第一个表(FROM子句中最后的那个表)并对记录举行派序,然后扫描第二个表(FROM子句中最后第二个表),最后将全部从第二个表中检索出的记录与第一个表中符合记录举行归并.

比方:

表TAB1 16,384 条记录

表TAB2 1条记录

挑选TAB2作为底子表(最好的办法)

select count(*) from tab1,tab2 履行时间0.96秒

挑选TAB2作为底子表(不佳的办法)　

select count(*) from tab2,tab1 履行时间26.09秒

假若有3个以上的表衔接查询,那就需求挑选穿插表(intersection table)作为底子表,穿插表是指那个被其他表所引用的表.

比方:EMP表描写了LOCATION表和CATEGORY表的交集.

SELECT *
FROM LOCATION L ,
CATEGORY C,
EMP E
WHERE E.EMP_NO BETWEEN 1000 AND 2000
AND E.CAT_NO = C.CAT_NO
AND E.LOCN = L.LOCN 

将比下列SQL更有效率

SELECT *
FROM EMP E ,
LOCATION L ,
CATEGORY C
WHERE E.CAT_NO = C.CAT_NO
AND E.LOCN = L.LOCN
AND E.EMP_NO BETWEEN 1000 AND 2000