Performance Problem

From: <jatinder.1975_at_gmail.com>
Date: 21 Apr 2006 04:28:26 -0700
Message-ID: <1145618906.269688.242790@z34g2000cwc.googlegroups.com>

Hello All,

I am facing a very weired problem in performance. The particular query is running on and on .... I think I should try Full Scan on tables Particularly Voucher Tables and Join should be Hash Join rather than nested. Any help would be greatly appreciated.

Below is the query.
SELECT
    A.fin_year,
    A.doc_br_cd,
    dvsn_cd,
    party_cd cust_cd ,
    D.ser_cd,
    DECODE(b.ref_doc_typ, 'AD', 0, ROUND(DECODE(dbcr_ind, 'C', effectv_date, vchr_date) - (DECODE(e.doc_date, NULL, TRUNC(b.ref_doc_date), e.doc_date) + CREDIT_DAYS))) diff_date,

    DECODE(dbcr_ind,'C',-1,1)*amt v_amt,

    BR.Fn_Get_Fin_Year(DECODE(e.doc_date, NULL, b.ref_doc_date,
e.doc_date)) bill_fin_year,
    BR.Fn_Get_Fin_Year(DECODE(e.doc_date, NULL, b.ref_doc_date,

e.doc_date) + CREDIT_DAYS) due_fin_year
FROM BR.VOUCHER_Master A,

    BR.VOUCHER_Detail b,
    BR.SeriesUsed d,
    BR.INVOICE_HDR e

WHERE A.vchr_hdr_key = b.vchr_hdr_key

    AND

• trunc(a.vchr_date) >= sysdate-365 and
  (
  A.doc_br_cd NOT IN ( SELECT br_cd FROM BR.SBB_IMPL_BRS ) OR TRUNC(VCHR_DATE) < '01-APR-05' ) AND b.gl_cd LIKE '0807%' AND b.gl_cd = d.gl_cd AND A.doc_br_cd = '01' AND b.ref_doc_typ IN ('AD','SI') AND b.party_cd = e.cust_cd(+) -- AND SUBSTR(b.party_cd,1,2) = '01' And b.party_cd Like '01%' AND SUBSTR(b.party_cd,1,2) = e.doc_br_cd(+) AND b.ref_doc_typ = e.DOC_TYP(+) AND b.ref_doc_number = e.DOC_NUMBER(+) AND BR.Fn_Get_Fin_Year(b.ref_doc_date) = e.fin_year(+) AND NVL(amt,0) NOT BETWEEN -1 AND 1 AND DECODE(e.doc_date, NULL, b.ref_doc_date, e.doc_date) + CREDIT_DAYS BETWEEN sysdate-365 AND sysdate AND
• BR.Fn_Get_Fin_Year(decode(e.doc_date, null, b.ref_doc_date, e.doc_date)) <= pfin_year and NVL(A.iba_typ,'1') <> '4' AND d.ser_cd IN
  (
  SELECT ser_cd FROM BR.DOC_NUMBER WHERE b.ref_doc_number BETWEEN start_number AND max_number AND DOC_TYP='SI' AND br_cd = '01' ) UNION ALL
  • FROM SBB SELECT A.fin_year, A.doc_br_cd, dvsn_cd, A.cust_cd , A.ser_cd, DECODE(A.ref_doc_typ, 'AD', 0, ROUND(DECODE(dbcr_ind, 'C', effectv_date, e.DOC_date) - (DECODE(e.doc_date, NULL, TRUNC(A.ref_doc_date), e.doc_date) + CREDIT_DAYS))) diff_date, DECODE(dbcr_ind,'C',-1,1)*amt v_amt, BR.Fn_Get_Fin_Year(DECODE(e.doc_date, NULL, A.ref_doc_date, e.doc_date)) bill_fin_year, BR.Fn_Get_Fin_Year(DECODE(e.doc_date, NULL, A.ref_doc_date, e.doc_date) + CREDIT_DAYS) due_fin_year FROM BR.CUST_VCHR_DATA_SBB A, BR.SeriesUsed d, BR.INVOICE_HDR e WHERE
    (
    A.doc_br_cd IN ( SELECT br_cd FROM BR.SBB_IMPL_BRS ) AND TRUNC(A.doc_DATE) >= '01-APR-05' ) AND A.SER_CD = D.SER_CD AND A.doc_br_cd = '01' AND A.ref_doc_typ IN ('AD','SI') AND A.CUST_cd = e.cust_cd(+) AND SUBSTR(A.CUST_cd,1,2) = '01' AND SUBSTR(A.CUST_cd,1,2) = e.doc_br_cd(+) AND A.ref_doc_typ = e.DOC_TYP(+) AND A.ref_doc_number = e.DOC_NUMBER(+) AND BR.Fn_Get_Fin_Year(A.ref_doc_date) = e.fin_year(+) AND NVL(amt,0) NOT BETWEEN -1 AND 1 AND DECODE(e.doc_date, NULL, A.ref_doc_date, e.doc_date) + CREDIT_DAYS BETWEEN sysdate-365 AND sysdate AND NVL(A.iba_type,'1') <> '4';

