聆听安菲尔德的呐喊

字数 3413阅读 1415评论 0赞 0

传统数据仓库项目的优化手段（针对 Oracle+DataStage ）

普通手段分区，HASH-JOIN，数据仓库函数，物化视图，位图索引等等为大伙在数据仓库常用的技术，

而下面列举的tips为项目中常用的优化手段/技巧，绿色背景highlight的部分属于非常规手段，使用场景较为极端，需谨慎考量。

Oracle并行场景：

SQL*Loader 的parallel参数
事务失败回滚的并行处理 FAST_START_PARALLEL_ROLLBACK参数
expdp设置parallelism参数，设置多个datapump文件
大批量处理+并行处理(parallel)，减少select次数，逻辑清晰，尽可能一次select……jion 之后再进行统一分析函数的处理，

select
/*+ PARALLEL(Table_Name,并行数) */
统计函数 sum avg
case when then else end
over(partition by order by )
分析函数 lead/lag,rank,ratio_to_report,Period-over-period comparisons 等等...
from
Table_Name
group by
rollup ,cube 等等...
创建索引、rebuild、设置并行参数（譬如大批量ETL全量时，drop索引，ETL之后再create）
收集统计信息的 degree参数
还有aleter session enable parallel dml;
insert /*+ append parallel(Table_I,并行数) */
into Table_I nologging
select /*+ PARALLEL(A,并行数) PARALLEL(B,并行数) PARALLEL(C,并行数) */
……

nologging 在DML时往往很有用

Insert、updateInsert ,update,delete 场景

1、当然最快的仍然是create table NEW_TAB as select * from OLD_TAB

2、delete的时候如果数据量过大，可以权衡考虑全量导出建立：

CREATE TABLE NEW_TAB NOLOGGING PARALLEL as select * from OLD_TAB where 条件<>要delete的数据，再truncate原表，rename重命名新表。

update也可以同理，把update的思路写到select里面再truncate原表，rename新表。

MERGE：（同样可以使用并行,nologging）减少扫描表的次数，替代insert then update语句

例如：每月计算生产库里的会计科目成本，放入数据仓库的事实表，但有少部分的冲销凭证会影响近几个月的操作。原本的total delete+insert,或是insert新数据+时间范围内update的操作，换为merge where 时间覆盖可能发生冲销的范围即可。

参数：1、

alter session set workarea_size_policy=manual;

alter session set sort_area_size=107341824;

alter session set sort_area_retained_size=107341824;

alter session set db_file_multiblock_read_count=128;

在并行dml、并行select的ETL JOB里可以添加如上参数，10g版本似乎要重复两次才可以生效。

db_file_multiblock_read_count配合32k、16k的大block表空间使用，针对传统SATA盘，FC盘有效，增加单次IO的收益。

同理，有时候反范式冗余多维度与事实表到一块，组成长表，db_file_multiblock_read_count+32K/16K大block的性能也很好，但数据的适用场景就减少了，多数用于临时主题分析，数据集市。

2、

修改参数 _smm_auto_min_io_size 、smm_auto_max_io_size

增大每次hash join 的内存分配大小，提升group by性能，配合大PGA使用。

3、极端环境下（或测试环境，或是同步数据的非关键过渡库），打开参数 alter system set commit_write='batch,nowait'; （10gR2开始才有的特性）使得db在commit的时候，无需等待 LOG BUFFER写出到REDO LOGFILE，即返回commit完成，需要评估灾难时断电带来的风险，如有UPS可考虑打开。

注意：极端环境是指频繁的commit带来的log file sync等待成为瓶颈点的时候才考虑，才考虑！打开参数，多数情况下数据仓库不会有这个问题。

再极端一些，还可以把Online Redo文件加大至1~2G甚至更大，关闭归档，减少日志切换带来的等待，本条需要权衡场景，勿在生产环境随意使用。

外部表