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:

• Identify the poorly running SQL via any conventionals means (V$SQLAREA etc)
• Create an outline for this SQL (with no tuning performed)
• Determine a set of hints to improve the access path of the SQL
• Create another outline for this new improved SQL.
• Then swap the outlines by tweaking the OL$HINTS table

  UPDATE OUTLN.OL$HINTS
  SET OL_NAME='tmp'
  WHERE OL_NAME = 'THE_BAD_SQL';
  
  UPDATE OUTLN.OL$HINTS
  SET OL_NAME='THE_BAD_SQL'
  WHERE OL_NAME = 'THE_NEW_SQL';
  
  UPDATE OUTLN.OL$HINTS
  SET OL_NAME='THE_NEW_SQL'
  WHERE OL_NAME = 'tmp';
  

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)