OPT_ESTIMATE-Hint für Materialized View Fast Refresh

Die folgende Beobachtung verdanke ich Randolf Geist, der mir auf meine Anfrage im OTN-Forum General Database Discussions den entscheidenden Hinweis gegeben hat. Beim Fast Refresh für Materialized Views in 11g ergeben sich regelmäßig Merge-Statements der folgenden Form:

/* MV_REFRESH (MRG) */ 
MERGE INTO "TEST"."T_MV" "SNA$" 
USING (SELECT /*+ OPT_ESTIMATE(QUERY_BLOCK MAX=1000) */

Grunsätzlich sind die zugehörigen Mechanismen bekannt - insbesondere Alberto Dell'Era hat dazu in seinem Blog umfassende Erläuterungen geliefert -, aber zum enthaltenen OPT_ESTIMATE-Hint habe ich nicht viel gefunden; die Suchmaschine meines Vertrauens hat mich in diesem Zusammenhang wieder zurück auf meine eigene Materialsammlung geführt, die ich hier vor einigen Jahren notiert hatte, aber dort steht nur, dass der Hint in diesem Zusammenhang erscheint. Mein Problem damit war, dass sich der Wert des OPT_ESTIMATE-Hints  bei unterschiedlichen Ausführungen verändert und mir bisher nicht klar war, woraus sich diese Parametrisierung ergibt. Ich hatte cardinality feedback im Verdacht und auf sql profiles, aber auch nach Deaktivierung des feedbacks bzw. in Abwesenheit von profiles änderte sich das Verhalten nicht. Nach Randolfs Hinweis, dass hier  habe ich folgenden Test durchgeführt:

-- 11.2.0.1
-- creation of table, mview log and mview
create table t
as
select rownum id
     , mod(rownum, 50) col1
     , mod(rownum, 10) col2
     , lpad('*', 50, '*') col3
  from dual
connect by level <= 1000000;

create materialized view log on t with rowid (id, col1, col2, col3) including new values;     

create materialized view t_mv
refresh fast on commit
as
select col1
     , sum(col2) sum_col2
     , count(*) cnt
     , count(col2) cnt_col2
  from t
 group by col1;

-- exec dbms_stats.set_table_stats(user, 'T_MV', numrows=>100)
-- exec dbms_stats.set_table_stats(user, 'T', numrows=>10000)
-- exec dbms_stats.set_table_stats(user, 'MLOG$_T', numrows=>10000)

select sql_id
     , substr(st.sql_text, instr(st.sql_text, 'OPT_ESTIMATE'), 40) sql_text
     , last_active_time
  from v$sql st
 where upper(st.sql_text) like '%OPT_ESTIMATE%'
   and upper(st.sql_text) like '/* MV_REFRESH (MRG) */%'
 order by last_active_time;

Dabei habe ich die Statistiken für die Anzahl der Datensätze für Tabelle, MView log und MView über dbms_stats.set_table_stats angepasst und dabei folgende Muster beobachtet:

• die Statistiken für Tabelle T und MView log MLOG$_T spielen für den OPT_ESTIMATE-Hint keine Rolle.
• ohne Statistiken für die MView T_MV wird ein default-Wert 1000 eingesetzt.
• wenn Statistiken vorliegen, dann wird der für den OPT_ESTIMATE-Hintverwendete Wert berechnet als trunc(num_rows/10)
• das Muster bleibt erhalten bis zum Wert 21474836479 (im Hint erscheint 2147483647). Mit 21474836480 scheint ein Überlauf erreicht zu sein, denn dann erscheint im Hint ein negativer Wert: -2147483648. Ich behaupte mal, dass hier 2³¹ erreicht ist.

Das Muster scheint also nicht besonders kompliziert zu sein. Ausgangspunkt meiner Suche war übrigens die Hoffnung, die Pläne der MView-Refresh-Statements über sql plan baselines stabilisieren zu können, was durch die Veränderung der Hints (bzw. der Statistiken) schwierig wird, aber dafür gibt es vermutlich noch andere Lösungen.