oracle数据库优化--基本概念

oracle性能不好，首要检查数据库服务器的硬件配置。包括内存参数调整，oracle9i以后可以在 企业管理器 enterprise manager 中 例程--配置--内存 中查看内存以及pool的大小，通过建议值来设置，该工作应该隔段时间就检查一次，因为pool的设置和实际的数据量是相关的。
一般windows32位服务器上，分配给oracle的内存不必超过1.7G，因为寻址能力就这么大，多余了也是浪费。整个物理内存的70%应该分配给oracle。
在 例程--配置--内存 中，可以看到共享池share pool，它里面存放的是package,procedure,function,常用sql，以及oracle数据字典等信息。因为这些信息是常用的，置入此内存可以避免经常解析和io读取，以提高性能。高速缓冲区（SGA）的作用时，将之前查询的结果集置入次内存，以方面再次查询时可以直接从内存读取，减少IO.还有一个PGA的区，是与用户连接的session有关的，以及用来排序的区域。在8i以前没个session都回分配一块内存用来排序，而9i以后，所有用户session可以共享，这样避免一个session的pga不够而另外用户的pga浪费的情况。

一般来说，数据库调优顺序是：磁盘(读的次数越少越好，因此需要改变查询策略，优化查询sql来减少读取次数)、网络(数据库服务器与应用服务器交互时，如果通过网络，则网络带宽可能会有限制，因为带宽分上下行，并且还会传输其他网络数据，因此带宽也是个要考虑的因素)、内存、CPU(后两者如果太差，性能肯定不好)。

oracle查询优化方式，两种:RBO,CBO
RBO rule based optimize 基于规则的优化 8i以前采用。因此关联查询中，要注意表的顺序
CBO cost based optimize 基于成本的优化 8i（包括）以后采用。与表的顺序、where中字段顺序无关。

关于索引：
B-TREE索引，结构如下：
root
/ | /
branch1 ........
/|/ ......................
leaf1......

leaf节点的结构是这样的：
|索引头|列长度|列内容|rowid(s)|

一个叶子节点的大小大概是8192*8bit，因此一个字段的长度即使是50，那么一个节点大概能分成100个子节点，那么100万的记录，也只需要3级节点即可索引完毕，因此一般b-tree的深度不超过4级，这样根据b-tree来查找一条记录，最多只需遍历4个节点找到rowid，再根据rowid查找磁盘即可得到最终记录数据。

对某列查询的结果集记录数如果通常都小于7%，则应该在该列添加索引。对b-tree来说，where xx is null条件是不会利用索引的，因此建议这种列应该设置默认值，以避免该列的值存在null的情况，同样group by 中如果该列有null索引也可能无效。

bitmap索引:
bitmap索引的结构也是树形结构，但是叶子节点的结构与b-tree不一样。bitmap叶子节点的结果大概如下：
<key1 start-rowid end-rowid bitmap>
<key2 start-rowid end-rowid bitmap>
......
其中bitmap的内容是110010100011100001这样的01组合形式，它的长度与start-rowid和end-rowid之间包含的rowid数量一致。这样假设范围内第9个rowid对应列的内容是key1，那么kei1对应的bitmap中第9个字符的值就是1，否则就是0。同样每个块的大小是8192*8，那么一个bitmap索引的叶子节点大概能索引10000条记录。
bitmap索引对null的字段依然有效，具体null的值在bitmap中是取0还是专门有个keyX的值是null来区分需要再查证。bitmap索引对or条件的查询效果非常好，它不适应与索引列的值经常变化的情况，如果索引列的值经常变化，那么对bitmap索引将是灾难性的，因为它要锁定所有相关的叶子节点所在的块来更新bitmap的值，它适用于决策支持系统。

函数索引：是b-tree索引的一种
函数索引需要注意的是，实际sql中用到的函数掩码的格式、大小写需要与简历索引中函数掩码的格式、大小写一致，否则函数索引可能无效。另外trunc()，trim[可以采用ltrim(rtrim(col_xxx))的方式来避免bug]索引可能会有不正常的情况，may be bug。建立函数索引需要当前用户有query rewrite的权限才能建立。
因为函数分为确定性函数和非确定性函数，因此建立函数索引只能建立在确定性函数上。确定性函数的意思是：针对同一个传入值，该函数将在任何时候任何情况下都返回一个确定的结果。

reverse索引：反转索引 是函数索引的一种
它通过反转记录的值，来得到高效的查询性能。在特殊的一些列中可以采用。

为表添加索引并执行analyze分析(后面会提到分析方式)后，执行执行某个sql时，数据库将根据分析的结果来确定它认为最优化的方式读取数据库得到最终结果，这种基于成本的优化由数据库自动完成。如果要强制某个sql优先采用某个索引，可以添加hint来实现，即sql书写成如下格式：
select /*index(user1.index_xxxx)*/ from table11 where .... 这样实际执行中会优先采用user1.index_xxxx这个索引，其中user1表示索引所在的owner，index_xxxx表示索引的名称(而不是索引列的名称)

为优化查询，一个规则是：尽早过滤更多的数据

通过plsql developer软件，view一个table，在general选项卡中，可以看到 Initial extent/next extent/%increase/max extent等内容，这些内容界定了该table的扩展大小方式等。initail extent表示初始大小，next extent表示当前extent不够用时下次增加的extent大小，%increase表示增加的比例，max extent表示最多可扩展的次数。其中next extent 和%increase的设置需要注意，因为此二者是相关的，如果扩展次数较多时，可能会发现next extent的变得非常大，甚至上G都有可能。

