Session trace affects query performance
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 4 of 4

Thread: Session trace affects query performance

  1. #1
    Join Date
    Mar 2003
    Posts
    38

    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.

    Thanks

  2. #2
    Join Date
    Mar 2003
    Posts
    38
    Never mind, should have searched the forum first!

  3. #3
    Join Date
    Nov 2002
    Location
    Mooresville, NC
    Posts
    349
    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.
    http://www.perf-engg.com
    A performance engineering forum

  4. #4
    Join Date
    Mar 2003
    Posts
    38
    Malay,

    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:

    http://www.oracle.com/technology/ora...o18asktom.html

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width