Doc ID 61535.1
Performing operations in SQL may be faster than performing them in Oracle
Reports or PL/SQL. The list below explains the most common cases where using
SQL would improve performance:
- perform calculations directly in your query rather than in a formula or
summary,
- use a WHERE clause instead of a group filter or format trigger to exclude
records,
- use the SUBSTR function to truncate character strings instead of
truncating in Oracle Reports.
SQL can perform calculations more quickly than a summary or formula. WHERE
and SUBSTR can reduce unnecessary fetching because they operate on the data
during, rather than after, data retrieval.
SRW.DO_SQL Statements
---------------------
SRW.DO_SQL enables you to add any DDL or DML operation to your report. This
functionality is very valuable, but it can also be very expensive if used
unwisely.
Only use SRW.DO_SQL when necessary. SRW.DO_SQL statements are parsed, a
cursor is opened to the database, and then the statement is executed. Unlike
queries, an SRW.DO_SQL statement will do those things each time its owner
(a group) fetches data. For example, if your SRW.DO_SQL statement is owned by
a group that fetches 10 records, the statement will be parsed 10 times, 10
cursors will be opened, and the statement will be executed 10 times.
Perform computations within the query or PL/SQL instead of SRW.DO_SQL
owned by a group.
CDE_MM.GET_REF
--------------
Only use the CDE_MM.GET_REF packaged procedure when necessary. It is
intended to reduce the amount of temporary space used by Oracle Reports.
Oracle Reports will not cache a column retrieved via CDE_MM.GET_REF in a
temporary file. While this reduces the need for temporary space, it slows
performance because the column's values must always be retrieved from the
database.
When You Should Use Multi-Query Data Models
-------------------------------------------
Reduce the number of queries in your report as much as possible. The fewer
queries it contains, the faster your report will run. Multi-query data models
are easier to understand, but single-query data models tend to execute more
quickly.
Use multi-query data models only when:
- you are fetching many large columns from the parent and
only a few small columns from the child,
- you are trying to do things that SELECT does not support directly
(multi-way outer join),
- you have complex views (distributed queries or GROUP BY queries).
- you need, but do not have or want, to use a view.
For a one-query report, only one cursor is opened to fetch all the master
and detail records. For a two-query report, Oracle Reports opens two cursors
(one for each query) after appending the detail query's link to the WHERE
clause of the detail query. For each master record fetched, Oracle must
rebind, execute, and fetch data from the detail query.
Indexes
-------
Be sure to have indexes on columns used in the SELECT statements' WHERE clauses,
on database key columns, and on the table(s) in the detail queries. Indexes
have little impact on master queries, because those queries access the database
only once. Indexes significantly improve performance of master/detail reports.
The lower the ratio of master to detail records, the more important indexes on
the detail query become for two-query reports.
Indexes are recommended for tables in the detail queries because Oracle
Reports implicitly creates a WHERE clause from the parent/child relationships
and adds it to the detail query.
IMPORTANT: Query Modifications
------------------------------
Oracle Reports modifies your queries in the following cases:
1. For each link you create, Oracle Reports will append a clause to the
child query as specified in the link.
For example:
SELECT deptno, ename, sal
FROM emp
WHERE sal > 1000
If you create a link to this query using DEPTNO as the child column, a SQL
clause of WHERE, and a condition of "equal to", then your query will be
modified as follows:
SELECT deptno, ename, sal
FROM emp
WHERE (sal > 1000) AND (deptno = :deptno)
NOTE: This is not true for multi-query matrix report data models.
2. For each database column with Break Order set, Oracle Reports will
PREPEND an ORDER BY clause to the query.
For example:
SELECT deptno, ename, sal
FROM emp
ORDER BY sal
If you create a break group with DEPTNO as the break column, then your
query will be modified as follows:
SELECT deptno, ename, sal
FROM emp
ORDER BY 1, sal
These SQL statements will be sent to the database, then the SQL optimizer
will determine the optimal way to get the data from the database and return
it to Oracle Reports. The optimizer will determine whether to use indexes,
which table to use as the "driving" table, and so forth.
Break Columns
-------------
When you create a break group, place as few columns as possible in the group.
Try to keep a 1:1 ratio of break columns to break groups. Try to ensure
that the break column is as small as possible. A break column that is shorter
in length will typically give better performance than a break column that is
longer. For larger break columns, it may help performance to use the SUBSTR
function to reduce the length of the column.
For each break group, Oracle Reports prepends its break columns to the ORDER
BY clause of the query. (The only exception to the rule is when the break
column is a formula column.) By minimizing the number of break columns in
your break groups, you minimize the number of columns that are added to the
ORDER BY clause. The fewer the columns added to the ORDER BY clause, the less
the processing that needs to be done when the query runs. The size of the key
that Oracle Reports uses internally to build indexes will also be smaller,
resulting in better performance.
Maximum Rows And Group Filters
------------------------------
Use the Maximum Rows property in the Query property sheet to reduce the number
of records retrieved by the report's queries. When designing a report that
accesses large amounts of data, you may want to restrict the amount of data
retrieved, so the report will run more quickly during testing.
Maximum Rows in the Query property sheet restricts the number of records
fetched by the query. A group filter determines which records to include and
which records to exclude. Since Maximum Rows actually restricts the amount of
data retrieved, it is faster than a group filter in most cases.
If you use a group filter of Last or Conditional, Oracle Reports must retrieve
all of the records in the group before applying the filter criteria. Maximum
Rows or a Filter of First is faster. Typically Maximum Rows is faster than a
Filter of First because it only retrieves as many records as needed. The
performance difference may vary depending upon the ARRAYSIZE you have
specified.
Unused Data Model Objects
-------------------------
Make sure that you remove or suppress any data model objects that are not
actually used in your report. If your data model includes a query that is
only used in certain cases (when a parameter is set to a certain value), you
can conditionally suppress the query with the SRW.SET_MAXROW packaged
procedure. SRW.SET_MAXROW (queryname, 0) will cause the query to fetch no
records.
Unused Frames
-------------
Remove any unnecessary frames from the layout. When Oracle Reports creates a
default layout, it puts frames around virtually everything. This is done to
protect the objects in the frames from being overwritten by other objects in
the output. If you know that the objects in the frames are not in danger of
being overwritten, you can eliminate the frame without adversely affecting
your report output.
The fewer objects in the layout, the fewer objects Oracle Reports must format
at runtime. As a result, performance is better when you reduce the number of
objects in the layout.
Total Number Of Pages
---------------------
Limit your use of total number of pages as the source of fields (Total Logical
Pages). When you use a total number of pages field source, Oracle Reports
must save all of the pages in temporary storage in order to determine the
total number of pages. This can significantly increase the amount of
temporary disk space used by Reports, and the additional writing to files can
slow performance.
Format Triggers
---------------
Place PL/SQL in the Format Trigger of the object with the lowest frequency
possible. PL/SQL in the Format Trigger of a frame instead of a field
typically makes the report run faster.
PL/SQL in Format Triggers is executed for each instance of its object. The
lower the frequency of the object, the fewer times the PL/SQL will be executed
and the faster the report will run.
Oracle Graphics Integration
---------------------------
If an Oracle Graphics display referenced by a report uses some or all of the
same data as the report, pass the data from the report to the display. If the
report and the display use the same data, passing the data reduces the amount
of fetching that needs to be done. If you do not pass the data from the
report to the display, the data is actually fetched twice: once for the report
and once for the display.
