Why You Shouldn’t Rely on Oracle Trace Event 10046 for Performance Data Collection

Trace event 10046, not to be confused with wait events, is the best utility to trace processes, bar none. The trace file contains information on SQL statements, parses and executions, bind variables, and wait events. You can trace foreground and background processes with it. It is perfect for ad hoc tracing when you need runtime information at the lowest granular level and you have established the tracing timeframe.

However, the 10046 trace facility does not meet the always-on and low overhead requirement established in the previous section. There are two main reasons why you cannot use this trace facility as a continuous performance data collector:

•  It generates too much data. The fine-grain data that it offers is synonymous with high volume, especially at levels 8 and 12. The amount of trace data generated at these levels can quickly consume all the space in your UDUMP/BDUMP directory. For this reason, trace event 10046 is seldom enabled at the instance level. If it is, it is only for a brief moment, usually at the request and guidance of Oracle Support. Forget about enabling this for all processes on a 24×7 basis. It is not even practical to trace one long-running session from start to finish. You have to be very selective about when to start and stop tracing, which means you need to have a rough idea of when the problem will arise.
• It is expensive. The comprehensiveness of this trace facility is synonymous with overhead. The overhead will further degrade the performance of an already slow-running process.

Besides these reasons, there are other reasons the trace event 10046 is unsuitable for the task, including the following:

• Despite the vast amount of data generated at levels 8 and 12, it is still not enough. You will only find raw event data in the trace file. You may see something like “WAIT #12: nam=‘db file scattered read’ ela= 0 p1=106 p2=60227 p3=8”. Obviously, it is not user-friendly and there is no value-added interpretation. You have to manually translate P1 and P2 to discover the object name, which is a laborious task, especially when many objects are present. Furthermore, translations performed after the fact can be erroneous if the object is dropped and/or the same space is occupied by another object. There is also no cross-referencing in the trace file. For example, when there is an enqueue wait, the trace file records the P1, P2, and P3 values for the enqueue event, but there is no data about the blocking session or the SQL statement that the blocker executes. In this case, you only get half of the story.
• Depending on the Oracle version, the trace files may be bug prone and are not always reliable. Consider the following snippet of an event 10046 trace file that belongs to the DBWR process in Oracle Release 9.2.0.1.0. Notice the negative P1 values. (This bug is fixed in 9.2.0.4.)

• Perhaps the biggest drawback with the trace event 10046 facility is that it is impractical as an ongoing data collector, though it is technically possible.