Home » RDBMS Server » Performance Tuning » Relate question to MViews (Oracle 10g, cross platform)
Relate question to MViews [message #377847] Thu, 25 December 2008 22:28 Go to next message
trantuananh24hg
Messages: 668
Registered: January 2007
Location: Ha Noi, Viet Nam
Senior Member
Hi all!

We're designing MViews. The thing I'm consider which demonstrate from this example

In the master_site with master_tables are DEPARTMENTS and EMPLOYEES
hr@METADWH> create materialized view log on departments;

Materialized view log created.

hr@METADWH> create materialized view log on employees;

Materialized view log created.

hr@METADWH>


In the DB contains MViews
metatest@META> create materialized view HR_MVIEW_METADWH
  2  refresh force start with sysdate
  3  next sysdate+3/(24*60)
  4  as
  5  select E.employee_id ID, E.last_name NAME, sum(E.salary) TOTAL_SAL,
  6  D.department_name
  7  from employees@metadwh_hr E, departments@metadwh_hr D
  8  where E.department_id=D.department_id
  9  group by E.employee_id, E.last_name, D.department_name
 10  /

Materialized view created.

metatest@META>


Question 1: In the master_site, I created 2 MView logs. As I understand, the MView log will captures anything change from master_tables (Departments & Employees), sends it (rows) to the MView (HR_MVIEW_METADWH) in MView_site.
1.1 Am I wrong or correct?
1.2 Will the MView Log do anything to impact the Master_table_Machine's CPU whenever rescan? If yes, the operation cause poor performance?

Question 2: When I have got a select statement such like
metatest@META> select * from hr_mview_metadwh;

  ID NAME             TOTAL_SAL DEPARTMENT_NAME
---- --------------- ---------- --------------------
 119 Colmenares            2500 Purchasing
 115 Khoo                  3100 Purchasing
 118 Himuro                2600 Purchasing
 199 Grant                 2600 Shipping
....


Which the CPU make more actions? In master_table_site or master_mview_site?

As I know that, the MViews is the physical object, it contains real_data, locates in real physical datafile, sothat, whenever clients retrieve data from MViews, the operation is not like retrieve data from db_links. Due to this, whenever client retrieve data from MViews, the MView_site's CPU really work more!

Am I wrong?

Thank you very much!

[Updated on: Thu, 25 December 2008 22:31]

Report message to a moderator

Re: Relate question to MViews [message #377848 is a reply to message #377847] Thu, 25 December 2008 22:37 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
Every insert, update and delete on the master table is logged to the mv log in real time, this can have an impact on the performance of those inserts etc, especially noticeable in those that ins/upd/del a lot of rows.

When you refresh a MV, it will pull data from the master table, thereby imposing another performance impact.

Wit regards to your last question, when you query a MV, it is just like querying a table (in fact, it IS a table). It does not affect the source table or source DB at all.

Ross Leishman
Re: Relate question to MViews [message #377853 is a reply to message #377848] Thu, 25 December 2008 23:09 Go to previous messageGo to next message
trantuananh24hg
Messages: 668
Registered: January 2007
Location: Ha Noi, Viet Nam
Senior Member
rleishman wrote on Fri, 26 December 2008 11:37
Every insert, update and delete on the master table is logged to the mv log in real time, this can have an impact on the performance of those inserts etc, especially noticeable in those that ins/upd/del a lot of rows.


Yes, I know that. However, I do not care about DML to the master_tables, because of period time which I monitored them, I understand the DML from client's requests did not make poor performance. So that, any operations such as Insert/Update/Delete are in my control.

Only select statements from them made CPU in OLTP DB increasing to 90-100% busy time, because all of master tables are 80, 100 million rows, the select statements join to many, many tables. I'm sorry of it, because we must build DWH before we opened this application.

Quote:

When you refresh a MV, it will pull data from the master table, thereby imposing another performance impact.


It's the thing I fear.

The option Refresh fast..next sysdate+3/(24*60) means to 3 minutes refresh one time. Therefore, I must create MView log to capture anything changes from the last change (as I understand), and send the last change from the MView log to MView, instead of rescan all of data from master_tables.

And I hope it will decrease OLTP_DB's CPU busy time.

Quote:

Wit regards to your last question, when you query a MV, it is just like querying a table (in fact, it IS a table). It does not affect the source table or source DB at all.

Ross Leishman


Thank you for confirming the question 2.

At the end, thank you for your reply!

[Updated on: Thu, 25 December 2008 23:11]

Report message to a moderator

Re: Relate question to MViews [message #377855 is a reply to message #377853] Thu, 25 December 2008 23:15 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
Sounds like you should be using Change Data Capture, not Materialised Views.

Ross Leishman
Re: Relate question to MViews [message #377902 is a reply to message #377855] Fri, 26 December 2008 03:09 Go to previous message
trantuananh24hg
Messages: 668
Registered: January 2007
Location: Ha Noi, Viet Nam
Senior Member
Thank you for suggesting!

I'm consider about DWH in the near feature, but MView is now our last chance because of time is not so much!

Thanks again!
Previous Topic: I've got a slowly query
Next Topic: Query Rewrite to Utilize Materialized Views on a Remote Database
Goto Forum:
  


Current Time: Fri Dec 09 04:11:33 CST 2016

Total time taken to generate the page: 0.08051 seconds