Oracle 性能调整手册SQL篇.docx

Oracle 性能调整手册SQL篇.docx

《Oracle 性能调整手册SQL篇.docx》由会员分享，可在线阅读，更多相关《Oracle 性能调整手册SQL篇.docx（93页珍藏版）》请在冰豆网上搜索。

Oracle性能调整手册SQL篇

目录

1Oracle基本组件预览4

2应用层面调整4

2.1调整说明4

2.2最大限度使用索引4

2.2.1使用％的注意事项5

2.2.2尽量避免使用null值5

2.2.3“<>”的避免5

2.2.4尽量避免使用“||”5

2.2.5避免在索引列上计算5

2.2.6复合主键的应用5

2.2.7复合索引的应用6

2.2.8避免使用!

=6

2.2.9子查询的位置与效率6

2.2.10隐式转换的注意6

2.2.11适当的函数索引运用7

2.3SQL层面调整7

2.3.1where子局中的连接顺序7

2.3.2尽量多使用COMMIT8

2.3.3删除表中全部数据应该使用TRUNCATE而不使用delete8

2.3.4Select子句中避免使用*8

2.3.5减少访问数据库的次数8

2.3.6如果可以建议使用where替换having8

2.3.7减少对表的查询9

2.3.8EXISTS与IN的替代关系10

2.3.9用EXISTS替换DISTINCT10

2.3.10重建索引提高效率11

2.3.11避免使用索引列上NOT11

2.3.12用>=替代>12

2.3.13用UNION替换OR（适用于索引列）12

2.3.14总是使用索引的第一个列14

2.3.15Uniol-ALL替换UNION（如果可实现）15

2.3.16Orderby16

2.3.17使用decode来减少处理时间16

2.3.18索引的操作17

2.3.19多个平等索引18

2.3.20不明确的索引等级18

2.3.21自动选择索引19

2.3.22删除重复行19

2.3.23优化GROUPBY19

2.3.24EXPORT和IMPORT的优化20

2.3.25条带化表和索引20

2.4尽量避免可能引起排序的操作20

3数据库层面性能查看20

3.1低效SQL识别20

3.1.1排序最多的前30条SQL20

3.1.2使用频率最高的前30条SQL20

3.1.3最多磁盘操作才能完成的前20个sql20

3.1.4最多逻辑读操作的前20个21

3.1.5命中率低于80％的SQL语句21

3.1.6通过PID扑获相关SQL21

3.2查看死锁21

3.3表级查询22

3.3.1查询CONSTRAINT22

3.3.2查询INDEX22

3.3.3查询该表所在表空间22

3.3.4定期REBUILD索引22

3.4表空间22

3.5临时表空间23

3.6回滚段23

3.7REDO查看23

3.8磁盘IO争用情况查看24

3.9磁盘排序情况（5%左右可以）24

3.10命中率查看24

3.10.1SGA命中率24

3.10.2共享池命中率（正常情况应该大于98%）24

3.10.3库高速缓冲区中率（正常情况应该大于98%）25

3.10.4数据字典缓冲区命中率（正常情况应该大于85%）25

3.10.5SQL语句命中率25

3.10.6数据缓冲区命中率（正常情况应大于90%）25

3.10.7REDO命中率（至少需要大于98%）26

3.11常用SQL26

3.12Oracle10G初始化参数说明27

Oracle基本组件预览

应用层面调整

调整说明

所谓应用层面的优化最主要是先从设计角度入手，就是如何能够以最少的资源来实现预期的业务目标；这里所谓最少的资源不是指SQL语句代码最少就效率最高，有可能使用相对比较多的SQL代码可以获得更高的执行效率，譬如表的数据量排序操作等各种因素都要考虑进去；或者也有可能一个存储过程由于设计层面的优势可以用5次循环就可以实现，也有可能由于设计不当用到20次的循环才能够实现；自己要根据实际情况权衡利弊。

最大限度使用索引

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

a.全表扫描

全表扫描就是顺序地访问表中每条记录。

ORACLE采用一次读入多个数据块（databaseblock）的方式优化全表扫描。

b.通过ROWID访问表

你可以采用基于ROWID的访问方式情况，提高访问表的效率，ROWID包含了表中记录的物理位置信息……ORACLE采用索引（INDEX）实现了数据和存放数据的物理位置（ROWID）之间的联系。

通常索引提供了快速访问ROWID的方法，因此那些基于索引列的查询就可以得到性能上的提高。

CBO下使用更具选择性的索引

　基于成本的优化器（CBO，Cost-BasedOptimizer）对索引的选择性进行判断来决定索引的使用是否能提高效率。

如果索引有很高的选择性，那就是说对于每个不重复的索引键值，只对应数量很少的记录。

