Gather and Analyze Query Logs

Query logs contain powerful, diagnostic information that allows administrators to analyze and troubleshoot issues related to query performance, error scenarios and wrong results. When you enable query logs in Oracle Analytics, information about parsing, optimization, execution plans, physical query, summary statistics, and so on are written into the query log.

• Accessing Query Logs
• Query Log Levels
• Reading a Query Log
  • Logical SQL Query
  • Logical Request
  • Execution Plan
  • Physical or Database Requests
  • Summary Statistics
• Query Log Considerations
• Accessing Query Logs for a Workbook

Accessing Query Logs

Query logs are written serially in the same order of execution of queries across the system. Each session and request is identified by a unique ID. Administrators can access these query logs from the Session and Query Cache page in the Console. To learn how to access this page, see Analyze SQL Queries and Logs.

Note:

Workbook authors can also access query information such as query time, server time, and streaming time for visualization components in their workbooks. See Accessing Query Logs for a Workbook, at the end of this topic.

Query Log Levels

• Log level determines the detail and amount of log generated.

• You can set the log level at system, session, or report level.

• You can define the global log level for your semantic model (RPD) using the System logging level property (under Tools, Option, Repository) or use the session variable.

  
  Description of the illustration ceal_query_log1.jpg

• You can override the log level for a report, by adding the LOGLEVEL variable to the Prefix property, available in the Advanced tab for the report.

• To ensure you get complete logs by avoiding cache hits, you can include the variable DISABLE_CACHE_HIT=1 alongside the LOGLEVEL.

  
  Description of the illustration ceal_query_log2.jpg

• Log level (LOGLEVEL) values range between 0-7.

  • LOGLEVEL=0 means logging is disabled.
  • LOGLEVEL=7 is the highest logging level used mainly by the Oracle development team.
  • LOGLEVEL=2 is suitable for performance tuning and basic understanding.
  • LOGLEVEL=3 is required to troubleshoot row-level data security filters.

• Depending on the log level, query logs contain information about the query including the logical request, navigation and execution plan, physical query generated, execution time, rows and bytes retrieved at different execution nodes, and cache-related information.

Administrators can extract query logs from the Issue SQL page in the Console by running the query with the appropriate LOGLEVEL and variables settings.

Description of the illustration ceal_query_log3.jpg

Reading a Query Log

The Session and Query Cache page lists all the queries and sessions that are currently active. Administrators can access this page from the Console.

Description of the illustration ceal_query_log4.jpg

Each entry on the page provides access to the query log for a particular query, at the level set (that is, at the semantic model, session or report level).

Description of the illustration ceal_query_log5.jpg

Each request has a unique requestid in Oracle Analytics.

Logical SQL Query

Here is a sample logical SQL query in Oracle Analytics.

Description of the illustration ceal_query_log6.jpg

These are some common variables you might see in a logical SQL request:

• QUERY_SRC_CD: Origin of the query: Prompt, Report, DV, Issue SQL, and so on.
• SAW_SRC_PATH: If the query is saved, path to the query in the catalog.
• SAW_DASHBOARD: If the query is included in a dashboard, path to the dashboard in the catalog.
• SAW_DASHBOARD_PG: Name of the dashboard page.

Logical Request

The logical request is the translation of a query from the presentation layer to the business model and mapping layer after adding security filters, if any.

Description of the illustration ceal_query_log7.jpg

Based on the logical request, Oracle Analytics decides whether the query hits an existing cache or must be retrieved from the database.

Description of the illustration ceal_query_log8.jpg

Execution Plan

The execution plan is the transformation of the actual logical request into an optimized plan for execution. This includes a shipping plan for each operation, and whether it's performed in the database or in Oracle Analytics. When an operation is processed in Oracle Analytics, the query log indicates [for database 0:0,0].

Description of the illustration ceal_query_log9.jpg

During query execution, Oracle Analytics exactly traverses through this tree. In detailed logs, information about the rows processed is available at every node of the execution tree.

Description of the illustration ceal_query_log10.jpg

Physical or Database Requests

Based on the execution plan, Oracle Analytics generates physical SQL to be executed on the specified database. There could be one or more requests sent to one or more databases.

Description of the illustration ceal_query_log11.jpg

For every physical request sent to the database, there is a log of the number of rows and bytes retrieved.

Description of the illustration ceal_query_log12.jpg

When there are multiple queries, you can use the query ID (in this example, 1914627) to match the exact query logged in the section Sending query to the database. This allows you to map the query with rows retrieved when there are multiple database requests.

One report could send multiple queries to one or more databases depending on the report structure and the semantic model definition. For example, in this query log 3 physical queries were sent to the database.

Description of the illustration ceal_query_log13.jpg

The log provides similar rows processed information for all the nodes in execution plan. Finally, the rows sent to the client is logged.

Description of the illustration ceal_query_log14.jpg

The log also includes a final summary of statistics that includes the complete execution time. You can correlate the time here to analyze and investigate performance issues.

Description of the illustration ceal_query_log15.jpg

Summary Statistics

Several timing statistics appear in the query log summary.

• Elapsed time - Total elapsed time from when the logical query is received until the client closes the cursor. If the client allows the user to scroll through the result, as Oracle Analytics does, then the cursor may stay open for a long time until the user either navigates to another page or logs out.
• Compilation time - Time that Oracle Analytics uses to generate the execution plan and physical queries from the logical SQL query.
• Total time in BI Server - Total amount of time that the client is waiting for a response. This includes physical query execution time, wait time during fetching, and time spent in Oracle Analytics for internal execution.
• Execution time - Time from when the logical query is received by Oracle Analytics until the logical query execution completes. This doesn't include any time spent after the logical query execution is complete when the client is fetching results.
• Response time - Time from when the logical query is received by Oracle Analytics until the first row is returned to the client.

Query Log Considerations

• Single-threaded activity. Under adverse circumstances, you might experience performance bottle neck for log levels greater than 2.

• Times listed and computed are for when entries are written to the log and this is almost always when the event occurred (that is, the activity that initiated the log entry). Unless, there are other bottlenecks that impact logging.

• Query logging is diagnostic and not intended for collecting usage information. To learn about usage tracking, see Track Usage.

Accessing Query Logs for a Workbook

Only administrators can access logs through the Session and Query Cache page in the Console. However, content authors can access log information for visualization queries in their workbooks through the Developer menu and this is useful tool for authors who want to troubleshoot query performance. To access the performance tool for workbooks (Developer menu option), users must switch on Enable Developer Options which is in the Advanced menu under My Profile.

Description of the illustration ceal_query_log16.jpg

When enabled, the Developer menu option displays in the workbook menu.

Description of the illustration ceal_query_log17.jpg

The Developer option enables users can view and analyze various logs on the fly for any visualization on a canvas. A separate frame appears below the canvas that has different tabs for each type of information. By default, logs are not populated or refreshed when the visualization is run.

Description of the illustration ceal_query_log18.jpg

Select the visualization you want to analyze and click Refresh to generate the logs. Once refreshed, various information related to the visualization displays and you can analyze the log information for the specific visualization. To analyze multiple visualizations, you must refresh them individually and analyze them one after the other.

Description of the illustration ceal_query_log19.jpg

With the Developer option, content authors can analyze a range of information, such as performance logs, JSON, XML and also dataset-related information. This means they can analyze logs without the need for administrator access to the Session and Query Cache page.

Note:

The Developer menu is available only to workbooks. For classic analyses and dashboards, you access query logs through the Session and Query Cache page.