oracle性能优化总结
oracle是一个很成熟的数据库产品，当然性能方面也有不俗的表现。尤其是9i之后又做了很多好的改进。现在已经到12c了，不过本人只用过11g,最近有时间了，我把自己对性能的一些拙见总结一下。（有一些是来自网上，自己又给整理了一下）

ORACLE性能的体现主要在CPU利用率和I/O读写次数这两个方面。
自然优化也是围绕这两个方面展开。

1:oracle的内存结构
要说性能，先得说一下oracle的内存结构。
1.1：主要分SGA(system global area)和PGA(program global area)两大部分。
SGA主要包括SQL语句，数据，数据字典，redo日志等的缓存区。
PGA主要包括SQL语句排序区，游标状态区，会话信息区以及堆栈区。
这两部分的内存分配在一定程度上也会影响性能。虽然这两部分的大小，oracle会根据情况自动分配，不过也可以根据自己的需要进行设定，毕竟oracle没有业务人员懂业务，自然分配的不是那么智能。
ORACEL官方的建议是，PGA(20%)SGA(80%)

1.2数据块
每一次I/O读写的大小。一般是，2K,4K,8K,16K
块的设定会通过I/0读写次数影响性能。
比如说OLTP（事务联机）系统，它每次执行的语句比较单一，但是执行次数很多，所以它的块可以设的小一些。DSS（数据仓库）系统它每次执行的语句非常复杂，但是执行次数少，它的块就可以设的大一些。

2：优化措施 （开发人员的角度）
优化之前，先说一下怎么去看执行计划。
命令：
set autotrace on
set autotrace traceonly (不显示语句执行内容，只显示执行计划)
执行计划主要看已下指标：
响应时间，cost(越小越好),consistent gets(内存消耗)，physical reads(I/0消耗)
这里要说明一下，每回进行性能测试的时候，要清一下缓存。
命令：
alter system flush buffer_cache;
alter system flush shared_pool;

2.1索引建立的策略
Oracle的索引主要包含两类：BTree和位图索引。没有特别声明（create bitmap ind index on table(column)），oracle都建的是BTtree索引。BTtree索引又分为唯一索引，复合索引（聚簇索引），反向索引和函数索引（不推荐）等。
对于OLTP系统，一般都用BTtree索引（注意不是二叉树）。
对于DSS系统，最好用位图索引。
2.1.1BTtree索引
1）唯一索引：一般是主键字段，约束条件字段（where条件），基数大的字段（可分性高）
order by 字段，也尽量建立索引。这样就不用进行order by 了，减少了排序的消耗。
2）复合索引：条件定义模式固定，例如where条件中经常一起出现的字段。如果组合比较灵活，则分别建单一索引。
复合索引要注意前缀性风险。例如复合索引ind_1(a,b,c),如果没有a字段出现，则索引失效。
复合索引优先于单字段索引。复合索引中也要注意顺序，要按可选性高低或者条件定义的频度进行排序。例如，纳税人识别号，税务机关代码，月份。
3）反向索引：反向索引主要是建立在那些以序列号生成的列上，可以将本来是连在一起的index entry分散到不同的leaf block中去。当索引是从序列中取的时候，如果是一般的b-tree 索引，在大量的插入后会导致块的分裂以及树的倾斜，使用reverse key index可以使索引段条目被更均匀的分布。很多事务访问同一个块，对同一个块并发操作产生的I/0竞争。反向索引可以避免I/O竞争。缺点是当应用需要获取一段范围的数据时，reverse key index将不会被使用，因为键值不是连续的排列的。在这种情况下，CBO将会选择全表扫描。
4）函数索引：某表的一列在平常SQL中该列都是放在函数里面，为了能用到索引来提高检索速度。例如：create index idx_fun on emp (upper(name));

2.1.2位图索引

位图索引非常适合于决策支持系统(Decision Support System，DSS)和数据仓库，它们不应该用于通过事务处理应用程序访问的表。它们可以使用较少到中等基数(不同值的数量)的列访问非常大的表。尽管位图索引最多可达30个列，但通常它们都只用于少量的列。

例如，您的表可能包含一个称为Sex的列，它有两个可能值：男和女。这个基数只为2，如果用户频繁地根据Sex列的值查询该表，这就是位图索引的基列。当一个表内包含了多个位图索引时，您可以体会到位图索引的真正威力。如果有多个可用的位图索引，Oracle就可以合并从每个位图索引得到的结果集，快速删除不必要的数据。

下面的程序清单给出了一个创建位图索引的例子：
create bitmap index dept_idx2_bm on dept (deptno);
Index created.

对于有较低基数的列需要使用位图索引。性别列就是这样一个例子，它有两个可能值：男或女(基数仅为2)。位图对于低基数(少量的不同值)列来说非常快，这是因为索引的尺寸相对于B树索引来说小了很多。因为这些索引是低基数的B树索引，所以非常小，因此您可以经常检索表中超过半数的行，并且仍使用位图索引。

当大多数条目不会向位图添加新的值时，位图索引在批处理(单用户)操作中加载表(插入操作)方面通常要比B树做得好。当多个会话同时向表中插入行时不应该使用位图索引，在大多数事务处理应用程序中都会发生这种情况。位图索引更新时用的不是行级锁，而是位图锁，所以不适合平凡更新的OLTP系统，更合适查询量大的DSS系统。


2.3表连接的策略
在进行多表连接的时候，oracle有三种算法。
merge join，hash join，Nested Loops

2.3.1 merge join
操作通常分三步：
1）对连接的每个表做table access full;
2）对table access full的结果进行排序。
3）进行merge join对排序结果进行合并。
在全表扫描比索引范围扫描再通过rowid进行表访问更可取的情况下，merge join会比nested loops性能更佳。当表特别小或特别巨大的时候，实行全表访问可能会比索引范围扫描更有效。mrege join的性能开销几乎都在前两步。

2.3.2 hash join
对两个表进行全表扫描，然后oracle读取涉及连接的其中一个表，并且在内存里创建来自表的连接列的唯一关键字的位图。当读取和处理第二个表的行时，创建值的位图被用做过滤器。如果一个行成功的通过位图过滤，则hash算法用于数据查找和后来的连接。（适用于DSS系统。）

以下条件下hash join可能有优势：
两个巨大的表之间的连接。
在一个巨大的表和一个小表之间的连接。

2.3.3Nested Loops
会循环外表（驱动表），逐个比对和内表的连接是否符合条件。在驱动表比较小，内表比较大，而且内外表的连接列有索引的时候比较好。当SORT_AREA空间不足的时候，Oracle也会选择使用NL。基于Cost的Oracle优化器(CBO)会自动选择较小的表做外表。适用于OLTP系统。

2.3.4总结
对于子查询in/exsits来说，能用多表查询，尽量用多表查询。
如果不可避免使用，要遵循下面的规则。
1）限制性强的条件在子查询中，建议用in
2）限制性强的条件在主查询中，建议用exsits
原理：in是先执行子查询，exsits是先执行主查询。
对于多表查询，尽量将限制性最强的表作为驱动表，在被驱动表上的外键字段建立索引。
（oracle根据基数来自动识别驱动表和被驱动表）
另外，多表的顺序也要从小到大。
如果oracle优化器没有按照指定算法和顺序优化，那就用hint进行强制指定。

select /*+ ORDERED USE_NL(c, s, p)*/
c.channel_desc, s.quantity_sold, s.amount_sold
  from kj_sales s, kj_channels c, kj_produ