比如，表中共有100条记录而其中有80个不重复的索引键值。

这个索引的选择性就是80/100=0.8.选择性越高，通过索引键值检索出的记录就越少。

如果索引的选择性很低，检索数据就需要大量的索引范围查询操作和ROWID访问表的操作。

也许会比全表扫描的效率更低。

　下列经验请参阅：

　a.如果检索数据量超过30%的表中记录数。

使用索引将没有显著的效率提高。

b.在特定情况下，使用索引也许会比全表扫描慢，但这是同一个数量级上的区别。

而通常情况下，使用索引比全表扫描要快很多倍！

使用％的注意事项

Selectnamefromdept

Whereenamelike‘%JOHN’索引失效

Whereenamelike‘%JOHN％’索引失效

Whereenamelike‘JOHN%’索引生效

尽量避免使用null值

Select*fromdept

Whereenameisnull

Whereenameisnotnull

“<>”的避免

Select*fromdept

Whereempno<>1000

Whereempno<1000andempno>1000

尽量避免使用“||”

select*fromdepartment

wheref_name||''||l_name='jacksonmicheal'索引失效

wheref_name='jackson'andl_name='micheal'索引生效

避免在索引列上计算

Select*fromdept

Wheresalary*2.25<10000索引失效

Wheresalary<（10000/2.25）索引生效

复合主键的应用

PKdept（dept_no,dept_name,tel）

前导列dept_no必须要在where条件中复合主键索引才会生效

Select*fromdept索引失效

Wheredept_no=001索引生效

Wheredept_no=001andename=’sales’索引生效

Wheredept_no=001andtel=6668索引生效

Whereename=’sales’andtel=6668索引失效

复合索引的应用

Createindexmulti_indexondept（dept_no,dept_name,tel）

前导列dept_no必须要在where条件中复合主键索引才会生效

Select*fromdept索引失效

Wheredept_no=001索引生效

Wheredept_no=001andename=’sales’索引生效

Wheredept_no=001andtel=6668索引生效

Whereename=’sales’andtel=6668索引失效

避免使用!

=

Select*fromdepartment

Wheredept_no!

=1001索引失效

子查询的位置与效率

Select（子查询位置）最低效

From（子查询位置）高效

Where（子查询位置）次低效

隐式转换的注意

由于字段类型不同的关系有时会发生自动的字段类型隐式转换导致索引失效但是也有时会生效，应注意一下,避免改变索引列的类型。

当比较不同数据类型的数据时，ORACLE自动对列进行简单的类型转换。

假设EMPNO是一个数值类型的索引列。

SELECT…

FROMEMP

　WHEREEMPNO=‘123’

　实际上，经过ORACLE类型转换，语句转化为：

SELECT…

FROMEMP

　WHEREEMPNO=TO_NUMBER（‘123’）

幸运的是，类型转换没有发生在索引列上，索引的用途没有被改变。

现在，假设EMP_TYPE是一个字符类型的索引列。

SELECT…

FROMEMP

WHEREEMP_TYPE=123

　这个语句被ORACLE转换为：

　SELECT…

FROMEMP

　WHERETO_NUMBER（EMP_TYPE）=123

因为内部发生的类型转换，这个索引将不会被用到，为了避免ORACLE对你的SQL进行隐式的类型转换，最好把类型转换用显式表现出来。

注意当字符和数值比较时，ORACLE会优先转换数值类型到字符类型。

适当的函数索引运用

createindexmane_idxondept（UPPER（username））;

analyzeindexmane_idxcomputestatistics;

analyzetabledeptcomputestatistics;

SELECTcount（*）FROMdeptWHEREUPPER（username）='MICHEAL'索引生效

FBI索引必须遵守下面的规则：

a）必须使用基于成本的优化器，而且创建后必须对索引进行分析

b）不能存储NULL值。

因为任何函数在任何情况下都不能返回NULL值。

c）如果一个用户定义的PL/SQL例程失效了，而且这个例程被FBI索引用到了，那么相应的这个FBI索引会变成DISABLED

d）创建FBI索引得函数必须是确定性的。

即，对于指定的输入，总是会返回确定的结果。

e）索引的属主如果没有了在FBI索引里面使用的函数的执行权限，那么这个FBI索引会变成DISABLED.

f）在创建索引得函数里面不能使用SUM等总计函数。

g）要把一个DISABLED了的索引重新变成ENABLED，这个函数必须首先是ENABLED的才可以。

限制：

对于下面这些限制，不能创建FBI索引：

a）LOB列b）REF

c）Nestedtable列d）包含上面数据类型的对象

SQL层面调整

where子局中的连接顺序

ORACLE采用自下而上的顺序解析WHERE子句，表之间的连接必须写在其他WHERE条件之前，那些可以过滤掉最大数量记录的条件推荐写在WHERE子句的末尾。

