Oracle 9i Performance Tuning at EDF Energy

In March 2008 EDF Energy asked us to help with performance tuning their production database. We were able to identify where in the code the poor performing queries were defined, and to develop more efficient versions of them using the Oracle plan tables (explain plan) to reduce overall cost, physical I/O and ultimately time taken to execute.

The Lodestar framework is very flexible and is easy to customise. For EDF Energy, this framework has been heavily customised to meet all the business requirements. It contains many custom queries and lots of PL/SQL procedures and functions. Geode has been working in a small team with the EDF Energy DBAs to improve performance of the system without any sacrifice of functionality.

The database is Oracle 9i and contains millions of contracts, customers, accounts and so on. Half-hourly consumption and forecast data is stored in the database and is used to generate pricing data. With such a large amount of data, it was important to tune the database performance for the specific queries that were performing badly.

We concentrated on the queries with worst performance first, and in general we were able to reduce the time users were having to wait from several minutes down to a second or two.

Summary

• Added missing indexes, including computed (function based) indexes
• Re-wrote original SQL for better performance
• Used "explain plan" to monitor improvements
• Reduced overall cost, physical I/O, and execution time
• Introduced pipelined functions. Execution time reduced from 17 minutes down to 5 seconds