Oracle RDBMS and Tools Services - Tuning Framework

The Problem

The client had recently converted a small but important VB/Access Risk Assessment application to VB/Oracle in line with policy stating that all critical applications must reside on the companies standard supported platform. A simple conversion had been done from Access to Oracle, and the initial re-work yielded some severe perfomance problems in 3 areas.

The Remit

To review, advise and where necessary apply changes in the badly performing areas.

The Solution Outline

• The first area considered was a oft needed report query that ran for 40 minutes each time. Interestingly we found that the query ran in under 5 seconds on 8i, but on the clients 9i database it ran very slowly. Analysis showed missing indexes (none of the foreign keys had any supporting indexes), and once these were added, some improvement was seen but some considerable rework of the query was able to make use of these indexes and the query then ran in under 5 seconds.
• A housekeeping batch job, which actually ran at set times during the day was running for hours. The main operation performed was to delete old data. We found excessive log switching to be the cause. Solution was to increase the log size from the default 1Mb to 20Mb. Concerns were raised about how this would lengthen the intervals during quiet weekend days when the logs would be archived, so we suggested the log_interval be set to an acceptable value (30 minutes). Increasing the log size reduced the time taken to run the process to around 5 minutes.
• A data load was taking far too long. A VB program read a file record by record, and posted "UPSERT"s to the database. The solution provided was to write the relevant data to a PLSQL collection via a call to a PLSQL procedure, then call another PLSQL procedure at the end of the file to use bulk INSERTs and UPDATEs. Again drastic reduction in run time was achived.

Benefits Realised

• All three areas of concern were fixed
• The developers learnt lessons not only in tuning techniques, but also in effective client/server partitioning to achieve optimal performance.