oracle10g数据库开发优化指南.docx
《oracle10g数据库开发优化指南.docx》由会员分享，可在线阅读，更多相关《oracle10g数据库开发优化指南.docx（20页珍藏版）》请在冰豆网上搜索。
oracle10g数据库开发优化指南
ORACLE10G数据库开发
优化指南
目录
目录2
1：
绑定变量3
2：
不要频繁提交，不要使用自动提交5
3：
索引7
什么情况下适合建立索引：
7
如何使优化器使用索引：
8
4：
高效SQL10
查询中使用索引10
表联结查询11
5：
常见的、流传很广的传言13
6：
临时表的使用15
7：
autotrace16
8：
收集统计信息17
1：
绑定变量
绑定变量是一个数据库开发人员所应该具备的最基本的习惯之一。
但是在现实中，很多开发人员完全不使用绑定变量，甚至根本不知道绑定变量是何物。
这个很大程度上是因为他们不了解使用或者不使用绑定变量对数据库造成的影响。
如果他们知道了，我敢肯定他们一定会很吃惊。
任何一句提交给ORACLE数据库运行的语句，在正式运行前，都需要通过数据库引擎的解析。
解析分为2种，一种是硬解析，一种是软解析。
为了理解方便，简单的说，如果你使用了绑定变量，那么数据库会倾向于使用软解析，如果你没有使用绑定变量，那么数据库会倾向于使用硬解析。
从字面上理解，硬解析使用的资源会比软解析多。
但是到底有多少？
很难在这个文章里面进行全面描写，但是可以从其中的一个方面进行理解：
在一个SQL语句的硬解析中，会产生数百个乃至数千个LATCH，而这些LATCH锁住的都是数据库必须在实例级别串行访问的资源，并且请注意，这么多的LATCH是硬解析一句SQL语句中产生的！
软解析则不需要产生这些LATCH。
单从这一点就可以知道，硬解析对数据库而言是一个极其昂贵的操作。
如果开发人员在一个循环里面调用了一个SQL语句而又没有绑定变量，那么可以想象对数据库会造成多大的压力！
****************************************************************************
LATCH不等于锁LOCK，它是一种轻量级的LOCK，用来保证对资源的串行访问。
对于被LOCK住资源的申请，如果不能满足，那么申请者会被阻塞以后进入一个等待队列等待，当资源被释放的时候，按照先到先得的顺序唤醒队列里面的第一个等待者。
而对被LATCH住的资源的申请，如果不能满足，那么申请的进程不会被阻塞也不会等待，它只会在很短的一个时间以后再次重新申请。
申请LATCH住资源的进程能否得到这个资源全凭运气：
它们不会排队等待，而是随机的去碰运气—不停的尝试申请。
****************************************************************************
CURSOR_SHAREING参数是ORACLE提供的一个对于不使用绑定变量的解决方案，它如果设置成FORCE，可以在数据库实例上对所有提交给数据库的SQL语句强制进行绑定变量。
这看来好象是对开发人员的一种解放，但是，请注意下面2点：
1：
CURSOR_SHAREING会引入它自己的额外开销。
2：
它有可能会引发一些与手动绑定变量不同的、不可意料的SQL执行返回结果，这增加了测试和排错的难度。
CURSOR_SHAREING绝对不是解决绑定变量的万能方法，相反，它这是ORACLE提供的一种不得已而为之的解决方案，只有当运行系统因为硬解析而不堪重负，而短期又不可能全面重写代码的情况下使用的，而且使用以前一定要经过充分严格的测试。
当然，过度绑定变量是有可能导致某个查询性能下降的，实际中也会有这样的场景。
但是，相比较而言，不使用绑定变量而带来的性能问题要更普遍和严重得多。
以JAVA为例，所有提交给数据库执行的SQL语句应该如下面这样绑定变量。
{
Connectioncon=DriverManager.getConnection
（"jdbc:
oracle:
oci:
@database","scott","tiger"）;
conn.setAutoCommit（false）;//把自动提交修改为非自动提交。
Stringv_sql='selectnamefromtable_awhereid=?
';//嵌入绑定变量
ReparedStatementstmt=conn.prepareStatement（v_sql）;
stmt.setString（1,'xxxx'）;//为绑定变量赋值
stmt.executeQuery（）;
mit（）;
}
所以，请开发人员特别注意：
除非有充分的理由，否则，请确保你提交给数据库执行的任何一句SQL语句都是手工绑定了变量的。
2：
不要频繁提交，不要使用自动提交
要根据业务逻辑的需求，保证业务数据一致性的情况下提交。
实际上，开发人员使用频繁提交、自动提交主要是他们的认识里面有下面2条
1：
在做删除和修改的过程中，对数据行的锁定是稀有资源，而且会阻塞对数据库相应行的读。
2：
ODBC，JDBC模型中事务默认都是自动提交（隐式提交）。
对于第一点，不能说是错误的，因为在其他的主流数据库（DB2，SYBASE，SQLSERVER）中，这个概念是正确的。
但是在ORACLE中，完全不是这么回事情。
ORACLE的并发模型以及对于行锁的实现方法使得它对10000行数据的写锁定所消耗的资源与对1行数据的写锁定所消耗的资源是完全相同的--它们实际上都不消耗任何额外资源。
而且，任何的写锁定都不会阻塞其他会话对该数据的读取。
对于第2点，开发人员应该手动把它修改成非自动提交。
在ODBC中默认设置是自动提交是很好理解的，因为ODBC是为SQLSERVER的开发人员设计的，SQLSERVER的并发模型使得锁定是一种非常稀有的资源，必须尽快提交并且释放锁定。
但是这个特性不应该被继承到JDBC中。
JDBC被用来设计支持企业级的应用，而事务则是企业级应用的核心特征，自动提交几乎肯定会破坏事务的原子性。
以JAVA为例，我认为任何与数据库事务相关的代码都应该是这样的：
{
Connectioncon=DriverManager.getConnection
（"jdbc:
oracle:
oci:
@database","scott","tiger"）;
conn.setAutoCommit（false）;//把自动提交修改为非自动提交。
//业务代码
//业务代码
//业务代码
mit（）;//根据业务需求最后再一并提交。
}
如果不把自动提交修改成非自动提交，那么代码是极其糟糕的。
实际上，频繁的递交还会大大加强ORACLE数据库的压力，导致性能下降。
这是因为ORACLE的多版本控制和行级锁定的特征使得它提交1行数据所产生的负荷与提交10000行数据所产生的负荷几乎相等。
很显然，对比下面的这两段代码：
代码1（糟糕，如果使用自动提交，那么即使没有代码中的这2句commit，也等效与下面的代码）：
for（inti=0;i<10000;i++）
{
deletefromtable1where....
commit;
insertintotable2where....
commit;
}
代码2（优良）：
for（inti=0;i<10000;i++）
{
deletefromtable1where....
insertintotable2where....
}
commit;
就算不考虑代码1的频繁commit已经破坏了事务的原子性，与业务初衷相背，单从性能来说，代码2已经高出代码1很多倍了。
我曾经不止一次的在网上看到所谓ORACLE优化的文章，里面说：
commit越频繁越好，越能加快释放资源，增加性能。
这样的文章实际上是不对的。
在别的主流数据库中，这个说法大多正确，但是在ORACLE不是这样的！
有必要再重复总结一次，在基于ORACLE的数据库开发中：
1：
不要有意缩短事务的大小，而应该根据业务逻辑合理安排事务，业务需要多大的事务就安排多大的事务，业务逻辑需要事务什么时候提交才提交。
2：
在ORACLE数据库中，行级锁不是一种稀缺资源，不会造成数据库性能低下，行级锁不会消耗数据库的任何额外资源。
3：
索引
索引是数据库永恒而重要的话题。
ORACLE中主要有下面类型的索引：
B*树索引，位图索引，位图联结索引，基于函数索引，应用域索引。
而我们平时所说的索引指的是B*树索引，这是数据库最常用的一类索引结构。
但是，索引不是提高查询效率的万能灵药。
我经常听到开发人员这样评价：
"索引肯定能加快查询。
""多加几个索引不要紧，反正它不会让我们的系统更慢"。
实际上，这样的话是绝对错误的。
请开发人员记住，你增加的任何一个索引都有可能对你的数据库查询产生下面的影响：
1：
更快。
2：
不快也不慢，速度不变。
3：
更糟糕了，更慢了。
在我所见过的所有生产系统中，我都见过大量的能让数据库更慢、负担更重的索引。
我曾经还在一个15个字段的表中见到了14个索引，唯一没有被索引的字段是那个长长的Description字段。
而实际上，这14个索引中，有10个大概是永远不会被用到的。
而那可能被用到的4个索引也可以通过1到2个组合索引来代替。
无用索引使系统变慢的原因：
1：
数据库加载大量无用索引到内存，并在运行中管理它们会消耗大量的资源。
2：
系统的增、删、改操作会因为需要同步大量无用的索引而变得很慢。
所以，开发人员在建立任何一个索引以前都需要认真考虑：
这个索引是必须的吗？
它能被数据库使用吗？
如果回答是坚定的肯定，那么这个索引就是值得建立的。
否则，请不要建。
什么情况下适合建立索引：
1：
搜索列有比较高的选择性
比如说有一个员工登记表，它有2个字段（name,gender,age）.如果有100条记录，那么我们知道，在name字段上，将有100个（或者非常接近100）不同的值，那么我们就说这个字段选择性是很高的，在这个字段上面的索引将很有可能在查询中被优化器选中。
而在gender字段上，100条记录最多只能有2个不同的值，这样我们就所这个字段选择性是很低的，在这个字段上的索引几乎不会被优化器选中。
现在，在这2个字段上面分别建立索引index1onname,index2ongender.
如果有一个查询是根据name进行搜索，那么查询优化器会偏向于使用index1.如果有一个查询是基于gender，那么可以肯定的说，查询优化器几乎不会去使用index2，换一句话说，index2几乎永远不会不被使用-它的存在是没有意义的，只能对我们的系统产生负面影响。
2：
索引能单独回答SQL查询。
在这样的情况下，索引做为一个比较“瘦”版本而且是排序过的表，对查询性能是有很大帮助的。
还是用上面的员工登记表来说明情况，如果现在有一个查询是基于用户的name和gender，而且查询出来的字段也只包括name和gender，（selectname,genderfromtablewheregender='XXX'andname='XXX'）那么，单独建立在name上的索引和单独建立在gendar上面的索引都不能回答。
这个时候，如果有一个索引是建立在name和gender上面的，比如index3on（name,gender）或者index3on（gender,name），那么，数据库单独访问索引就能提供所有的信息，于是，数据库就会偏向于使用这个索引。
实际上，在上面的这个例子中，可以肯定的说，在任何情况下数据库都几乎一定会使用这个索引。
还有一点，建立索引的时候应该适当考虑组合索引，既几个字段组成一个索引。
如何使优化器使用索引：
不要在索引列上引入任何计算，包括函数。
select*fromtable1wherecol1+2=10;
这样的语句，即使col1上面有合适的索引，优化器也肯定不会使用它。
而如果使用下面完全等价的写法：
select*fromtable1wherecol1=8;
那么，优化器会偏向于使用合适的索引。
基于函数的索引
ORACLE现在已经发展了一种叫做“基于函数的索引”（function-basedindex）的技术。
这个技术是直接对计算得出的列建立索引，并在查询中使用。
例如可以如下面一样建立一个函数索引：
createindextable1_idx1ontable1（upper（col1））;
这样，下面的查询就会倾向于使用索引。
select*fromtable1whereupper（col1）='ABC';
搜索中请尽量包含组合索引的前导列，这样在搜索中，索引将会最大限度的被正确使用。
如果在表table1（col1,col2,col3,col4）上有一个对前3个字段的组合索引index1on（col1,col2,col3）那么只有在搜索中where语句后面引用了col1，那索引才肯定会被正确的使用。
会正确使用索引的情况：
select*fromtable1wherecol1='xxx'andcol2='xxxx';
select*fromtable1wherecol1='xxx'andcol3='xxxx';
在上面的情况中，如果优化器认为使用索引会降低COST，那么它一定会使用索引。
很有可能不会正确使用索引的情况：
select*fromtable1wherecol3='xxx';
select*fromtable1wherecol2='xxx';
上面的情况中，有的时候优化器可能正确的使用索引；但是有的时候并不会使用索引，即使使用索引是一种比较廉价的解决方案。
使用复合索引且第一个索引字段没有出现在where中时，建议使用hint强制
如果在搜索条件中有“isnull”这样的条件，将肯定不会使用索引。
select*fromtable1wherecol1isnull;
这个查询肯定不会使用索引。
原因是ORACLE根本不会为字段为NULL值的记录编制索引，也就是说，你必须去做表扫描才能得到相关记录信息。
搜索中有“!
=”的肯定不会使用索引
select*fromtable1wherecol1！
="XXX";
这个查询肯定不会使用索引，无论使用索引是不是更加廉价，索引都不会在优化器的考虑范围以内。
对于“！
=”的查询，
比如select*fromtable1wherecol1！
=10000;
可以考虑使用"col1>9999andcol1<10001"的查询来代替。
这样的情况下，如果优化器认为使用索引的搜索方案比较廉价，那么索引就会被使用。
比如select*fromtable1wherecol1>9999andcol1<10001;
4：
高效SQL
查询中使用索引
避免使用NOTIN，尽量使用NOTEXISTS
1：
NOTIN将偏向于不使用索引。
而NOTEXISTS可以正确的使用索引。
下面2种情况下都将使用索引，它们执行的COST几乎相同。
SELECT*fromEMPwheredeptnonotin（selectdeptnofromtest2wheredeptno=10）;
SELECT*
FROMEMPD
WHEREnotEXISTS（SELECTnull
FROMtest2E
WHEREE.DEPTNO=D.DEPTNOande.deptno=10）;
但是在下面的情况下：
Select*fromEMPwheredeptnonotin（selectdeptnofromtest2）;
SELECT*
FROMEMPD
WHEREnotEXISTS（SELECTnull
FROMtest2E
WHEREE.DEPTNO=D.DEPTNO）;
使用NOTIN的就无法使用索引，而使用NOTEXISTS的可以使用索引，当后面的一个表比较大的时候，它们的执行效率相差非常大。
查询返回尽量少的字段
SELECT后面不连接*
比如SELECT*FROMTABLE1
这样会把表TABLE1中的所有字段全部返回，而实际上应该只把需要的字段放在SELECT后面。
比如
SELECTCOL1,COL2FROMTABLE2.这样就会查询需要的2个字段并返回，而不会把所有的字段都查询出来。
EXISTS替换DISTINCT
下面2个SQL是逻辑等效，但是性能相差很大。
因为数据库做distinct操作的时候，是先最整个数据集进行SORT排序，然后再遍历数据集，遇到重复的记录就去除。
例子：
例如：
低效
selectdistinctdept_no,dept_name
fromdeptd,empe
whered.dept_no=e.dept_no
高效
selectdept_no,dept_name
fromdeptd
whereexists（selectnull
fromempe
wheree.dept_no=d.dept_no）;
用UNIONALL替换OR（适用于索引列）
通常情况下,用UNIONALL替换WHERE子句中的OR将会起到较好的效果.对索引列使用OR将造成全表扫描.注意,以上规则只针对多个索引列有效.如果有column没有被索引,查询效率可能会因为你没有选择OR而降低.当然，UNIONALL有可能输出重复的行数据，所以这个需要开发人员具体问题具体对待了。
在下面的例子中,LOC_ID和REGION上都建有索引.
高效:
SELECTLOC_ID,LOC_DESC,REGION
FROMLOCATION
WHERELOC_ID=10
UNIONALL
SELECTLOC_ID,LOC_DESC,REGION
FROMLOCATION
WHEREREGION=“MELBOURNE”
低效:
SELECTLOC_ID,LOC_DESC,REGION
FROMLOCATION
WHERELOC_ID=10ORREGION=“MELBOURNE”
like子句尽量前端匹配
like参数使用得非常频繁，因此如果能够对于like子句使用索引，将很好地提高查询的
效率。
例如：
查询城市代码（city表上有基于city_name列的索引）
selectcity_name_chinesefromcitywherecity_namelike‘%ZHEN%’
优化器偏向于不使用索引。
如果对上面的语句进行修改，成下面的表达形式
selectcity_name_chinesefromcitywherecity_namelike‘SHNEZHEN%’
那么优化器偏向于使用索引。
表联结查询
在ORACLE的RBO以及早期的CBO时代，对于表连接查询的书写规则，有很多技巧。
不同的书写有可能产生相差十分巨大的查询路径。
到现在，虽然CBO已经发展非常成熟，可以对很多SQL语句进行对于转换和重写，但是它对很多不优良的SQL语句仍然无法象人一样进行有效的优化。
表联结查询是及其昂贵的操作，尤其是随着表数目的增加，COST增加更快。
一般情况下，一个表联结查询不应该连接超过4个以上的表（注意是表，不是视图），否则容易使查询的性能急剧下降。
联结的表数目太多，说明有过多业务逻辑被放到了数据层，这个时候开发人员应该好好考虑是否应该一次性把如此多工作交给数据库查询引擎去做。
1:
在表联结中，同一个表应该只出现一次。
如果在一个表联结查询中同一个表出现多次，那么ORACLE就会对这个表扫描多次。
很显然，扫描多次是没有必要的，因为扫描一次就可以把这个查询需要的表信息全部读取出来。
优化器目前还没有聪明到可以自己把这个联结重写成只需要扫描一次的表联结查询语句。
比如，下面的语句
selectt1.col1,t2.col2from
（selecta.col1,b.col2fromtable1a,table2bwhere......）t1,
（selecta.col1,c.col2fromtable1a,table3cwhere......）t2where....
整个查询联结了３张表，但是对于表table1而言，数据库查询了２次。
开发人员一般这样写是出于对业务的理解以及查询的可读性：
第１次对table1和table2的联结代表了一个业务上的意义，而第２次对table1和table３的联结又代表了另一个业务上的意义。
最后把这２个中间数据集合联接起来又是一个业务上的意义。
如果对上面的语句做下面的更改，可以得到等效的查询语句：
selecta.col1,b.col2fromtable1a,table2b,table3cwhere.......
这样数据库对table1,table2,table3分别只进行了一次读取，显然会更快。
尽量消除排序操作
如果可以使用UNIONALL，那么就应该尽量不使用UNION操作。
因为UNION操作会把连接的2张表分别进行排序以后将重复的记录剔除然后输出，而UNIONALL就不会排序，也不会把重复记录消除。
排序操作是非常昂贵的，尤其对于大表。
我曾经见过一个SQL语句，原本是使用UNION连接2个数据集，这个查询大概需要4秒钟。
我仔细查看了UNION连接的2个数据集，发现从数据集过滤的条件上判断它们根本不可能有重复的行集，于是我把UNION修改成了UNIONALL，于是这个查询时间缩短到了1秒以内。
5：
常见的、流传很广的传言
这些传言并不是错误的。
在某些其他的数据库上，以及在一些ORACLE9I以及更早的版本上，它们是正确的。
但是在现在的ORACLE10G上，它们已经不再正确或者过时了。
尽管严格按照这些“传言”来写SQL并不会对数据库带来任何负面的影响，但是我认为还是很有比较把它们交代说明一下。
传言：
在表连接当中，选择小表做为驱动表可以大大提高效率。
表TAB1大表，上万条条记录
表TAB2小表几条条记录
选择TAB2作为基础表（最好的方法）
selectcount（*）fromtab1,tab2执行时间很短。
选择TAB2作为基础表（不佳的方法）
selectcount（*）fromtab2,tab1执行时间很长。
实际情况：
这个传言在ORACLE的RBO中是正确的。
但是在ORACLE
升级会员 