We are experiencing a very peculiar problem with a custom report where it times out after the stipulated 30 minutes (rdisp/max_wprun_time = 30 minutes). What is unusual is that, the same report, with the same selection criteria, finishes within 20 minutes at some times or even in a lesser duration. I have been unable to establish any correlation between when it runs and when it times out.
1. There are enough free dialog wp, memory and CPU utilization is approximately 50% (or even less) when it does/doesn't finish. By this, I mean that the system is by no means under any kind of stress.
2. Executing the report in the background is an option, but for what the report is meant for, it is necessary to run it in the foreground.
3. I created indices on the tables it reads, which I see being used in the Explain Plan. But using the same indices, the report doesn't finish at other times.
4. The table does an inner join of 2 huge tables; EKPO and EKKO and also an inner join on EBELN and EKET
5. I have checked for locks in the system, but with locks on EKPO and EKKO, the report is still able to finish at some times.
I can probably increase the time limit further, but that is the last thing I would like to do. What is perplexing is that the report finishes within the stipulated time at times. Am I missing something here ? Any help would be greatly appreciated.