Oracle query running too slow (approximately taking 5 hrs)

Question

SELECT t1.member_id ,
  SUM(t1.paid_amt) AS paid_amt
FROM
  (SELECT DISTINCT fm.member_id,
    fc.claim_skey_no,
    fc.claim_id,
    fc.claim_line_no ,
    CASE
      WHEN fc.claim_type_cd = 'RX'
      THEN NVL(fc.rx_paid_amt,0) -- For RX claims use rx_paid_amt as paid amount
      ELSE NVL(fc.approved_amt,0)
    END AS paid_amt -- For all other claims use approved_amt
    ,
    CASE
      WHEN fc.claim_type_cd = 'RX'
      THEN fc.submit_dt --For RX claims use submit_dt as paid date
      ELSE NVL(fc.paid_dt,NVL(fc.edi_eob_dt,NVL(fc.eob_run_dt,fc.outsource_vndr_paid_dt)))
    END AS paid_dt --For all other claims use paid_dt
  FROM dwprod.fct_claim fc ,
    dwprod.fct_member fm
  WHERE fc.mbr_skey_no = fm.member_skey_no
    --  The service_from_dt on the claim must be between the reimbursement time period.
  AND fc.service_from_dt BETWEEN '31-MAY-2013' AND '30-Jun-2014'
    -- The follwong 2 conditions make sure that the calims selected are final-status (unadjusted)
    -- For non-RX claims, the adjust_type_cd must be Null and the dw_backout_tag must be Null or 'N'
    -- For RX claims only the dw_backout_tag must be Null or 'N', the adjust_type_cd is ignored
  AND
    CASE
      WHEN fc.claim_type_cd = 'RX'
      THEN 1
      WHEN fc.claim_type_cd <> 'RX'
      AND fc.adjust_type_cd IS NULL
      THEN 1
      ELSE 0
    END                          = 1
  AND NVL(fc.dw_backout_tag,'N') = 'N'
    -- The claim must be in an 'Approved' status, indicated by a status_type_cd = 'A'
  AND fc.status_type_cd = 'A'
    --  QNXT claims must be in a 'PAID' status
    --  Non QNXT claims in the warehouse are assumed to be paid - There are no pended RX claims.
  AND
    CASE
      WHEN fc.dw_source_cd <> 'QNXT'
      THEN 1
      WHEN fc.dw_source_cd  = 'QNXT'
      AND fc.last_status_nm = 'PAID'
      THEN 1
      ELSE 0
    END = 1
    -- Dental claims are excluded
  AND fc.dw_source_cd <> 'DBP'
    -- Excludes any Medicare Non-RCI claims
  AND
    CASE
      WHEN NVL(fc.program_nm,'OTHER')    = 'MEDICAID'
      AND NVL(fc.enroll_ratecode,'RCI') IN ('RCII','RCV','RCVII')
      THEN 0
      ELSE 1
    END = 0
    -- It Fits! claims are excluded
  AND NVL(fc.expense_cat_nm,'Other')  <> 'FITNESS'
  AND NVL(fc.proc1_skey_no,12345) NOT IN (21586,21588,21589)
    --
  AND
    CASE
      WHEN NVL(fc.program_nm,'OTHER')    = 'MEDICAID'
      AND NVL(fc.enroll_ratecode,'RCI') IN ('RCII','RCV','RCVII')
      THEN 1
      WHEN EXISTS
        (SELECT 1
        FROM dwprod.fct_member_enroll me
        WHERE fm.member_skey_no = me.mbr_skey_no
        AND fc.service_from_dt BETWEEN me.segment_effect_dt AND me.segment_term_dt
        AND me.program_nm       = 'MEDICAID'
        AND me.enroll_ratecode IN ('RCII','RCV','RCVII')
        )
      THEN 1
      ELSE 0
    END = 1
  ) t1
  --Where t1.paid_dt < '31-JAN-2014'
GROUP BY t1.member_id
HAVING SUM(t1.paid_amt) > 175000

Answer 1

Run an explain plan to see what's causing the slowdown. From the top of my head, this is what's 'killing' you:

WHEN EXISTS
        (SELECT 1
        FROM dwprod.fct_member_enroll me
        WHERE fm.member_skey_no = me.mbr_skey_no
        AND fc.service_from_dt BETWEEN me.segment_effect_dt AND me.segment_term_dt
        AND me.program_nm       = 'MEDICAID'
        AND me.enroll_ratecode IN ('RCII','RCV','RCVII')
        )

See if you can somehow change this exists logic to something with better performance. The explain plan is a must though!

Answer 2

I'm gonna make a semi-blind guess here, based on similar queries in the DW I'm working with. Oracle's optimizer gets easily confused by predicates such as:

where (case when ... then ... else ... end) = 1;

The reason is that Oracle grossly over estimate the selectivity. Check the explain plan like others have said. If you find that the estimated cardinality of table dwprod.fct_claim seems way too low, try unrolling the case statements.

For example, instead of:

  AND CASE WHEN fc.dw_source_cd <> 'QNXT' THEN 1
            WHEN fc.dw_source_cd  = 'QNXT' AND fc.last_status_nm = 'PAID' THEN 1
                                                                          ELSE 0
       END = 1

Write:

and (    fc.dw_source_cd <> 'QNXT'
     or (fc.dw_source_cd  = 'QNXT' and fc.last_status_nm = 'PAID')
    )

Final note. This seems to be less of a problem in version 11, but I have not yet had time to investigate why.

Answer 3

FROM dwprod.fct_claim fc ,
    dwprod.fct_member fm
WHERE fc.mbr_skey_no = fm.member_skey_no

This cross join is effectively an inner join. I can't say whether Oracle will optimize this, but there's no reason not to make its job easier:

FROM dwprod.fct_claim fc ,
    INNER JOIN dwprod.fct_member fm
    ON fc.mbr_skey_no = fm.member_skey_no