TRACE_REPORT

Use TRACE_REPORT configuration setting to debug performance for multiple, concurrent queries. Query tracing helps you monitor Essbase query performance metrics. The level of information you want to keep is reported in the application log and/or a separate tracing log, trace_report.log.

Description

TRACE_REPORT enables query tracing for grid retrievals, MDX queries, and transparent partition sources. The query tracing output file includes information on:

The query type
The time of the query
User name and IP address
Report text

Syntax

TRACE_REPORT n

where n should be set to -1, to enable query tracing. The default value is 0 (query tracing is off).

Note:

Oracle Support may set a different non-zero integer value to reduce the items logged to trace_report.log.

Example

TRACE_REPORT -1

Configuring Ongoing Query Tracing

Use the configuration settings TRACE_REPORT and LONGQUERYTIMETHRESHOLD together to manage the query tracing information that should be collected for the cube in the application’s Oracle Diagnostic Log (ODL), and/or printed to trace_report.log.

You can enable TRACE_REPORT for tracking general query performance, but Oracle recommends filtering out all but the longest running queries by also setting LONGQUERYTIMETHRESHOLD.

Ideally,

Configure Essbase to only log information for the top 5 percent of longest-running queries. If you don't know this value, 60 seconds may be a good place to start.
Regularly check that log files do not grow too large.

Configuration Type	TRACE_REPORT value	LONGQUERYTIMETHRESHOLD value	Effect on Logs
ODL only, with threshold	Set to 0 (or not set at all)	Set to a value n (seconds) that is greater than 0	Performance metrics on all queries running longer than n seconds are printed to the application’s ODL log.
ODL and trace, with threshold	Set to -1	Set to a value n (seconds) that is greater than 0	Performance metrics on all queries running longer than n seconds are printed to the application ODL log. Information on queries running longer than n seconds is printed to `trace_report.log`.
Trace without threshold - not recommended!	Set to -1	Set to 0 (or not set at all)	Information on all queries, regardless of running time, is printed to `trace_report.log`. This configuration is not recommended, because `trace_report.log` can become too large if not filtered using a threshold, and query performance is affected if the threshold is too low.

The trace_report log is located in <Domain Home>/servers/<Essbase-Managed-Server-Name>/logs/essbase/essbase/app/<application-name>/<cube-name>/trace_report.log.

The application ODL log is located in <Domain Home>/servers/<Essbase-Managed-Server-Name>/logs/essbase/essbase/app/<application-name>/<application-name>_ODL.log.

For details about log file locations, refer to Environment Locations in the Essbase Platform.

When to Use Different Query Tracing Options

The QUERYTRACE configuration setting is designed for debugging a single query, in case any problems are observed. It prints the trace log to the cube directory as query_trace.txt, and the file is cleared by default before each query execution. QUERYTRACE should not be left enabled in the application configuration for long term use, as it may affect performance.

The TRACE_REPORT configuration setting provides fewer details than QUERYTRACE, but is able to trace multiple, concurrent queries. TRACE_REPORT is a good option for gathering information on many concurrent queries running over a period of time.

Enabling query logging by setting the QUERYLOG parameter in dbname.cfg (in the cube directory) is designed for tracking user query patterns for a cube, in XML format.

Notes

TRACE_REPORT applies to block storage (BSO) databases, including those in hybrid mode. TRACE_REPORT does not apply to aggregate storage (ASO) databases.