A computer systems response time for loading advanced search pages was 2 minutes< and the Oracle database at times needed to be restarted manually.
The SolutionsThe methodology was to stimulate the production load by having 100 virtual users hitting advances search page concurrently, while we closely monitored the systems resource usage in terms of CPU and memory usage from all layers, database activities, file system IO and network traffic,. There were 2 issues being addressed
One of the database access dll files did not release staled database connections, so the connection pool was exhausted. This also caused randomly failed LoadRunner transactions when the load went up.
The low-efficiency of the stored procedure resulted in excessively long response times. Development advice was given regarding the efficiency logic of the stored procedure.
The Benefits
The availability of the database increased and the Client’s productivity increased significantly from these improvements as advance search page was one of the most used ones. The response time went from 2minutes< to an average of 8 seconds