oracle性能优化总结

金融行业构建数据仓库的过程中，大部分用到ORACLE，数据量虽无TB级，也有GB级了，性能优化自然必不可少。

总体上性能优化可以从两方面入手，一是业务，二是技术。

业务因各行业不同，看实际业务需求而定。

技术方面经常使用的是索引，分区，HINT等等。以下列举工作中常用的一些ORACLE优化技能。

1.  分区

Oracle可以对表进行分区，包括范围分区、哈希分区、列表分区，三种分区应用于不用的数据情况：

1)        范围分区可以对数据进行过期化处理。

2)        哈希分区可以让数据均匀分布，但是要求较低的字段重复率。

3)        当数据的值可能对应到某个分区时，可以使用列表分区。

分区表不仅可以提高查询效率，也易于对数据进行管理。

 

 

2.  CBO提示

Oracle中使用HINTS对SQL执行计划进行人工定制。

1)        /*+index(tablename indexname)*/

在查询语句中指定表tablename使用索引 indexname进行扫描，其中的表名可用别名代替，如下所示。

SELECT/*+INDEX(TIDX_FUNDBAL_DT_5)*/

 SUM(T.SHARES * B.NETVALUE) AS CAPITAL, SUM(T.SHARES) AS SHARES

  FROM FACT_FUNDBAL_DETAIL T,FACT_FUNDDAY_D B

 WHERE T.EFFECTIVE_FROM <= B.SK_EVALDATE

   AND T.EFFECTIVE_TO > B.SK_EVALDATE

   AND T.BK_FUNDCODE = B.BK_FUNDCODE

   AND B.SK_EVALDATE BETWEEN &PI_STARTDATE AND &PI_ENDDATE;

2)        /*+parallel(tablename degree)*/

对表使用并行，tablename对应表名，degree对应并行度。可在查询语句中使用如下。对fact_fundbal_detail使用并行。

SELECT/*+PARALLEL(T 4)*/

 SUM(T.SHARES * B.NETVALUE) AS CAPITAL, SUM(T.SHARES) AS SHARES

  FROM FACT_FUNDBAL_DETAIL T,FACT_FUNDDAY_D B

 WHERE T.EFFECTIVE_FROM <= B.SK_EVALDATE

   AND T.EFFECTIVE_TO > B.SK_EVALDATE

   AND T.BK_FUNDCODE = B.BK_FUNDCODE

   AND B.SK_EVALDATE BETWEEN &PI_STARTDATE AND &PI_ENDDATE;

在DML中也可以使用并行。

Alter session enable parallel dml; --DML中使用并行前，须要开户此项

insert/*+parallel(tgt_tablename4)*/into tgt_tablename

select * from src_tablename;

3)        /*+append*/

通过该提示，ORACLE不会检测目标表是否存在空闲的段，直接插入到表的最后，可以提高速度。

insert /*+append*/ into tgt_tablename

select * from src_tablename;

        同时该提示功能可能和并行提示一起使用，进一步提升速度。

insert/*+appendparallel(tgt_tablename 4)*/into tgt_tablename

select * from src_tablename;

Oraclt中还有其他hint提示的功能，例如rule，nowait，USE_NL等。这些提示功能，可以帮助我们进行SQL优化的分析及提升效率，但是在实际使用中，并不建议在SQL中大量使用这类功能，因为这样会降低SQL的灵活性及可维护性。而且ORACLE有自带的CBO可以自行确认最佳的执行方案，添加提示未必会比ORACLE给出的执行计划好。所以，在实际情况中，这类提示功能应该更多的应用于进行SQL优化的分析工作中，从而对系统数据库模型进行调整达到优化的目的，尽可能少的编入程序中。

 

 

3.  索引

Oracle中有三种索引：B树索引、位图索引、全文索引。各个索引的适用范围如下：

1)        当字段数据的重复率不是很高时，可以考虑使用B树索引。

2)        当字段数据的重复率较高且包含特定的查询方式，比如逻辑运算，这时可以考虑使用位图索引。

3)        如果需要对字段进行模糊查询，则应该考虑使用全文索引。

 

 

4.  其他优化技巧

1)        压缩表

对表进行压缩，不仅可以节省存储空间，也可以提高查询效率。当然，不是所有的表都需要进行压缩的，如果表的数据经常进行更新操作，那么这个表就不应该进行压缩，否则会适得其反。

如果已经有一张表但是没有进行压缩，可以使用下列语句进行压缩，如果表上有索引，还需要进行索引重建。

altertable table_name movecompress;--一般的表

altertable table_name movepartition partition_name compressparallel 8 nologging;--针对分区表,使用了并行进行压缩

alterindex INDEX_NAME rebuildparallel 8 nologging;--重建索引

也可以通过DDL进行压缩设置，如下所示:

createtable table_name compress

asselect * from src_tablename;