Article ID: 222

Seeing poor system performance following a large data import

Solution to poor performance following large data imports

Problem


Retail Pro is exhibiting poor performance following a recent large data import.

Symptom


  • During the initialization of a Retail Pro Prism POA (Point of Authority), there is a noticeable slow-down or hang during the inventory loading.
  • The loading of items in the Retail Pro merchandise "list view" screen takes a very long time. (Open Retail Pro > Merchandise > Inventory)

Cause


The execution plan in use for applying the request for rows from the Oracle database is using outdated (0) cost values.

Solution


1. Launch the Technicians Toolkit and go to: Monitoring and Tuning > Tuning.

2. Select "Start". This will update the cost values for the tables in the database which is an essential procedure to maintain good performance in the database.

 

Special Note on Oracle 12.c (Retail Pro 9.40)

Beginning with Retail Pro 9.4, Retail Pro has enabled an Oracle feature known as cursor sharing. This feature allows different sessions to share cached queries between them.

This means once a query is run in one session, it is stored along with its associated execution plan characteristics in a global cache and made available to all other sessions that need to use the same query.

Most of the time this will improve performance by allowing a session that has not already cached a query to take advantage of an existing query.

However, in a situation where a slow query is cached this can cause any session in use to execute slowly when using that area of the program.

Since a query's execution plan is based on the statistics gathered by tuning the database, this means that once a 'bad' query is cached it will remain available to be used until that cache is cleared. For now, the only way to clear that cache is to restart the Oracle database.

For this reason, until Techtoolkit is enhanced to handle this, if you experience a poorly performing query directly after a large data load (as noted in the examples above) we recommended clearing the cache by restarting the RPROODS database. Then, rerun the poorly performing query and a new execution plan will be created.