优化技巧【五、六】

技巧5：超大表与超小表HASH JOIN优化方法

做hash join时一定要是小表做驱动表，hash join时说的小表是segment_size小的表。区别于nested loop时的判定，返回结果少的表。以前我sql优化遇见一个sql做hash join发现，驱动表是大表，后来用swap_join_inputs 搞定了，后来就6分钟。

提问：

olap环境，一个超大的表几十GB与一个超级小表几十MB进行join，fact表（几十GB）与维度表进行连接（几十M），返回数据是你fact的80%以上（一般都是几亿条数据），这种情况我们怎么优化呢？

答：
首先我们可以几乎确定这种join总hash join ，其次，超大表和小表之间做HASH JOIN，一般会启用用并行，ORACLE在并行HASH JOIN的时候会用到很多技术，比如 HASH HASH, 或者BROADCAST，对于超大表和小表做HASH JOIN,一定要让小表进行广播(Broadcast)，通常情况下CBO会选择正确，但是如果统计信息不准，或者基数计算错误CBO选择了 HASH HASH join，这个时候就很慢，观察现象就是它在direct path write temp,（hash on disk join）这个时候就可以用HINT PQ_DISTRIBUTE 进行调整

PQ_DISTRIBUTE(驱动表 None, Broadcast) 如果外层表很小(HASH_AJ)，

这个时候可以用 PQ_DISTRIBUTE(驱动表 Broadcast,None)

下面我们看个例子：

下面我们看执行计划最后一列，PQ Distrib ，fact表有673G，第一个执行计划是错的，第一个sql在做

direct path write temp，我们观察执行计的时候也要注意 PQ Distrib 这列。

oracle在做hash 运算的时候，驱动表被hash到pga中，按常理说小表被hash到pga中是不会出现direct path write temp的，一个 进程 最大的 PGA 是2g（手工管理模式，自动管理模式默认1G），这个时候如果被hash的表不超过1G，那么基本上走的是hash hash，如果开启并行多个进程读取大表，broadcast是说多个进程，主进程分发任务给各个进程这样速度快。两个大表做hash join时不能走广播，只能走hash hash 这个时候绝对有direct path write temp等待，两个大表做hash 最好把数据分散，把数据分散到几个表中再做hash，大表hash这个时候pga会爆的，pga尽量设置超大，ssd，尽量把数据弄小。把temp表空间指定ssd上，因为这是数据仓库环境。

OLAP其实就是分区+并行。

OLAP性能瓶颈在CPU

一般的系统几乎没有说IO和CPU性能同时达到瓶颈。

技巧6：<>优化方法

点击(此处)折叠或打开

1. select count(*)
   
2. from test
   
3. where owner = ‘SYS‘
   
4.    AND CREATED < to_date(‘2012-01-01‘, ‘YYYY-MM-DD‘)
   
5.    AND OBJECT_ID <> 10

其中object_id是唯一键

有些web网站就要统计排除自己的用户人数。就这么搞count （object_id）-1

点击(此处)折叠或打开

1. select (select count(*)
   
2.           from test
   
3.          where owner = ‘SYS‘
   
4.            AND CREATED < to_date(‘2012-01-01‘, \‘YYYY-MM-DD\‘)) -
   
5.        (select count(*) from test where object_id = 10)
   
6. 
   
7.   from dual

优化技巧【五、六】

原文：http://blog.itpub.net/29990276/viewspace-1454935/