EDI reporting slow

[Former Member]

Dear All,

I am in a pickle again.

I have written a very straight forward report to extract information about EDIs. It runs fairly fast on the QA system. On the production system takes about 8-10 times slower. We have looked at all the tables involved in ST05 & SAT. Also, we looked at the underlying Oracle (11.x), the optimiser anything one could poke a stick at. We went the full Monty, rebuild indexes etc. Both systems have the same set up. QA was refreshed from the production about a month ago. What is more intriguing that the only non EDI related table I use has similar access times in both systems. Reports we tested outside this area run faster in production.

I hope I explained everything: found no differences between the systems, yet one of them is lot slower than the other for a (maybe more) report. The $64K question is: what else to look at?

Thanx

[Former Member]

Dear All,

I am in a pickle again.

I have written a very straight forward report to extract information about EDIs. It runs fairly fast on the QA system. On the production system takes about 8-10 times slower. We have looked at all the tables involved in ST05 & SAT. Also, we looked at the underlying Oracle (11.x), the optimiser anything one could poke a stick at. We went the full Monty, rebuild indexes etc. Both systems have the same set up. QA was refreshed from the production about a month ago. What is more intriguing that the only non EDI related table I use has similar access times in both systems. Reports we tested outside this area run faster in production.

I hope I explained everything: found no differences between the systems, yet one of them is lot slower than the other for a (maybe more) report. The $64K question is: what else to look at?

Thanx

[yuri_ziryukin]

Well, if all tables have comparable number of entries and the execution plan of the statement(s) is identical in both systems, the reason may be in the bufferung (in production system blocks may be displaced from the buffer faster and significantly more disk reads are required to fetch the data).

I would also compare both statements via the Shared Cursor Cache in QA and Prod. You may notice some differences in terms of disk reads, buffer gets per row, etc.

It will give you more information for further analysis.

[Former Member]

Thanx Yuri.

Rejigging the SELECT and giving a hint to ORACLE solved the problem.

Cheers