How To : APC - Seeing Performance Issue While Saving Item But Update Actually Saves

 

 

APC - Seeing Performance Issue While Saving Item But Update Actually Saves [ID 758383.1] 

---

 Modified 26-DEC-2008     Type HOWTO     Status MODERATED 

In this Document
  Goal
  Solution
  References

---

 

This document is being delivered to you via Oracle Support's Rapid Visibility (RaV) process, and therefore has not been subject to an independent technical review.

 

Applies to:

Oracle Advanced Product Catalog - Version: 11.5.10.2
Information in this document applies to any platform.

Goal

Users Updating Items experience extreme slowness when they apply their changes;
they experince issues creating items.

This typically takes 3 seconds. It is currently taking 5+ minutes, which is causing a timeout
message to be displayed on their APC HTML session

What was found:
Discovered that a batch item update program, using the standard Oracle API, was processing
105,000 records for update over a period of 19 hours (submitted by a user). Even after this
finished, still having slow performance BUT no longer are experiencing the timeouts.

Looks like the API waits for the last record to be updated to commit to the database.

ERROR
-----------------------
Error: Cannot Display Page

STEPS
-----------------------
Steps to Reproduce:
Responsibility CAN Development Manager
1 - Change Organization: (Pick any inventory organization)
2 - Item Catalog > Item Simple Search (enter a specific item), press Search
3 - Click on the found Item Link
4 - Select Planning Page Link
5 - Click the Update Button
6 - Change the value in the Planner Code field
** Timing Starts Here **
7 - Press the Apply button

Solution

Try the following where the performance issue is occuring.

(a) Please rebuild the index using the 'Item Catalog Text Index Build' concurrent program with
      the 'Rebuild Index' option.
(b) After this, run the 'Optimize Item Catalog Text Index' concurrent request

Please follow the Note 382809.1 to understand how to clear pending records in dr$waiting,
dr$pending tables.

Build the Item Catalog Text Index or Build the Change Management Text Index:
----------------------------------------------------------------------------------------------------

To take advantage of the Oracle Text search features available such as keyword search,
stemming search, and fuzzy search— run the concurrent program Item Catalog Text
Index Build. Any user with the Development Manager responsibility can submit this
concurrent program from the Submit Request window. Select the concurrent request
parameter Action=CREATE when submitting the request.


Optimizing the Item Catalog Text Index:
-------------------------------------------------------
As item data changes over time due to ongoing item insert, delete, or update operations, the query
response time may gradually decrease. Optimizing the index using the Optimize Item Catalog Text
Index concurrent program removes old data and minimizes index fragmentation, and therefore can
improve query response time.

The Item Catalog Text Index should be optimized:
• After the import of many items
• After deleting or updating of many items
• On a regular basis (it is recommended that you do this at regular intervals--for example, twice
a week)
• When you notice slow performance for item simple search
Note: This program optimizes at most 16,000 items per single run. To continue optimizing more
items, re-run the program.

To optimize the catalog text index:
1. In the Applications tree menu, click the "View Concurrent Requests" forms link.
2. In the Find Requests form, click Submit a New Request.
3. In the Submit a New Request form, select Single Request and click OK.
4. In the Submit Request form, select Optimize Item Catalog Text Index from the Name list of values.
5. In the Parameters form, provide the Program parameter values:
FAST
This optimization method compacts fragmented rows. However, old data remaining from deleted rows
is not removed.
FULL
This method both compacts rows and removes old data (for example, performs garbage collection).
Maximum Optimization Time
Specify the maximum optimization time, in minutes, for FULL optimize.
If you do not enter a value, the program runs until the entire index is optimized.
If you specify a maximum time, the program performs as much work as allowed by
the imposed time limit. The optimization picks up and continues the next time the
program is run. When you specify 0 for maximum time, Oracle performs minimal
optimization.
6. Click Submit.

References

BUG:5577145 - EXPERIENCING SLOW PERFORMANCE IN APC
BUG:5927070 - PERFORMANCE ISSUE - ITEM MASTER SAVING TAKES MORE THAN 7 MINUTES FOR EACH ITEM
NOTE:382809.1 - There Are Millions Of Records In CTXSYS.DR$PENDING And CTXSYS.DR$WAITING Tables

---