Execution Plan

---

   0 SELECT STATEMENT Optimizer=CHOOSE (Cost=1471 Card=2 Bytes=189)

   1 0 UNION-ALL

   2    1     FILTER
   3    2       FILTER
   4    3         NESTED LOOPS (OUTER)
   5    4           NESTED LOOPS (Cost=1191 Card=1 Bytes=70)
   6    5             NESTED LOOPS (Cost=1189 Card=1 Bytes=52)
   7    6               TABLE ACCESS (FULL) OF 'SeriesUsed' (Cost=1
Card=3 Bytes=39)
   8    6               TABLE ACCESS (BY INDEX ROWID) OF
'VOUCHER_Detail' (Cost=396 Card=1 Bytes=39)
   9    8                 INDEX (RANGE SCAN) OF
'ID_VOUCHER_Detail_GL_CD' (NON-UNIQUE) (Cost=387 Card
  10    5             TABLE ACCESS (BY INDEX ROWID) OF 'VOUCHER_Master'
(Cost=2 Card=1 Bytes=18)
  11   10               INDEX (UNIQUE SCAN) OF 'PK_VOUCHER_Master'
(UNIQUE) (Cost=1 Card=1)
  12    4           TABLE ACCESS (BY INDEX ROWID) OF 'INVOICE_HDR'
(Cost=2 Card=1 Bytes=28)
  13   12             INDEX (UNIQUE SCAN) OF 'UK_INVOICE_HDR' (UNIQUE)
(Cost=1 Card=1)
  14    2       TABLE ACCESS (FULL) OF 'SBB_IMPL_BRS' (Cost=1 Card=1
Bytes=2)
  15    2       INDEX (RANGE SCAN) OF 'IDX_DOC_NUMBER_COMP'
(NON-UNIQUE) (Cost=2 Card=1 Bytes=13)
  16    1     FILTER
  17   16       NESTED LOOPS (OUTER)
  18   17         NESTED LOOPS (Cost=276 Card=1 Bytes=63)
  19   18           MERGE JOIN (CARTESIAN) (Cost=275 Card=1 Bytes=56)
  20   19             VIEW OF 'VW_NSO_1' (Cost=6 Card=1 Bytes=3)
  21   20               SORT (UNIQUE) (Cost=6 Card=1 Bytes=2)
  22   21                 TABLE ACCESS (FULL) OF 'SBB_IMPL_BRS' (Cost=1
Card=1 Bytes=2)
  23   19             BUFFER (SORT) (Cost=275 Card=1 Bytes=53)
  24   23               TABLE ACCESS (FULL) OF 'CUST_VCHR_DATA_SBB'
(Cost=269 Card=1 Bytes=53)
  25   18           TABLE ACCESS (BY INDEX ROWID) OF 'SeriesUsed'
(Cost=1 Card=1 Bytes=7)
  26   25             INDEX (UNIQUE SCAN) OF 'PK_SeriesUsed' (UNIQUE)
  27   17         TABLE ACCESS (BY INDEX ROWID) OF 'INVOICE_HDR'
(Cost=2 Card=1 Bytes=28)
  28   27           INDEX (UNIQUE SCAN) OF 'UK_INVOICE_HDR' (UNIQUE)

(Cost=1 Card=1)

With Warm regards
Jatinder Singh Received on Fri Apr 21 2006 - 06:28:26 CDT