Tune SQL Query

Query tuning is the most important step to improve performance of any report.

Publisher provides a mechanism to generate the explain plan and SQL monitoring reports. This functionality is applicable to SQL statements executing against Oracle Database. Logical queries against any other type of database aren't supported.

Generate Explain Plan

You can generate an Explain plan at the dataset 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 Oracle Database SQL Tuning Guide.

Explain Plan for a Single Query

From the SQL dataset Edit dialog you can generate an explain plan before actually executing the query. This provides a best guess estimation of a 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.

1. From the New menu, select Report Job.
2. Select the report to schedule then click the Diagnostics tab.

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

3. Select Enable SQL Explain Plan and Enable Data Engine Diagnostic.

   • Enable SQL Explain Plan — Generates a diagnostic log with Explain plan/SQL monitor report information.

   • Enable Data Engine Diagnostic — Generates a data processor log.

   • Enable Report Processor Diagnostic — Generates FO (Formatting Options) and server related log information.

   • Enable Consolidated Job Diagnostic — Generates the entire log, which includes scheduler log, data processor log, FO and server log details.

4. Submit the report.
5. From the Home page, under Browse/Manage, select Report Job History.
6. Select the report to view the details. Under Output & Delivery click Diagnostic Log to download the explain plan output.

Sample Explain plan:

Description of the illustration explain_outp.gif

Guidelines for Tuning Queries

Tune queries by following a set of guidelines.

• Analyze the explain plan and identify high impact SQL statements.

• Add required filter conditions and remove unwanted joins.

• Avoid and remove FTS (full table scans) on large tables. Note that in some cases, full table scans on small tables are faster and improve query fetch. Ensure that you use caching for small tables.

• Use SQL hints to force use of proper indexes.

• Avoid complex sub-queries and use Global Temporary Tables where necessary.

• Use Oracle SQL Analytical functions for multiple aggregation.

• Avoid too many sub-queries in where clauses if possible. Instead rewrite queries with outer joins.

• Avoid group functions like HAVING and IN / NOT IN where clause conditions.

• Use CASE statements and DECODE functions for complex aggregate functions.