Home » Server Options » Replication » Materialized View Fast Refreshes are Slow
Materialized View Fast Refreshes are Slow [message #410540] Sun, 28 June 2009 05:39 Go to next message
behi
Messages: 12
Registered: June 2009
Junior Member
Hi all,

I created a Materialized view using joining of two tables with FAST REFRESH ON COMMIT option. These tables are the main tables of the DB and contains alot of data(about 15 millions of records). two master tables have index but mview doesn't have any indexes. I've thought the entire concept of the fast refresh is that it should be a relatively quick operation but the result showed that DB performance reduced, for example I could insert 900 records per minute thru application into master tables before but now I can just insert about 400 records per hour!.Also, It slows down the select operation, went from 0.11 seconds(with joining two tables) to 33 seconds (in mview)!.

First of all, I considered following possibilities and test them:

1) The most likely solution was that a complete refresh was happening. However, the materialized view refresh was confirmed to be a fast refresh by querying USER_MVIEWS.

2) I checked the query of the materialized view and confirmed that it was a simple select from the master tables without any aggregation or sorting.

3) DBMS_MVIEW.explain_mview was run on the materialized view. MV_CAPABILITIES_TABLE did not reveal any problems.

4) A query of dba_registered_snapshots showed that no other snapshots appeared to be using these logs. So, After each refresh, the records in mview logs were deleting.

As you see, it seems everything is ok, but what was causing this fast refresh to go so slow?

Meanwhile, I use oracle 10.2 on Suse Linux enterprise server.

Many Thanks
Re: Materialized View Fast Refreshes are Slow [message #411203 is a reply to message #410540] Thu, 02 July 2009 07:41 Go to previous messageGo to next message
babuknb
Messages: 1734
Registered: December 2005
Location: NJ
Senior Member


>> created a Materialized view using joining of two tables with FAST REFRESH ON COMMIT option.

Ok but you have created complex materialized view so your refresh type should be "Complete" NOT "Fast"

>> two master tables have index but mview doesn't have any indexes.

Why you don't have proper indexes?

Thanks
Re: Materialized View Fast Refreshes are Slow [message #411451 is a reply to message #411203] Fri, 03 July 2009 22:00 Go to previous messageGo to next message
behi
Messages: 12
Registered: June 2009
Junior Member
This is my mview. do you think it's a complex mview yet?

create materialized view mview_test
tablespace test_mview
refresh fast on commit
as
select e.id,e.name,e.family,e.rowid as emp_rowid, d.dept_id, d.dept_name,d.dept_location,d.rowid as dept_rowid
from employee e, dept d
where e.dept_id=d.dept_id

about indexes, because number of insert operations in master tables are alot ( for example 6 thousands during a day), so I think creating index for mview increases its refresh time. isn't it correct?

Thanks
Re: Materialized View Fast Refreshes are Slow [message #411461 is a reply to message #411203] Sat, 04 July 2009 01:47 Go to previous message
babuknb
Messages: 1734
Registered: December 2005
Location: NJ
Senior Member

>>This is my mview. do you think it's a complex mview yet?

Refer: http://download.oracle.com/docs/cd/B10501_01/server.920/a96567/repmview.htm#25391

Quote:
Joins other than those in a subquery

For example, the following statement creates a complex materialized view:

CREATE MATERIALIZED VIEW hr.emp_join_dep AS
SELECT last_name
FROM hr.employees@orc1.world e, hr.departments@orc1.world d
WHERE e.department_id = d.department_id;


>bout indexes, because number of insert operations in master tables are alot ( for example 6 thousands during a day), so I think creating index for mview increases its refresh time. isn't it correct?

Yes. It's correct only; What do you think ??

Thanks
Previous Topic: Archive generation from Linux to Windows
Next Topic: Gather Statistics on Materialized views
Goto Forum:
  


Current Time: Thu Dec 08 04:04:18 CST 2016

Total time taken to generate the page: 0.07267 seconds