关于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 fromfetch 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 3parallel process 1

sales

table

parallel process 2 parallel process 4parallel process 2

注意：并行从进程不会多于并行度的2倍

比如前面假设并行度为2，那么2*2=4个从进程