Oracle Analytics Best Practices - Gathering and Analyzing Query Logs

August 31, 2021 | 9 minute read
Anitha Ramarao
Consulting Solutions Architect
Text Size 100%:

­­This blog is part of a series of “best practice” blogs for Oracle Analytics. 

Query Logging is powerful diagnostic information available to the Admin users to analyze and troubleshoot issues related to query performance, error scenario and wrong results.   On an Analytics instance when query logging is enabled - the information about parsing, optimization, execution plans, physical query, summary stats etc., are written into the query log by the Analytics Server.   Log is written serially in the same order of execution of queries across the system.   Each session and request is identified by a unique id. Query log can be retrieved from Administration/Manage Sessions in Answers or from Console/Session within DV.  

Another optional feature   "Developer tool" is available for logging of visualizations in a  DV.  This has been explained under section "Getting logs for DV queries"

Query Log Level

  • Detail and amount of log generated depends on the LOGLEVEL set at system, session or report level
  • Log level can be defined in the RPD globally under Tools/Option/Repository/System logging level OR by using session variable in the RPD. 

  • This can also be overridden at report level by adding the  LOGLEVEL variable in prefix section of the “Advanced” tab in reports
  • You may need to use variable DISABLE_CACHE_HIT=1 along with LOGLEVEL to make sure you get complete logs by avoiding cache hit

 

 

LOGLEVEL value ranges between 0-7.   0 values means logging disabled and LOGLEVEL 7 is the highest level which is mainly for use by the Oracle development team. LOGLEVEL 2 is enough for performance tunic and basic understanding.   LOGLEVEL 3 must be used to troubleshoot row-level data security filters.

Based on the log level selected the query logs contain information on the execution of query including the logical request, navigation and execution plan, physical query generated, execution time, rows/bytes retrieved at different execution nodes  and also the caching related information.

Admin users can also extract the logs from Console->Issue SQL by running the needed query with appropriate LOGLEVEL and variables settings.

 

Reading Query Log

Manage Sessions contains list of all the queries and sessions that are currently active.   You can get to this page either thru Answers->Administration->Manage Sessions OR Console->Sessions in DV.

 

Each individual entry above would have complete logs related to one particular query as per the level set at RPD/session/report level.

 

 

Every request is identified by a unique requestid across the Analytics Server

 

Logical SQL Query

Here is a sample query that is sent to the BI Engine for execution.