Markus Winand's Blog

Posts Tagged ‘trace’

Oracle Trace File Rotation

In Maintainability on 2010-02-01 at 17:30

Under very rare circumstances, I need Oracle SQL trace files from a long period of time. Because trace files usually grow large—especially over several days—there is the need to rotate the trace file during that time so that they can be compressed and put away. The problem is that there is no “rotate tracefile“ button in Oracle. However, I have found a “undocumented feature“ that does exactly that—without disabling tracing.

My procedure uses the close_trace call of oradebug. This call closes the currently written trace file for a session. Alex Gorbachev has used this to delete big trace files that are still open. My procedure goes a little bit further and exposes one more side effect of oradebug close_trace.

Read the rest of this entry »

To Trace or Not to Trace

In Maintainability on 2009-11-24 at 17:49

I have always been a fan of the oracle SQL trace facility. It’s a very powerful method to analyze performance problems, especially if you don’t know the application very well. If you don’t know oracle SQL tracing yet, you will find some info at orafaq and some more details at orable-base. Obviously, Oracle has also some documenation about it.

In short, the Oracle SQL tracing facility causes the database server to write all executed SQL statements to a file for later analysis. One of the main issues with SQL tracing is the huge size of the trace files; every statement execution is logged, potentially with additional data about the execution plan, wait events, and bind values.

This article introduces the new possibilities of Oracle 10g to enable tracing on a very fine level. With a properly instrumented application, tracing can be enabled for specific users or program modules only. With such a fine control over the tracing, the generated data volume can be kept small and SQL tracing becomes a much more useful tool.

Read the rest of this entry »