Aix 6.1 5L
We have a big table EMP in schema HR. This table is very busy as lots of programs or queries, select, update, inserts, delete is being done against it by many users.
To relieve stress on this table, I plan to create another table as materialized view EMP_VW on the same schema. I just want divide the workload that is all the queries (select) be directed to the VIEW,
and all the DMLs to the source table. Is this a good idea? Does it help performance dramatically?
Thanks a lot,
It's not going to help. And likely to hurt. What possible reason could you have to believe this might be beneficial?
You now have two copies of the data. So you have two copies of the data in the buffer cache. Assuming you don't have tons of memory lying around that no one is using, that invariably means that you're going to age out a ton of useful blocks in order to store multiple copies of the blocks for this table.
You now have to maintain a materialized view log on the base table. So you now have to do extra work every time a transaction touches the table. And, assuming you're building an ON COMMIT materialized view so that you don't have to deal with data being out of sync, you'll incur the cost of updating the materialized view when you commit your transaction. You could reduce the impact by letting the materialized view lag the base table but now you have issues where someone inserts a row into the base table, queries the materialized view, and doesn't see it immediately.
Plus, you'd then have to go to the effort of modifying code to split the workload and testing all those changes. And, of course, the cost of doubling the storage, maintaining two sets of indexes, etc.
The thing is, as you said it yourself, there is a lot of DML activity on top of SELECTs. You will introduce one more layer witch can become a bottleneck.
Every DML activity on the base tables (lots of it) will have to be propagated to the MVIEW which will cause oracle to use even more resources (cpu, logical I/O, physical I/O) just to maintain it. MVIEWs are good for remote propagation of data or somewhat static data to be preprocessed for queries, I don't it would help you very much on this case.
I think what you should be doing is:
1. Is all the SQL tuned against this table? This should be your first priority.
2. Partition: Have you considered partitioning the table? The goal here is to spread the DML across many partitions as possible to avoid I/O an buffer busy contentions.
3. Is the tablespace on ASSM-enable uniform size tablespace? Both are good ideas for high-DML tables.
4. Have you set INITRANS and PCTFREE accordingly? On high DML segments often the standard values are not enough. INITRANS is to fix ITL waits and PCTFREE can help you reduce chained/migrated rows and also alleviate some of the Buffer Busy contention if you have (table will be spread along more blocks -- beware though it may hurt full table scans)
As always these are just possibilities which should be investigated and adapted to your own enviroment. I think partitioning and SQL tuning are no-brainers in most situations though.
A Materialized View as a copy of a table in the same database isn't helpful.
You might create a MV in a local database that reflects data from a remote database.
You might create an MV that simplifies a complex join.
You might create an MV that summarises (aggregates) data.
You might create an MV that is a subset of existing data (table).
Hemant K Chitale
Well, if you are incapable of reading documentation and you're not determined enough to try things out, you're unlikely to have a successful career as a DBA. You'll be completely unable to learn how to do anything new and completely dependent on others to do your job for you. And the folks around this forum are rapidly going to start ignoring your questions if you continually demonstrate an unwillingness to do any of your own work.
Well for me...after I read 1 page, my eyes get sleepy then turn to fb and chat so I would stay awake.
Your eyes get sleepy because :
1.You read, for just sake of read; increase the count of hours of reading.
2.You don't have eagerness/curiosity to know what and how exactly things works.
3.You don't correlate the topics, which you read, turn on sqlplus, see ORAs.
4.You don't write down whatever you read in your own words/understanding.
Your eyes get awake when you turn to fb and chat because :
1.You thinks that this is a good tonic!
2.You feels, it is interesting.
3.You attracts by bogus chat and other stuff.
I appreciate that you exposed your demerit on the international forum, but I guess, I am trying to give you solution as well.
PS:I am sorry, if it looks some personal comment, but in general it is not. There are many yxes2013 in the world!