Oracle Expert

I was asked to look at a finance system, which primarily is a reconciliation system between front office and back office sub ledger systems. The amount of records they have to deal with is about 2.5 million rows/day. Sizable, but not all that big. However, the system was so slow, it was unusable.

I can simply discount lack of hardware resource as an issue, as they are running on a relatively new and powerful server.

It turned out that the system was designed on Microsoft Access, and simply ported to Oracle 10g (version 10.2.0.4). Developers have no real understanding of basic oracle fundamentals. It astonished me that this happened in a large financial institution that is not short of oracle expertise.

To cut the story short, the solutions are simple:

• Partition the large balance tables by date. This is almost a gospel in finance-IT. You will read your data by business date, therefore partition your data by business date. Simple.
• Do not use global indexes for your partition tables. Really, you should be able to survive with local indexes only, and this will save you a lot of time that otherwise you would need to spend maintaining the global indexes.
• Do not use DEFAULT partition. I would rather have my system fail when it tries to write to a non-existing partition, rather than have this hidden from me, and a few days later, I end up having to deal with performance issue because my DEFAULT partition gets so big
• Do not SPLIT partition. ADD. It’s faster.
• Create yourself a simple but robust partition maintenance package, which will perform partition add, drop, truncate. This package can then be granted to operator accounts.
• Enhance the package so it also caters for statistics maintenance.
• If you rely on a lot of staging tables, make sure that statistics for these tables are not generated when the tables are empty. Generate statistics when these tables are populated. If necessary, simply do this once, and then lock the stats.
• If your rely on a lot of inserts and updates in your code, spend some time learning about MERGE command. It is a god send for finance-IT systems.

When designing your system, make it self maintaining. Your batch should include these steps

• roll business date
• add all required partition for the new business date (do not take the lazy way of pre-creating 100 partitions ahead of time)
• after populating a partition, do your stats generation there and then

OK, rant over.