Performance issues with AP Invoices Search Screen (Doc ID 2019334.1)

Last updated on MARCH 08, 2017

Applies to:

Oracle Payables - Version 12.2.2 and later
Information in this document applies to any platform.
Performance issues with AP Invoices Search Screen

Symptoms

SQL ID: 4hp0007p2a86h Plan Hash: 1767258300

SELECT /*+ push_pred(AP_INVOICES_V.pv) push_pred(AP_INVOICES_V.pvs) */
 CUST_REGISTRATION_NUMBER,INVOICE_TYPE,QUICK_PO_NUMBER,VENDOR_NAME,
 VENDOR_NUMBER,VENDOR_SITE_CODE,INVOICE_DATE,INVOICE_NUM,
 INVOICE_CURRENCY_CODE,INVOICE_AMOUNT,ORIGINAL_INVOICE_AMOUNT,DISPUTE_REASON,
 TOTAL_TAX_AMOUNT,CONTROL_AMOUNT,GL_DATE,PAYMENT_CURRENCY_CODE,
 PAYMENT_CROSS_RATE_DATE,PAYMENT_CROSS_RATE_TYPE,PAY_CURR_INVOICE_AMOUNT,
 PAYMENT_CROSS_RATE,DISTRIBUTION_SET_NAME,DESCRIPTION,QUICK_CREDIT,
 CREDITED_INVOICE_ID,CREDITED_INVOICE_NUM,PROJECT,TASK,EXPENDITURE_ITEM_DATE,
 EXPENDITURE_TYPE,EXPENDITURE_ORGANIZATION_NAME,PA_QUANTITY,USER_RATE_TYPE,
 EXCHANGE_DATE,BASE_AMOUNT,TERMS_DATE,TERMS_NAME,PAYMENT_METHOD_CODE,
 IBY_PAYMENT_METHOD,PAY_GROUP_LOOKUP_CODE,EXCLUSIVE_PAYMENT_FLAG,
 AMOUNT_APPLICABLE_TO_DISCOUNT,INVOICE_RECEIVED_DATE,GOODS_RECEIVED_DATE,
 PREPAYMENT_TYPE_LOOKUP_CODE,EARLIEST_SETTLEMENT_DATE,AWT_GROUP_NAME,
 PAY_AWT_GROUP_NAME,CUST_REGISTRATION_CODE,AMOUNT_WITHHELD,PREPAID_AMOUNT,
 DOC_CATEGORY_NAME,DOC_SEQUENCE_NAME,APPROVED_AMOUNT,APPROVAL_STATUS,
 APPROVAL_DESCRIPTION,CANCELLED_DATE,CANCELLED_AMOUNT,BATCH_NAME,ROW_ID,
 ACCTS_PAY_CODE_COMBINATION_ID,ALWAYS_TAKE_DISC_FLAG,AMOUNT_PAID,ATTRIBUTE1,
 ATTRIBUTE2,ATTRIBUTE3,ATTRIBUTE4,ATTRIBUTE5,ATTRIBUTE6,ATTRIBUTE7,
 ATTRIBUTE8,ATTRIBUTE9,ATTRIBUTE10,ATTRIBUTE11,ATTRIBUTE12,ATTRIBUTE13,
 ATTRIBUTE14,ATTRIBUTE15,ATTRIBUTE_CATEGORY,AUTHORIZED_BY,AWT_FLAG,
 AWT_GROUP_ID,PAY_AWT_GROUP_ID,BATCH_ID,CANCELLED_BY,CREATED_BY,
 CREATION_DATE,DISCOUNT_AMOUNT_TAKEN,DISTRIBUTION_SET_ID,DOC_CATEGORY_CODE,
 DOC_SEQUENCE_ID,DOC_SEQUENCE_VALUE,EXCHANGE_RATE,EXCHANGE_RATE_TYPE,
 EXCLUDE_FREIGHT_FROM_DISCOUNT,EXPENDITURE_ORGANIZATION_ID,HIGHEST_LINE_NUM,
 HOLD_FUTURE_PAYMENTS_FLAG,INVOICE_AMOUNT_LIMIT,LINE_TOTAL,INVOICE_ID,
 INCOME_TAX_REGION,INVOICE_TYPE_LOOKUP_CODE,LAST_UPDATED_BY,LAST_UPDATE_DATE,
 LAST_UPDATE_LOGIN,MINIMUM_ACCOUNTABLE_UNIT,NUM_1099,ORG_ID,
 PA_DEFAULT_DIST_CCID,PAY_DATE_BASIS_LOOKUP_CODE,PAYMENT_AMOUNT_TOTAL,
 PAYMENT_PRIORITY,PAYMENT_STATUS,PAYMENT_STATUS_FLAG,PO_HEADER_ID,
 POSTING_STATUS,PRECISION,PROJECT_ID,QUICK_PO_HEADER_ID,TYPE_1099,
 RECURRING_PAY_NUM,RECURRING_PAYMENT_ID,RECURRING_PAYMENT_TYPE,
 RECURRING_PERIOD_TYPE,REFERENCE_1,REFERENCE_2,SET_OF_BOOKS_ID,
 SET_OF_BOOKS_NAME,SITE_ALLOW_AWT_FLAG,SOURCE,TASK_ID,TEMP_CANCELLED_AMOUNT,
 TERMS_DATE_BASIS,TERMS_ID,VENDOR_ID,VENDOR_PREPAY_AMOUNT,VENDOR_SITE_ID,
 VOUCHER_NUM,TAXATION_COUNTRY_NAME,TAXATION_COUNTRY,
 TRX_BUSINESS_CATEGORY_NAME,TRX_BUSINESS_CATEGORY,USER_DEFINED_FISC_CLASS,
 TAX_RELATED_INVOICE_NUM,TAX_RELATED_INVOICE_ID,DOCUMENT_SUB_TYPE,
 SELF_ASSESSED_TAX_AMOUNT,TAX_INVOICE_INTERNAL_SEQ,
 SUPPLIER_TAX_INVOICE_NUMBER,TAX_INVOICE_RECORDING_DATE,
 SUPPLIER_TAX_INVOICE_DATE,SUPPLIER_TAX_EXCHANGE_RATE,PORT_OF_ENTRY_CODE,
 REMIT_TO_SUPPLIER_NAME,REMIT_TO_SUPPLIER_ID,REMIT_TO_SUPPLIER_SITE,
 REMIT_TO_SUPPLIER_SITE_ID,RELATIONSHIP_ID,EXTERNAL_BANK_ACCOUNT_ID,
 BANK_ACCOUNT_NAME,BANK_ACCOUNT_NUM,PAYMENT_REASON_CODE,PAYMENT_REASON,
 PAYMENT_REASON_COMMENTS,UNIQUE_REMITTANCE_IDENTIFIER,URI_CHECK_DIGIT,
 BANK_CHARGE_BEARER,BANK_CHARGE_BEARER_DSP,DELIVERY_CHANNEL_CODE,
 DELIVERY_CHANNEL,SETTLEMENT_PRIORITY,SETTLEMENT_PRIORITY_DSP,
 REMITTANCE_MESSAGE1,REMITTANCE_MESSAGE2,REMITTANCE_MESSAGE3,
 FORCE_REVALIDATION_FLAG,AWARD_ID,GLOBAL_ATTRIBUTE_CATEGORY,
 GLOBAL_ATTRIBUTE1,GLOBAL_ATTRIBUTE2,GLOBAL_ATTRIBUTE3,GLOBAL_ATTRIBUTE4,
 GLOBAL_ATTRIBUTE5,GLOBAL_ATTRIBUTE6,GLOBAL_ATTRIBUTE7,GLOBAL_ATTRIBUTE8,
 GLOBAL_ATTRIBUTE9,GLOBAL_ATTRIBUTE10,GLOBAL_ATTRIBUTE11,GLOBAL_ATTRIBUTE12,
 GLOBAL_ATTRIBUTE13,GLOBAL_ATTRIBUTE14,GLOBAL_ATTRIBUTE15,GLOBAL_ATTRIBUTE16,
 GLOBAL_ATTRIBUTE17,GLOBAL_ATTRIBUTE18,GLOBAL_ATTRIBUTE19,GLOBAL_ATTRIBUTE20,
 WFAPPROVAL_STATUS,APPROVAL_READY_FLAG,REQUESTER_ID,APPROVAL_ITERATION,
 LEGAL_ENTITY_ID,WFAPPROVAL_STATUS_DSP,NET_OF_RETAINAGE_FLAG,
 RELEASE_AMOUNT_NET_OF_TAX,PARTY_ID,PARTY_SITE_ID,PAYMENT_FUNCTION,
 PAY_PROC_TRXN_TYPE_CODE,DISC_IS_INV_LESS_TAX_FLAG,APPLICATION_ID
FROM
AP_INVOICES_V WHERE (VENDOR_NUMBER LIKE :1) order by
 CUST_REGISTRATION_NUMBER


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      2.05       4.01          0          0          0           0
Fetch        1    169.22     605.06    1193552   20153238          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        3    171.27     609.08    1193552   20153238          0         


Cause

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms