QUERYTRACE

Use QUERYTRACE to debug performance for a single query. 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, query_trace.txt.

Description

This setting applies to block storage cubes (including hybrid mode).

This setting enables query tracing for calculation flows. The query tracing output file includes:

The input query
An expanded query odometer
General information about query calculation units
A list of formulas and aggregations
An ordered list of all output cells that are calculated or aggregated during the query, according to solve order

The query tracing output file, query_trace.txt, is written to the cube directory.

Note:

Query tracking and query tracing are different.

Query tracking enables you to capture user retrieval statistics against an aggregate storage cube, so that Essbase can make view-based optimizations to improve the performance of aggregations. It is on by default. Related MaxL statements include:

import query_tracking
export query_tracking
alter database enable query_tracking
query database appname.dbname get cube_size_info

Query tracing helps you monitor Essbase query performance metrics for block storage cubes (including hybrid mode). It is off by default. If you enable it, Essbase logs metrics in a trace report. Related configuration parameters: TRACE_REPORT, QUERYTRACE, QUERYTRACETHRESHOLD, LONGQUERYTIMETHRESHOLD.

Syntax

QUERYTRACE n

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

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.

Example

QUERYTRACE -1

Sets a tracing query to be run that includes all tracing features listed in Description.