No announcement yet.

Oracle Reporting DB Performance

  • Filter
  • Time
  • Show
Clear All
new posts

  • Oracle Reporting DB Performance

    Hi everyone,

    I have a requirement to generate a relatively complex report via OTM reports. I have two options to try to improve the performance of the reports:

    1) Create materalised views in the OTM DB under another schema owner.
    2) Use 10g DataGuard and run the reports over the 10g DataGuard instance.
    3) Migrate 10g to 11g and use Active DataGuard (but still run OTM

    I can't use ROD because of cost / implementation constraints so that option is out.

    I am in the favour of materalised views. However, I am not certain I will be able to get the performance I require.

    Has anyone been able to do the above and what is the recommended approach?


  • #2
    Re: Oracle Reporting DB Performance


    We've run into similar issues with Reports under 5.5. We are currently using materialized views for some of the operational and historical reports. For historical reports where it runs once a day or less it's fine but for real time operational reports the refresh time of the view and the redo that it creates can be issues. We've looked into the possibility of using a logical standby but there are a few tables that have unsupported datatypes. Regardless I think a logical standby would work if those tables were excluded. I don't know if I would recommend upgrading to 11g as that is not supported under 5.5.



    • #3
      Re: Oracle Reporting DB Performance

      Hi Alan,

      Thanks for the information. It was certainly helpful. I would need to have several operational reports using mviews on the same instance as I can't move to 11g and hence no logical standby.

      In your experience, what are the minefields to avoid when using mviews for operational reports under 5.5.

      Looks like I am between the devil and the deep blue sea - I gotta choose the lesser of the 2 evils

      Your insights would be helpful.



      • #4
        Re: Oracle Reporting DB Performance

        Hi Ianlo,

        I think I would first look at alternatives to using an mview. If for example you determine that VPD is causing a performance issue for your query it is possible to drop the VPD on specific child tables without necessarly affecting security. The shipment_refnum and shipment_status tables are good examples where dropping VPD would probably not affect security because you're not going to logically retrieve data from them without also joining to the parent shipment table. As long as VPD is enforced at the parent level your security will remain intact. I have used this technique to improve several saved queries that were previously coded using mviews and I was then able to eliminate the mview entirely. This technique is explained in Rosenbloom's 2008 OTM SIG presentation "Rosenbloom, Eric - TopDownPerfTuning (Symphony-Tues).pdf" available on this site. I used his instructions to create a PL/SQL procedure to make it easier to quickly drop and enable VPD profiles on specific tables.

        Aside from trying to avoid mviews if you do find that you absolutely need them then I would at least try to minimize the impact on database performance by refreshing them only when absolutely necessary. There are also techniques you can use to mimize the amount of REDO generated if that becomes an issue for you. I found this one among others...

        Ask Tom "Materialized View Refresh"

        Last edited by acuartero; April 19, 2010, 17:15.