查询效率非常低的SQL如何优化它

以下一条线上的SQL执行耗时7分钟，如何优化： SELECT S_ECIF_IC_MONITOR.NEXTVAL AS PK_ID, BLGTO_INST_CODE, T2.COMCNAME AS BLGTO_INST_NAME, ZB_LOCATION_POLICY, ZB_ALLOCATED_POLICY, ZB_WAIT_POLICY, ZB_VERIFY_POLICY, TB_LOCATION_CUSTOMER, TB_ALLOCATED_CUSTOMER, TB_WAIT_CUSTOMER, TB_VERIFY_CUSTOMER, QZ_LOCATION_CUSTOMER, QZ_ALLOCATED_CUSTOMER, QZ_WAIT_CUSTOMER, QZ_VERIFY_CUSTOMER, SYSDATE AS IMP_TIME FROM (SELECT SUM(P7.ZB_LOCATION_POLICY) AS ZB_LOCATION_POLICY, SUM(P7.ZB_ALLOCATED_POLICY) AS ZB_ALLOCATED_POLICY, SUM(P7.ZB_WAIT_POLICY) AS ZB_WAIT_POLICY, SUM(P7.ZB_VERIFY_POLICY) AS ZB_VERIFY_POLICY, SUM(P7.TB_LOCATION_CUSTOMER) AS TB_LOCATION_CUSTOMER, SUM(P7.TB_ALLOCATED_CUSTOMER) AS TB_ALLOCATED_CUSTOMER, SUM(P7.TB_WAIT_CUSTOMER) AS TB_WAIT_CUSTOMER, SUM(P7.TB_VERIFY_CUSTOMER) AS TB_VERIFY_CUSTOMER, SUM(P7.QZ_LOCATION_CUSTOMER) AS QZ_LOCATION_CUSTOMER, SUM(P7.QZ_ALLOCATED_CUSTOMER) AS QZ_ALLOCATED_CUSTOMER, SUM(P7.QZ_WAIT_CUSTOMER) AS QZ_WAIT_CUSTOMER, SUM(P7.QZ_VERIFY_CUSTOMER) AS QZ_VERIFY_CUSTOMER, UPPER_CODE_2 AS BLGTO_INST_CODE FROM (SELECT P6.ZB_LOCATION_POLICY, P6.ZB_ALLOCATED_POLICY, P6.ZB_WAIT_POLICY, P6.ZB_VERIFY_POLICY, P6.TB_LOCATION_CUSTOMER, P6.TB_ALLOCATED_CUSTOMER, P6.TB_WAIT_CUSTOMER, P6.TB_VERIFY_CUSTOMER, P6.QZ_LOCATION_CUSTOMER, P6.QZ_ALLOCATED_CUSTOMER, P6.QZ_WAIT_CUSTOMER, P6.QZ_VERIFY_CUSTOMER, SUBSTR(T1.UPPERPATH, 10, 8) AS UPPER_CODE_2 FROM (SELECT P5.IMPORTCOMECODE AS BLGTO_INST_CODE, (SELECT COUNT(1) FROM RESOURCE_DATA P1 WHERE P1.IMPORTCOMECODE = P5.IMPORTCOMECODE AND SOURCETYPE = '2') AS ZB_LOCATION_POLICY, (SELECT COUNT(1) FROM RESOURCE_DATA P2 WHERE P2.IMPORTCOMECODE = P5.IMPORTCOMECODE AND SOURCETYPE = '2' AND TARGETVALUE IS NOT NULL) AS ZB_ALLOCATED_POLICY, (SELECT COUNT(1) FROM RESOURCE_DATA P3 WHERE P3.IMPORTCOMECODE = P5.IMPORTCOMECODE AND SOURCETYPE = '2' AND TARGETVALUE IS NULL) AS ZB_WAIT_POLICY, (SELECT COUNT(1) FROM RESOURCE_DATA P4 WHERE P4.IMPORTCOMECODE = P5.IMPORTCOMECODE AND SOURCETYPE = '2' AND PERSONAL_STATUS = '1') AS ZB_VERIFY_POLICY, (SELECT COUNT(1) FROM RESOURCE_DATA P1 WHERE P1.IMPORTCOMECODE = P5.IMPORTCOMECODE AND SOURCETYPE = '3') AS TB_LOCATION_CUSTOMER, (SELECT COUNT(1) FROM RESOURCE_DATA P2 WHERE P2.IMPORTCOMECODE = P5.IMPORTCOMECODE AND SOURCETYPE = '3' AND TARGETVALUE IS NOT NULL) AS TB_ALLOCATED_CUSTOMER, (SELECT COUNT(1) FROM RESOURCE_DATA P3 WHERE P3.IMPORTCOMECODE = P5.IMPORTCOMECODE AND SOURCETYPE = '3' AND TARGETVALUE IS NULL) AS TB_WAIT_CUSTOMER, (SELECT COUNT(1) FROM RESOURCE_DATA P4 WHERE P4.IMPORTCOMECODE = P5.IMPORTCOMECODE AND SOURCETYPE = '3' AND PERSONAL_STATUS = '1') AS TB_VERIFY_CUSTOMER, (SELECT COUNT(1) FROM RESOURCE_DATA P1 WHERE P1.IMPORTCOMECODE = P5.IMPORTCOMECODE AND SOURCETYPE = '1') AS QZ_LOCATION_CUSTOMER, (SELECT COUNT(1) FROM RESOURCE_DATA P2 WHERE P2.IMPORTCOMECODE = P5.IMPORTCOMECODE AND SOURCETYPE = '1' AND TARGETVALUE IS NOT NULL) AS QZ_ALLOCATED_CUSTOMER, (SELECT COUNT(1) FROM RESOURCE_DATA P3 WHERE P3.IMPORTCOMECODE = P5.IMPORTCOMECODE AND SOURCETYPE = '1' AND TARGETVALUE IS NULL) AS QZ_WAIT_CUSTOMER, (SELECT COUNT(1) FROM RESOURCE_DATA P4 WHERE P4.IMPORTCOMECODE = P5.IMPORTCOMECODE AND SOURCETYPE = '1' AND PERSONAL_STATUS = '1') AS QZ_VERIFY_CUSTOMER FROM RESOURCE_DATA P5 --WHERE TRUNC(MODIFY_TIME) = TRUNC(SYSDATE) GROUP BY P5.IMPORTCOMECODE) P6 INNER JOIN INNERINS T1 ON P6.BLGTO_INST_CODE = T1.COMCODE) P7 WHERE P7.UPPER_CODE_2 IS NOT NULL GROUP BY P7.UPPER_CODE_2) P8 INNER JOIN INNERINS T2 ON P8.BLGTO_INST_CODE = T2.COMCODE;