Re: Tuning SQL query with views

I will try to post more accurate information next time! The lack of aliases that you all mentioned was due to the fact that the statement was composed dynamically through SQR (a Sqribe product). In the next run another statement will be generated with different 'variables', but with the same where-clause. I just took the information from the Oracle trace-file because that was the only way to extract the statement. I added all the info that seemed relevant.
I posted the SQL-statement with all added information in the first place to visualize my problem. It was the first time I was confronted with a performance problem in a SQL-statement with views, and I didn't know how to start tuning these kind of queries.

In article <3A4AD939.7A117048_at_erols.com>,   localhost_at_127.0.0.1 wrote:
> ordabber_at_my-deja.com wrote:
> >
> > Hi there,
> >
> > I have a bad performance problem. My query seems easy, but the only
> > accessed "table" is in fact a VIEW, and I can't find anywhere tips
 to
> > tune these kind of statements.
> >
> > I'm running ORACLE 8.0.5 on Windows NT.
> > The database is RULE-BASED !!
>
> ...
>
> >
> > select AP.BANK_SETID, AP.BANK_CD, AP.BANK_ACCT_KEY, AP.PYMNT_ID,
> > BUSINESS_UNIT_GL, ACCOUNTING_DT, AP.DOC_TYPE, AP.DOC_SEQ_NBR
> > FROM
> > PS_PAYMENT_TBL_VW5 AP
> > WHERE DOC_SEQ_DATE IS NULL AND DOC_SEQ_STATUS = 'N'
> > AND PAY_CYCLE = 'BACOB1' and PAY_CYCLE_SEQ_NUM = 516
> > and DOC_TYPE <> ' '
> > ORDER BY AP.BUSINESS_UNIT_GL, AP.DOC_TYPE, AP.ACCOUNTING_DT
>
> ...
>
> > -------------------------------------------------
> > PS_PAYMENT_TBL_VW5
> > -------------------------------------------------
> >
> > select
> > a.BANK_SETID
> > ,a.BANK_CD
> > ,a.BANK_ACCT_KEY
> > ,a.PYMNT_ID
> > ,a.PYMNT_ID_REF
> > ,a.BANK_ACCOUNT_NUM
> > ,a.BRANCH_ID
> > ,a.CHECK_DIGIT
> > ,a.REMIT_SETID
> > ,a.REMIT_VENDOR
> > ,a.vndr_loc
> > ,a.EMPLID
> > ,a.NAME1
> > ,a.NAME2,
> > %subrec(ADDRess_SBR,a)
> > ,a.PYMNT_DT
> > ,a.CREATION_DT
> > ,a.OPRID
> > ,a.PYMNT_AMT
> > ,a.CURRENCY_PYMNT
> > ,a.PYMNT_METHOD
> > ,a.PYMNT_HANDLING_CD
> > ,a.PYMNT_STATUS
> > ,a.PYMNT_RECONCILE_DT
> > ,a.CANCEL_ACTION
> > ,a.CANCEL_DT
> > ,a.ACCOUNTING_DT
> > ,a.POST_STATUS_AP
> > ,a.PROCESS_INSTANCE
> > ,a.PAY_CYCLE
> > ,a.PAY_CYCLE_SEQ_NUM
> > ,a.RECON_TYPE
> > ,a.RECON_CYCLE_NBR
> > ,a.RECONCILE_OPRID
> > ,a.BNK_ID_NBR
> > ,a.RECON_STATUS
> > ,a.DFI_ID_NUM
> > ,a.DFI_ID_QUAL
> > ,a.EFT_PYMNT_FMT_CD
> > ,a.STTLMNT_DT_EST
> > ,a.STTLMNT_DT_ACTUAL
> > ,a.EFT_TRANS_HANDLING
> > ,a.REMIT_BANK_ACCOUNT
> > ,a.REMIT_BRANCH_ID
> > ,a.REMIT_CHECK_DIGIT
> > ,a.REP_RTNG_NUM
> > ,a.FORM_NBR_CONFIRMED
> > ,a.IN_PROCESS_FLG
> > ,a.POSITIVE_PAY_DT
> > ,a.POSITIVE_PAY_STAT
> > ,a.MICR_LINE
> > ,a.CASH_CLEARED_FLG
> > ,a.CASH_CLEARED_DT
> > ,a.REPLACED_DT
> > ,a.PYMNT_TYPE
> > ,a.doc_type
> > ,a.doc_seq_nbr
> > ,a.doc_seq_date
> > ,a.doc_seq_status
> > ,a.preferred_language
> > ,b.business_unit_gl
> > from ps_payment_tbl a,
> > ps_bank_acct_tbl b
> > where a.bank_setid = b.setid and
> > a.bank_cd = b.bank_cd and
> > a.bank_acct_key = b.bank_acct_key
>
> ...
>
> > -------------------------------------------------
> > ps_bank_acct_tbl
> > -------------------------------------------------
> >
> > SELECT
> > A.SETID,
> > B.COUNTERPARTY,
> > A.BANK_ACCT_KEY,
> > A.BANK_ACCOUNT_NUM,
> > A.DESCR,
> > A.DESCRSHORT,
> > A.PYMNT_HANDLING_CD,
> > A.PYMNT_OVRD_AMT,
> > A.RECON_TYPE_FLG,
> > A.RECON_HEADER,
> > A.CURRENCY_CD,
> > A.CUR_RT_TYPE,
> > A.RATE_INDEX,
> > A.SETTLEMENT_ID,
> > B.BUSINESS_UNIT_GL,
> > A.LAST_PYMNT_ID_USED,
> > A.PYMNT_ID_REF_LEN,
> > A.FRACT_RTG_NUM,
> > A.DFI_ID_NUM,
> > A.DFI_ID_QUAL,
> > A.NPL_BNK_CHRT_VALUE,
> > A.IU_PYBL_NPL_ACCT,
> > A.IU_RCVBL_NPL_ACCT,
> > A.CSH_PL_CLRG_ACCT,
> > A.CSH_PL_ACCT,
> > A.PL_BNK_ACCT_FLG,
> > A.LOCATION,
> > A.AR,
> > A.AP,
> > A.TR,
> > A.BI,
> > A.PP_SW,
> > A.ENABLE_WF_SW,
> > A.DEPOSIT_TYPE,
> > A.FORECAST,
> > A.TARGET_BALANCE,
> > A.PRE_RECONCILE,
> > A.CASH_CNTL_USE_FLG,
> > A.DRAFT_CNTL_USE_FLG,
> > A.BRANCH_ID,
> > A.CHECK_DIGIT,
> > A.INT_BASIS,
> > A.MARGIN_PTS,
> > A.CREATION_DT,
> > A.OPRID_ENTERED_BY,
> > A.OPRID_LAST_UPDT,
> > A.DEPOSIT_BU,
> > A.CHRG_BANK_CD,
> > A.CHRG_BANK_ACCT_KEY,
> > A.RCN_ACCTG_SW,
> > A.RCN_TLR_DT_FROM,
> > A.RCN_TLR_DT_TO,
> > A.DEFAULT_ACCT_SW,
> > A.BANK_ACCT_QUAL,
> > A.PREFERRED_LANGUAGE,
> > A.PAYER_ID_NUM,
> > A.BANK_ACCT_TYPE,
> > A.CREDIT_RATE,
> > A.DEBIT_RATE
> > FROM PS_BANK_ACCT_DEFN A,
> > PS_BANK_ACCT_CPTY B
> > WHERE A.SETID = B.SETID
> > AND A.BANK_CD = B.BANK_CD
> > AND A.BANK_CD_CPTY = B.BANK_CD_CPTY
> > AND A.BANK_ACCT_KEY = B.BANK_ACCT_KEY
>
> You are certainly thorough. Your message was difficult to digest due
 sheer
