Announcement

Collapse
No announcement yet.

Reports Performance

Collapse
X
 
  • Filter
  • Time
  • Show
Clear All
new posts

  • Reports Performance

    Hello

    I have a reports question that I hope someone can answer. If I run a report from the Oracle Reports Developer tool vs the application, the performance is drastically different. For example, I have one report that will run in under 1 minute in the Reports Developer tool. That same rdf with the same parameters when run in the application takes > 5 minutes to come back with results. Any thoughts?

    Thanks
    Shayne

  • #2
    Re: Reports Performance

    This is likely related to VPD performance and you can find some additional information here:

    http://www.otmfaq.com/forums/f21/ins...rformance-513/

    http://www.otmfaq.com/forums/f27/otm...rovements-515/

    --Chris

    Comment


    • #3
      Re: Reports Performance

      I agree with what Chris has said, it is probably due to VPD. You can test this out by running the report as DBA.ADMIN and then a normal user. It should be significantly faster as the DBA.ADMIN user.

      The only suggestion would be to improve the underlying queries in the report.
      If my post was helpful please click on the Thanks! button

      MavenWire Hosting Admin
      15 years of OTM experience

      Comment


      • #4
        Re: Reports Performance

        Appreciate the quick reply!

        I'm fairly new to the VPD concept, but I found a document that states "VPD works by adding a WHERE clause to every table in a SQL statement." I'm guessing that WHERE clause references the "DOMAIN_NAME" column? Just want some background, thanks.

        I tried logging into the application as DBA.ADMIN and the report executes very fast, but returns no data. I'm guessing it's because the DOMAIN_NAME in the GL_USER table doesn't match the records I'm retreiving?

        With that being said...I've checked through the links you sent me (and drilled into some links from there) and don't see a distinct recommendation on how I can resolve this problem. Maybe I missed it, not sure...

        Thanks,
        Shayne

        Comment


        • #5
          Re: Reports Performance

          Shayne,

          Correct on how VPD works, though depending on the tables referenced, many additional where clauses can get tacked on to your query. By using the "exec vpd.set_user('DOMAIN.USER')" command, you can run the report as various users/domains. In order to tune the report, you can do various things:
          • Tune the SQL of your report in order to get it to run effectively even with VPD. It's important to get an explain plan for the SQL execution, which includes the VPD calls, so you're looking at the full picture.
          • Add indexes, if you're noticing full table scans
          • Add a view and use this to get around some of the inherent performance issues around joins
          • etc.
          --Chris

          Comment


          • #6
            Re: Reports Performance

            Thanks for your reply.

            One more question about this functionality. I see a note out on Metalink that references the glog.properties setting "glog.query.vpdOptimization". Is that setting applicable with reports?

            Thanks,
            Shayne

            Comment


            • #7
              Re: Reports Performance

              No - this setting will only affect the core OTM application, not reports.

              Ian Lo (a customer) and I have hit this before when the migrated to v5.5. SQL tuning is going to be your best bet for getting the performance back up to par.

              --Chris

              Comment


              • #8
                Re: Reports Performance

                You mention getting the sql with all of the VPD calls. I'm assuming the best way to do this is through a trace? I've discussed this with our DBA and we tried tracing my session. However, all of the sessions look exactly the same. Any thoughts?

                Comment


                • #9
                  Re: Reports Performance

                  I usually use a tool like Toad to help with the SQL optimization. You'll just need to ensure that the call is wrapped within the context of VPD (that either you have a login trigger to setup the vpd.set_user command or that your SQL is setup within a script that first runs this command), so that you're looking at the true explain plan.

                  --Chris

                  Comment

                  Working...
                  X