EMEA VAT Selection Process Performance Issues (Doc ID 1901854.1)

Last updated on JANUARY 13, 2017

Applies to:

Oracle Receivables - Version 12.1.3 and later
Information in this document applies to any platform.

Symptoms

On : 12.1.3 version, EMEA VAT reporting

Attempting to run the EMEA VAT Selection Process (JGZZVATSP) and it is still running, after 15 hours.

The following statement, from the tkprof file, is a very expensive cost has been met:

  DELETE FROM ZX_REP_TRX_DETAIL_T DTL1
  WHERE DTL1.ROWID <>
  ( SELECT MIN (DTL2.ROWID)
  FROM ZX_REP_TRX_DETAIL_T DTL2
  WHERE DTL2.REQUEST_ID = :B1
  AND DTL2.TRX_ID = DTL1.TRX_ID
  AND DTL2.TAX_LINE_NUMBER = DTL1.TAX_LINE_NUMBER
  AND DTL2.TAX_RATE_ID = DTL1.TAX_RATE_ID
  AND DTL2.TAXABLE_ITEM_SOURCE_ID =
  DTL1.TAXABLE_ITEM_SOURCE_ID
  AND DTL2.EVENT_CLASS_CODE = DTL1.EVENT_CLASS_CODE
  AND DTL2.APPLIED_FROM_EVENT_CLASS_CODE =
  DTL1.APPLIED_FROM_EVENT_CLASS_CODE
  AND DTL2.ACTG_SOURCE_ID = DTL1.ACTG_SOURCE_ID
  AND DTL2.APPLICATION_ID = DTL1.APPLICATION_ID
  GROUP BY DTL2.REQUEST_ID,
  DTL2.TRX_ID,
  DTL2.EVENT_CLASS_CODE,
  DTL2.TAX_LINE_NUMBER,
  DTL2.ACTG_SOURCE_ID,
  DTL2.TAX_RATE_ID
  HAVING COUNT (DTL2.ACTG_SOURCE_ID) >= 2)
  AND DTL1.REQUEST_ID = :B1
  AND DTL1.EVENT_CLASS_CODE IN ('EDISC', 'UNEDISC', 'APP')
  AND DTL1.APPLIED_FROM_EVENT_CLASS_CODE = 'APP'
  AND DTL1.APPLICATION_ID = 222


call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0  0
Execute 1 32600.29 54604.33 983 315023309 18230  0
Fetch 0 0.00 0.00 0 0 0  0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2 32600.29 54604.33 983 315023309 18230 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