1 2 Previous Next 16 Replies Latest reply: May 22, 2012 6:17 AM by Girish Sharma RSS

    Triggers or Materialized views

    user13005731
      Hi,

      I'm on 11.2.0.1. We have a production system (an OLTP). There's a vendor who's developing a reporting application to pull data from the prod system and produce reports in near
      real-time as much as possible.

      The reporting server is hosted separately with it's own database. The vendor is proposing to put triggers on some of the online prod database, and have these triggers insert the data on the reporting server over a database link. I'm very reluctant to accept this proposal, first because it looks very inefficient and secondly because if the reporting system is down, any inserts on the prod tables that have triggers will fail as well, since it will be one transaction that succeeds in whole or fails in whole.


      I'm proposing the use of materialized view, but his objection is that this will not be as near real-time as they want. Any suggestions?

      dula
        • 1. Re: Triggers or Materialized views
          Dom Brooks
          You're absolutely right to object to the trigger approach.

          Not only does this put an overhead on likely each and every transaction (or at least each transaction that is relevant to entities being reported on) but it creates a dependency between production and this reporting instance, etc, etc.

          It's just a very bad idea.

          There are all sorts of better alternatives from Streams, asynchronous CDC, or Goldengate to reporting off a standby, etc.
          • 2. Re: Triggers or Materialized views
            JustinCave
            While seconding Dom's comments, I'd also ask why the reporting application needs the data to be lagging less than a couple minutes behind production which is what you'd get with materialized views. It's possible that they really are building a reporting system that will generate actionable alerts where even a minute or two of delay in generating the alert is costly to the business. But that sort of thing is pretty rare in the real world. And most of the time that the alerts really are that critical, you want the alerting to be driven from the source system so that the alerts aren't dependent on replication at all.

            On the other hand, if there is no real business benefit to having data up to the second-- if the reports are just going to sit in an email inbox for a while until someone reviews them and the alerts let people know that something has to be done soon but not necessarily right this moment-- you'll probably save everyone some grief (now and down the line) by making sure the requirements (and expectations) reflect a reasonable delay rather than trying to guarantee real-time data replication.

            Justin
            • 3. Re: Triggers or Materialized views
              anand prakash - oracle
              Hi,

              As you are on 11gR2, and if you have oracle active data guard license, you can use real-time query feature for reporting purpose from physical standby database which would be easier method.

              http://docs.oracle.com/cd/E11882_01/server.112/e25608/manage_ps.htm#CHDFFFAJ

              Anand
              • 4. Re: Triggers or Materialized views
                rp0428
                I would more forcefully reiterate what others have said: under NO circumstances should you use triggers on your DB to move data to a remote DB. Anything a trigger does might be rolled back and wind up not happening at all. Any data sent to another DB should be only committed data.

                Since that aspect has already been addressed I would comment on this
                >
                I'm proposing the use of materialized view, but his objection is that this will not be as near real-time as they want.
                >
                The vendor can't possibly know if the use of MVs will be as near real-time as they want.

                This would require fast refresh ON COMMIT views and until the actual data requirements are finalized not even you can know if your tables and data requirements will support the use of fast refresh MVs for all of the data that might be required.

                If your only options were a 'manual' approach (rather than data guard or others) then using MVs would be a good way to go because 1) you do not introduce a dependency between the two systems and 2) it used PULL rather than PUSH which gives you maximum control over the data and the updating.
                • 5. Re: Triggers or Materialized views
                  JustinCave
                  rp0428 wrote:
                  This would require fast refresh ON COMMIT views and until the actual data requirements are finalized not even you can know if your tables and data requirements will support the use of fast refresh MVs for all of the data that might be required.
                  Since the reporting database is a separate database, ON COMMIT materialized views are not an option. ON COMMIT materialized views are only an option if the materialized view is on the same database as the source tables.

                  Justin
                  • 6. Re: Triggers or Materialized views
                    rp0428
                    >
                    Since the reporting database is a separate database, ON COMMIT materialized views are not an option. ON COMMIT materialized views are only an option if the materialized view is on the same database as the source tables.
                    >
                    Maybe I wasn't clear.

                    The MVs ARE on the source system. The other database accesses those MVs, pulling the data, over DB links.
                    • 7. Re: Triggers or Materialized views
                      Girish Sharma
                      Justin,

                      But I am bit confused that why OP needs a seperate reporting database when Oracle don't block reader vs writers ? What is that technical enhancement they will achieve just for "SELECT..." / reporting purpose.

                      Can't they just use real/production system for reporting purpose, because Oracle just maintaine read consistency with UNDO/REDO and there is no blocking for reader.

                      Kindly reply by your valuable inputs please, probably I am missing something else.

                      Regards
                      Girish Sharma
                      • 8. Re: Triggers or Materialized views
                        Hemant K Chitale
                        why OP needs a seperate reporting database when Oracle don't block reader vs writers ?
                        To offload query processing. Queries and Reports can generate a lot of I/O and consume CPU cycles.


                        Hemant K Chitale
                        • 9. Re: Triggers or Materialized views
                          Girish Sharma
                          Hemant K Chitale wrote:
                          why OP needs a seperate reporting database when Oracle don't block reader vs writers ?
                          To offload query processing. Queries and Reports can generate a lot of I/O and consume CPU cycles.
                          Thank you for your reply. But does it makes much senses : If I says :
                          select * from mytable <----Production db
                          or
                          select * from mytable <----reporting db

                          Because for the same server it is the same IO and CPU cycle whether it is for Production or for reporting db. The only difference that for the reporting db, there will not writers, but there may be same or more no. of readers. So, for just minus IO and CPU cycles for writers; one should go for another db, is just seems me less productive.

                          But, when I re-read OP's question, he/she is talking about "reporting server", so it seems that separate server will be there and then yes, your point worth.

                          But, still how much efficient SQLs have been written, application design etc. removes necessity of separate reporting server and db.

                          Thanks for pointing it out.

                          Regards
                          Girish Sharma
                          • 10. Re: Triggers or Materialized views
                            Hemant K Chitale
                            The reporting server is hosted separately with it's own database.
                            Hemant K Chitale
                            • 11. Re: Triggers or Materialized views
                              Girish Sharma
                              Just part of discussion, this is almost same scenario which was my SQL server dba friend; he told me that we are planning to have separate server and db for one of our application, because when we runs that application (only select statement, running procedures and views etc.); server's IO graph and RAM consumption touches its height and then there seems us almost all lock/hang. I told him that, I don't think that you would be able to achieve good performance even if you have separate server and db, because problem may be in bad sql, corrupted indexes, bad/wrong statistics, wrong datatypes, unnecessary column selection, opened cursors etc. I am not saying that the same scene applies for OP too, but just part of discussion / attack on the problem causes please.

                              From this forum and specially from Billy, I have learnt that before doing "how", I must think for "why". If I have answers for all "whys" then there are answer for "hows" for sure.

                              Regards
                              Girish Sharma
                              • 12. Re: Triggers or Materialized views
                                user13005731
                                Thanks a lot everyone! We had a discussion and the DBAs expressed their concerns. We seem to come to an agreement to use Streams, with synchronous replication.


                                The only catch now is that their reporting database is on MySQL. Can heterogeneous replication be done to MySQL?


                                regards,
                                dula
                                • 13. Re: Triggers or Materialized views
                                  user13005731
                                  The issue is turning out to be more complicated than we thought. Even though Streams are possible, it looks like MVs would be the better option in this case. Anyone who has done MV between Oracle and MySQL, please share the hints/info?

                                  regards
                                  • 14. Re: Triggers or Materialized views
                                    Hemant K Chitale
                                    between Oracle and MySQL,
                                    You should look at GoldenGate.

                                    MVs, by definition, are between Oracle Databases. Remember that you reporting environment needs to create the "MV" that queries the source.

                                    Why MySQL for the reporting environment ?


                                    Hemant K Chitale
                                    1 2 Previous Next