　（低效，执行时间156.3秒）

SELECT…

FROMEMPE

WHERESAL>50000

ANDJOB=‘MANAGER’

AND25<

（SELECTCOUNT（*）FROMEMPWHEREMGR=E.EMPNO）;

（高效,执行时间10.6秒）

SELECT…

FROMEMPE

WHERE25<（SELECTCOUNT（*）FROMEMP

WHEREMGR=E.EMPNO）

ANDSAL>50000

ANDJOB=‘MANAGER’;

尽量多使用COMMIT

　在程序中尽量多使用COMMIT，这样程序的性能得到提高，需求也会因为COMMIT所释放的资源而减少。

删除表中全部数据应该使用TRUNCATE而不使用delete

Select子句中避免使用*

在SELECT子句中列出所有的COLUMN时，使用动态SQL列引用*是一个方便的方法。

但这是一个非常低效的方法。

ORACLE在解析的过程中，会将*依次转换成所有的列名，这个工作是通过查询数据字典完成的，这将耗费更多的时间。

减少访问数据库的次数

　当执行每条SQL语句时，ORACLE在内部执行了许多工作：

解析SQL语句，估算索引的利用率，绑定变量，读数据块等等。

由此可见，减少访问数据库的次数，就能实际上减少ORACLE的工作量。

例如，以下有三种方法可以检索出雇员号等于0342或0291的职员。

方法1（最低效）

SELECTEMP_NAME,SALARY,GRADE

FROMEMP

WHEREEMP_NO=342;

SELECTEMP_NAME,SALARY,GRADE

FROMEMP

WHEREEMP_NO=291;

方法2（次低效）

DECLARE

CURSORC1（E_NONUMBER）IS

SELECTEMP_NAME,SALARY,GRADE

FROMEMP

WHEREEMP_NO=E_NO;

BEGIN

OPENC1（342）;

FETCHC1INTO…,..,..;

OPENC1（291）;

FETCHC1INTO…,..,..;

CLOSEC1;

END;

方法3（高效）

SELECTA.EMP_NAME,A.SALARY,A.GRADE,

B.EMP_NAME,B.SALARY,B.GRADE

FROMEMPA,EMPB

WHEREA.EMP_NO=342

ANDB.EMP_NO=291;

　注意：

　在SQL*Plus，SQL*Forms和Pro*C中重新设置ARRAYSIZE参数，可以增加每次数据库访问的检索数据量，建议值为200.

如果可以建议使用where替换having

　避免使用HAVING子句，HAVING只会在检索出所有记录之后才对结果集进行过滤。

这个处理需要排序，总计等操作。

如果能通过WHERE子句限制记录的数目，那就能减少这方面的开销。

例如：

　低效：

SELECTREGION，AVG（LOG_SIZE）

FROMLOCATION

GROUPBYREGION

HAVINGREGIONREGION!

=‘SYDNEY’

ANDREGION!

=‘PERTH’

　高效

SELECTREGION，AVG（LOG_SIZE）

FROMLOCATION

WHEREREGIONREGION!

=‘SYDNEY’

ANDREGION!

=‘PERTH’

GROUPBYREGION

HAVING中的条件一般用于对一些集合函数的比较，如COUNT（）等等。

除此而外，一般的条件应该写在WHERE子句中。

减少对表的查询

例如：

　低效

SELECTTAB_NAME

FROMTABLES

WHERETAB_NAME=（SELECTTAB_NAME

FROMTAB_COLUMNS

WHEREVERSION=604）

AND　DB_VER=（SELECTDB_VER

FROMTAB_COLUMNS

WHEREVERSION=604）

高效

SELECTTAB_NAME

FROMTABLES

WHERE（TAB_NAME,DB_VER）

=（SELECTTAB_NAME,DB_VER）

FROMTAB_COLUMNS

WHEREVERSION=604）

Update多个Column例子：

　低效：

UPDATEEMP

SETEMP_CAT=（SELECTMAX（CATEGORY）FROMEMP_CATEGORIES）,

SAL_RANGE=（SELECTMAX（SAL_RANGE）FROMEMP_CATEGORIES）

WHEREEMP_DEPT=0020;

　高效：

UPDATEEMP

SET（EMP_CAT,SAL_RANGE）

=（SELECTMAX（CATEGORY）,MAX（SAL_RANGE）

FROMEMP_CATEGORIES）

WHEREEMP_DEPT=0020;

EXISTS与IN的替代关系

用EXISTS替代IN

　在许多基于基础表的查询中，为了满足一个条件，往往需要对另一个表进行联接。

在这种情况下，使用EXISTS（或NOTEXISTS）通常将提高查询的效率。

