Too many subselects in Oracle Query?

Question

I'd like to know how I could improve the performance of the below query, since it is taking way too long to run, after all, it returns millions of rows... I'm a dummy when it comes to SQL...

SELECT CIAM.EXTERNAL_ID, 
       (SELECT NEW_CHARGES / 100 
        FROM   BI_OWNER.CMF_BALANCE 
        WHERE  ( ACCOUNT_NO, BILL_REF_NO ) = (SELECT ACCOUNT_NO, 
                                                     MAX(BILL_REF_NO) 
                                              FROM   BI_OWNER.CMF_BALANCE 
                                              WHERE 
               ACCOUNT_NO = CIAM.ACCOUNT_NO 
                                              GROUP  BY ACCOUNT_NO)) 
       "AMOUNT LAST BILL", 
       (SELECT 'ACTIVE DISCOUNT' 
               || ' ' 
               || CCK.AVAIL_PERIODS 
               || '/' 
               || CC.TOTAL_PERIODS 
        FROM   BI_OWNER.CUSTOMER_CONTRACT_KEY CCK, 
               BI_OWNER.CUSTOMER_CONTRACT CC 
        WHERE  CC.PARENT_ACCOUNT_NO = CIAM.ACCOUNT_NO 
               AND CC.END_DT IS NULL 
               AND EXISTS (SELECT 1 
                           FROM   CONTRACT_TYPES 
                           WHERE  CONTRACT_TYPE = CC.CONTRACT_TYPE 
                                  AND PLAN_ID_DISCOUNT IS NOT NULL 
                                  AND DURATION_UNITS = -3) 
               AND ROWNUM = 1 
               AND CCK.TRACKING_ID = CC.TRACKING_ID 
               AND CCK.TRACKING_ID_SERV = CC.TRACKING_ID_SERV) "DISCOUNT", 
       (SELECT CC.TOTAL_PERIODS 
        FROM   BI_OWNER.CUSTOMER_CONTRACT_KEY CCK, 
               BI_OWNER.CUSTOMER_CONTRACT CC 
        WHERE  CC.PARENT_ACCOUNT_NO = CIAM.ACCOUNT_NO 
               AND CC.END_DT IS NULL 
               AND EXISTS (SELECT 1 
                           FROM   CONTRACT_TYPES 
                           WHERE  CONTRACT_TYPE = CC.CONTRACT_TYPE 
                                  AND PLAN_ID_DISCOUNT IS NOT NULL 
                                  AND DURATION_UNITS = -3) 
               AND ROWNUM = 1 
               AND CCK.TRACKING_ID = CC.TRACKING_ID 
               AND CCK.TRACKING_ID_SERV = CC.TRACKING_ID_SERV) "CYCLE"
       , 
       (SELECT SUM(BALANCE_DUE) 
        FROM   BI_OWNER.CMF_BALANCE 
        WHERE  ACCOUNT_NO = CIAM.ACCOUNT_NO 
               AND PPDD_DATE < TRUNC(SYSDATE)) 
       "DEBT" 
FROM   BI_OWNER.CUSTOMER_ID_ACCT_MAP CIAM 
WHERE  EXTERNAL_ID_TYPE = 1 
       AND EXISTS (SELECT 1 
                   FROM   BI_OWNER.CMF 
                   WHERE  ACCOUNT_NO = CIAM.ACCOUNT_NO 
                          AND PREV_CUTOFF_DATE > SYSDATE - 30)

Answer 1

Here is an initial stab and may provide significant improvement. Many of your queries were correlated subqueries being executed for every record. Instead, I tried to build pre-query aggregates per account number in the select from/join section. Query first, then I'll explain logic after.

