Poor Performance in Book Field In Asset Categories Form (Doc ID 1608415.1)

Last updated on AUGUST 04, 2017

Applies to:

Oracle Assets - Version 11.5.10.2 and later
Information in this document applies to any platform.
FAXSUCAT - Asset Categories

Symptoms

When clicking on the 'Book' field after querying the asset category at header level, the application is taking many minutes to render the book. Some validation seems to be happening as the busy cursor is displayed.

The SQL*Trace of this action in the form shows the following poorly performing select;

SELECT COUNT (1)
FROM SYS.DUAL   WHERE EXISTS  
(SELECT AH.CATEGORY_ID
FROM FA_ASSET_HISTORY AH ,  FA_BOOKS BK  
 WHERE AH.CATEGORY_ID =  :b1  AND AH.ASSET_ID =  BK.ASSET_ID  
AND AH.DATE_EFFECTIVE <  SYSDATE   AND BK.BOOK_TYPE_CODE =  :b2  AND
BK.TRANSACTION_HEADER_ID_OUT IS NULL    )  

Rows     Execution Plan
-------  ---------------------------------------------------
0      INDEX   MODE: ANALYZED (RANGE SCAN) OF 'FA_BOOKS_U2'
               (INDEX)
0      INDEX   MODE: ANALYZED (RANGE SCAN) OF
               'FA_ASSET_HISTORY_N1' (INDEX)

 

Changes

 Thousands of new asset additions have recently been loaded.

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