| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.server -> Tuning SQL query with views
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 !!
To make it visual, I added my case study, but every help is welcome, also when more general!
For my case study I post here :
- the tkprof-output of the Oracle trace
When I look at the existing indexes I understand that the used indexes (PSAPAYMENT_TBL), can't speed up the statement, because they don't have the correct and necessary columns. But when I try to create an index with the correct columns, Oracle doesn't want to take these indexes!
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 <> ' '
call count cpu elapsed disk query current rows
Parse 3 0.15 0.22 0 0 0 0 Execute 179 0.04 0.04 0 0 0 0 Fetch 179 2997.68 6255.64 3048958 54689691 895 1735
total 361 2997.87 6255.90 3048958 54689691 895 1735
Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 21 (SYSADM)
Rows Execution Plan
------- ---------------------------------------------------
0 SELECT STATEMENT GOAL: CHOOSE
178 SORT (ORDER BY)
42607 NESTED LOOPS 42607 TABLE ACCESS (FULL) OF 'PS_BANK_ACCT_CPTY' 42607 INDEX (UNIQUE SCAN) OF 'PS_BANK_ACCT_DEFN' (UNIQUE) 132864 TABLE ACCESS (BY INDEX ROWID) OF 'PS_PAYMENT_TBL' 175471 INDEX (RANGE SCAN) OF 'PSAPAYMENT_TBL' (NON-UNIQUE) ************************************************************************ -------------------------------------------------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,
,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
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
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
Existing indexes:
SQLWKS> select table_name, index_name, COLUMN_POSITION, COLUMN_NAME
2> from dba_ind_columns where table_name in ('PS_BANK_ACCT_CPTY', 'PS_PAYMENT_TBL', 'PS_BANK_ACCT_DEFN')
3> order by table_name, index_name, COLUMN_POSITION;
TABLE_NAME INDEX_NAME
COLUMN_POS
PS_PAYMENT_TBL PS#PAYMENT_TBL1
PS_PAYMENT_TBL PS#PAYMENT_TBL2
PS_PAYMENT_TBL PS#PAYMENT_TBL3
PS_PAYMENT_TBL PS#PAYMENT_TBL4
PS_PAYMENT_TBL PS#PAYMENT_TBL5
PS_PAYMENT_TBL PS#PAYMENT_TBL6
PS_PAYMENT_TBL PS0PAYMENT_TBL1
PS_PAYMENT_TBL PS0PAYMENT_TBL2
PS_PAYMENT_TBL PS0PAYMENT_TBL3
PS_PAYMENT_TBL PS0PAYMENT_TBL4
PS_PAYMENT_TBL PS0PAYMENT_TBL5
PS_PAYMENT_TBL PS0PAYMENT_TBL6
PS_PAYMENT_TBL PSAPAYMENT_TBL1
PS_PAYMENT_TBL PSAPAYMENT_TBL2
PS_PAYMENT_TBL PSAPAYMENT_TBL3
PS_PAYMENT_TBL PSAPAYMENT_TBL4
PS_PAYMENT_TBL PSBPAYMENT_TBL1
PS_PAYMENT_TBL PSBPAYMENT_TBL2
PS_PAYMENT_TBL PSBPAYMENT_TBL3
PS_PAYMENT_TBL PSCPAYMENT_TBL1
PS_PAYMENT_TBL PSCPAYMENT_TBL2
PS_PAYMENT_TBL PSCPAYMENT_TBL3
PS_PAYMENT_TBL PSDPAYMENT_TBL1
PS_PAYMENT_TBL PSDPAYMENT_TBL2
PS_PAYMENT_TBL PSDPAYMENT_TBL3
PS_PAYMENT_TBL PSDPAYMENT_TBL4
PS_PAYMENT_TBL PSFPAYMENT_TBL1
PS_PAYMENT_TBL PSFPAYMENT_TBL2
PS_PAYMENT_TBL PSFPAYMENT_TBL3
PS_PAYMENT_TBL PSFPAYMENT_TBL4
PS_PAYMENT_TBL PS_PAYMENT_TBL1
PS_PAYMENT_TBL PS_PAYMENT_TBL2
PS_PAYMENT_TBL PS_PAYMENT_TBL3
PS_PAYMENT_TBL PS_PAYMENT_TBL4
Sent via Deja.com
http://www.deja.com/
Received on Mon Dec 18 2000 - 12:16:59 CST
 |
 |