This discussion is archived
14 Replies Latest reply: Dec 12, 2012 10:32 PM by sb92075 RSS

suggestion for reporting database

977635 Newbie
Currently Being Moderated
I'm sorry to ask such an open question, but when I queried this, I found too many postings that were not relevant.

I'm running 11.2.0.2 Enterprise Edition on Solaris 10.

Our production database is getting beat up too much by reports and my manager has asked that I look at some 'reporting' database options.
Our requirements are that the reporting database must be real time and in sync with production.
We also do not want to impact production (e.g., use of MVs).

Options that I'm considering, but open to others:

logical standby - can we have this open for reporting while it SQL is being applied?
I think so, but I'm wondering what are the cons of this option.

physical standby - I think I can only open the standby database while having redo applied, but only if we purchase Active DataGuard which is cost prohibitive.

Golden Gate - again, cost prohibitive

MVs would cause too much impact on primary database
  • 1. Re: suggestion for reporting database
    Marcus Rangel Journeyer
    Currently Being Moderated
    Before all that, I would try to optimize these reports. Have you looked into that ?
  • 2. Re: suggestion for reporting database
    Richard Harrison . Expert
    Currently Being Moderated
    Hi,
    The simplest approach is active data guard - but as you say it could get expensive. Logical standby should do what you want but I've only ever heard people say bad things about logical standby (though I've never used it myself so I can't really comment). Streams is a nice solution but is not that straightforward to setup and maintain and could have some impact on your primary (particularly if you don;t have primary/unique keys on your tables as supplemental logging would need to be enabled at a higher level - but that's the same for golden gate). Streams is like the free version of golden gate and in some ways is better (it's free and it's easier to set up using grid than gg is). The nice thing about streams/gg is that you don;t have to take everything and you can do transformations on the data if you want to.

    MV's would be a pain as you can't refresh on commit over a dblink anyway you'd have to fast refresh on a schedule every few seconds so there would be a lag of sorts.

    Cheers,
    Harry
  • 3. Re: suggestion for reporting database
    Brian Bontrager Expert
    Currently Being Moderated
    If in sync real time is the requirement, then RAC is another option. It sounds like cost is a higher-priority requirement than either real-time sync or database performance. If Active Data Guard is not a viable option due to cost, RAC will be even less viable (if you are on Enterprise Edition). With RAC you can connect your application to once instance and your reporting tools to a second instance. They can share data but run on separate hardware, SGA, etc. so poorly written reports have less impact on other users. It's not the most common use case for RAC but it works.

    If you are on Standard Edition, RAC is included (I think with some caveats).

    I've implemented fast-refresh MVs with as little as a 6-minute lag from production. The MV log and refresh impact on the production DB was negligible, but the maintenance effort and refreshing in a way that preserves relational integrity in the reporting database was challenging. Given the modern options I would not do it that way today.
  • 4. Re: suggestion for reporting database
    Carlovski Pro
    Currently Being Moderated
    The simplest solution might be to use the time, money and resources you were going to spend on the reporting database on adding more capacity to the production system.
    However I realise that may not be considered an acceptible solution - people get quite risk averse around this sort of thing - the same reason that any sort of overhead for MV refreshes etc are not approved.
    You may be best by starting with revisiting the requirements. Do you really need the whole database? And does it really have to be real time? Do all the reports need this real time data? Could you run heavy hitting, but less time critical reports on a day/week old copy, and just run the current ones on the live system? It's amazing how often must have requirements go away once you start questioning them (Or putting a cost around meeting them!)

    Carl
  • 5. Re: suggestion for reporting database
    6363 Guru
    Currently Being Moderated
    Carlovski wrote:

    You may be best by starting with revisiting the requirements.
    Or possibly revisiting the problem
    974632 wrote:

    Our production database is getting beat up too much by reports
    I would question what metrics are being used to identify reports as the issue and what is the definition of getting beat up?
  • 6. Re: suggestion for reporting database
    977635 Newbie
    Currently Being Moderated
    Ugh... Yeah, I suppose Rac would be another option, but we are already running a physical standby (for high availability). I would not want to make the system too complicated by also having it a RAC option. Plus, the expense of another server might also make it cost prohibitive. But mainly, I don't want to extra overhead of it being a RAC system too.
  • 7. Re: suggestion for reporting database
    977635 Newbie
    Currently Being Moderated
    Well, I could agree with you, but this decision was made above my level and before I just joined this new team.
    So, the question now is what option makes the most sense to provide the reporting database.
    Apparently they have already been down the path and tested logical standby but feel it was problematic.
    Now that I've joined the team, they are asking me which option makes the most sense.
    I'm not a report writer, nor a developer, so I wouldn't want to tackle upteen hundreds of reports, and I guess there are no simple silver bullets (like optimizer parameters, or using bitmap indexes since the tables are very active OLTP tables).
    They are already using parallel query, and most tables have plenty of indexes, and yes, all statistics are fresh and up to date. (Also no invalid objects.)
    My objective at this point is to answer their question that they already decided to look at a report database option and what option makes the best sense.
  • 8. Re: suggestion for reporting database
    sb92075 Guru
    Currently Being Moderated
    Options that I'm considering, but open to others:
    RAC
  • 9. Re: suggestion for reporting database
    EdStevens Guru
    Currently Being Moderated
    974632 wrote:
    Well, I could agree with you, but this decision was made above my level and before I just joined this new team.
    So, the question now is what option makes the most sense to provide the reporting database.
    Apparently they have already been down the path and tested logical standby but feel it was problematic.
    Now that I've joined the team, they are asking me which option makes the most sense.
    I'm not a report writer, nor a developer, so I wouldn't want to tackle upteen hundreds of reports, and I guess there are no simple silver bullets (like optimizer parameters, or using bitmap indexes since the tables are very active OLTP tables).
    They are already using parallel query, and most tables have plenty of indexes, and yes, all statistics are fresh and up to date. (Also no invalid objects.)
    My objective at this point is to answer their question that they already decided to look at a report database option and what option makes the best sense.
    I'd say the first thing that makes the most sense is to question the requirement for 'real time' updates on a reporting database. After all, what good is 'real time' when the data becomes stale even as the report is being generated? It's amazing how often a PHB has a knee-jerk reaction that all data must be "real-time" without thinking about the implications. I find that once you really try to pin them down - explaining how, if they cut it too finely, the report is obsolete before it even completes -- "real time" becomes, "well, current as of the last n-umber of hours."

    Edited by: EdStevens on Dec 10, 2012 11:42 AM
  • 10. Re: suggestion for reporting database
    jgarry Guru
    Currently Being Moderated
    I just went through one of these exercises. The upshot was someone wants a dashboard type screen showing what's happening right now all over the factory. Actually sounds like a fun and challenging project.
  • 11. Re: suggestion for reporting database
    6363 Guru
    Currently Being Moderated
    974632 wrote:

    Well, I could agree with you, but this decision was made above my level and before I just joined this new team. So, the question now is what option makes the most sense to provide the reporting database.
    What do you mean by sense other than it needs to be fast, cheap and reliable, because in reality you can only choose two of the three.
    Apparently they have already been down the path and tested logical standby but feel it was problematic.
    What were the problems?
    Now that I've joined the team, they are asking me which option makes the most sense.
    The appropriate response would depend on what the underlying problem is. Whether you were a part of that investigation or not, you need to understand the problem.
    They are already using parallel query,
    That could be contributing to the problem
    My objective at this point is to answer their question that they already decided to look at a report database option and what option makes the best sense.
    With the level of detail that you have shared and say that you have, you don't have enough information to meet that objective.

    If there was a simple solution that is fast, cheap, reliable, consumes no resources and is easy to implement, a simple google search would find the result and this discussion would not be necessary
  • 12. Re: suggestion for reporting database
    EdStevens Guru
    Currently Being Moderated
    jgarry wrote:
    I just went through one of these exercises. The upshot was someone wants a dashboard type screen showing what's happening right now all over the factory. Actually sounds like a fun and challenging project.
    Joel,


    I'm not sure that type of app would need a separate "reporting" database. Those kinds of things are usually pretty non-intrusive. When I think "reporting" I think of some fairly heavy read activity - possibly with a fair amount of number crunching - of large swaths of the DB. Without knowing more, I'd call what you just described as 'monitoring'. Had an app that provided that basic functionality (though without a flashy "dashboard") for an auto assembly plant. And one of the plant floor supervisors seemed to think his job was defined by continually pressing the 'enter' key to get up-to-the-SECOND counts. Up to the second was probably a legit requirement for the app, but no one anticipated someone would just sit there and stare at the numbers as they clicked by. A bit like a Sesame Street character .... ;-)

    That really gets back to demands made by PHBs who don't think through what they are asking for ("real time reporting") and get their IT knowledge from an article in American Airlines in-flight magazine (a separate "reporting database" is by definition A Good Thing). Which is why I continually preach that DBAs should be willing to push back (educate) against certain "requirements" and not just roll over for everything a PHB asks for. We've all observed people on this forum who never question a requirement. But then I know I'm preaching to the choir at the moment.
  • 13. Re: suggestion for reporting database
    andrewmy Journeyer
    Currently Being Moderated
    PHB
    Took awhile to figure that out then I remembered Dilbert...
  • 14. Re: suggestion for reporting database
    sb92075 Guru
    Currently Being Moderated
    andrewmy wrote:
    PHB
    Took awhile to figure that out then I remembered Dilbert...
    http://search.dilbert.com/comic/Mauve

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points