Oracle Sql性能优化调整

OracleSql性能优化调整以下sql性能优化条例，绝大部分来自网上(《Oralce性能优化》等文章)，我只作了整理和稍微的加工。大家如有更多的经验，可以进行追加补充。虽然优化条例针对Oracle，但是，很多条例，对其他数据库同样有效。特别提醒的是，有些优化条例，并不适合您正在使用的数据库版本。开发时，还是需要结合实际，多实践，多测试，才能正真实现数据库性能的提升。1.选用适合的oracle优化器oracle的优化器共有3种:a.rule(基于规则)b.cost(基于成本)c.choose(选择性)设置缺省的优化器,可以通过对init.ora文件中optimizer_mode参数的各种声明,如rule,cost,choose,all_rows,first_rows（注：如果你没设定optimizer_mode参数则默认用的是choose这种方式。）.你当然也在sql句级或是会话(session)级对其进行覆盖（通过sqlaltersessionsetoptimizer_mode=mode;来设定。）.为了使用基于成本的优化器(cbo,cost-basedoptimizer),你必须经常运行analyze命令,以增加数据库中的对象统计信息(objectstatistics)的准确性.如果数据库的优化器模式设置为选择性(choose),那么实际的优化器模式将和是否运行过analyze命令有关.如果table已经被analyze过,优化器模式将自动成为cbo,反之,数据库将采用rule形式的优化器.在缺省情况下,oracle采用choose优化器,为了避免那些不必要的全表扫描(fulltablescan),你必须尽量避免使用choose优化器,而直接采用基于规则或者基于成本的优化器.补充：analyze命令是建立表的统计信息。一般来说,一个表上的某一列建了索引后，如果不对表或索引进行统计的话，cbo(rbo)不会选择进行索引扫描,而是全表扫描.如果一个表中的数据进行频繁的插入/删除/更新操作,那么需要定期更新这个表上的统计信息。一般我的做法是建立一个job,每个月底定期更新统计信息.analyzetabletablenamecomputestatistics是对全表进行统计,在数据量很大时，非常慢,也非常占用temp表空间.为了节省时间,可以采用部分数据，进行统计.例如:analyzetabletablenameestimatestatisticssample20percent上面语句的意思是采用表中20%的数据进行统计，可以自行设置，一般不能小于20%,否则统计出来的信息会不准确.rbo就是基于规则的优化cbo是基于成本的优化我举个例子，很多人会认为一个查询走索引快，对吗？不完成对，70%－80%的语句走索引会快，但另外的就会慢，最常见的就是当你的数据量占总数据的百分比足够大时，反而会是全表扫描比索引快而基于rbo的，这时候会去走索引，而cbo不会另外rbo对书写等有一定要求，书写顺序不对，会造成效率降低øchoolse:这是我们应关注的,默认的情况下oracle用的便是这种方式。指的是当一个表或或索引有统计信息,则走cbo的方式,如果表或索引没统计信息,表又不是特别的小,而且相应的列有索引时,那么就走索引,走rbo的方式。例如：select/*+choose*/bkdh,bkmcfrombkmlbwherenian=2005;ørule：表示对所有的sql语句使用基于规则的优化器。例如：select/*+choose*/bkdh,bkmcfrombkmlbwherenian=2005;øfirst_row：表示使用基于代价的优化器，减少立即显示时间，减少资源消耗，以最短时间返回前n行数据。（n＝1，10，100，1000）。例如：select/*+first_rows(10)*/bkdh,bkmcfrombkmlbwherenian=2005;øall_rows：表示对所有的sql语句使用基于代价的优化器，减少总的相应时间。当一个表有统计信息时,它将以最快的方式返回表的所有的行,从总体上提高查询的吞吐量。没有统计信息则走基于规则的方式。例如：select/*+all_rows*/bkdh,bkmcfrombkmlbwherenian=2005;2.访问table的方式oracle采用两种访问表中记录的方式:a.全表扫描全表扫描就是顺序地访问表中每条记录.oracle采用一次读入多个数据块(databaseblock)的方式优化全表扫描.b.通过rowid访问表你可以采用基于rowid的访问方式情况,提高访问表的效率,,rowid包含了表中记录的物理位置信息..oracle采用索引(index)实现了数据和存放数据的物理位置(rowid)之间的联系.通常索引提供了快速访问rowid的方法,因此那些基于索引列的查询就可以得到性能上的提高.3.共享sql语句为了不重复解析相同的sql语句,在第一次解析之后,oracle将sql语句存放在内存中.这块位于系统全局区域sga(systemglobalarea)的共享池(sharedbufferpool)中的内存可以被所有的数据库用户共享.因此,当你执行一个sql语句(有时被称为一个游标)时,如果它和之前的执行过的语句完全相同,oracle就能很快获得已经被解析的语句以及最好的执行路径.oracle的这个功能大大地提高了sql的执行性能并节省了内存的使用.可惜的是oracle只对简单的表提供高速缓冲(cachebuffering),这个功能并不适用于多表连接查询.数据库管理员必须在init.ora中为这个区域设置合适的参数,当这个内存区域越大,就可以保留更多的语句,当然被共享的可能性也就越大了.当你向oracle提交一个sql语句,oracle会首先在这块内存中查找相同的语句.这里需要注明的是,oracle对两者采取的是一种严格匹配,要达成共享,sql语句必须完全相同(包括空格,换行等).共享的语句必须满足三个条件:a.字符级的比较:当前被执行的语句和共享池中的语句必须完全相同.例如:select*fromemp;和下列每一个都不同select*fromemp;select*fromemp;select*fromemp;b.两个语句所指的对象必须完全相同:例如:用户对象名如何访问jacksal_limitprivatesynonymwork_citypublicsynonymplant_detailpublicsynonymjillsal_limitprivatesynonymwork_citypublicsynonymplant_detailtableowner考虑一下下列sql语句能否在这两个用户之间共享.sql能否共享原因selectmax(sal_cap)fromsal_limit;不能每个用户都有一个privatesynonym-sal_limit,它们是不同的对象selectcount(*0fromwork_citywheresdesclike'new%';能两个用户访问相同的对象publicsynonym-work_cityselecta.sdesc,b.locationfromwork_citya,plant_detailbwherea.city_id=b.city_id不能用户jack通过privatesynonym访问plant_detail而jill是表的所有者,对象不同.c.两个sql语句中必须使用相同的名字的绑定变量(bindvariables)例如：第一组的两个sql语句是相同的(可以共享),而第二组中的两个语句是不同的(即使在运行时,赋于不同的绑定变量相同的值)a.selectpin,namefrompeoplewherepin=:blk1.pin;selectpin,namefrompeoplewherepin=:blk1.pin;b.selectpin,namefrompeoplewherepin=:blk1.ot_ind;selectpin,namefrompeoplewherepin=:blk1.ov_ind;4.使用提示(hints)对于表的访问,可以使用两种hints.full和rowidfullhint告诉oracle使用全表扫描的方式访问指定表.例如:select/*+full(emp)*/*fromempwhereempno=7893;rowidhint告诉oracle使用tableaccessbyrowid的操作访问表.通常,你需要采用tableaccessbyrowid的方式特别是当访问大表的时候,使用这种方式,你需要知道roiwd的值或者使用索引.如果一个大表没有被设定为缓存(cached)表而你希望它的数据在查询结束是仍然停留在sga中,你就可以使用cachehint来告诉优化器把数据保留在sga中.通常cachehint和fullhint一起使用.例如:select/*+full(worker)cache(worker)*/*fromwork;索引hint告诉oracle使用基于索引的扫描方式.你不必说明具体的索引名称例如:select/*+index(lodging)*/lodgingfromlodgingwheremanager=‘billgates’;在不使用hint的情况下,以上的查询应该也会使用索引,然而,如果该索引的重复值过多而你的优化器是cbo,优化器就可能忽略索引.在这种情况下,你可以用indexhint强制oracle使用该索引.oraclehints还包括all_rows,first_rows,rule,use_nl,use_merge,use_hash等等.译者按:使用hint,表示我们对oracle优化器缺省的执行路径不满意,需要手工修改.这是一个很有技巧性的工作.我建议只针对特定的,少数的sql进行hint的优化.对oracle的优化器还是要有信心(特别是cbo)注：oraclehint是一种让dba自行指定优化器的行为的方法，当优化器的优化结果不能满足我们的要求时我们可以通过hint的方法人为地指定查询计划。理论上一个优秀的优化器是不用人去干预它的行为，但智者千里必有一失，有时优化器也不是万能的，也需要人的智慧去补充其行为，hint就是这个最后的银弹。hint行为有多种方式，具体的hint可以查询oracle的相关的文档。[q]如何使用hint提示[a]在select/delete/update后写/*+hint*/如select/*+index(table_nameindex_name)*/col1...注意/*和+之间不能有空格如用hint指定使用某个索引select/*+index(cbotab)*/col1fromcbotab;select/*+index(cbotabcbotab1)*/col1fromcbotab;select/*+index(acbotab1)*/col1fromcbotaba;其中，table_name是必须要写的，且如果在查询中使用了表的别名，在hint也要用表的别名来代替表名;index_name可以不必写，oracle会根据统计值选一个索引;如果索引名或表名写错了，那这个hint就会被忽略;在oss的存储过程中，经常用到如下的hint，还是有一些效果的/*+appendnologging*/表示不需要记录日志。这样，也能提高性能。insert/*+appendnologging*/intotesttblselectuseridfromsubscriberinfo--8.172sinsertintotesttblselectuseridfromsubscriberinfo--13.125关于hints的使用，网上的资料也比较少，这里提供一个，可以参考：SQL语句中的优化提示Hints的总结.doc5.使用提示要谨慎(hints)我看到我司短消息部