Couple of my last posts were solely based on oracle profiles and baselines.
This time, I thought of putting some stuff related to Mviews.
There are various blogs which explains the working on Mviews refresh and usage of SYS.SNAP$ and SYS.MLOG$.
Before proceeding further, I would like to echo, that you should have basic understanding on mviews and fast refreshes.
Coming back to the main topic,
1. what is the difference between a snapshot and a mview.
2. Are these same, and If yes, why they coexisted.
3. If not, then what is the difference.
Snapshot & Mviews
The term snapshots and mviews are used interchangeably. Various blogs will report that these are the same things with no difference.
If there is no difference, then why do we have dba_snapshots and dba_mviews in the database.
And if both these mviews co-exists , just for “backward compatibility”, then why their definitions are different. Why not just a synonym.
I stumble on these questions during one of my production issue.
As I dig further,I found that there is a difference, which in Newton’s language should be called as “Frame of Reference”.
SNAPSHOTS
Consider a situation, where multiple snapshot sites are pulling data from one master site.
Think if you are sitting right at top of the box and can see the data flowing downstream to the snapshot sites.
So, the master site is your frame of reference and you use the term snapshot.
MVIEWS
Imagine you are sitting at the snapshot site and you can see the data flowing into the site from master sites.
Your frame of refernce is snapshot site and you use the term mview.
What does it means to oracle
Lets consider a real time example.
Imagine if two different snapshot sites are performing fast refresh from a master site.
The Mlog at the master site can not purge the data, unless all the snapshot sites have refreshed.
It reads OLDEST_PK and YOUNGEST from sys.mlog$ to decide which data it can purge.
select OLDEST_PK,SYSDATE,YOUNGEST from sys.mlog$ where master='TEST'; OLDEST_PK SYSDATE YOUNGEST -------------------- -------------------- -------------------- 14-apr-2012 11:55:01 14-apr-2012 12:00:50 14-apr-2012 12:00:42
As we can see, it can safely purge data before 14-apr-2012 11:55:01.
Lets make the situation complex.
What if these two databases are running on ten different countries in ten different timezones.
What will happen if Master site happens to be in US (UTC timezone) and one client site in China(CST-8) and other in US(PST8PDT).
As we know, China’s time is ahead of US.
For E.g
Lets say, currently at 4pm china time (1am US time), CHINA1 gets refreshed from master site.
After that, another snapshot site US2 gets refreshed.
In case Master site uses Snapshot Sites local sysdate to record when they got last refresh, then CHINA2 will report 4PM which is ahead of sysdate of master site.
Clearly, this can’t be the case. SYSDATE has to be ahead of LAST_REFRESH.
Therefore, whenever a site gets refreshed, master updates oldest_pk and youngest with local sysdate(timezone).
Or in short, SYS.mlog$ will report time from master site timezone.
Now, lets say, you logged into China database.
You wanted to check last_refresh date of the mview.
Now, in this case if oracle uses Master Site date, then it will show you that mview got refreshed 13hours before. This is not correct.
Therefore Oracle created 2 Views
1. DBA_SNAPSHOTS which gets last_refresh from sys.snap_reftime$ which reports date of master site.
2. DBA_MVIEWS which gets current_snapshot from sys.sum$ which reports sysdate from local database.
Therefore, if you are at master site and you wanted to check which mview got refreshed at what time, you should use dba_snapshpot_logs (sys.slog$) which shows last_refresh with respect to master site.
In case, you are at snapshot site, you should use dba_mviews , which will show you last_refresh with respect to local sysdate.
TestCase
Master site timezone (UTC)
Snapshot Site (CST-8)
MASTER>>select sysdate from dual; SYSDATE -------------------- 14-apr-2012 04:36:01 CLIENT>>select sysdate from dual; SYSDATE -------------------- 14-apr-2012 12:36:20 MASTER>>select site.SNAPSHOT_SITE,logs.CURRENT_SNAPSHOTS,sysdate from dba_registered_snapshots site,dba_snapshot_logs logs where site.snapshot_id=logs.snapshot_id and MASTER='TEST' ORDER BY 3 DESC; SNAPSHOT_SITE CURRENT_SNAPSHOTS SYSDATE -------------------- ------------------- -------------------- CHINA1 14-apr-2012 04:36:01 14-apr-2012 04:41:33 US1 14-apr-2012 04:36:21 14-apr-2012 04:41:33
Lets check the behavior on snapshot site, first using dba_snapshot view which should report last_refresh wrt master_site
CHINA1>>select name,sysdate,last_refresh from dba_snapshots where name='TEST';
NAME SYSDATE LAST_REFRESH
------------------- -------------------- --------------------
TEST 14-apr-2012 12:38:34 14-apr-2012 04:36:01
Now, if we query the same thing from dba_mviews this should report wrt local timezone.
CHINA1>>select last_refresh_date,sysdate from dba_mviews where mview_name='TEST'; LAST_REFRESH_DATE SYSDATE -------------------- -------------------- 14-apr-2012 12:36:02 14-apr-2012 12:39:59
In the nutshell
sys.slog$ and sys.snap_reftime$ shows last_refreshed_date as per master_site timezone and therefore dba_snapshots and dba_snapshot_logs reports time as per master_site sysdate.
sys.sum$ updates current_snapshot as per snapshot site time and therefore dba_mviews reports last_refresh_date as of snapshot site time.
For DBA 