Adding outline hints to poorly performing Mat Views

I recently did an upgrade to 12.1.0.2 on one of our warehouse style databases and set the optimizer to the new level. We saw a lot of improvement for some materialized views and some degradation in full refresh time in others, some ridiculously so. We did run into an interesting situation with a couple where the refresh would take hours but the query itself only took a couple of minutes. To solve this we used the outline of the properly performing query as a hint for the materialized view.

First the query was run:

SELECT /*FINDME*/
      a.term
       a.id,
       b.subj_code
FROM term a, subj b
WHERE     a.term = b.term
       AND a.crn = b.crn;

Find the SQL_ID for the query:

select SQL_TEXT, SQL_ID from v$sql where sql_text like '%/*FINDME%';

And display the plan cursor for the SQL_ID:

select * from table(dbms_xplan.display_cursor('b0rzrxmb96ska',0,'ADVANCED ROWS ALLSTATS LAST'));

From the output copy the outline section that looks similar to the following: 

 /*+
    BEGIN_OUTLINE_DATA
    IGNORE_OPTIM_EMBEDDED_HINTS
    OPTIMIZER_FEATURES_ENABLE('12.1.0.2')
    DB_VERSION('12.1.0.2')
    ALL_ROWS
    OUTLINE_LEAF(@"SEL$2")
    MERGE(@"SEL$1")
    OUTLINE(@"SEL$1")
    FULL(@"SEL$2" "A"@"SEL$2")
    END_OUTLINE_DATA
*/

Hint the materialized view using that hint:

Create materialized view my_mv as SELECT  /*+
    BEGIN_OUTLINE_DATA
    IGNORE_OPTIM_EMBEDDED_HINTS
    OPTIMIZER_FEATURES_ENABLE('12.1.0.2')
    DB_VERSION('12.1.0.2')
    ALL_ROWS
    OUTLINE_LEAF(@"SEL$2")
    MERGE(@"SEL$1")
    OUTLINE(@"SEL$1")
    FULL(@"SEL$2" "A"@"SEL$2")
    END_OUTLINE_DATA
*/      a.term
       a.id,
       b.subj_code
FROM term a, subj b
WHERE     a.term = b.term
       AND a.crn = b.crn;

In performing the above steps I was able to bring the materialized view creation and refreshes to a time close to the query on which it was based.

*The select statement I'm using here does not necessarily match the outline as I had to change the structure of the query for publication.