29 超大表与超小表HASH JOIN优化方法--优化主题系列

select * from a,b where a.id=b.id;

a表100GB b表50MB 如何优化？？

 

select *,(select * from b wherea.id=b.id) from a;  --标量子查询

A表100GB 是不是几十亿的数据？？如果是标量子查询 A返回一条 B就会扫描1次 相当于B被扫描几十亿次

即使B走索引 走索引至少读3个块 然后回表一个块 至少读四个快 几十亿*4 逻辑读很大吧 是不是要搞挂

B的索引是热点块是吧？？如果发现索引热点块不要盲目去反键索引keep索引最好办法是从SQL+业务去优化

 

select * from a,b where a.id=b.id(+);

这个sql如果走NL 去死吧 A做驱动表 是不是B表被扫描几十亿次 同上 如果走HASH如何优化呢？？开并行

 

超大表和小表之间做HASHJOIN 一般会启用并行 ORACLE在并行HASHJOIN的时候会用到很多技术比如HASHHASH 或者BROADCAST 对于超大表和小表做HASHJOIN 一定要让小表进行广播(Broadcast) 通常情况下CBO会选择正确但是如果统计信息不准或者基数计算错误CBO选择了HASHHASH join 这个时候就很慢观察现象就是它在做directpath write temp 此时可以用HINTPQ_DISTRIBUTE进行调整 PQ_DISTRIBUTE(驱动表None,Broadcast) 如果外层表很小(HASH_AJ)此时可以用 PQ_DISTRIBUTE(驱动表Broadcast,None)

 

/*+cardinaity(a100) */指定表a返回的基数

如果发现走HASHJOIN 并发现direct path write temp等待事件 是不是PGA不够 要写入临时表？？

 

假设 A表有100GB数据B有50MB 如果A做驱动表 那么100GBPGA肯定放不下 一个进程的PGA 不会超过2GB

如果B做驱动表 可以 但因为并行 去探测的时候还是不够 因为A太大 手工管理也不行

下面是个具体的例子 F是一个超大表 T是一个小表

第一个SQL在做directpath write temp 第二个SQL能很快返回结果 我们也要注意PQDistrib这列

 

如果小表 不是broadcast说明执行计划是错的 比如开并行进程8 可以把小表广播出去

A有100GB100/8=12.5GB 用12.5GB与B进行JOIN然后一共有8个12.5

每个12.5都要与B进行join因为B表很小 单独的进行HASHJOIN是不是很快 是不是性能提升N倍？？

扫描一次 传播出去 想成短信群发

如果是HASHHASH 国外的Lewis研究过 写基于成本算法的第二个执行计划里面有 可翻阅参考学习