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
    set OL_NAME=decode(OL_NAME,’QUERYH’,’QUERYO’,’QUERYO’,’QUERYH’)
    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)

Pierre

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s