Materialized views ddl and dml stati (and the infamous NEEDS_COMPILE)


introduction

Employing materialized views (or snapshot or mview for short) for producing simple sum-up reports is really cool stuff. You just launch some statement like below and will be served with a well performing collection of data that is more or less current (that is why i call that simple). Oracle will care for the refresh of the mview by self-registering a job on calling dbms_refresh.refresh('some_mv');, that starts immediately and takes the interval from the statement. Handing in the force keyword just chooses a fast refresh method iff possible and a complete refresh method otherwise. There are more defaults applied by leaving other keywords unset, the refresh mode for example, but this is not the point of the article. It is:

Why does that … mview always show a state of NEEDS_COMPILE although no ddl action has happend along the objects dependency chain lately?

create materialized view some_mv
  build immediate
  refresh force next (sysdate + 1/24/12)
as
select X.ts, Y.name from (
  select to_char(max(A.ts), 'dd.mm.yyyy hh24:mi') as ts, B.blubb
    from tab1 A, tab1_1 B where A.id = B.id
    group by B.blubb
) X, tab2 Y
  where X.blubb = Y.id;

Uuh, the answer is not easy to give and much less easy to explain (by means of the oracle dictionary). Generally spoken, a NEEDS_COMPILE in fact implies another compile to be executed on the mview but, however, not to re-evaluate code integrity of the mview but data integrity instead. in other words, a recompile will find out whether some mview is stale concerning the underlying data (you know that from statistics and stuff). But let’s find out about that. A good starting point is the following article from metalink:

(454552.1) How To: What Causes So Many Invalid Materialized Views

testbed and tests

However, it will only show you expected behaviour, in a sense that oracle assumes what is to be expected behaviour and what is not. As a dba you may turn the medal up and down, you’ll not manage to bypass detailed testing in order to know what you get. My eye into the system is the following statement that i have been setting up from dba_mviews, dba_mview_refresh_times and dba_objects. dba_snapshots is no longer regarded, since it has not been documented from oracle since 10gR1 onwards (iff i remember it correctly), but is still available though. Some typical columns of dba_snapshots, as master_owner, master and master_link can now be sourced from dba_mview_refresh_times.master_owner,
dba_mview_refresh_times.master and dba_mviews.master_link, respectively. Please note also, that dba_objects contains two entities for the mview, one of object_type = 'MATERIALIZED VIEW' and one of object_type = 'TABLE'. This is important later on, when checking the status of the mview as seen from dba_objects. Another point to puzzle around with was the timestamps given with the three different views. Along my tests any data of dba_mviews.last_refresh_date, dba_mview_refresh_times.last_refresh and dba_objects.last_ddl_time where object_type = 'MATERIALIZED VIEW' was identical. Ok, here we go for the eye into the system statement (the commented columns will not be shown below but you may probably need them for another exercise):

select
  A.owner as owner, A.mview_name as name,
  (select A.compile_state || '/' || invalid from dba_mview_analysis
    where owner = A.owner and mview_name = A.mview_name) as c_stat_inval,
  (select master_owner || '.' || master ||
      decode(A.master_link, null, null, '@' || master_link) from dba_mview_refresh_times
    where owner = A.owner and name = A.mview_name) as master,
  (select status from dba_objects
    where owner = A.owner and object_name = A.mview_name
      and object_type = 'MATERIALIZED VIEW') as mvw_status,
  (select status from dba_objects
    where owner = A.owner and object_name = A.mview_name
      and object_type = 'TABLE') as tab_status,
  (select A.staleness || '/' || known_stale from dba_mview_analysis
    where owner = A.owner and mview_name = A.mview_name) as stale_known,
  /*A.stale_since, A.refresh_mode as refresh_mode, A.refresh_method as refresh_method,*/
  /*A.last_refresh_type,*/ A.last_refresh_date as last_refr_ts
from dba_mviews A
where A.mview_name in ('SOME_MV')
order by 1, 2, 3;

and this is a couple of tables and constraints to define a mview upon:

create table tab1 ( id number not null, ts date not null, flag varchar2(1) default not null,
  constraint tab1_pk primary key(ts, id) using index );
alter table tab1 add ( constraint tab1_uq unique (id) not deferrable using index );
create table tab1_1 ( id number not null, blubb number not null,
  constraint tab1_1_pk primary key(id) using index );
alter table tab1_1 add ( constraint tab1_1_tab1_fk foreign key( id)
  references tab1( id) not deferrable enable );
create table tab2 ( id number not null, name varchar2(128) not null,
  constraint tab2_pk primary key(id) using index );
