LONGQUERYTIMETHRESHOLD
This Essbase configuration setting enables capturing statistics on long-running grid and MDX queries. This setting lets you specify the lowest query-time length, in seconds, for which you want to capture statistical information.
- briefly summarizes query performance in the Oracle Diagnostic Log (ODL) for each query running longer than the specified time value
- sets a threshold/filter on what is logged for query tracing (when the TRACE_REPORT setting is enabled)
Syntax
LONGQUERYTIMETHRESHOLD n
Where n is a number of seconds. If set to 0, then this setting is off (this is the default). If set to -1, this setting is always on.
Example
LONGQUERYTIMETHRESHOLD 60
Specifies statistics collection for queries running 60 seconds or longer.
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.
- 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 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
|
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
.
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. For more details, see Configuring Ongoing Query Tracing.
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.
See Also