关于SQL优化的一些知识

　　关于SQL优化我提提几点总结，这里没有具体的实例，如果大家有兴趣，可以把他贴上来
　　
　　1.除非写频率很低，否则不要用RAID5，但是对于cache能够减少RAID5的负荷
　　
　　2.对于一个chained fetch ratio的，建议需要用一个高的pctfree来rebuild table
　　
　　3.如果你有一个很高的磁盘排序率，那么建议修改sort_area_size,将其增大
　　
　　4.buffer busy waits常常是由于很频繁的insert ，需要重建，或者没有充足的回滚段引起的
　　
　　还应观注一下event这列其他值，这是我们调优的关键一列，下面对常出现的event做以简要的说明：
　　
　　free buffer waits这个参数所标识是dbwr是否够用的问题，与IO很大相关的，
　　
　　当v$session_wait中的free buffer wait的条目很小或没有的时侯，说明你的系统的dbwr进程决对够用，
　　
　　不用调整；free buffer wait的条目很多，你的系统感觉起来一定很慢，这时说明你的dbwr已经不够用了，
　　
　　它产生的wio已经成为你的数据库性能的瓶颈，这时的解决办法如下：
　　
　　a.1增加写进程，同时要调整 db_block_lru_latches 参数
　　
　　示例：修改或添加如下两个参数
　　
　　db_writer_processes=4
　　
　　db_block_lru_latches=8
　　
　　a.2开异步IO，IBM这方面简单得多，hp则麻烦一些，可以与Hp工程师联系。
　　
　　5.递归SQL
　　
　　为了决定所有表和列在SQL中的名字是否正确，ORACLE必须通过数据字典检查有效性，这些信息通常在内存中（ROW CACHE）
　　
　　找到了，但是如果在内存中找不到，那么就需要要进行递归SQL
　　
　　6.在不改变源代码的前提下，可以通过两个参数调整来减少解析
　　
　　cursor_sharing
　　session_cached_cursors
　　
　　7.open cursor通常意义上仅是得到一个handle,但在某些情况下不是。
　　
　　open cursor只是简单的定位首记录的指针
　　
　　8.创建新表后，高水位为第5块
　　
　　9.hash join
　　
　　如果整个hash表能够填充到内存，将很好，否则就需要临时表来进行分配。
　　
　　所以hash join常常对于两个不同大小的表进行连接，也可以在并行模式和反向连接中工作的很好
　　
　　10.存储概要能够帮助从一个低版本移植到高版本，也可以将RULE移植到COST
　　
　　11.OR常常可以利用union来实现索引的使用，当然ORACLE优化器有时也会自动进行转换
　　
　　12.经常被> <等操作的列都要在可能的情况下使用索引
　　
　　13.叶块包含了上一块和下一块的指针，所以允许对于索引过的列，使用order by和使用> <等操作进行利用
　　
　　另外，对于索引扫描，即使没有任何记录，也会有3-4个IO，因为有纵向SCAN
　　
　　对于大表，深度为4（1个都块，2级分支块和叶块），但是常常头块和分支块通常都会在内存，所以只会有1到2个物理读块
　　
　　14.索引键值分离是很昂贵的，对于使用插入增量序列的值，可以避免索引分离
　　
　　15.不能压缩分区索引和位图索引
　　
　　16.唯一索引或者主键常常是很好的hash key，因为hash key不适合区域扫描
　　
　　17.由于普通的B树索引，仍然消耗了很大磁盘空间，并且insert update delete也同样有很大的负载，所以考虑采用IOT，这样就没有表了，索引本身就是表，但是结构可是索引结构。主键将和所有列存储在叶节点，这样就造成了可能对索引结构的性能下降，所以可以通过设置including 来将某些不常用的大字段列放到益出段，对于想BOLB字段，可以采用disable storage in row使得分离不常查询的BOLB字段分离到别的段和表空间
　　
　　18.query+current为逻辑读
　　
　　autotrace没有每一步的row count,也不包括cpu or elapsed times
　　
　　19.对于绑定变量与直方图的选择
　　
　　条件　　　　　　 绑定　　　　　　 直方图
　　----------------------------　 -------------------　　　-----------------------
　　搜索值不变　　　　不需要　　　　　　可能需要
　　range scan　　　　　　　　 不需要　　　　　　 需要
　　执行精确查找，
　　但有些值分布很少　　不需要　　　　　　需要
　　这个SQL执行很频繁，
　　期望快速执行　　　　需要　　　　　　　　 不需要
　　用户特定使用的
　　复杂的SQL　　　　　　　　可能不需要，因为最终　需要
　　用户可能不喜欢
　　
　　20.对于有null值的列要建立索引，那么最好使用default(where改成默认值)，并设置not null
　　
　　21.hash cluster能提高精确匹配，但不能在range scan中得到好处
　　
　　22.你可以分离表中频繁被访问的和不频繁被访问的列到不同的段中或者表中，来提高性能，如果是IOT，可以使用INCLUDING
　　
　　23. 范围搜索有时用PLSQL也是很好的，比如找到MAX和MIN值
　　
　　24.优化位图索引,bitmap_merge_area_size 和 minimize records_per_block (该参数还可以优化pctfree)
　　
　　minimize records_per_block能减少索引大小，但对全表扫描有副作用，他能造成块数的增长
　　
　　25.reset 高水位
　　move table
　　exp + truncate table + imp
　　ctas
　　
　　HWM 和 direct insert 有关系，direct insert 是从HWM 位置开始分配新的block 而不会使用HWM 以下的即使可以使用的block
　　
　　26.如果pctfree很小，那么可以考虑增长initrans在ctas中,如果pctfree=0，那么可能一个事务提出请求时已经添满了块，那么象UPDATE等在多SESSION操作时就会失败。所以，一个表被频繁的表扫描，那么确保pctfree不要太高，特别是在没有UPDATE的情况下
　　
　　27.多块读
　　
　　在WINNT和2000中，多块读不能超过128K(32bit)，所以最多是16个多块读在大多数UNIX文件系统中（不是RAW）是64K-1M
　　
　　28.并行查询
　　
　　满足下面条件：
　　
　　有多个CPU
　　
　　有剩余的CPU
　　
　　数据分离在多个磁盘上
　　
　　29.对于以响应时间为目标的，那么就要注意for update和order by操作，因为他们很难以first row方式优化
　　
　　30.nested loop响应时间较好
　　
　　32.hash_multiblock_io_count
　　
　　该参数为影响的hash bucket数，这个值大，可以减少hash bucket数对于很大的表，可以通过减低hash_multiblock_io_count来增加bucket数来提高性能
　　
　　33.尽管cluster是提供了优化的选项，但是在真实环境是很少用的，如果对于非常频繁的JOIN的可能有很大的提高性能，但通常是很伤害性能的
　　
　　34.star hint可以将维度表进行笛卡儿积join，而事实表可以经过组合索引
　　
　　35.不要期望使用多个位图索引来替换组合索引提高性能
　　
　　36.对于自join的SQL语句使用PLSQL可能更有效
　　
　　因为可以通过PLSQL的某些变量的处理方式，而不用多次访问同一个表
　　
　　37.如果你有一个exists子查询，而且子查询没有能使用索引，那么你就可以使用semijoin来提高性能
　　
　　38.一个in子查询，只是会执行一次，而exists子查询是对于父查询中的每行执行一次
　　
　　如果IN子查询不能通过子查询中有索引来提高性能，那么exists能
　　
　　如果exists不能通过父查询中的索引来提高性能，那么in可以
　　
　　39.不要总是设置always_semi_join为true，那样的话，可能对于不需要semi join也会使用该连接
　　
　　40.如果磁盘排序需要，每个排序进程都将分配自己的临时段在临时表空间中
　　
　　当使用并行查询，如果有很高的CPU负载，那么将会有很大的风险
　　
　　41.记住索引全扫描可能并不比全表扫描一定有效，因为他不能想全表扫描那样可以利用多块读，而且还可以采用并行
　　
　　42.并行进程能够将SQL执行分散成多个任务
　　
　　每个任务通过使用不同的CPU会有对应的从进程去并行处理
　　
　　（一个进程是一个拥有自己内存的执行单元，一个线程是在进程内的不同线程共享内存的执行单元）
　　
　　43.并行执行只是在全表处理或者分区及在分区表中执行本地索引时用到
　　
　　如：全表扫描
　　rebuild index
　　update (全表或分区表)
　　insert的并行子查询
　　本地索引使用
　　批量插入，象SQLLDR
　　创建临时表
　　
　　比如我们执行
　　select /*+ parallel(c1 ,2) */
　　con...
　　...
　　from customers c1
　　order by ...
　　
　　process a　　　　　　　　　　　 process b
　　fetch rows from　　　　　　fetch rows from
　　customers　　　　　　　　　 customers
　　|　　　　　　　　　　　　|
　　|　　　　　　　　　　　　|
　　process c　　　　　　　　　　　 process d
　　sort rows(a-k)　　　　　　 sort rows(l-z)
　　combine rows
　　|
　　return result set
　　
　　这里我们看到这个进程分散成4个进程，排序中各负责a-k和l-z，这样就可以并行处理
　　
　　我们在分区表中，也可以用一个并行从进程对应一个分区表
　　
　　如果你的并行度是3，那么你可能就比普通的执行速度提高3倍
　　
　　注意在单CPU下，如果使用并行，那么就可能造成性能下降，而且也要设置合适的并行度
　　
　　并行适合对于长时间运行的语句
　　
　　在oltp可能并不适合使用并行处理，因为事务并发比较多，每个用户都要使用CPU，CPU的负载本来就比较高
　　
　　所以并行处理还是比较适合olap中的批量导入,sqlldr,mis report和oltp中rebuild index
　　
　　可以通过sar -u看cpu使用情况
　　
　　44.并行度的设置
　　
　　ORACLE通常设置两个并行处理进程集，如果语句有scan、group by 和order by，那么需要3个并行处理集，但是
　　
　　由于scan并行进程已经用完，所以可以重用。
　　select /*+ parallel(s,2) */ customer_id,sum(sale_value)
　　from sales s
　　group by customer_id
　　order by 2
　　|-----scan------|　　|--sort group by--| |--sort order by --这里被重用了|
　　parallel process 1　 parallel process 3　parallel process 1
　　
　　sales
　　table
　　
　　parallel process 2　 parallel process 4　parallel process 2
　　
　　注意：并行从进程不会多于并行度的2倍
　　
　　比如前面假设并行度为2，那么2*2=4个从进程
　　
　　相关的参数
　　
　　parallel_threads_per_cpu
　　
　　这个是系统默认的并行度，如果建表的属性设置了并行度，那么在使用该表全表扫描时使用并行方式
　　
　　如果没有指定，但是在SQL语句中加了并行提示，在没有写并行度的情况下使用系统有个默认并行度
　　
　　使用v$pq_tqstat视图连接并行计划的细节
　　
　　在一些复杂的并行SQL语句，确保所有重要的步骤在并行下执行很重要，这样不至于由于某一步变成串行，而使并行的优势丢失
　　
　　45.如果有not in到自己的查询，那么使用antijoin可以改善性能，并能使用并行
　　
　　46.union可以使用并行
　　
　　minux和intersect不能使用并行
　　
　　47.当位图索引被更新时，会产生全表行级锁，所以在oltp强烈建议不要使用
　　
　　48.如果删除增加了系统的负载，那么可以采用逻辑删除的方式，比如设置一个删除列，更新该列
　　
　　49.直接sqlldr
　　
　　这种模型可以允许将数据块构件在内存中，并且直接插入数据到数据文件
　　
　　而通常模型是要经过buffer cache in sga 和dbwr
　　
　　并且直接创建新的块，就算free space存在也不使用
　　
　　如果是分区表，那么能够插入数据到高水位以上的取
　　
　　如果是非分区表，那么新的区必须生成，那么如果有2个并行度，就必须先新建2个新的分区。所以直接sqlldr可能影响全表扫描的性能
　　
　　另外他也不支持外键参考，约束，触发器，复制，IOT，LOB等
　　
　　当内存buffer cache产生冲突或者数据库写产生瓶径，那么对于传统的INSERT就可能造成等待，可以考虑直接LOAD
　　
　　如果支持异步IO，那么直接模式可以同时插入到多个数据文件
　　
　　插入可以并行的运行在分区或未分区的表，但是UPDATE和DELETE只能运行在分区表
　　
　　50.提交频率要尽量的少
　　
　　51.分区索引也可以在非分区表中
　　
　　52.不要建立全局位图索引，也不要建立分区位图索引和cluster table
　　
　　53.hash 分区的个数是2的乘方
　　
　　54.snapshot存在快照日志，而只要数据库有任何变动，都会记录到快照日志中
　　
　　有两种刷新方法
　　
　　fast refresh 和 complete refreshes
　　
　　如果源表有很频繁的更新，那么fast refresh 可能没有complete refreshes更有效
　　
　　如果变动比较小，对快照日志的负载也较小，就可以使用fast refresh
　　
　　批量load时，可以先drop snapshot，然后load，create snapshot
　　
　　55.物化视图
　　
　　基于事务刷新on commit
　　
　　基于时间间隔on demand
　　
　　56.触发器
　　
　　如果能使用after触发器的尽量用after,因为before，触发器执行前他会先锁住触发器表的行
　　
　　57.java提供更好的算术运算
　　
　　plsql偏向于数据库敏感类型的，比如数据库的类型操作啊，如果是java，那么由于类型不匹配而增加了转换工作
　　
　　58.sequence 中order 主要在ops和rac中使用
　　
　　59.如果SQL语句中有系统视图，那么由于系统视图没有统计信息，所以优化程序不知道执行的驱动顺序，所以要使用象ordered这样的提示
　　
　　60.natural key是由多个有意义的字段组成
　　
　　artificial key通常由序列号生成，他不需要更新，减少了锁冲突，而且索引也较小，是比较好的选择
　　
　　63.异步IO对于WIN总是有效，而UNIX常常使用RAW设备情况下使用异步IO
　　
　　64.对于多表联接的优化
　　
　　1).explain plan for
　　select ....;
　　set pagesize 0
　　set linesize 130
　　select * from table(dbms_xplan.display);
　　
　　2).看看评估的结果,查看多表连接的方式,谁是驱动表,是否使用索引,返回的数据集大小决定是否需要使用hash_join
　　
　　对于复杂的应用,你可以指定
　　alter session set workarea_size_policy = manual;
　　alter session set hash_area_size=???一个合理的值; --通常设置比初始化参数大
　　alter session set sort_area_size=???
　　
　　3).不要在客户段执行,通过设置sqlnet.ora可以避免通过net8这一层,直接在服务器上执行,减少客户连接传输消耗的时间和资源
　　
　　如果是客户段连接,可能设置更大的sdu,tdu(不能超过最大值),及相应的arraysize大小.
　　
　　4).多表联接的优化原则是:
　　
　　对性能产生影响最大的是表的驱动顺序,他会影响到执行计划和索引的使用
　　
　　驱动表的选择原则：
　　
　　4.1)在选择驱动表的时候尽量先选择返回结果集最小的表作为驱动表(如果是hashjoin，那么会用他建立hash表),
　　
　　然后选择跟另一个表进行join会得到最小集合的表进行join,依次类推
　　
　　例如from t1,t2,t3,t4 where ....
　　
　　oracle会根据user_tables等表中的信息及where过滤条件分析出返回最少数据集的第一个驱动表是谁（比如t1），
　　
　　然后分析,下一个与之join的表，通常选择：
　　
　　.where条件中这两个表有直接关联的字段（否则看执行计划就会看到类似于MERGE JOINCARTESIAN，这就是笛卡儿积），
　　
　　.找出与之关联后返回的结果集最小的那个表
　　.....
　　4.2交叉表
　　
　　如果是三个表join同一个字段,如t1.c1 = t2.c1 and t2.c1 =t3.c1,那么交叉表就是t2,因为t2出现了两次,
　　
　　这个交叉表的选择是个关键,ORACLE会把交叉表作为驱动表,所以我们优化的原则的交叉表尽量的小,在交叉表中约束条件尽量的多,以使该交叉表返回的记录集尽量的小
　　
　　附加条件(除去连接本身),在连接相同的列,附加条件有些时候列上的索引会被限制住
　　
　　比如 where t1.id = t2.id and t2.id =1111.那么附加条件在t2表上,并将其作为驱动表
　　
　　4.3外连接的表
　　
　　对于有外连接的表好象应该尽量作为最后驱动的表
　　
　　4.4如果自己根据explain plan for后，并结合应用，及丰富的优化的经验的话，就可以针对某个应用增加提示 （可能的话可以设置存储概要）
　　
　　相关的主要提示有ordered（这个用处非常大，他也可以节省下oracle去分析谁是驱动表的过程了），index相关的提示（会影响驱动表的选择）,full,use_hash、user_nl...等很多，会影响到join顺序
　　
　　5). keep一下小表对象
　　
　　6).另外可以通过建立索引，对于查少量列的尽量创建索引使其走index_ffs，如果有多个索引,想使用index_ffs,可以产生mergeindex_ffs
　　
　　7). 另外非长有用的几个提示是driving_site，richs_secret_hint
　　
　　8).注意，如果有子查询或者视图，就特别要注意是否能把外面的条件merge到视图和子查询里

查看本文来源