My Oracle Support Banner

How to Enable Oracle Text Tracing on Specific WebCenter Content Queries (Doc ID 1405144.1)

Last updated on SEPTEMBER 20, 2023

Applies to:

Oracle WebCenter Content - Version 11.1.1.4.0 and later
Information in this document applies to any platform.

Goal

When troubleshooting search performance on WebCenter Content, the following note can be used to convert WCC searches into SQL queries. 

<Document 1333414.1> - Troubleshooting OracleTextSearch in UCM 10g and 11g: How To Convert UCM Searches into SQL Queries 
In addition to using explain plans, Oracle Text tracing options can be enabled to see where the bulk of time is spent searching the index. 
Oracle Text provides various trace sections that can be enabled before a CONTAINS query is executed. For WCC, to view specific Oracle Text tracing these options can be turned on before a query is executed in SQL Developer or SQLPlus.  Note that the "time" trace sections are in microseconds. (1 million microseconds = 1 second).
For information on specific tracing options, see Oracle Text Reference: Chapter 9: CTX_OUTPUT Package.
http://docs.oracle.com/cd/B28359_01/text.111/b28304/coutpkg.htm
This chapter provides reference information for using the CTX_OUTPUT PL/SQL package.  Table 9-1 in the documentation explains the trace options that can be enabled, and what each trace measures. 

Table 9-1 Available Traces

Symbol ID Metric

TRACE_IDX_USER_DATASTORE

1

Time spent executing user datastore

TRACE_IDX_AUTO_FILTER

2

Time spent invoking the  AUTO_FILTER filter. (Replaces the deprecated  TRACE_IDX_INSO_FILTERtrace)

TRACE_QRY_XX_TIME

3

Time spent executing the  $X cursor

TRACE_QRY_XF_TIME

4

Time spent fetching from  $X

TRACE_QRY_X_ROWS

5

Total number of rows whose token metadata was fetched from  $X

TRACE_QRY_IF_TIME

6

Time spent fetching the  LOB locator from  $I

TRACE_QRY_IR_TIME

7

Time spent reading  $I  LOB information

TRACE_QRY_I_ROWS

8

Number of rows whose  $I  token_info was actually read

TRACE_QRY_I_SIZE

9

Number of bytes read from  $I  LOBs

TRACE_QRY_R_TIME

10

Time spent fetching and reading  $R information

TRACE_QRY_CON_TIME

11

Time spent in  CONTAINS processing ( drexrcontains/drexrstart/drexrfetch)

TRACE_QRY_S_TIME

15

Time spent fetching and reading  $S information

Solution

To view full details, sign in with your My Oracle Support account.

Don't have a My Oracle Support account? Click to get started!

In this Document
Goal
Solution
 Enable all trace sections
 Run the query
 Show the trace output
 Reset the tracing values to zero, or turn off the trace options
References


My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.