Reports Performance

[[email protected]]

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

[chrisplough]

Re: Reports Performance

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

http://www.otmfaq.com/forums/f21/instructions-testing-vpd-query-performance-513/

http://www.otmfaq.com/forums/f27/otm-5-5-vpd-query-performance-improvements-515/

--Chris

[nick.polites]

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.

[[email protected]]

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

[chrisplough]

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

[[email protected]]

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

[chrisplough]

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

[[email protected]]

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?

[chrisplough]

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