alter table tab1_1 add ( constraint tab1_1_tab2_fk foreign key( blubb)
  references tab2( id) not deferrable enable );

What follows now is essentially a do something and look what happens, mostly by executing the eye into the system statement (only the last two trials use pl/sql). The tests were executed on a 11.2.0.1 / windows 64.

Firstly, the mview gets created (actually the mview statement from above) and we look for the results:

create materialized view some_mv
  build immediate
  refresh force next (sysdate + 1/24/12)
as
select X.ts, Y.name from (
  select to_char(max(A.ts), 'dd.mm.yyyy hh24:mi') as ts, B.blubb
    from tab1 A, tab1_1 B where A.id = B.id
    group by B.blubb
) X, tab2 Y
  where X.blubb = Y.id;
NAME    | C_STAT_INVAL    | MASTER | MVW_STATUS | TAB_STATUS | STALE_KNOWN     | LAST_REFR_TS
----------------------------------------------------------------------------------------------------
SOME_MV | VALID/N         | TAB2   | VALID      | VALID      | FRESH/N         | 08.04.2011 18:37:44
.

Jep, for C_STAT_INVAL the source column dba_mviews.compile_status looks fine and dba_mview_refresh_times.invalid is not true. For MVW_STATUSand TAB_STATUS, which represent the above mentioned status of the mview as seen from dba_objects, we get what we expect. Also dba_mviews.staleness is in order and dba_mview_refresh_times.known_stale does not “know” about any staleness. The next step is to insert some data and look again:

insert into tab2 (id, name) values (1, 'name 1');
insert into tab2 (id, name) values (2, 'name 2');
insert into tab1 (id, ts, flag) values (1, sysdate, 'T');
insert into tab1 (id, ts, flag) values (2, sysdate-1/12, 'T');
insert into tab1_1 (id, blubb) values (1, 1);
insert into tab1_1 (id, blubb) values (2, 2);
commit;
NAME    | C_STAT_INVAL    | MASTER | MVW_STATUS | TAB_STATUS | STALE_KNOWN     | LAST_REFR_TS
----------------------------------------------------------------------------------------------------
SOME_MV | NEEDS_COMPILE/Y | TAB2   | INVALID    | VALID |    | NEEDS_COMPILE/N | 08.04.2011 18:42:44
.

Hhm, the stati change, except for “knowing” about staleness and the mview status from dba_objects has flipped too. Ok, why not. Since the given data convinced me to compile the mview, i go for that:

alter materialized view some_mv compile;
NAME    | C_STAT_INVAL    | MASTER | MVW_STATUS | TAB_STATUS | STALE_KNOWN     | LAST_REFR_TS
----------------------------------------------------------------------------------------------------
SOME_MV | VALID/N         | TAB2   | VALID      | VALID      | STALE/Y         | 08.04.2011 18:54:40
.

Aah, everything ok gain, but the mview has now been explicitely marked stale (the underlying data is inconsistent with the mview) and oracle does “know” about that. Fine. However, since the mview has now lost consistency with the underlying data, we should do an on-demand refresh (will actually be triggered from the job being created from oracle automatically iff you follow the straight path):

exec dbms_refresh.refresh('SOME_MV');
NAME    | C_STAT_INVAL    | MASTER | MVW_STATUS | TAB_STATUS | STALE_KNOWN     | LAST_REFR_TS
----------------------------------------------------------------------------------------------------
SOME_MV | VALID/N         | TAB2   | VALID      | VALID      | FRESH/N         | 08.04.2011 18:57:02
.

Yes, everything shiny again. Now, the following will execute an update on the given master table of the mview:

update tab2 set name = 'name 22222222' where id = 2;
commit;
NAME    | C_STAT_INVAL    | MASTER | MVW_STATUS | TAB_STATUS | STALE_KNOWN     | LAST_REFR_TS
----------------------------------------------------------------------------------------------------
SOME_MV | NEEDS_COMPILE/Y | TAB2   | INVALID    | VALID      | NEEDS_COMPILE/N | 08.04.2011 19:03:17
.

Obviously the same as with the initial inserts … expected behaviour. Just to be shure, we take a look at the real data:

select 'MV' as srcc, ts, name from some_mv
union all
select 'STMT' as srcc, X.ts, Y.name from (
  select to_char(max(A.ts), 'dd.mm.yyyy hh24:mi') as ts, B.blubb
    from tab1 A, tab1_1 B where A.id = B.id
    group by B.blubb
) X, tab2 Y
  where X.blubb = Y.id;
SRCC | TS               | NAME
---------------------------------------
MV   | 08.04.2011 18:42 | name 1
MV   | 08.04.2011 16:47 | name 2
STMT | 08.04.2011 18:42 | name 1
STMT | 08.04.2011 16:47 | name 22222222
.

