Phased release of a global risk system - gotchas!
A credit risk system, which provides limit exposure management of counterparties, is having various performance issues. This system was designed as a global system which will be implemented worldwide, however at the moment is only used to deal with London trading activities.
As expected, tables and indexes’ designs are driven by location, say LOCATION_ID.
Unfortunately because at the moment there is only 1 location using this, this is causing a lot of issues with oracle optimizer.
Table/index statistics were generated with the following option:METHOD_OPT => ‘FOR ALL INDEXED COLUMNS SIZE AUTO’. Because of this, Oracle favours the use of full table scans, rather than the nicely designated indexes. The result is disastrous.
As an outsider, with no intimate knowledge of the running of the system, there are 2 tactical solutions which I can offer here:
1) simplify the table/index statistics generation method. Do not generate histogram, in this case by default Oracle will create 2 buckets for most columns, and this will result in the designated indexes to be used in queries.
2) recrate indexes with LOCATION_ID as leading columns. Pick other columns that are much more suitable as leading columns, and if needed LOCATION_ID can be kept as third/fourth columns.
That’s it.