Outlines: Introducing a Hint on a non-modifiable query

Last week I created 2 new indexes to solve a performance issue. Following the weekly statistics gathering of the schema, a query started to use one of those 2 indexes.

The effect was disastrous as a main application screen took around 12 seconds to populate instead of a fraction of it.

This application being a third party product, the queries are not modifiable.

My only option was to introduce a HINT into the query through an outline, so I proceeded as follow:

  • alter system set query_rewrite_enabled=TRUE scope=both;
  • alter system set use_stored_outlines=TRUE; (not a database parameter yet)
  • created an outline (QUERYO) with the original query (without the HINT)
  • created an outline (QUERYH) with the original query (including the required HINT)
  • Exchanged the outlines plans:
  • As SYS: update OUTLN.OL$HINTS
    where OL_NAME in (‘QUERYO’,’QUERYH’); commit;
  • drop outline QUERYH;
  • Created a database trigger to run ‘alter system set use_stored_outlines=TRUE’ on database startup (since this parameter is not a database parameter yet)