Tuning SQL in pre-packaged applications


Updates at the bottom

We've all done the hard yards of tuning SQL's that developers throw our way in order to keep our databases humming along. However, this assumes that we have access to the code.

For pre-packaged applications, we don't access to the code, so up until 8i, all we could do is possibly tune other areas of the database, cache some tables, add (or remove) indexes etc etc, all without being able to correct the main source of the problem...offensive SQL

Fortunately, help is now at hand. Version 8i introduces stored outlines, a facility which lets you 'fix' the access path for a given SQL. By hacking into the tables used for stored outlines, you can add hints to pre-packaged SQL's without actually changing any application code.

Use the following procedure:

Is this supported ? Unlikely

Update

Apparently Oracle has relaxed their stance (Metalink Note: 92202.1) and the swapping of outlines is supported in 8i, and in 9i, an editing feature is also available. Note that one additional step that you should consider is that the outline parent table (OL$) also has a HINTCOUNT column. Obviously if when swapping the outlines as per the process above, the hintcount should be amended. (Acknowledgement to JL's article here)