Oracle training, tutorials and consulting
Search For:


For Better, Faster, Smarter, Training and Consultancy




Oracle Consultancy Case Studies

Smartsoft have many years experience with Oracle and related technologies helping many organisations including CGI(Europe), BP, Royal&Sun Alliance, British Energy, Lloyds Bank, Robert Gordon University with the design, development, testing and tuning of Oracle applications since 1987

Some examples of what we have achieved for previous clients are :-

1. CGI(Europe)

CGI have developed a generic insurance application for managing policies, claims, broker commissions, quotations, payments etc. for insurance companies which is then tailored to the exact requirements of each client. We have helped CGI with several projects over the years.

One of their projects was suffering due to the poor performance of the application. We provided consultancy to them to optimise the performance of the policy management subsystem.

The first step was to analyse the existing design and the source code of the major components to identify the specific areas and modules that needed improvement.

The next step was to redesign and rewrite those components including introducing new views and stored procedures to maixmise code reuse and minimise the amount of code on the client (particularly database queries and data manipulation) . This resulted in performance improvements of 60% in some cases.

2. Royal&Sun Alliance

We have been involved with a number of projects with Royal&Sun Alliance, one of which was a data warehouse that was used for reporting on the long-term profitability of commercial insurance polices sold to cover things like gradually-developing diseases such as silicosis.

R&SA had a problem in that the monthly refresh of the data warehouse used to take a week of elapsed time which obviously reduced the availability and therefore usefulness of the data warehouse. The monthly refresh also required baby-sitting by 3-4 highly paid technical staff during the week, which obviously made the data warehouse very expensive to operate.

We were asked to analyse the problems and provide a solution.

One of the problems we found was that the various SQL scripts used to update the database were very inefficient and some were also incorrect (in the sense that they did not meet the specification). By optimising and correcting these we were able to reduce the month-end update process of the data warehouse to a week-end..

That was the first stage - shortening the process - the second stage was to automate it, so that it could be left in the hands of the operators and involve technical staff only if serious problems occurred.

By making each of the update scripts re-runnable (by knowing where it failed and being able to restart from that point) and by chaining the update scripts together by means of a controlling script the whole process was automated and required no manual intervention unless there was an error. This reduced the costs of running the data warehouse considerably and freed the technical staff to concentrate on other areas.

We have also helped Royal&Sun Alliance on the Life Insurance side. One of their projects involved the development of a system for their Group Risk section. R&SA asked for our help to improve the performance of this system.

One of the changes we introduced was to remove most of the business logic (apart from data validation) from the user interface and put in the Oracle database in the form of PL/SQL packages. This allowed the developers (who had little experience with Oracle databases) to concentrate on the GUI and enabled the system to have an object-oriented, 3-tier structure as well as significantly improving the system performance. A side-effect of this change was a reduction in the development costs due to an increase in the amount of code that could be reused.

3. Clerical Medical (now part of Lloyds Bank)

Clerical Medical developed their own systems to provide illustrations to potential customers and take on the new business. We provided support to the development team and built new environments as required for development and testing of the system.

WhenSamrtsoft took over this support function, there were two issues with it:-

By automating the environment build process and using scripts to apply data changes to relevant environments, we were able to reduce the time required to build new environments from 1 day to about 1 hour.

4. BP

We helped BP with various performance issues that they had with their business intellligence systems.

Their fuel cards system produced monthly reports of information extracted from the Oracle database but was very slow. We were able to optimise the performance of the system which resulted in the reports being produced in just half the time taken previously.

BP's Industrial and Commercial division had their own BI system which also had performance issues, particularly with one set of reports which were not able to be produced due to the way the code had been written. We simplified the code which reduced the load on the Oracle database thereby allowing these reports to be produced.

We also helped BP with the development and performance tuning of a new system for the commercial division, Our efforts led to a significant performance improvement. 

5. Logica

Logica asked us to investigate the performance of a 3rd party application which was used to support an energy trading platform being developed for their client.

We were able to do this by extracting from the Oracle database the queries being run, the time the queries took to run and the number of rows returned. This raw data was then manipulated to produce reports which were sent to the supplier along with recommendations on how to improve performance.

Oracle secrets revealed ! Subscribe to our ezine, jam-packed full of tips and tricks to help you slash costs, sky-rocket productivity and incrase your return on investment. Subscribe today and your first issue will soon be winging its way to your mailbox.


Need help with your systems?
Discover how to develop your systems better, faster and smarter than ever before with the expert help of our highly experienced Oracle specialists. Click here and complete the form to have somebody contact you, or call us to talk to one of our consultants with no obligation .

Does training pay? Gartner certainly thinks so.
"It is 2 to 27 times more cost-effective to hire an accomplished developer" - Gartner. Upskill your developers today with expert Oracle training from Smartsoft

Smartsoft Computing Ltd
Bristol, England

Tel: 0845 0031320
Contact Us

View our privacy policy .

This site uses to gather statistical information about our visitors. This data is aggregated to show industry trends (such as browser share). However, this data shall be the average of many thousands of visits and is in no way linked to individuals. View woopra privacy policy .

All trademarks areacknowledged and are the property of their respected owners.

Copyright Smartsoft Computing Ltd 2001-2013. All rights reserved.