SELECT 
      CIAM.EXTERNAL_ID,
      CMF_BALANCE.New_Charges / 100.0 "AMOUNT LAST BILL", 
      CCKs.Discount,
      CCKs.Cycle,
      AcntLast30.SumBalance "DEBT" 
   FROM
      (SELECT 
            CMF.Account_No,
            max( Bal.Bill_Ref_No ) MaxBillRef,
            sum( case when Bal.PPDD_Date < TRUNC(SYSDATE )
                     then Bal.Balance_Due else 0 end ) SumBalance
         from 
            BI_OWNER.CMF
               JOIN BI_OWNER.CMF_BALANCE BAL
                  on CMF.Account_No = Bal.Account_No
         where 
            CMF.PREV_CUTOFF_DATE > SYSDATE - 30
         group by
            CMF.Account_No ) AcntLast30

         JOIN BI_OWNER.CUSTOMER_ID_ACCT_MAP CIAM 
            on AcntLast30.Account_No = CIAM.Account_No 
            AND CIAM.EXTERNAL_ID_TYPE = 1 

         JOIN BI_OWNER.CMF_BALANCE
            on AcntLast30.Account_No = CMFBalance.Account_No 
            AND AcntLast30.MaxBillRef = CMFBalance.Bill_Ref_No

         JOIN
         (select
               CC.Parent_Account_No,
              CC.TOTAL_PERIODS "CYCLE",
               'ACTIVE DISCOUNT' || ' ' || CCK.AVAIL_PERIODS || '/' || CC.TOTAL_PERIODS "DISCOUNT"
            FROM
              BI_OWNER.CUSTOMER_CONTRACT CC 
                  JOIN BI_OWNER.CUSTOMER_CONTRACT_KEY CCK
                    ON CC.TRACKING_ID = CCK.TRACKING_ID
                    AND CC.TRACKING_ID_SERV = CCK.TRACKING_ID_SERV
                    AND ROWNUM = 1 
                 JOIN ( select distinct Contract_Type
                            FROM CONTRACT_TYPES
                            WHERE PLAN_ID_DISCOUNT IS NOT NULL 
                              AND DURATION_UNITS = -3) CT
                     on CC.Contract_Type = CT.Contract_Type
            WHERE
                   CC.END_DT IS NULL ) CCKs
            on AcntLast30.Account_No = CCKs.Parent_Account_No

The initial "FROM" clause, I have a subquery because you appear to be only interested in accounts within the last 30 days. So, while I'm there, I am joining to your CMF_Balance table and getting the maximum Bill_Ref_No per account AND the sum of the balance when the PPDD_Date is less than the TRUNC(sysdate) which is your "DEBT" result column. So now we have the finite list of accounts you are interested in with the account, max bill on file and the balance due summed up.

 (SELECT 
                CMF.Account_No,
                max( Bal.Bill_Ref_No ) MaxBillRef,
                sum( case when Bal.PPDD_Date < TRUNC(SYSDATE )
                         then Bal.Balance_Due else 0 end ) SumBalance
             from 
                BI_OWNER.CMF
                   JOIN BI_OWNER.CMF_BALANCE BAL
                      on CMF.Account_No = Bal.Account_No
             where 
                CMF.PREV_CUTOFF_DATE > SYSDATE - 30
             group by
                CMF.Account_No ) AcntLast30

Next, simple join to the CIAM table to only get accounts for External_ID_Type = 1. This too could have been merged into the query above for "AcntLast30" alias result.

     JOIN BI_OWNER.CUSTOMER_ID_ACCT_MAP CIAM 
        on AcntLast30.Account_No = CIAM.Account_No 
        AND CIAM.EXTERNAL_ID_TYPE = 1 

Now, since the "AcntLast30" query has the account and max bill reference we then join back to the CMF_Balance on the account and bill reference # once thus giving us the CMF_BALANCE.New_Charges / 100.0 "AMOUNT LAST BILL"

JOIN BI_OWNER.CMF_BALANCE
    on AcntLast30.Account_No = CMFBalance.Account_No 
   AND AcntLast30.MaxBillRef = CMFBalance.Bill_Ref_No

Finally the subquery alias result "CCKs". Since the Discount and Cycle use the same query/subquery/exists, I just ran it once that qualified on the discounts types and pulled the Account_No for the JOIN condition. Now we have the Discount and Cycle values per account.

If you are returning so many rows, I believe the performance gained by grabbing these pre-query aggregates once up-front and joining to by the account will be much faster than that of each time individually subquerying at every row.

There was a reference to ROWNUM without any table/alias reference so I am not sure the impact of that one within the query.

Final note. For things like the discount that may not be applicable, you may need to change it to a LEFT JOIN, of which those values would show as NULL. But without knowing the extent of data, Cartesian products of 1:many entries in given tables, I think this will work well for you. For the most part it looked like everything was resulting in only one record qualified per account where higher importance on join (such as the max bill reference).

Answer 2

I would recommend identifying the SQL id for the query then using the SQL Monitor Report as it will tell you exactly what the execution plan is and where the SQL is spending most of it's time.

A simple way to get the SQL Monitor Report from SQL*Plus follows:

spool c:\temp\SQL_Monitor_rpt.html

SET LONG 1000000
SET LONGCHUNKSIZE 1000000
SET LINESIZE 1000
SET PAGESIZE 0
SET TRIM ON
SET TRIMSPOOL ON
SET ECHO OFF
SET FEEDBACK OFF

alter session set "_with_subquery" = optimizer;

SELECT DBMS_SQLTUNE.report_sql_monitor(
  sql_id       => '&SQLID' ,
  type         => 'HTML',
  report_level => 'ALL') AS report
FROM dual;

spool off

Basically, you need to know your table sizes and how to get the large tables to have data access via an index (e.g. index on columns found in the where clause).