在plsql developer中，新建一个explain plan window(执行计划窗口)，将实际要执行的sql输入到窗口中，再按F5或者F8,则将显示数据库基于成本的执行计划。第一列description中，显示了实际查询会通过全表扫描还是索引等，一般来说全表扫描必将导致性能下降。其中最后三列值得注意:
cost 表示成本，通过数据库io访问和cpu性能计算得来
cadinality 根据遍历索引或者全表扫描的记录计算得来
bytes 表示访问的数据量
一个优化后的sql查询，以上3个值应该是越小越好。

oracle10g以后，oracle将会自动分析各个table，以便每次执行查询时都能获得最佳速度。之前版本必须手工分析(后面会提到分析方式)。

高水平线概念：

每个table有个高水平线，它将记录该table最后一次extent的块的最后一个点的物理地址，如rowid.实际执行全表扫描时，将扫描table的起点到该高水平线之前所有的数据块。因此即使对改表执行了delete操作，这些delete的记录会有一个标记标志其已经删除，但是全表扫描依然会扫描这些即使delete掉的数据块，因此会影响性能。所以如果要删除一个表的全部记录，应该采用truncate关键字，必要的时候可以重建改表 alter table xxx rebuild;

分析表 analyze :
执行分析的语句是
全表分析：analyze table xxx compute statistics;
分析5%的数据(速度更快)以及特定列和索引:analyze table xxx estimate statistics sample 5 percent for table for all indexes for all indexed columns;
执行analyze后，oracle会将分析结果存入数据字典中，而数据字典会存在共享池share pool中，因此执行sql时会马上查询该数据字典并得到优化的执行路径从而快速得到查询结果。

关于绑定变量：
看下面这段代码：

declare
v_aa varchar2(2);
v_temp number :=0;

v_aa :='00';
select col_22 into v_temp from table_xx where col_11 =v_aa;---sql1
....
v_aa :='01';
select col_22 into v_temp from table_xx where col_11 =v_aa;---sql2
....

上述代码中v_aa就是采用了绑定变量的方式，可以看到，以上sql1和sql2一模一样，oracle在实际执行时，只需要在执行sql1时做词法、语法、编译、安全检查等分析，而执行sql2时，它会发现该sql已经在共享池share pool中存在了，因此就省去了词法、语法、编译、安全检查等分析等分析过程，从而提高性能。但是很多时候，我们采用的是如下方式：
select col_22 into v_temp from table_xx where col_11 ='00';---sql1
select col_22 into v_temp from table_xx where col_11 ='01';---sql2
结果oracle不认为这两个sql是一致的，因此执行sql2之前又会做词法、语法、编译、安全检查等分析，则性能下降。
在实际程序开发中，如java中，我们建议采用perparedstatement就是这个原理。而在oracle过程函数中，游标是会默认的被认为是绑定变量方式的，因此如果采用了游标，就不必专门再定义一个变量来利用该策略。

但是有时候绑定变量可能也会导致性能下降，如某表有10万条记录，其中性别这一列分男、女两个值。如果使用了绑定变量where gender= v_gender 的方式，oracle可能会采用全表扫描的方式来查询(why?)，而如果采用了硬编码where gender='男'的方式，则直接利用了gender列上的索引则会非常快。

柱状图分析
对某一列进行柱状图分析，将会得到该列各种值存在的比例，从而可以避免绑定变量方式可能带来的低效.

关于全索引：
如果某个查询的结果以及where中条件列都在一个索引里面，则可以利用上全索引方式来达到高效。如：
select a,b from t_xxxx where id='zz'非常的慢，而这种sql又会经常频繁的执行，则可以添加一个索引：id,a,b(条件列id要放在第一位)这种全索引。这样执行上述sql时，只需要检索索引就能得到结果，而索引的查询是非常高效的。在9i之前，这种索引需要注意字段的顺序。

物化视图：meterialized view
要创建物化试图，必须enable query rewrite
物化试图的刷新方式有 fast、complete 方式。物化视图会将视图的结果集存入一个table中固化，并且可以在table上添加索引，因此能提高性能。实际运用mview时，可以直接查询mview对应的固化table，也可以查询与mview创建语句条件一致的原始表,oracle会自动从mview对应的table来做检索，而不会检索原始表。

视图的联合方式：交叉型、星型
如4个表的一个联合，则实际联合方式可能是
tab1------tab2
| |
| |
tab3------tab4
也可能是
tab1----tab2----tab3
|
|
tab4
不同的联合方式，每次两两联合过滤的数据不一样，则速度也可能不一样。

关于toad工具中比较有用的几个点：
如
dba-healthy check，能查询出数据命中率、缓存丢失率等。
最大缓存命中率应该>95%的才算好，否则说明必须要进行调整了
库缓存、字典缓存丢失率应该是0才好
分区数据占用率应<5%
dba-server statistics
tols-sga-trace等

数据分区：
通常一个表的记录超过1千万就应该考虑采用分区的方式。分区相当于将一个大表分成很多个小表
分区方式有：hash(计算出列的hash值来根据hash值分区)
range(范围分区)-----》list(从range引申出来的方式)
分区也是在某个字段上进行的，分区后再添加索引，必须添加分区索引而不是全索引
符合分区时，可以采用先range再hash或者先list再hash的方式，符合分区不能采用先hash再xx的方式。

将经常访问的枚举、字典、对照表置入缓冲区能提高性能
8i以前：alter table xxx cache;
8i后:alter table storage (buffer_pool keap);
缓存有三种方式：
default:默认方式，置入缓存中，如果缓存空间不够，根据某种算法可能会被移出缓存
keep:一直放入keep池中，始终不会清除
recycle：只要空间不够，就会清除。因此比default方式的更容易被清除。