I am running Oracle 7.4.3 on a Digital Alpha machine. We have an application that runs on this platform. It used to perform 100 transactions per second. To our surprise, at this time it is only doing one transaction per second. Assuming there is no change to the in-house application in terms of amendments and all the devices are operating normally, plenty of free memory, diskspace, and CPU utilization is below 50%, what else can cause the application to run slowly?
One of the main reasons that database performance drops when nothing else changes is that the amount of data grows or changes. If the data changes then execution plans that worked well may no longer be optimal. Because you are on Oracle 7 the usual tools are not available, such as Statspack and the cost-based optimizer. The whole advantage of using the cost-based optimizer is that it will re-evaluate your execution plans as the data changes and modify them to keep them optimal. I would recommend, if possible, you move to at least a 9iR2 database. If not, insure that your tablespaces are not fragmented (reorganize them if needed) and look at SQL tuning to regain the lost performance.
Start the conversation
0 comments