NEST LOOP改为HASH JOIN 性能提高6倍
Kevin Zou
2011-8-31
在看系统的AWR report时,发现有个SQL语句出现频率很高,而且都是在消耗资源的top 5内。决定把这个SQL拉出来看看。
523,370 1 523,370.0 2.7 253.31 2576.45 bcy886xsqp4u1
Module: sqr@phcmdb (TNS V1-V3)
SELECT b.EMPLID, b.DUR, b.TRC, b.TL_QUANTITY, b.ACTUAL_PUB_DATE, b.PAYABLE_STATU
S, b.TL_PYBL_REASON_CD from ps_job a, ps_tl_payable_time b where a.emplid = b.e
mplid and a.empl_rcd = b.empl_rcd and a.effdt = (select max(a_ed.effdt) from ps_
job a_ed where a.emplid = a_ed.emplid and a.empl_rcd = a_ed.empl_rcd and a_ed.ef
通过V$SQL找到完整的SQL语句:
SELECT b.EMPLID, b.DUR, b.TRC, b.TL_QUANTITY, b.ACTUAL_PUB_DATE, b.PAYABLE_STATUS,
b.TL_PYBL_REASON_CD from ps_job a, ps_tl_payable_time b where a.emplid = b.emplid
and a.empl_rcd = b.empl_rcd
and a.effdt = (select max(a_ed.effdt) from sysadm.ps_job a_ed where a.emplid = a_ed.emplid
and a.empl_rcd = a_ed.empl_rcd and a_ed.effdt <= B.DUR)
AND a.EFFSEQ =(SELECT MAX(a_es.EFFSEQ) FROM sysadm.ps_job a_es WHERE a.EMPLID=a_es.EMPLID
AND a.EMPL_RCD=a_es.EMPL_RCD AND a_es.EFFDT=a.EFFDT) AND A.COMPANY = '&py'
AND B.DUR BETWEEN '01-JAN-2011' AND '15-AUG-2011' AND A.PAYGROUP IN('2DS')
AND B.TRC IN ('2OT10','2OT15','2OT20','2OT30') Order by b.EMPLID,b.DUR,b.TRC
ps_job表有130W的数据;
ps_tl_payable_time 有430W的数据;
查看其执行计划:
|
看到两个大表在做NEST LOOP.这明显是不合理的。
查看其消耗的资源:
SYS@hr9prd>select RUNTIME_MEM , FETCHES , EXECUTIONS , DISK_READS , BUFFER_GETS ,USER_IO_WAIT_TIME ,ROWS_PROCESSED
from v$sqlarea
where sql_id ='bcy886xsqp4u1' 2 3 ;
RUNTIME_MEM FETCHES EXECUTIONS DISK_READS BUFFER_GETS USER_IO_WAIT_TIME ROWS_PROCESSED
----------- ---------- ---------- - --------- ----------- ----------------- --------------
26448 132435 1 599315 20765595 2611266235 1324338
USER_IO_WAIT_TIME为2611266235microseconds = 2611 seconds. 而在AWR REPORT中SQL elapsed time为 2576.45 (这里USER_IO_WAIT_TIME >elapsed_time ,可能是两者的统计有出入导致,在这里不是重点)。说明SQL的等待时间全部都是发在IO 等待上。
加入HINT,改写SQL
|
整个SQL用时6分15秒就执行完毕。
对比RUNTIME STATS:
前 后 比较
physical reads 599315 1572401 增加了973086
consistent gets 20765595 7622155 减少了13143440
Elaped Time 2576.45 375 仅为之前的15%
如果看到两个大表直接做联合,如果优化器选择了NEST LOOP 作为ACCESS PATH,执行效率很差,要考虑改为HASH JOIN 以提高性能。
-THE END-