| Materialized View Fast Refreshes are Slow [message #410540] |
Sun, 28 June 2009 05:39  |
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 #411451 is a reply to message #411203] |
Fri, 03 July 2009 22:00  |
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
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
