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