Jep, as expected again. Not missing to reset up the testbed, i.e. the mview stati, for the next case using dbms_refresh.refresh();, see above, i ask what happens when i update a column of a non master table that even does not appear with the mview setup statement:

update tab1 set flag = 'F' where id = 2;
commit;
NAME    | C_STAT_INVAL    | MASTER | MVW_STATUS | TAB_STATUS | STALE_KNOWN     | LAST_REFR_TS
----------------------------------------------------------------------------------------------------
SOME_MV | NEEDS_COMPILE/Y | TAB2   | INVALID    | VALID      | NEEDS_COMPILE/N | 08.04.2011 19:07:12
.

Uuh, everything invalid again. This was not expected, at least by me (got to rtfm). I do another manual refresh to clean up the testbed and now switch from dml to ddl test mode. I wonder what will be the result of the following:

alter table tab1 add(blabla number null );
NAME    | C_STAT_INVAL    | MASTER | MVW_STATUS | TAB_STATUS | STALE_KNOWN     | LAST_REFR_TS
----------------------------------------------------------------------------------------------------
SOME_MV | NEEDS_COMPILE/Y | TAB2   | INVALID    | VALID      | NEEDS_COMPILE/N | 08.04.2011 19:34:40
.

Nothing essentially new it seems and, most important, nothing different from the dml cases? Clean up the testbed one more time and try harder now, be really nasty:

drop table tab1_1;
NAME    | C_STAT_INVAL    | MASTER | MVW_STATUS | TAB_STATUS | STALE_KNOWN     | LAST_REFR_TS
----------------------------------------------------------------------------------------------------
SOME_MV | NEEDS_COMPILE/Y | TAB2   | INVALID    | VALID      | NEEDS_COMPILE/N | 08.04.2011 19:37:23
.

Aha, needs some compile. We saw that before, did’nt we. I supposed to do something really out of style but oracle still seems to keep calm. Again, i might need a recompile. I’ll just have a try:

alter materialized view some_mv compile;
NAME    | C_STAT_INVAL    | MASTER | MVW_STATUS | TAB_STATUS | STALE_KNOWN     | LAST_REFR_TS
----------------------------------------------------------------------------------------------------
SOME_MV | COMPILATION_ERROR/N | TAB2 | INVALID  | VALID      | COMPILATION_ERROR/N | 08.04.2011 19:37:23
.

Bingo, only now the code inconsistency gets reflected with the dictionary. But, out of the habits of a dba, let’s try a show errors after the compile with a pl/sql shell:

show errors
Keine Fehler.

Puuuuh, no errors. This can’t be true! And what will dbms_refresh.refresh(...); tell us?

exec dbms_refresh.refresh('SOME_MV');
FEHLER in Zeile 1:
ORA-12018: Folgender Fehler bei Codegenerierung f³r SOME_MV aufgetreten
ORA-00942: Tabelle oder View nicht vorhanden
ORA-06512: in "SYS.DBMS_SNAPSHOT", Zeile 2558
ORA-06512: in "SYS.DBMS_SNAPSHOT", Zeile 2771
ORA-06512: in "SYS.DBMS_IREFRESH", Zeile 685
ORA-06512: in "SYS.DBMS_REFRESH", Zeile 195
ORA-06512: in Zeile 1

Finally, we get a thorough exception, although, however, the exception contains ora-00942 which is another running infamous error message, for not telling us what table or view is actually missing … I was also trying a view access based on the dropped table scenario and the information from the dictionary did not change compared to the data above. Last but not least I re-created the table along with the foreign key and found that one has to do an alter materialized view ... compile; before any other exec dbms_refresh.refresh(...); to fix the situation, looks logical.

conclusions

Throughout the article and in regard to a mview, I was talking a lot about consistency, namely concerning the underlying data and the code dependency. After testing the behaviour of a mview under different scenarions, I may tend to have learned that the implementation of the mview sql or pl/sql interface with oracle is not consistent with other interfaces, i.e. for regular views or for tables. The most astonishing point is however, that you can’t tell at any given point in time, whether some mview is invalid due to some dml or ddl changes of the underlying layers. In general, i believe that the seen mview interface comprises a design flaw in integrating the conceptually different aspects of code and data into a usually code-dedicated compile statement. I would, iff someone out there would ask me, ha ha, propose to include a pseudo-column with every mview that tells you about the staleness of the underlying data with just this executed select (using every day oracle read consistency).

Have fun, nevertheless.

Advertisements

Leave a Reply

Please log in using one of these methods to post your comment:

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