Tuning Oracle Reports 6i - II

Efficient SQL

Oracle Reports uses the Structured Query Language (SQL) to retrieve data from the relational database. So an efficient SQL increases the performance of the report execution.

There are many sql editors which show the performance of an SQL by generating the explain plan of the sequel. So based on the EXPLAIN PLAN, we can identify the time consuming (full table scan) sql and create indexes where needed.

Calculations

When performing calculations within a report (either through summary or formula columns), the general thumb rule is the more calculations that can be performed within the SQL of the report queries, the better.

If the calculations are included in the SQL, then they are performed before the

data is retrieved by the database, rather than the performed on the retrieved data by the Report.

Database-stored user-defined PL/SQL functions can also be included in the query select list. This is more efficient then using a local PL/SQL function (e.g. in a formula column), since the calculated data is returned as part of the result set from the database.

So, the more the number of formula or summary columns used in the report query, the less efficient is the report performance.

Redundant Queries

Ideally a report should have no redundant queries (queries which return data which is not required in the report), since they will clearly have an effect on performance.

Break Groups

Limiting the number of break groups can improve the performance of a report. For each column in the data model that has the break order property set

The creation of a break group may make an ORDER BY clause defined as part of the query redundant. If this is the case then the redundant ORDER BY should be removed, since this will require extra processing on the database.

Group Filter

The main use for group filters in the database is to reduce the number of records retrieved from the database. When using a group filter, the query is still passed to the database and all the data is returned to reports, where the filtering will take place.

Therefore, even if the filter is defined to only displays the top five records, the result set returned to reports will contain all the records returned by the query.

So it is always suggested to use condition ‘WHERE’ clause in the report sql query, instead of using the group filters.

Data Link

When designing the data model in the report, it is preferable to minimize the actual number of queries by using fewer, larger (multi-table) queries, rather than several simpler (single-table) queries.

Each time a query is issued, Oracle Reports needs to parse, bind and execute a cursor. A single query report is therefore able to return all the required data in a single cursor rather than many.

Also be aware that with master-detail queries, the detail query will be re-parsed, re-bound and re-executed for each master record retrieved. In this instance it is often more efficient to merge the two queries and use break groups to create the master-detail effect.

Credit goes to the below website(s) :

http://oracleapps4u.blogspot.com/search/label/Tuning%20Oracle%20Reports%206i