How to Do SQL Query Tuning in Oracle Cloud BIP reports: Oracle Cloud BIP Tool for SQL Tuning

How to Do SQL Query Tuning in Oracle Cloud BIP reports: Oracle Cloud BIP Tool for SQL Tuning

In this post , we will be discuss about SQL query tuning in Oracle Cloud BIP reports. BIP report tool helps to tune our sql query to develop the BIP report data models. We can use this standard tool to tune our SQL queries. This tool helps to generate the complete sql plain which helps to fine tune our sql query and we can find out that which part of the sql query consuming or taking lot of time.

 

Query tuning is the most important step to improve performance of any report. Explain plan, SQL Monitoring, SQL Trace facility with TKPROF are the most basic performance diagnostic tools that can help to tune SQL statements in applications running against the Oracle Database.
Oracle BI Publisher provides a mechanism to generate the explain plan and SQL monitoring reports and to enable SQL session trace. This functionality is applicable to SQL statements executing against Oracle Database only. Logical queries against BI Server or any other type of database are not supported.

 

Generate Explain Plan in BIP report tool

 You can generate an Explain plan at the data set level for a single query or at the report level for all queries in a report. For more information about interpreting the explain plan, see the Oracle Database SQL Tuning Guide.

 

Explain Plan for a Single Query
From the SQL data set Edit dialog you can generate an explain plan before actually executing the query. This will provide a best guess estimation of plan. The query will be executed binding with null values.

 

 

Click Generate Explain Plan on the Edit SQL Query dialog. Open the generated document in a text editor like Notepad or WordPad.

 

Explain Plan for Reports
To generate an explain plan for a report, run the report through the Scheduler:

 On the New menu, select Report Job.

 Select the report to schedule then click the Diagnostics tab.

Note: You must have BI Administrator or BI Data Model Developer privileges to access the Diagnostics tab.

 Select one or more of the options to enable SQL Explain Plan, Data Engine Diagnostic and Report Processor Diagnostic. You can also chose to enable consolidated job diagnostic to create all the logs

 

– Scheduler, Data Engine, Report Processor and Server log in one file.