oracle是一个很成熟的数据库产品，当然性能方面也有不俗的表现。尤其是9i之后又做了很多好的改进。现在已经到12c了，不过本人只用过11g,最近有时间了，我把自己对性能的一些拙见总结一下。（有一些是来自网上，自己又给整理了一下）
ORACLE性能的体现主要在CPU利用率和I/O读写次数这两个方面。
自然优化也是围绕这两个方面展开。
[size=medium][b]1:oracle的内存结构[/b][/size]
要说性能，先得说一下oracle的内存结构。
1.1：主要分SGA(system global area)和PGA(program global area)两大部分。
SGA主要包括SQL语句，数据，数据字典，redo日志等的缓存区。
PGA主要包括SQL语句排序区，游标状态区，会话信息区以及堆栈区。
这两部分的内存分配在一定程度上也会影响性能。虽然这两部分的大小，oracle会根据情况自动分配，不过也可以根据自己的需要进行设定，毕竟oracle没有业务人员懂业务，自然分配的不是那么智能。
ORACEL官方的建议是，PGA(20%)SGA(80%)
[b]
1.2数据块[/b]
每一次I/O读写的大小。一般是，2K,4K,8K,16K
块的设定会通过I/0读写次数影响性能。
比如说OLTP（事务联机）系统，它每次执行的语句比较单一，但是执行次数很多，所以它的块可以设的小一些。DSS（数据仓库）系统它每次执行的语句非常复杂，但是执行次数少，它的块就可以设的大一些。
[size=medium][b]2：优化措施 （开发人员的角度）[/b][/size]
优化之前，先说一下怎么去看执行计划。
命令：
set autotrace on
set autotrace traceonly (不显示语句执行内容，只显示执行计划)
执行计划主要看已下指标：
响应时间，cost(越小越好),consistent gets(内存消耗)，physical reads(I/0消耗)
这里要说明一下，每回进行性能测试的时候，要清一下缓存。
命令：
alter system flush buffer_cache;
alter system flush shared_pool;
[b]2.1索引建立的策略[/b]
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系统。
[b]2.3表连接的策略[/b]
在进行多表连接的时候，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进行强制指定。
[b]2.4 Oracle优化排序的操作[/b]
尽可能避免排序;尽可能在内存中排序;分配合适的临时空间以减少空间分配调用。
需要进行排序的操作：
A、创建索引;
B、涉及到索引维护的并行插入
C、order by或者group by(尽可能对索引字段排序)
D、Distinct
E、union/intersect/minus
F、sort-merge join
G、analyze命令(仅可能使用estamate而不是compute)
[b]2.5 Oracle 并行处理[/b]
2.5.1查询
Sql代码
SELECT /*+ Parallel(t,8) */ * FROM emp t;
2.5.2创建索引
Sql代码
create index idx_emp_test on emp(empno,ename,job) nologging parallel 32;
2.5.3.插入
Sql代码
INSERT /*+ append parallel(30) */
INTO t_a
SELECT /*+ parallel(30) */
FROM t_b
2.5.4 总结
自动化并行度，数据仓库应用程序通常会利用并行处理信息的优势，迅速有效地处理数据，特别是在大表上查询数据时，或者是一个非常复杂的连接查询时经常使用并行查询，在执行并行操作时，优化器应该使用并行度（Degree of Parallelism ，DOP），可以在查询本身内指定（通过+PARALLEL优化器提示），也可以作为表或索引本身的一个属性（通过它的PARALLEL属性），但要精确地确定一个合适的DOP是有难度的，通常需要对表是如何连接，哪些索引对并行处理有益，同时执行哪些查询都要有一个详细的了解。
Oracle 11g R2为我们带来了好消息，现在它可以自动为任何并行语句确定DOP，优化器使用两个新的初始化参数：PARALLEL_DEGREE_POLICY和PARALLEL_MIN_TIME_THRESHOLD来计算自动化并行度（ADOP）。例如，如果PARALLEL_DEGREE_POLICY被设为AUTO，如果查询确实能从并行操作受益，Oracle 11g R2优化器会首先确定一个合适的DOP值，如果查询的预计执行时间超出了PARALLEL_MIN_TIME_THRESHOLD可接受的值（单位：秒），并且有足够资源支持并行处理，它会允许查询执行，否则就会延迟执行，直到有足够的资源释放出来，这样可以防止一个并行查询过度地消耗资源，例如，所有并行执行线程，或集群环境中的所有CPU都被其它非并行操作占用了，值得注意的是ADOP特性无法扩展到并行恢复或并行复制，它们只适用于并行查询。
[b]2.6 Oracle 绑定变量 [/b]
变量绑定是OLTP系统中一个非常值得关注的技术。良好的变量绑定会使OLTP系统数据库中的SQL 执行速度飞快，内存效率极高；不使用绑定变量可能会使OLTP 数据库不堪重负，资源被SQL解析严重耗尽，系统运行缓慢。
当一个用户与数据库建立连接后，会向数据库发出操作请求，即向数据库送过去SQL语句。 Oracle 在接收到这些SQL后，会先对这个SQL做一个hash 函数运算，得到一个Hash值，然后到共享池中寻找是否有和这个hash 值匹配的SQL存在。 如果找到了，Oracle将直接使用已经存在的SQL 的执行计划去执行当前的SQL，然后将结果返回给用户。 如果在共享池中没有找到相同Hash 值的SQL，oracle 会认为这是一条新的SQL。 会进行解析。
在OLTP系统中，我们可以使用绑定变量是因为在OLTP中，SQL语句大多是比较简单或者操作的结果集都很小。当一个表上创建了索引，那么这种极小结果集的操作使用索引最合适，并且几乎所有的SQL的执行计划的索引都会被选择，因为这种情况下，索引可能只需要扫描几个数据块就可以定位到数据，而全表扫描将会相当耗资源。 因此，这种情况下，即使每个用户的谓词条件不一样，执行计划也是一样的，就是都用索引来访问数据，基本不会出现全表扫描的情况。 在这种执行计划几乎唯一的情况下，使用绑定变量来代替谓词常量，是合适的。
在OLAP系统中，SQL的操作就复杂很多，OLAP数据库上大多数时候运行的一些报表SQL，这些SQL经常会用到聚合查询（如：group by），而且结果集也是非常庞大，在这种情况下，索引并不是必然的选择，甚至有时候全表扫描的性能会更优于索引，即使相同的SQL，如果谓词不同，执行计划都可能不同。
[b]2.7 global temporary table[/b]
常用于临时表或中间表的应用。特点是，使用完后，oracle会自动清除表的数据。
http://hwhuang.iteye.com/blog/551864
[b]2.8物化视图[/b]
2.8.1物化视图的类型：ON DEMAND、ON COMMIT
二者的区别在于刷新方法的不同，ON DEMAND顾名思义，仅在该物化视图“需要”被刷新了，才进行刷新(REFRESH)，即更新物化视图，以保证和基表数据的一致性；而ON COMMIT是说，一旦基表有了COMMIT，即事务提交，则立刻刷新，立刻更新物化视图，使得数据和基表一致。
2.8.2ON DEMAND物化视图
物化视图的创建本身是很复杂和需要优化参数设置的，特别是针对大型生产数据库系统而言。但Oracle允许以这种最简单的，类似于普通视图的方式来做，所以不可避免的会涉及到默认值问题。也就是说Oracle给物化视图的重要定义参数的默认值处理是我们需要特别注意的。
物化视图的特点：
(1) 物化视图在某种意义上说就是一个物理表(而且不仅仅是一个物理表)，这通过其可以被user_tables查询出来，而得到佐证；
(2) 物化视图也是一种段(segment)，所以其有自己的物理存储属性；
(3) 物化视图会占用数据库磁盘空间，这点从user_segment的查询结果，可以得到佐证；
创建语句：create materialized view mv_name as select * from table_name
默认情况下，如果没指定刷新方法和刷新模式，则Oracle默认为FORCE和DEMAND。
物化视图的数据怎么随着基表而更新？
Oracle提供了两种方式，手工刷新和自动刷新，默认为手工刷新。也就是说，通过我们手工的执行某个Oracle提供的系统级存储过程或包，来保证物化视图与基表数据一致性。这是最基本的刷新办法了。自动刷新，其实也就是Oracle会建立一个job，通过这个job来调用相同的存储过程或包，加以实现。
ON DEMAND物化视图的特性及其和ON COMMIT物化视图的区别，即前者不刷新(手工或自动)就不更新物化视图，而后者不刷新也会更新物化视图，——只要基表发生了COMMIT。
创建定时刷新的物化视图：create materialized view mv_name refresh force on demand start with sysdate
next sysdate+1 （指定物化视图每天刷新一次）
上述创建的物化视图每天刷新，但是没有指定刷新时间，如果要指定刷新时间（比如每天晚上10：00定时刷新一次）：create materialized view mv_name refresh force on demand start with sysdate next to_date( concat( to_char( sysdate+1,'dd-mm-yyyy'),' 22:00:00'),'dd-mm-yyyy hh24:mi:ss')
2.8.3ON COMMIT物化视图
ON COMMIT物化视图的创建，和上面创建ON DEMAND的物化视图区别不大。因为ON DEMAND是默认的，所以ON COMMIT物化视图，需要再增加个参数即可。
需要注意的是，无法在定义时仅指定ON COMMIT，还得附带个参数才行。
创建ON COMMIT物化视图：create materialized view mv_name refresh force on commit as select * from table_name
备注：实际创建过程中，基表需要有主键约束，否则会报错（ORA-12014）
2.8.4物化视图的刷新
刷新（Refresh）：指当基表发生了DML操作后，物化视图何时采用哪种方式和基表进行同步。刷新的模式有两种：ON DEMAND和ON COMMIT。（如上所述）
刷新的方法有四种：FAST、COMPLETE、FORCE和NEVER。FAST刷新采用增量刷新，只刷新自上次刷新以后进行的修改。COMPLETE刷新对整个物化视图进行完全的刷新。如果选择FORCE方式，则Oracle在刷新时会去判断是否可以进行快速刷新，如果可以则采用FAST方式，否则采用COMPLETE的方式。NEVER指物化视图不进行任何刷新。
对于已经创建好的物化视图，可以修改其刷新方式，比如把物化视图mv_name的刷新方式修改为每天晚上10点刷新一次：alter materialized view mv_name refresh force on demand start with sysdate next to_date(concat(to_char(sysdate+1,'dd-mm-yyyy'),' 22:00:00'),'dd-mm-yyyy hh24:mi:ss')
2.8.5物化视图具有表一样的特征，所以可以像对表一样，我们可以为它创建索引，创建方法和对表一样。
2.8.6物化视图的删除：
虽然物化视图是和表一起管理的，但是在经常使用的PLSQL工具中，并不能用删除表的方式来删除（在表上右键选择‘drop’并不能删除物化视图），可以使用语句来实现：drop materialized view mv_name
普通视图和物化视图根本就不是一个东西，说区别都是硬拼到一起的，首先明白基本概念，普通视图是不存储任何数据的，他只有定义，在查询中是转换为对应的定义SQL去查询，而物化视图是将数据转换为一个表，实际存储着数据，这样查询数据，就不用关联一大堆表，如果表很大的话，会在临时表空间内做大量的操作。
普通视图的三个特征：
1）是简化设计，清晰编码的东西，他并不是提高性能的，他的存在只会降低性能（如一个视图7个表关联，另一个视图8个表，程序员不知道，觉得很方便，把两个视图关联再做一个视图，那就惨了），他的存在未了在设计上的方便性
2）其次，是安全，在授权给其他用户或者查看角度，多个表关联只允许查看，不允许修改，单表也可以同WITH READ ONLY来控制，当然有些项目基于视图做面向对象的开发，即在视图上去做INSTAND OF触发器，就我个人而言是不站同的，虽然开发上方便，但是未必是好事。
3）从不同的角度看不同的维度，视图可以划分维度和权限，并使多个维度的综合，也就是你要什么就可以从不同的角度看，而表是一个实体的而已，一般维度较少（如：人员表和身份表关联，从人员表可以查看人员的维度统计，从身份看，可以看不同种类的身份有那些人或者多少人），其次另一个如系统视图USER_TABLE、TAB、USER_OBJECTS这些视图，不同的用户下看到的肯定是不一样的，看的是自己的东西。
物化视图呢，用于OLAP系统中，当然部分OLTP系统的小部分功能未了提高性能会借鉴一点点，因为表关联的开销很大，所以在开发中很多人就像把这个代价交给定期转存来完成，ORACLE当然也提供了这个功能，就是将视图（或者一个大SQL）的信息转换为物理数据存储，然后提供不同的策略：定时刷还是及时刷、增量刷还是全局刷等等可以根据实际情况进行选择，总之你差的是表，不是视图。
[b]2.9 数据库的设计[/b]
要遵循三范式
1）属性的唯一性
2）每一条记录要有主键，并且其他字段依赖于主键。
3）每一个属性仅依赖于主键。
有些情况下，为了性能要求，可以违背三范式。这样有利有弊。虽然一定程度上提高了性能，但是破环了数据的一致性，需要程序去保证数据的一致性。
[b]2.10 merge into的用法[/b]
主要用于数据仓库系统。例如，从交易系统向数据仓库系统导数据，如果数据存在就更新，不存在则插入。
http://www.cnblogs.com/highriver/archive/2011/08/02/2125043.html
[b]2.11 分区技术[/b]
2.11.1分区表
1）分区种类
范围分区(Range)，HASH分区，LIST分区，复合分区(Range-Hash,Range-List)
11g新特性：
间隔分区(定时创建新的分区)，系统分区，组合分区(Range-Range,List-List,List-Hash,List-Range)，虚拟分区(建立一个虚拟字段当作分区字段，例如SUBSTR(order_id,1,3))，引用分区
2）分区的管理功能
增加（ADD），删除（DELETE），合并（MERGE），清空（TRUNCATE），交换（EXCHANGE），压缩（COALESE），移动（MOVE），分离（SPLIT），更名（RENAME）
3）分区的选择策略
范围分区特别适合按时间周期进行数据的存储。并且数据管理能力强（数据迁移，数据交换）
但是可能导致数据分布不均匀。
HASH分区分布均匀，易于实施，但是数据管理能力弱。
举个例子：
银行的数据有两个表，账户信息和交易明细。对于账户信息没有什么周期概念，适合用HASH分区，而交易明细则合适用范围分区。
列表分区通过对分区字段的离散值进行分区，只支持单个字段，数据不均匀，实施难度大。
复合分区，可以实现以上几种分区的组合，相对优点比较全面，可以实现粒度更细的操作。
2.11.2分区索引
1）分区种类
localPrefixed:如果局部索引的索引列以分区键开头，则称为前缀局部索引。GlobalPrefixed：分区字段和索引字段不一致（分区的变化，需要重新建索引）
表用a列作分区，索引用b做局部分区索引，若where条件中用b来查询，那么oracle会扫描所有的表和索引的分区，成本会比分区更高，此时可以考虑用b做全局分区索引
localNon-prefixed：如果局部索引的列不是以分区键开头，或者不包含分区键列，则称为非前缀索引。 （分区的变化，不需要重新建索引）
2）分区的管理功能
删除（DROP），修改（MODIFY），重建（REBUILD），分离（SPLIT），更名（RENAME）
3）分区索引的选择策略
表分区字段是索引字段的前缀（复合索引）的时候，用localPrefixed.
索引是唯一索引，并且不是分区字段，用GlobalPrefixed。
性能还可以，但对分区的管理性和可用性要求高要使用localNon-prefixed。
数据仓库选择localNon-prefixed，OLTP系统选择GlobalPrefixed。
2.11.3分区的应用
1）表的大小：当表的大小超过1.5GB-2GB，或对于OLTP系统，纪录超过1000万，都应考虑对表进行分区。
2）基于大表的查询访问，只访问表中少量的数据（分区），大大提高了查询速度。
3）数据维护方面，按时间段删除成批的数据，例如按月删除历史数据。
对于这样的表需要考虑进行分区，以满足维护需要。
4）数据的迁移：利用分区交换实现数据的迁移。不同于传统的delete,insert操作，更高效。分区交换，可以实现分区和非分区，以及子分区和分区表的数据交换。其中LOCAL索引自动维护。GLOBAL索引必须重建。
下面举个例子：
从表BF_FROM向BF_TO导入12月份数据。（BF_TO是按月份进行分区的）
步骤1：建立中间表（BF_TMP）
CREATE TABLE BF_TMP NOLOGGING TABLESPACE TS_TMP AS SELECT * FROM BT_FROM WHERE time between 20121101 and 20130101
步骤2: 交换分区
ALTER TABLE BF_TO EXCHANGE PARTITION P12 WITH TABLE BF_TMP INCLUDING INDEXES WITH VALIDATION;
5）并行数据操作：对于经常执行并行操作的表应考虑进行分区。
6）表的可用性：当对表的部分数据可用性要求很高的时候，应考虑分区。
[b]2.12 awr的使用[/b]
分析sql的性能。
http://yufeng0471.iteye.com/blog/888580
[b]2.13 CONTEXT 索引[/b]
用于全文检索
http://www.linuxidc.com/Linux/2012-06/63705.htm
[b]2.14 sql query result cache的使用[/b]
查询记录数很多，结果集很小。
重复查询频度高。
结果相对查询频度，变化频度不高。
select /*+ RESULT_CACHE */ owner,object_name
2 from ht01 where object_id=888;
几个重要的参数：
result_cache_mode
result_cache_max_size
result_cache_max_result
[b]
2.15 LOB大对象的使用[/b]
CLOB:字符LOB.用于存储大量的文本信息.采用默认字符集存储
NCLOB:用于存储字符LOB,采用数据库的国家字符集来存储字符.而不是数据库的默认字符集.
BLOB:二进制LOB,存储二进大量的二进制信息.存储时不会进行字符集转换.
CLOB和BLOG在ORACLE 10G中可存储8TB字节.
BFILE:二进制文件LOB,只是一个文件指针.具体的文件存储在操作系统中.
SecureFile功能是oracle 11g中对大对象（LOB）存储格式的完全重新设计实现，原来的LOB存储格式现在通称为BASIXFILE，它仍然是默认的存储方法，但是SECURFILE关键字开启了新的存储方法，它允许加密、利用压缩节约空间和数据重复消除。
[b]2.16 ORACLE的RAC[/b]
Oracle RAC采用了“sharing everything”的实现模式,通过CPU共享和存储设备共享来实现多节点之间的无缝集群，用户提交的每一项任务被自动分配给集群中的多台机器执行，
RAC是一个软件可以使你通过运行多个依赖相同Database的Instance，使用Cluster硬件。数据库files被存放在物理或是逻辑上连接每个节点的磁盘上。以便于每个活动的Instance都可以对files进行读写操作。RAC软件管理着数据的访问。所以更改操作在Instances之间是被相互协调的，并且每个Instance看到的信息和数据镜像都是一致的。
ORACLE性能的体现主要在CPU利用率和I/O读写次数这两个方面。
自然优化也是围绕这两个方面展开。
[size=medium][b]1:oracle的内存结构[/b][/size]
要说性能，先得说一下oracle的内存结构。
1.1：主要分SGA(system global area)和PGA(program global area)两大部分。
SGA主要包括SQL语句，数据，数据字典，redo日志等的缓存区。
PGA主要包括SQL语句排序区，游标状态区，会话信息区以及堆栈区。
这两部分的内存分配在一定程度上也会影响性能。虽然这两部分的大小，oracle会根据情况自动分配，不过也可以根据自己的需要进行设定，毕竟oracle没有业务人员懂业务，自然分配的不是那么智能。
ORACEL官方的建议是，PGA(20%)SGA(80%)
[b]
1.2数据块[/b]
每一次I/O读写的大小。一般是，2K,4K,8K,16K
块的设定会通过I/0读写次数影响性能。
比如说OLTP（事务联机）系统，它每次执行的语句比较单一，但是执行次数很多，所以它的块可以设的小一些。DSS（数据仓库）系统它每次执行的语句非常复杂，但是执行次数少，它的块就可以设的大一些。
[size=medium][b]2：优化措施 （开发人员的角度）[/b][/size]
优化之前，先说一下怎么去看执行计划。
命令：
set autotrace on
set autotrace traceonly (不显示语句执行内容，只显示执行计划)
执行计划主要看已下指标：
响应时间，cost(越小越好),consistent gets(内存消耗)，physical reads(I/0消耗)
这里要说明一下，每回进行性能测试的时候，要清一下缓存。
命令：
alter system flush buffer_cache;
alter system flush shared_pool;
[b]2.1索引建立的策略[/b]
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系统。
[b]2.3表连接的策略[/b]
在进行多表连接的时候，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_products p
where c.channel_id = s.channel_id
and p.prod_id = s.prod_id
and c.channel_desc = 'Internet'
and p.prod_name = 'Laptop carrying case';[b]2.4 Oracle优化排序的操作[/b]
尽可能避免排序;尽可能在内存中排序;分配合适的临时空间以减少空间分配调用。
需要进行排序的操作：
A、创建索引;
B、涉及到索引维护的并行插入
C、order by或者group by(尽可能对索引字段排序)
D、Distinct
E、union/intersect/minus
F、sort-merge join
G、analyze命令(仅可能使用estamate而不是compute)
[b]2.5 Oracle 并行处理[/b]
2.5.1查询
Sql代码
SELECT /*+ Parallel(t,8) */ * FROM emp t;
2.5.2创建索引
Sql代码
create index idx_emp_test on emp(empno,ename,job) nologging parallel 32;
2.5.3.插入
Sql代码
INSERT /*+ append parallel(30) */
INTO t_a
SELECT /*+ parallel(30) */
FROM t_b
2.5.4 总结
自动化并行度，数据仓库应用程序通常会利用并行处理信息的优势，迅速有效地处理数据，特别是在大表上查询数据时，或者是一个非常复杂的连接查询时经常使用并行查询，在执行并行操作时，优化器应该使用并行度（Degree of Parallelism ，DOP），可以在查询本身内指定（通过+PARALLEL优化器提示），也可以作为表或索引本身的一个属性（通过它的PARALLEL属性），但要精确地确定一个合适的DOP是有难度的，通常需要对表是如何连接，哪些索引对并行处理有益，同时执行哪些查询都要有一个详细的了解。
Oracle 11g R2为我们带来了好消息，现在它可以自动为任何并行语句确定DOP，优化器使用两个新的初始化参数：PARALLEL_DEGREE_POLICY和PARALLEL_MIN_TIME_THRESHOLD来计算自动化并行度（ADOP）。例如，如果PARALLEL_DEGREE_POLICY被设为AUTO，如果查询确实能从并行操作受益，Oracle 11g R2优化器会首先确定一个合适的DOP值，如果查询的预计执行时间超出了PARALLEL_MIN_TIME_THRESHOLD可接受的值（单位：秒），并且有足够资源支持并行处理，它会允许查询执行，否则就会延迟执行，直到有足够的资源释放出来，这样可以防止一个并行查询过度地消耗资源，例如，所有并行执行线程，或集群环境中的所有CPU都被其它非并行操作占用了，值得注意的是ADOP特性无法扩展到并行恢复或并行复制，它们只适用于并行查询。
[b]2.6 Oracle 绑定变量 [/b]
变量绑定是OLTP系统中一个非常值得关注的技术。良好的变量绑定会使OLTP系统数据库中的SQL 执行速度飞快，内存效率极高；不使用绑定变量可能会使OLTP 数据库不堪重负，资源被SQL解析严重耗尽，系统运行缓慢。
当一个用户与数据库建立连接后，会向数据库发出操作请求，即向数据库送过去SQL语句。 Oracle 在接收到这些SQL后，会先对这个SQL做一个hash 函数运算，得到一个Hash值，然后到共享池中寻找是否有和这个hash 值匹配的SQL存在。 如果找到了，Oracle将直接使用已经存在的SQL 的执行计划去执行当前的SQL，然后将结果返回给用户。 如果在共享池中没有找到相同Hash 值的SQL，oracle 会认为这是一条新的SQL。 会进行解析。
在OLTP系统中，我们可以使用绑定变量是因为在OLTP中，SQL语句大多是比较简单或者操作的结果集都很小。当一个表上创建了索引，那么这种极小结果集的操作使用索引最合适，并且几乎所有的SQL的执行计划的索引都会被选择，因为这种情况下，索引可能只需要扫描几个数据块就可以定位到数据，而全表扫描将会相当耗资源。 因此，这种情况下，即使每个用户的谓词条件不一样，执行计划也是一样的，就是都用索引来访问数据，基本不会出现全表扫描的情况。 在这种执行计划几乎唯一的情况下，使用绑定变量来代替谓词常量，是合适的。
在OLAP系统中，SQL的操作就复杂很多，OLAP数据库上大多数时候运行的一些报表SQL，这些SQL经常会用到聚合查询（如：group by），而且结果集也是非常庞大，在这种情况下，索引并不是必然的选择，甚至有时候全表扫描的性能会更优于索引，即使相同的SQL，如果谓词不同，执行计划都可能不同。
[b]2.7 global temporary table[/b]
常用于临时表或中间表的应用。特点是，使用完后，oracle会自动清除表的数据。
http://hwhuang.iteye.com/blog/551864
[b]2.8物化视图[/b]
2.8.1物化视图的类型：ON DEMAND、ON COMMIT
二者的区别在于刷新方法的不同，ON DEMAND顾名思义，仅在该物化视图“需要”被刷新了，才进行刷新(REFRESH)，即更新物化视图，以保证和基表数据的一致性；而ON COMMIT是说，一旦基表有了COMMIT，即事务提交，则立刻刷新，立刻更新物化视图，使得数据和基表一致。
2.8.2ON DEMAND物化视图
物化视图的创建本身是很复杂和需要优化参数设置的，特别是针对大型生产数据库系统而言。但Oracle允许以这种最简单的，类似于普通视图的方式来做，所以不可避免的会涉及到默认值问题。也就是说Oracle给物化视图的重要定义参数的默认值处理是我们需要特别注意的。
物化视图的特点：
(1) 物化视图在某种意义上说就是一个物理表(而且不仅仅是一个物理表)，这通过其可以被user_tables查询出来，而得到佐证；
(2) 物化视图也是一种段(segment)，所以其有自己的物理存储属性；
(3) 物化视图会占用数据库磁盘空间，这点从user_segment的查询结果，可以得到佐证；
创建语句：create materialized view mv_name as select * from table_name
默认情况下，如果没指定刷新方法和刷新模式，则Oracle默认为FORCE和DEMAND。
物化视图的数据怎么随着基表而更新？
Oracle提供了两种方式，手工刷新和自动刷新，默认为手工刷新。也就是说，通过我们手工的执行某个Oracle提供的系统级存储过程或包，来保证物化视图与基表数据一致性。这是最基本的刷新办法了。自动刷新，其实也就是Oracle会建立一个job，通过这个job来调用相同的存储过程或包，加以实现。
ON DEMAND物化视图的特性及其和ON COMMIT物化视图的区别，即前者不刷新(手工或自动)就不更新物化视图，而后者不刷新也会更新物化视图，——只要基表发生了COMMIT。
创建定时刷新的物化视图：create materialized view mv_name refresh force on demand start with sysdate
next sysdate+1 （指定物化视图每天刷新一次）
上述创建的物化视图每天刷新，但是没有指定刷新时间，如果要指定刷新时间（比如每天晚上10：00定时刷新一次）：create materialized view mv_name refresh force on demand start with sysdate next to_date( concat( to_char( sysdate+1,'dd-mm-yyyy'),' 22:00:00'),'dd-mm-yyyy hh24:mi:ss')
2.8.3ON COMMIT物化视图
ON COMMIT物化视图的创建，和上面创建ON DEMAND的物化视图区别不大。因为ON DEMAND是默认的，所以ON COMMIT物化视图，需要再增加个参数即可。
需要注意的是，无法在定义时仅指定ON COMMIT，还得附带个参数才行。
创建ON COMMIT物化视图：create materialized view mv_name refresh force on commit as select * from table_name
备注：实际创建过程中，基表需要有主键约束，否则会报错（ORA-12014）
2.8.4物化视图的刷新
刷新（Refresh）：指当基表发生了DML操作后，物化视图何时采用哪种方式和基表进行同步。刷新的模式有两种：ON DEMAND和ON COMMIT。（如上所述）
刷新的方法有四种：FAST、COMPLETE、FORCE和NEVER。FAST刷新采用增量刷新，只刷新自上次刷新以后进行的修改。COMPLETE刷新对整个物化视图进行完全的刷新。如果选择FORCE方式，则Oracle在刷新时会去判断是否可以进行快速刷新，如果可以则采用FAST方式，否则采用COMPLETE的方式。NEVER指物化视图不进行任何刷新。
对于已经创建好的物化视图，可以修改其刷新方式，比如把物化视图mv_name的刷新方式修改为每天晚上10点刷新一次：alter materialized view mv_name refresh force on demand start with sysdate next to_date(concat(to_char(sysdate+1,'dd-mm-yyyy'),' 22:00:00'),'dd-mm-yyyy hh24:mi:ss')
2.8.5物化视图具有表一样的特征，所以可以像对表一样，我们可以为它创建索引，创建方法和对表一样。
2.8.6物化视图的删除：
虽然物化视图是和表一起管理的，但是在经常使用的PLSQL工具中，并不能用删除表的方式来删除（在表上右键选择‘drop’并不能删除物化视图），可以使用语句来实现：drop materialized view mv_name
普通视图和物化视图根本就不是一个东西，说区别都是硬拼到一起的，首先明白基本概念，普通视图是不存储任何数据的，他只有定义，在查询中是转换为对应的定义SQL去查询，而物化视图是将数据转换为一个表，实际存储着数据，这样查询数据，就不用关联一大堆表，如果表很大的话，会在临时表空间内做大量的操作。
普通视图的三个特征：
1）是简化设计，清晰编码的东西，他并不是提高性能的，他的存在只会降低性能（如一个视图7个表关联，另一个视图8个表，程序员不知道，觉得很方便，把两个视图关联再做一个视图，那就惨了），他的存在未了在设计上的方便性
2）其次，是安全，在授权给其他用户或者查看角度，多个表关联只允许查看，不允许修改，单表也可以同WITH READ ONLY来控制，当然有些项目基于视图做面向对象的开发，即在视图上去做INSTAND OF触发器，就我个人而言是不站同的，虽然开发上方便，但是未必是好事。
3）从不同的角度看不同的维度，视图可以划分维度和权限，并使多个维度的综合，也就是你要什么就可以从不同的角度看，而表是一个实体的而已，一般维度较少（如：人员表和身份表关联，从人员表可以查看人员的维度统计，从身份看，可以看不同种类的身份有那些人或者多少人），其次另一个如系统视图USER_TABLE、TAB、USER_OBJECTS这些视图，不同的用户下看到的肯定是不一样的，看的是自己的东西。
物化视图呢，用于OLAP系统中，当然部分OLTP系统的小部分功能未了提高性能会借鉴一点点，因为表关联的开销很大，所以在开发中很多人就像把这个代价交给定期转存来完成，ORACLE当然也提供了这个功能，就是将视图（或者一个大SQL）的信息转换为物理数据存储，然后提供不同的策略：定时刷还是及时刷、增量刷还是全局刷等等可以根据实际情况进行选择，总之你差的是表，不是视图。
[b]2.9 数据库的设计[/b]
要遵循三范式
1）属性的唯一性
2）每一条记录要有主键，并且其他字段依赖于主键。
3）每一个属性仅依赖于主键。
有些情况下，为了性能要求，可以违背三范式。这样有利有弊。虽然一定程度上提高了性能，但是破环了数据的一致性，需要程序去保证数据的一致性。
[b]2.10 merge into的用法[/b]
主要用于数据仓库系统。例如，从交易系统向数据仓库系统导数据，如果数据存在就更新，不存在则插入。
http://www.cnblogs.com/highriver/archive/2011/08/02/2125043.html
[b]2.11 分区技术[/b]
2.11.1分区表
1）分区种类
范围分区(Range)，HASH分区，LIST分区，复合分区(Range-Hash,Range-List)
11g新特性：
间隔分区(定时创建新的分区)，系统分区，组合分区(Range-Range,List-List,List-Hash,List-Range)，虚拟分区(建立一个虚拟字段当作分区字段，例如SUBSTR(order_id,1,3))，引用分区
2）分区的管理功能
增加（ADD），删除（DELETE），合并（MERGE），清空（TRUNCATE），交换（EXCHANGE），压缩（COALESE），移动（MOVE），分离（SPLIT），更名（RENAME）
3）分区的选择策略
范围分区特别适合按时间周期进行数据的存储。并且数据管理能力强（数据迁移，数据交换）
但是可能导致数据分布不均匀。
HASH分区分布均匀，易于实施，但是数据管理能力弱。
举个例子：
银行的数据有两个表，账户信息和交易明细。对于账户信息没有什么周期概念，适合用HASH分区，而交易明细则合适用范围分区。
列表分区通过对分区字段的离散值进行分区，只支持单个字段，数据不均匀，实施难度大。
复合分区，可以实现以上几种分区的组合，相对优点比较全面，可以实现粒度更细的操作。
2.11.2分区索引
1）分区种类
localPrefixed:如果局部索引的索引列以分区键开头，则称为前缀局部索引。GlobalPrefixed：分区字段和索引字段不一致（分区的变化，需要重新建索引）
表用a列作分区，索引用b做局部分区索引，若where条件中用b来查询，那么oracle会扫描所有的表和索引的分区，成本会比分区更高，此时可以考虑用b做全局分区索引
localNon-prefixed：如果局部索引的列不是以分区键开头，或者不包含分区键列，则称为非前缀索引。 （分区的变化，不需要重新建索引）
2）分区的管理功能
删除（DROP），修改（MODIFY），重建（REBUILD），分离（SPLIT），更名（RENAME）
3）分区索引的选择策略
表分区字段是索引字段的前缀（复合索引）的时候，用localPrefixed.
索引是唯一索引，并且不是分区字段，用GlobalPrefixed。
性能还可以，但对分区的管理性和可用性要求高要使用localNon-prefixed。
数据仓库选择localNon-prefixed，OLTP系统选择GlobalPrefixed。
2.11.3分区的应用
1）表的大小：当表的大小超过1.5GB-2GB，或对于OLTP系统，纪录超过1000万，都应考虑对表进行分区。
2）基于大表的查询访问，只访问表中少量的数据（分区），大大提高了查询速度。
3）数据维护方面，按时间段删除成批的数据，例如按月删除历史数据。
对于这样的表需要考虑进行分区，以满足维护需要。
4）数据的迁移：利用分区交换实现数据的迁移。不同于传统的delete,insert操作，更高效。分区交换，可以实现分区和非分区，以及子分区和分区表的数据交换。其中LOCAL索引自动维护。GLOBAL索引必须重建。
下面举个例子：
从表BF_FROM向BF_TO导入12月份数据。（BF_TO是按月份进行分区的）
步骤1：建立中间表（BF_TMP）
CREATE TABLE BF_TMP NOLOGGING TABLESPACE TS_TMP AS SELECT * FROM BT_FROM WHERE time between 20121101 and 20130101
步骤2: 交换分区
ALTER TABLE BF_TO EXCHANGE PARTITION P12 WITH TABLE BF_TMP INCLUDING INDEXES WITH VALIDATION;
5）并行数据操作：对于经常执行并行操作的表应考虑进行分区。
6）表的可用性：当对表的部分数据可用性要求很高的时候，应考虑分区。
[b]2.12 awr的使用[/b]
分析sql的性能。
http://yufeng0471.iteye.com/blog/888580
[b]2.13 CONTEXT 索引[/b]
用于全文检索
http://www.linuxidc.com/Linux/2012-06/63705.htm
[b]2.14 sql query result cache的使用[/b]
查询记录数很多，结果集很小。
重复查询频度高。
结果相对查询频度，变化频度不高。
select /*+ RESULT_CACHE */ owner,object_name
2 from ht01 where object_id=888;
几个重要的参数：
result_cache_mode
result_cache_max_size
result_cache_max_result
[b]
2.15 LOB大对象的使用[/b]
CLOB:字符LOB.用于存储大量的文本信息.采用默认字符集存储
NCLOB:用于存储字符LOB,采用数据库的国家字符集来存储字符.而不是数据库的默认字符集.
BLOB:二进制LOB,存储二进大量的二进制信息.存储时不会进行字符集转换.
CLOB和BLOG在ORACLE 10G中可存储8TB字节.
BFILE:二进制文件LOB,只是一个文件指针.具体的文件存储在操作系统中.
SecureFile功能是oracle 11g中对大对象（LOB）存储格式的完全重新设计实现，原来的LOB存储格式现在通称为BASIXFILE，它仍然是默认的存储方法，但是SECURFILE关键字开启了新的存储方法，它允许加密、利用压缩节约空间和数据重复消除。
[b]2.16 ORACLE的RAC[/b]
Oracle RAC采用了“sharing everything”的实现模式,通过CPU共享和存储设备共享来实现多节点之间的无缝集群，用户提交的每一项任务被自动分配给集群中的多台机器执行，
RAC是一个软件可以使你通过运行多个依赖相同Database的Instance，使用Cluster硬件。数据库files被存放在物理或是逻辑上连接每个节点的磁盘上。以便于每个活动的Instance都可以对files进行读写操作。RAC软件管理着数据的访问。所以更改操作在Instances之间是被相互协调的，并且每个Instance看到的信息和数据镜像都是一致的。
扫一扫
4468
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
