Deep Dive into SQL Query Logs to Improve BI Performance

When we create reports using Data model, we can fetch only 200 rows. Often, we may need to fetch more than 200 rows to verify the data for many formal or in-formal uses. To accomplish it, we will be ended up in creating a template or report. Going forward, you can directly run the SQL and fetch all the rows without the use of any reports/templates.

Steps to fetch the complete records:

  1. Navigation -> Reports & Analytics -> Administration -> Issue SQL

  1. Enter your SQL Query as shown below

Click Issue SQL button to send the SQL statement to the Oracle BI Server.

  1. Complete set of records are returned unlike maximum of 200 records in BIP.

You can find a GUI through the Issue SQL link in the administration page of BI Presentation Services. The result gives the complete set of record for the appropriate query executed.

Logging level: Use this box to select the Oracle BI Server logging level, from 0 to 7. Each user receives a default logging level during user creation in the BI Server Administration tool. Select Default in this box to specify the use of your default level.

Default – You can find a default logging level in the option of the repository:

This option determines the default query logging level for the internal BI System user.

Level 0 – users have a logging level set to 0 (no logging and page shows no log found).

Level 1 – Logs the SQL statement issued from the client application Logs elapsed times for query compilation, query execution, query cache processing, and back-end database processing. Logs the query status (success, failure, termination, or timeout). Logs the user ID, session ID, and request ID for each query.

Level 2 – Logs everything logged in Level 1.

Additionally, for each query, logs the repository name, business model name, presentation catalog (called Subject Area in Answers) name, SQL for the queries issued against physical databases, 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.

Level 4 – Logs everything logged in Level 3

Additionally, logs the query execution plan.

Level 5 – Logs everything logged in Level 4

Additionally, logs intermediate row counts at various points in the execution plan.

Level 6 & 7 – Not used.

HOW TO DOWNLOAD LOG FILE:

  • The user can download the log file from download option.
  • The file can be downloaded according to the type of level fixed to the Logging Level.
  • The file will be in binary format of query results.
  • The log file can be used to diagnose and debug the issues in OBI reports.
  • Filters can be applied based on target such as date range, user, user transaction, or level of message (error, warning, notification) while fetching the log files.

USE ORACLE BI PRESENTATION SERVICES CACHE:

Use this below box to specify whether to use the Oracle BI Presentation Services Cache or not.

When users run analyses, Presentation Services can cache the results of those analyses. Presentation Services determines if subsequent analyses can use cached results. If the cache can be shared, then subsequent analyses are not stored.

Deselect this box to specify that you do not want the query to use the Presentation Services cache. In general, you should avoid deselecting this box as disabling the cache has potential performance degradation issues.

BENEFITS:

  • Eliminates Report dependency.
  • Handy for Users to generate reports on adhoc-basis.
  • No limitation on the records fetched.
  • Supports Log.

Author
John Chandrasekhar
Oracle HCM Consultant
Kovaion Consulting

Oracle HCM Service

Implement, Enhance, Update & Support Oracle HCM at Zero Cost!

Read More