Oracle Database Discussions

Hi!

I am doing a 10046 trace file analysis for a performance problem we see on a customer site, but we can't reproduce the problem on our local reference test system.

Basically, the cpu timings for FETCH calls for a SELECT statement are ten times as high on the customer system. There are no WAIT events in the trace file (for this statement), only a high cpu timing for the FETCHES:

Customer instance:
FETCH #19:c=140625,e=140189,p=0,cr=3358,cu=0,mis=0,r=1,dep=1,og=1,tim=1500177409

Reference system;
FETCH #4:c=15625,e=7984,p=0,cr=2262,cu=0,mis=0,r=1,dep=1,og=1,tim=5624714062

I see that the number of consistant reads is 50% higher, but I don't see why this result in 10 times the CPU time and about 20 times the elapsed time. This is does not seem to be a general problem with all statements, so the general timings are comparable to our reference system. The problem seems to be this Select statement only which only joins two tables.

I had the idea that a long running transaction that didn't commit would lead to this problem, since Oracle would need to reconstruct blocks from undo, but there are no such transactions on the system.

With no WAIT events emitted, where does the time go?

Thanks for your help,
Marcus