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
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.
-
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 theLOGLEVEL
. -
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
- 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
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.