Problem with Materialized View
A reader, June 27, 2002 - 11:16 am UTC
Tom,
I have created a materialized view on 4 million rows. I takes about 4 minutes to create partitioned MV. But when I do the update on the original table, it takes more than 35 minutes to refresh the materialized view. I used this with or without materialized log. Refresh timings are not coming down. And this not acceptable. How to solve this? Also How to refresh MV for a partition rather than whole table.
Create table t (
col1 varchar2(10),
col2 number,
col3 varchar2(20)
);
create materialized view log on t using rowid(col1,col2);
create materialzed view t_mv
partition by list (col1) (
partition part1 values ('NY','NJ') tablespace tblspc1,
partition part2 values ('TX','CA') tablespace tblspc2
)
parallel build immediate refresh fast on commit enable query rewrite
as select col1,col2,count(*) from t
group by col1,col2;
-- Creation of MV with complete build takes about 4 minutes on 4.5 million rows.
insert into t select * from t where rownum < 50001;
commit;
-- This commit takes about 35 minutes to refresh the MV.
Your response will be highly appreciated.
Thanks
June 27, 2002 - 12:02 pm UTC
well, that is a huge update don't you think, for an on commit refresh.
Bascally, the 50000 row insert is turned into 50,000 "mini refreshes". It is refreshing about 24 rows/second there (lots of code to go through). So, your "normal" user won't see this affect -- but a bulk modification most certainly will (it is like adding an index, update a single row of an indexed column -- no problem. Update 50,000 of them -- and wait).
If you plan on bulk modifications like this, then the more appropriate refresh mechansism is COMPLETE, not incremental.
You refresh an MV, not a partition within an MV.
A reader, August 28, 2002 - 5:15 pm UTC
Hi Tom,
I hope you can help me to understand why the performance is so bad when update one of the tables that is used by my mv.
There a couple of reports that need to be run on our OLTP application because they need to have the last information.
So I though that using a materialized view would be a good choice. So I decided to make some tests on our test database in order to see the behaivor of this alternative.
I created a mv over four big tables (2 of 2gigabytes and the others two over 200 megabytes), I created the log for the four tables and also I created four indexes on the mv using the rowid column that I used for each of those tables. The problem is that the performance for the updates or inserts on the tables on which the mv is based on is very poor. It took me 2 minutes to commit a simple "insert into ..values.." of one row in one of these tables. During the commit I checked the waits, and the only thing that I saw was db file scattered read waits for that session. So I checked that session in the top session and I was able to see that the insert on the mv was performing a full table scan over one of the table which has a size of 2 gigabytes. When I created the mv I used hints in order to achieve the best access path for the query and the explain plan for that code doesn't show a full scan for that table, but the insert on the mv is not doing a nested loop for that table when it is supposed to be doing so.
Do you know what this is happening?
Can you tell me what to do in order to avoid the full scan that insert on the mv is doing?
This is the code of the mv.
create materialized view mv_test
build immediate
refresh fast on commit
as
select /*+ ORDERED use_hash(AD) USE_NL(SM A2) */
ad.rowid ad_rid,a.rowid a_rid,sm.rowid sm_rid,a2.rowid a2_rid,
SM.account_id, AD.phone, A2.stage_cancel
from table_1 A, --200 MG
table_2 AD, --200 MG
table_3 SM, --2G
table_4 A2 --2G
where AD.ACCOUNT_ID = A.ACCOUNT_ID
and SM.ACCOUNT_ID = AD.ACCOUNT_ID
and A2.ACCOUNT_ID = SM.ACCOUNT_ID
This is the explain plan for the query of the mv
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 NESTED LOOPS
2 1 NESTED LOOPS
3 2 HASH JOIN
4 3 TABLE ACCESS (FULL) OF 'TABLE_1'
5 3 TABLE ACCESS (FULL) OF 'TABLE_2'
6 2 TABLE ACCESS (BY INDEX ROWID) OF 'TABLE_3'
7 6 INDEX (UNIQUE SCAN) OF 'U_ACCOUNTID' (UNIQUE)
8 1 TABLE ACCESS (BY INDEX ROWID) OF 'TABLE_4'
9 8 INDEX (RANGE SCAN) OF 'I_ACCOUNT_ID'(NON-UNIQUE)
HERE YOU have the insert statement that I run:
insert into TABLE_1
values (2363886,64,'8010000012','JEREMY','LARSEN',null,null,null,null,88,88,null,null,6,52,sysdate,
null,null,null,null,null,null,null,null,null,null,52,null) ;
1 row created.
Elapsed: 00:00:00.20
SQL>commit;
Commit complete.
Elapsed: 00:01:98.99
HERE you have the explain plan that the insert on the mv is doing
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 HASH JOIN
2 1 TABLE ACCESS (FULL) OF 'TABLE_2'
3 1 HASH JOIN
4 3 NESTED LOOPS
5 4 VIEW
6 5 NESTED LOOPS
7 6 VIEW OF 'VW_NSO_1'
8 7 SORT (UNIQUE)
9 8 FILTER
10 9 TABLE ACCESS (FULL) OF 'MLOG$_TABLE_2'
11 6 TABLE ACCESS (BY USER ROWID) OF 'TABLE_1'
12 4 TABLE ACCESS (BY INDEX ROWID) OF 'TABLE_3'
13 12 INDEX (UNIQUE SCAN) OF 'U_ACCOUNTID' (UNIQUE)
14 3 TABLE ACCESS (FULL) OF 'TABLE_4'
Thanks in advance for any help.
August 29, 2002 - 8:54 am UTC
so run a sql_trace and TKPROF on the whole thing and see what is really happening.
A reader, January 06, 2004 - 3:47 am UTC
hi tom,
I have a simple question here. I am using 8.1.5. Now there is a refresh group which is used for refreshing the materialied view. This will refresh every 15 minutes over a DB link.
now if the network is down while a refresh takes place, will next refresh happen without any problem if the network is restored before the next refresh and will it refresh the data which was supposed to be refreshed in the previous (last and lost) refresh.
thanks
January 06, 2004 - 8:38 am UTC
if the network isn't available, the job will increment "failures" and try it again in a while (using a "back off" algorithm timewise -it'll try soon, if still failed, try again -- but not as soon and so on). it'll do this 16 times.
If after 16 times, it is still "failing", the job will be marked as BROKEN and you'll have to "unbreak" it to get it going again.
A reader, January 06, 2004 - 10:59 pm UTC
Hi Tom,
letme tell you the scenario and the solution proposed for this.
We have a database say A, where in an application would add/modify/delete data. We have another read only database, say B, which has to be in sync with this database with a maximum delay of 1 hour.
So what I have proposed is that have REFRESH FAST ON DEMAND materialized views in B from A. Now refresh is done using DBMS_MVIEW.REFRESH which is in a shell script and this will be scheduled using crontab.
My question is when this refresh is being done, if somethin happens to the DB link used for refresh, crontab will fail. next time (say after 1 hour) when the crontab is trying to refrehs again will it refresh the data which was left unrefreshed last time ?
One more question I have is what will happen to dependent transactions. Say for e.g. I have a table t1 and t2 such t2 references t1. When the refresh started there were only 100 transactions in t1 and t2, after the refresh has been completed for t1, 2 more transactions get added into these tables and hence when the refresh starts for t2 we have 202 transactions, so wouldn't that refresh for t2 create mismatch transactions as we don't have foreign key relations between the materialized views.
One last question (sorry for asking multiple questions.) is there any problem in creating tables that reference materialized views.
Thanks
January 07, 2004 - 7:39 am UTC
why would you use cron when the database does the refresh on a timed basis -- with retries during failures and a single place to look for "database stuff" (eg: don't have to monitor alert logs and crontab logs -- just alerts).
don't use cron, use the start with / interval on the MV.
the refresh operation is designed to do the right thing -- if you group t1 and t2 into a single refresh group, they are refrehed transactionally consistent.
if you use DEFERRABLE constraints when referencing a MV, it should be OK (technically) as the MV refresh process defers all constraints at the start. HOWEVER, you now have a table that can kill the refresh due to a fkey constraint. That is, you have T3 pointing to T2 and T2 is a MV. On the master site, someone deletes from T2 a primary key that T3 has. That succeeds (t3 isn't on the master site). Now T2 cannot refresh anymore. So, while it would work -- it would be an excessively "fragile" design.
Weird refresh
A reader, June 22, 2004 - 6:06 pm UTC
Hi Tom,
I hope you can help to understand the following I found out while I was running test on a snapshot.
I have a snapshot created on database b and the master is on database A. I inserted 500,000 rows in the master table, and when the snapshot was being refreshed I was able to see update statements running on the snapshot even when I only had inserts statements to be refreshed (I checked this in the snapshot log table in database A, there were only inserts and as this snapshot is part of a test I am the only one generating modification in the master table). The snapshot on database b does not have any trigger of foreign key on it or referencing it.
Do you know why these updates are taking place?
The update statement I saw was updating all the columns for the snapshot using the primary key to access the snapshot. As this is a test nobody has access to the master table or the snapshot but me.
Thanks a lot for any help on this.
June 22, 2004 - 10:23 pm UTC
a snapshot refresh is a piece of code, also known as "a black box"
it does what it does.
it does what is needs to.
it uses whatever code path it wants to.
why is this "a problem". if the refresh ends up with the right data - that is about all any of us can expect?
under what situation is complete used when force is assigned
A reader, October 03, 2007 - 3:35 pm UTC
Hi
When is a MVIEW refreshed using complete method when it is defined using FORCE?
For example I have a few replicated tables using MVIEWs (simple SELECT * FROM MASTER) and I assigned FORCE to refresh but sometines I see last_refresh_type complete is used.