QUERYTRACE

Use QUERYTRACE to debug performance for a single query. Query tracing helps you monitor Essbase query performance metrics. The information you want to keep is reported in the separate tracing log file, query_trace.txt.

Description

The QUERYTRACE configuration applies only to aggregate storage (ASO) and BSO hybrid mode cubes.

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 (ASO) 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 aggregate storage (ASO) cubes, including block storage (BSO) with hybrid mode cubes. 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.

Note:

When QUERYTRACE is set to -1, concurrent queries may not execute in parallel and performance may be affected. Disable QUERYTRACE after troubleshooting is complete.

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.

Example

QUERYTRACE -1

Sets query tracing to include all tracing features listed in the description.