Configure Query Logging
This section includes information about setting the size of the query log, choosing a logging level, and enabling query logging for a user.
Because query logging can produce very large log files, the logging system is turned off by default. You can enable logging to test that the semantic model is configured properly, to monitor activity on the system, to help solve performance problems, or to assist Oracle Support Services. You must enable query logging on the system for each user whose queries you want logged. You do this using the Model Administration Tool.
Set the Query Logging Level
You can configure the amount of data query logs collect per user.
You can enable query logging levels for individual users, as described in Set the Query Log Level for a User. You can't configure a logging level for a group.
A session variable overrides the logging level for a particular user. For example, if the administrator has a logging level of 4 and the session variable logging level is defined as the default 0 (zero) in the semantic model, then the logging level for the administrator is 0.
Set the logging level based on the amount of logging that's appropriate for your organization. In normal operations, logging is generally disabled (that is, the logging level is set to 0). If you decide to enable logging, then select a logging level of 1 or 2. These two levels are designed for use by administrators.
You might want to diagnose performance or data issues by setting a temporary log level for a query. You can enable query logging for a select statement by adding a prefix clause in the Advanced SQL Clauses section of the Advanced tab in Oracle BI Presentation Services. For example, for the select statement:
SELECT year, product, sum(revenue) FROM time, products, facts;
You can specify the logging level of 5 in the Prefix field as follows:
Set Variable LOGLEVEL=5;
For this query, the logging level of 5 is used regardless of the value of the underlying LOGLEVEL
variable.
Note:
Use logging levels greater than 2 only with the assistance of Oracle Support Services.The query logging levels are described in the following table.
Logging Level | Information That Is Logged |
---|---|
Level 0 |
No logging. |
Level 1 |
Logs the SQL statement issued from the client application. Also logs the following:
|
Level 2 |
Logs everything logged in Level 1. Additionally, for each query, logs the semantic model name, business model name, subject area name, SQL statement issued against the physical database, queries issued against the cache, number of rows returned from each query against a physical database and from queries issued against the cache, and the number of rows returned to the client application. |
Level 3 |
Logs everything logged in Level 2. Additionally, adds a log entry for the logical query plan, when a query that was supposed to seed the cache was not inserted into the cache, when existing cache entries are purged to make room for the current query, and when the attempt to update the exact match hit detector fails. Don't select this level without the assistance of Oracle Support Services. |
Level 4 |
Logs everything logged in Level 3. Additionally, logs the query execution plan. Don't select this level without the assistance of Oracle Support Services. |
Level 5 |
Logs everything logged in Level 4. Additionally, logs intermediate row counts at various points in the execution plan. Don't select this level without the assistance of Oracle Support Services. |
Level 6 and 7 |
Not used. |