Seeing poor system performance following a large data import

Symptom

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

Examples

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

Problem

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

Solution

1. Launch the Technician's Toolkit and go to Monitoring and Tuning > select Tuning.

2. Choose Start

This will update the cost values for the tables in the database. This procedure is essential to maintain good performance in the database.

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

Beginning with 9.4 we have enabled an Oracle feature known as cursor sharing. This feature allows different sessions to share cached queries between them. What this means is that once a query is run in one session it, along with its associated execution plan characteristics, are stored 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. In a situation where a slow query is cached however this can cause any session to use to execute slowly when using that area of the program. As the execution plan a query uses to run 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 future enhancement to the Tech Toolkit allowing the global cache to be cleared has been implemented it is recommended that any time you have a large data load and you have experienced a poorly performing query directly after that, such as one the examples cited above, you should restart the RPROODS database in order to clear the cache. Then rerun the poorly performing query and a new execution plan will be executed.