Posted: Mon Apr 25, 2011 4:31 pm Post subject: Re: Materialized View fast Refresh Problem
If you could contact the DBA's I would ask you to sit with them, kick on your job and see what are all the complete list of SQL Scripts sent to Oracle by the user id that is on the Datastore and see what scripts goes into the DB.
I know you are going in rounds for this issue. _________________ Cheers
Posted: Tue Apr 26, 2011 5:25 pm Post subject: Re: Materialized View fast Refresh Problem
I have some experience working with materialized views, logs and refreshes. I have observed behavior similar to what you describe in scenarios where I have experimented with bulk/direct loading. Conventional DML, including inserts, are recorded in the materialized view log along with additional details (rowids, primary key, filter columns, etc) based the options you specify when the materialized view log is created. However, if you are employing bulk loading, you may find that rows inserted in that fashion are instead recorded in some behind-the-scenes system tables.
There is an ALL_SUMDELTA view/public synonym that sort of gets at that, but you'd need to join back to some other system tables to unravel the details. If you are doing bulk loading, then you might consider snagging the object ID for your table from the dictionary and taking a look in ALL_SUMDELTA for activity in the relevant time frame.
Have you done some hash counts or validations and confirmed that the incremental, fast refresh is in fact leaving the materialized view inconsistent with the base tables? Or are you just concerned because the MLOG$ tables appeared empty?
If you are doing conventional DML, I would agree that the activity should appear there. The materialized view logs are cleared down automatically once ALL dependent, associated materialized views have been refreshed. There again, I think there's an off-limits system table buried somewhere that stores the last refresh timestamp at a dictionary level for a given materialized view. That timestamp is bumped up against timestamps in the the materialized view log(s) and ALL_SUMDELTA to determine which base table activity needs to be propagated to the materialized view. If your Data Integrator job is updating base tables and then calling the fast refresh all in one swoop, then those logs may well be cleared down before you get in to look for the activity (except for entries that the system perceives to be needed by other un-refreshed materialized views).
With respect to the "list=>" option, I think that's just if you want to invoke multiple refreshes and pass in a list of comma-delimited materialized views. There are several parameters you can include if you check the package documentation. At our site, I tend to do a simple call and pass in just the name and let the refresh method default to whatever I specified during the initial creation.
Hope that helps a little or at least sparks some ideas. If none of that seems to fit your case, my next step would be to break out individual DF's and run them independently to validate how DML against each base table is being logged.
Posted: Wed Apr 27, 2011 1:30 am Post subject: Re: Materialized View fast Refresh Problem
This is a pure Oracle question. There are supporting stored procedures/Enterprise Manager screens to figure out what causes a fast refresh to be ignored and a whole set of pages in the Oracle docs and Ask-Tom for a list of things that need to be fulfilled.
Example: API Bulkload bypasses all extra database logic, hence the MView Log is not populated. Columns missing in the MView Log? Can't be used.
On top of that, when your tables are partitioned you can speed up the entire process as well. Sometimes. Primary keys,...
As a guideline, when we build DWHs with Oracle as a target we put two stored procedure calls around the dataflow where we maintain indexes, MViews, MViewLogs. The details and the code for an example of such stored procedures can be found in the sub-pages of this chapter:
The difference between the two
sql('DS_DEV','begin DBMS_MVIEW.REFRESH (list=>\'my_MV\',method=>\'F\'); end;');
sql('DS_DEV','begin DBMS_MVIEW.REFRESH (\'my_MV\',\'F\'); end;');
is, the first uses a parameter-by-name logic, so it does not matter which parameter you specify first as you name the input parameter. You said the "list" parameter should be assigned to the value 'my_MV'.
In the second format you use a positional syntax. So if the REFRESH procedure has "list" as first parameter, then it will get the value 'my_MV'. _________________ Werner Daehn
Product Manager Data Services
“The opinions expressed in this post are my own views and not those of SAP.”
PLEASE SUPPORT THIS: Run one ATL job and tell us the benchmark result. Details to be found here.