低效：

SELECT*

FROMEMP（基础表）

WHEREEMPNO>0

ANDDEPTNOIN（SELECTDEPTNO

FROMDEPT

WHERELOC=‘MELB’）

高效：

SELECT*

FROMEMP（基础表）

WHEREEMPNO>0

ANDEXISTS（SELECT‘X’FROMDEPTWHEREDEPT.DEPTNO=EMP.DEPTNOANDLOC=‘MELB’）

相对来说，用NOTEXISTS替换NOTIN将更显著地提高效率。

用NOTEXISTS替代NOTIN

　在子查询中，NOTIN子句将执行一个内部的排序和合并。

无论在哪种情况下，NOTIN都是最低效的（因为它对子查询中的表执行了一个全表遍历）。

为了避免使用NOTIN，我们可以把它改写成外连接（OuterJoins）或NOTEXISTS.

　例如：

SELECT…

FROMEMP

WHEREDEPT_NONOTIN（SELECTDEPT_NO

FROMDEPT

WHEREDEPT_CAT=’A’）;

　为了提高效率。

改写为：

　（方法一：

高效）

SELECT….

FROMEMPA,DEPTB

WHEREA.DEPT_NO=B.DEPT（）

ANDB.DEPT_NOISNULL

ANDB.DEPT_CAT（）=‘A’

　（方法二：

最高效）

SELECT….

FROMEMPE

WHERENOTEXISTS（SELECT‘X’FROMDEPTDWHERED.DEPT_NO=E.DEPT_NOANDDEPT_CAT=‘A’）;

用EXISTS替换DISTINCT

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

　例如：

　低效：

SELECTDISTINCTDEPT_NO,DEPT_NAME

FROMDEPTD,EMPE

WHERED.DEPT_NO=E.DEPT_NO

　高效：

SELECTDEPT_NO,DEPT_NAME

FROMDEPTD

WHEREEXISTS（SELECT‘X’

FROMEMPE

WHEREE.DEPT_NO=D.DEPT_NO）;

　EXISTS使查询更为迅速，因为RDBMS核心模块将在子查询的条件一旦满足后，立刻返回结果。

重建索引提高效率

索引是表的一个概念部分，用来提高检索数据的效率。

实际上，ORACLE使用了一个复杂的自平衡B-tree结构。

通常，通过索引查询数据比全表扫描要快。

当ORACLE找出执行查询和Update语句的最佳路径时，ORACLE优化器将使用索引。

同样在联结多个表时使用索引也可以提高效率。

另一个使用索引的好处是，它提供了主键（primarykey）的唯一性验证。

除了那些LONG或LONGRAW数据类型，你可以索引几乎所有的列。

通常，在大型表中使用索引特别有效。

当然，你也会发现，在扫描小表时，使用索引同样能提高效率。

虽然使用索引能得到查询效率的提高，但是我们也必须注意到它的代价。

索引需要空间来存储，也需要定期维护，每当有记录在表中增减或索引列被修改时，索引本身也会被修改。

这意味着每条记录的INSERT，DELETE，UPDATE将为此多付出4，5次的磁盘I/O.因为索引需要额外的存储空间和处理，那些不必要的索引反而会使查询反应时间变慢。

定期的重构索引是有必要的。

ALTERINDEXREBUILD

避免使用索引列上NOT

　通常，我们要避免在索引列上使用NOT，NOT会产生在和在索引列上使用函数相同的影响。

当ORACLE“遇到”NOT，他就会停止使用索引转而执行全表扫描。

举例：

低效：

（这里，不使用索引）

SELECT…

FROMDEPT

　WHEREDEPT_CODENOT=0；

高效：

（这里，使用了索引）

SELECT…

FROMDEPT

　WHEREDEPT_CODE>0andDEPT_CODE<0；

需要注意的是，在某些时候，ORACLE优化器会自动将NOT转化成相对应的关系操作符。

NOT>to<=

　NOT>=to<

　NOT=

　NOT<=to>

例子中的低效率SQL是不能被执行的。

一些测试：

SQL>select*fromempwhereNOTempno>1；

　norowsselected

　ExecutionPlan

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

0SELECTSTATEMENTOptimizer=CHOOSE

　10TABLEACCESS（BYINDEXROWID）OF'EMP'

　21INDEX（RANGESCAN）OF'EMPNO'（UNIQUE）

SQL>select*fromempwhereempno<=1；

　norowsselected

　ExecutionPlan

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

0SELECTSTATEMENTOptimizer=CHOOSE

　10TABLEACCESS（BYINDEXROWID）OF'EMP'

　21INDEX（RANGESCAN）OF'EMPNO'（UNIQUE）

两者的效率完全一样，也许这符合作者关于“