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  |
trantuananh24hg
Messages: 744
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  |
rleishman
Messages: 3728
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 |
trantuananh24hg
Messages: 744
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 |
|
|
|
|
|
|
|
Goto Forum:
Current Time: Fri Nov 22 18:36:41 CST 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
