10.6 Solving Index and Query Bottlenecks by Using Tracing

Tracing enables you to identify bottlenecks in indexing and querying. Oracle Text provides a set of predefined traces.

Each trace is identified by a unique number. CTX_OUTPUT includes a symbol for this number. Each trace measures a specific numeric quantity, such as the number of $I rows selected during text queries.

Traces are cumulative counters, so usage is as follows:

1. The user enables a trace.

2. The user performs one or more operations. Oracle Text measures activities and accumulates the results in the trace.

3. The user retrieves the trace value, which is the total value across all operations done in step 2.

4. The user resets the trace to 0.

5. The user starts over at Step 2.

So, for instance, if in step 2 the user runs two queries, and query 1 selects 15 rows from $I, and query 2 selects 17 rows from $I, then in step 3 the value of the trace is 32 (15 + 17).

Traces are associated with a session—they can measure operations that take place within a single session, and, conversely, cannot make measurements across sessions.

During parallel synchronization or optimization, the trace profile is copied to the secondary sessions if and only if tracing is currently enabled. Each secondary session accumulates its own traces and implicitly writes all trace values to its logfile before termination.