> volume (some of it unnecessary in the first post) and formatting.
>
> Here's a few questions:
>
> 1. There's a b.bank_cd referring to the ps_bank_acct_tbl view
 (unfortunate
> choice of name there). Can't see a bank_cd column in definition of
> ps_bank_acct_tbl.
>
> 2. Ultimately, the SQL is a simple join of a few tables using a minor
 sub-set of
> columns from base tables. Writing it as such simplifies debugging
 and maintenance.
> At the very least.
>
> Your query actually boils down to:
>
> SELECT
> A.BANK_SETID
> ,A.BANK_CD
> ,A.BANK_ACCT_KEY
> ,A.PYMNT_ID
> ,B.business_unit_gl
> ,A.ACCOUNTING_DT
> ,A.doc_type
> ,A.doc_seq_nbr
> FROM
> ps_payment_tbl A
> PS_BANK_ACCT_CPTY B,
> WHERE
> AND A.bank_setid = B.setid
> AND A.bank_cd = B.bank_cd
> AND A.DOC_SEQ_DATE IS NULL
> AND A.DOC_SEQ_STATUS = 'N'
> AND A.PAY_CYCLE = 'BACOB1'
> AND A.PAY_CYCLE_SEQ_NUM = 516
> AND A.DOC_TYPE <> ' '
> ORDER BY
> B.BUSINESS_UNIT_GL,
> C.DOC_TYPE,
> C.ACCOUNTING_DT
>
> PS_BANK_ACCT_DEFN is not even required. See how this query
 performs.
>
> --
> MikeC
>
> Please reply to the group.
>

Sent via Deja.com
http://www.deja.com/ Received on Fri Dec 29 2000 - 04:20:28 CST