1 2 Previous Next 21 Replies Latest reply: Aug 7, 2013 2:41 AM by Hemant K Chitale RSS

    Does it help performance?

    yxes2013

      Hi all,

       

      11.2.0.1

      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,

       

      zxy

        • 1. Re: Does it help performance?
          JustinCave

          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.

           

          Justin

          • 2. Re: Does it help performance?
            Alvaro

            Unlikely.

             

            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.

             

            Regards,

            Álvaro.

            • 3. Re: Does it help performance?
              Hemant K Chitale

              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


              • 4. Re: Does it help performance?
                yxes2013

                Wow you are all good Thanks

                 

                How were you able to master these Oracle Internals?

                • 5. Re: Does it help performance?
                  JustinCave

                  yxes2013 wrote:

                   

                  How were you able to master these Oracle Internals?

                  We read the documentation.  And then we read more documentation.  And books.

                   

                  And, to a lesser extent, we tried things out and worked through the issues.

                   

                  Justin

                  • 6. Re: Does it help performance?
                    yxes2013

                    >We read the documentation.  And then we read more documentation.  And books.

                     

                    Was there a time where you got bored of reading a lot?

                    • 7. Re: Does it help performance?
                      sb92075

                      yxes2013 wrote:

                       

                      >We read the documentation.  And then we read more documentation.  And books.

                       

                      Was there a time where you got bored of reading a lot?

                      we are bored reading your lazy & inane documentation questions!

                      • 8. Re: Does it help performance?
                        JustinCave

                        Sure.  That's when you try things out.  Or take a break and go for a walk.

                         

                        Justin

                        • 9. Re: Does it help performance?
                          yxes2013

                          Well for me...after I read 1 page, my eyes get sleepy then turn to fb and chat so I would stay awake.

                          • 10. Re: Does it help performance?
                            JustinCave

                            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.

                             

                            Justin

                            • 11. Re: Does it help performance?
                              sb92075

                              1+

                              • 12. Re: Does it help performance?
                                yxes2013

                                Thanks Justin,

                                 

                                Another situation is what if I do not create Materialized view on EMP,  but a CTAS of it?

                                How will it differ in performance? So I will create another table EMP2 as select * from EMP;

                                And direct all the queries towards it?

                                 

                                Thanks

                                • 13. Re: Does it help performance?
                                  Hemant K Chitale

                                  Unless you create your new table on a tablespace whose datafiles are on SSD  or defined Flash Cache ------ there is no upside or benefit from such a strategy.

                                   

                                  How will you keep EMP2 updated with all new rows / updates / deletes against EMP ?

                                   

                                   

                                  Hemant  K Chitale

                                  • 14. Re: Does it help performance?
                                    Girish Sharma

                                    yxes2013 wrote:

                                     

                                    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!

                                     

                                    Regards

                                    Girish Sharma

                                    1 2 Previous Next