【Troubleshooting】Choosing the wrong execution plan of HASH JOIN and NESTED LOOP

【问题描述】 项目人员反映一条SQL执行很慢,语句如下: [cce_oracle11] SELECT SAMPLEYEAR,SAMPLEMONTH,SAMPLEDATE, PROVINCE, D.CITYCN,D.TOWNCN,D.PORTCN, MONTH_DAY, (1 - WLNPZB13 / 15 * 60) * (DECODE((WLNPZB14 + WLNPZB18), 0, 0, (WLNPZB14 + WLNPZB18 - WLNPZB15 - WLNPZB20) / (WLNPZB14 + WLNPZB18))) NET_ACCESS_RATE FROM (SELECT TO_NUM(TO_CHAR(SYSDATE, 'YYYY')) AS SAMPLEYEAR, TO_NUM(TO_CHAR(SYSDATE, 'MM')) AS SAMPLEMONTH, TRUNC(SYSDATE - 1) AS SAMPLEDATE, '安徽' AS PROVINCE, P.WLNPZB13, P.WLNPZB14, P.WLNPZB18, P.WLNPZB15, P.WLNPZB20, P.WLNPZB16, TO_NUM(TO_CHAR(LAST_DAY(SYSDATE), 'dd')) AS MONTH_DAY FROM PPM_WLAN_AP_KEY_PERF_D P WHERE P.WLNPZB01 = TRUNC(SYSDATE - 1)) P LEFT JOIN (SELECT D.CITYCN, D.TOWNCN,D.PORTCN, D.MAC FROM DEVICE_INFOS D WHERE NODETYPE = 11 GROUP BY D.CITYCN, D.TOWNCN,D.PORTCN, D.MAC ) D on P.WLNPZB16 = D.MAC GROUP BY SAMPLEYEAR,SAMPLEMONTH,SAMPLEDATE, PROVINCE, D.CITYCN,D.TOWNCN,D.PORTCN, MONTH_DAY, (1 - WLNPZB13 / … [Read more...]