SQL优化训文档.ppt

什么是好的SQL语句？ 尽量简单，模块化 易读、易维护 节省资源 内存 CPU 扫描的数据块要少 少排序 不造成死锁 ORACLE优化器介绍 Oracle的优化器共有两种模式：RBO (基于规则)和CBO(基于成本)。 CBO在ORACLE7 引入，但在ORACLE8i 中才成熟。ORACLE 已经明确声明在ORACLE9i之后的版本中(ORACLE 10G )，RBO将不再支持。因此选择CBO 是必然的趋势。 RBO方式：优化器在分析SQL语句时,所遵循的是Oracle内部预定的一些规则。比如我们常见的，当一个where子句中的一列有索引时去走索引。 CBO方式：它是看语句的代价(Cost),这里的代价主要指Cpu和内存。优化器在判断是否用这种方式时,主要参照的是表及索引的统计信息。统计信息给出表的大小、有少行、每行的长度等信息。这些统计信息起初在库内是没有的，是通过对表进行分析后才出现的，很多时侯过期统计信息会令优化器做出一个错误的执行计划,因些应及时更新这些信息 CBO和RBO作为不同的SQL优化器，对SQL语句的执行计划产生重大影响。 以下提及的优化原则均基于CRO的优化模式。 SQL共享原理 ORACLE将执行过的SQL语句存放在内存的共享池(shared buffer pool)中，可以被所有的数据库用户共享。 当你执行一个SQL语句(有时被称为一个游标)时,如果它和之前的执行过的语句完全相同, ORACLE就能很快获得已经被解析的语句以及最好的 执行路径. 这个功能大大地提高了 SQL的执行性能并节省了内存的使用。 SQL共享的三个条件 当前被执行的语句和共享池中的语句必须完全相同 （包括大小写、空格、换行等） 两个语句所指的对象必须完全相同 （同义词与表是不同的对象） 两个SQL语句中必须使用相同的名字的绑定变量(bind variables) 什么叫做重编译问题 什么叫做重编译？ 下面这个语句每执行一次就需要在SHARE POOL 硬解析一 次，一百万用户就是一百万次，消耗CPU和内存，如果业务 量大，很可能导致宕库…… 如果绑定变量，则只需要硬解析一次，重复调用即可 select * from dConMsg where contract_no = 32013484095139 绑定变量解决重编译问题 未使用绑定变量的语句 begin for i in 1 .. 100 loop execute immediate insert into scott.test values (||i||); end loop; end; 使用绑定变量的语句 begin for i in 100 .. 200 loop execute immediate insert into scott.test values (:x) using i; end loop; end; SQL Tunning 的重点 SQL: insert, update, delete, select; 主要关注的是select 关注的是：如何用最小的硬件资源消耗、最少的响应时间定位数据位置 SQL优化的一般性原则 目标： 减少服务器资源消耗（主要是磁盘IO）； 设计方面： 尽量依赖oracle的优化器，并为其提供条件； 合适的索引，索引的双重效应，列的选择性； 编码方面： 利用索引，避免大表FULL TABLE SCAN； 合理使用临时表； 避免写过于复杂的sql，不一定非要一个sql解决问题； 在不影响业务的前提下减小事务的粒度； Tunning Tip的各个方面 1.不要让Oracle做得太多； 2.给优化器更明确的命令； 3.减少访问次数； 4.细节上的影响； 1.不要让Oracle做得太多 避免复杂的多表关联 select … from user_files uf, df_money_files dm, cw_charge_record cc where uf.user_no = dm.user_no and dm.user_no = cc.user_no and …… and not exists(select …) ??? 很难优化，随着数据量的增加性能的风险很大。 避免使用 ‘ * ‘ 当你想在SELECT子句中列出所有的COLUMN时,使用动态 SQL列引用 ‘*’ 是一个方便的方法.不幸的是,这是一个非常低 效的方法. 实际上,ORACLE在解析的过程中, 会将’*’ 依次转 换成所有的列名, 这个工作是通过查询数据字典完成的, 这意 味着将耗费更多的时间； 只提取你所要使用的列；