Session trace affects query performance
I administer the DB for an application. The application allows users to enable and disable tracing for the functions they perform within a screen in the application. When the user enables the trace, the application performs a:
alter session set events '10046 trace name context forever, level 12';
When a user complains about the performance of a screen, I have them create a trace. When they are done they disable the trace. This all seems normal until the trace is enabled and the screen performance improves 100%.
Here is the scenario put another way:
1) user runs query and it takes 2 minutes
2) user enables trace and runs the same query and it finishes immediately, user disables trace
3) user runs the same query and it takes 2 minutes
This makes analyzing traces for poorly performing queries impossible.
Has anyone ever seen this before? I have asked several DBA's none of them have seen this.
Never mind, should have searched the forum first!
I guess after facing performance issue, user notified you. Then again user executed same process to simulate the issue. If that's correct then i'm not surprised if it takes less time now. As the data is already in Buffer cache after the first time user executed the process, so when user is simulating the same scenario data being retrieved from buffer cache, which is why faster.
Thanks for the reply, but what you describe is not the problem. Please see Tom Kyte's article in the January/February 2008 copy of Oracle Magazine. My issue is a result of bind variable peeking. It is an interesting read:
Click Here to Expand Forum to Full Width