[SOLVED] Timeout problem on GC3 reports

[blasley]

We find that GC3 gives a java.io.exception that appears to indicate a connection timeout whenever a report run from the UI takes longer than 5 minutes to complete. Oracle has suggested numerous httpd.conf settings to change, but none of them has had any effect on this behavior. Anybody have any suggestions?

[chrisplough]

Re: Timeout problem on GC3 reports

I just worked on a similar issue and the timeout was a result of a setting within their reverse-proxy server. Once that was increased, they were able to run reports that took up to 8 or 9 minutes. Tracking down these timeout issues can be a bit tricky and usually involves finding and correcting a low timeout threshold in multiple components (reverse-proxies, load-balancers, firewalls, and OTM / GC3 itself).

In order to give a more complete answer, please post some additional version, including your GC3 version and the full error that is produced. Also (and sorry to get technical here), if you can describe a bit about your network architecture, it would help. For instance, is you report server (or db server) separated from your web and app servers via a firewall, or are they all on the same network?

Thanks,
Chris

[Shells]

Re: Timeout problem on GC3 reports

Hey Blasley - we recently had the same issue where I work. Altho yours may be something totally different or things you have already tried - the 2 changes below are what did it for us.


1. In the httpd.conf, change the timeout to 660 on web, app, and reports server. If your network is 3 tiered like ours and have apache installed on the app server but dont actually use it/bring it up, we found it still needs to be changed there -kinda like tender branding.

2. I'm not sure what kind of reports you are running, but we were able to fix the issue by adding a hint to our queries - SELECT /*+ INDEX(SHIPMENT PK_SHIPMENT)*/ DISTINCT s.shipment_gid. Even tho the explain plan showed that index was being used - adding the hint to our queries greatly improved the timing in bringing back the report without changing anything in the query itself. The greatest improvement was any reports involving shipment events.

A couple of additional things we were asked to check out, but didn't end up being the issue was to verify the firewall wasn't dropping any packets/connections and to make sure the nic card on the reports server was full duplex.

[blasley]

Re: Timeout problem on GC3 reports

Actually, we found that the timeout setting (5 minutes) that was actually taking effect was on the oracle reports server, in the httpd.conf for the apache process on the "oracle" side, not the gc3 side. I did not have sufficient privileges to see this, but our DBA found it and changed it to the equivalent of 25 minutes, and that worked.

The actual path to the httpd.conf file that had the real effect (on our reports server, at least) is
/opt/app/oas10g/product/9.0.4/Apache/Apache/conf

Thanks for your suggestions. We have some people working on optimizing the report queries too, since that would be a happier solution all around. What we have found is that, for queries involving the order-shipment relationship at least, the VPD profile appears to be killing performance. The same queries, when run from a straight sql client (i.e., with no VPD profile explicitly set), execute in a fraction of the time (seconds versus minutes). Any insight as to why this is so?

[Shells]

The reason is that gc3 uses additional logic to sort out the vpds to ensure the report executioner does not get any data he shouldn't. I agree that the VPD's slow it down and that aspect of the system is not quite tweaked the way it should be. Believe me, I have complained several times about it Hopefully it will get better in later versions. We are working on loading 5.5 now so we will see.