• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1226
  • Last Modified:

Oracle report

What is the best practices to make oracle reports faster

0
alialyosofy
Asked:
alialyosofy
5 Solutions
 
bvanderveenCommented:
1. Make sure your underlying query is optimized.
2. Make sure you have enough resources on the report server.
0
 
annamalai77Commented:
hi there

follow the link. it gives the basic technique's that a developer should follow while developing a report which can run fastter

http://www.dulcian.com/papers/ODTUG%202004_Balcu2_Oracle%20Reports%20Beginner's%20Guide.htm

regards
annamalai
0
 
schwertnerCommented:
To make Oracle Reports faster you have to make faster the query you use.
This means that you have to analyze the query and to provide indexes on it.
Be aware that if you use function like UPPER, LOWER, etc. on indexed columns
the indexes will not be used. Also sorting operations (ORDER BY clause) consumes not
only time, but also disk space in the temporary tablespaces.

The most hidden error is not to do ststistics collection. In this case even you create
enough good constructed indexes they ... are not used by the optimizer, for big
surprise and full table scan is used instead. So you have to ask the DBA to run the statistics collections
often.

A good idea is to place the tables tablespaces and the index tablespaces on different
disk drives (if possible).
0
 
rkogelheCommented:
shwertner is right in that the most common problem is to tune the original query, but here are some other ideas:

1) Try to keep it to one query if possible. Nested queries are going to slow down the process. Better to create a single fast query that you've tested to death.

2) Keep the formatting PL/SQL simple. Try to use your query to create any conditions (colours are chosen by a decode/case in the query). PL/SQL is interpreted and will slow things down.

3) At some point the thing has to be rendered. Lots of embedded graphics or multiple fonts make the engine work a bit harder.

4) Use Oracle's distribution mechanisms to batch-create reports and push them to users

5) Create multiple report servers if necessary

6) set rundebug=NO, recursive_load=NO.

7) Increase ARRAYSIZE to a larger value.

8) If you have LOB data, set LONGCHUNKSIZE to a large vaue

9) Don't use reports COPIES parameter, but instead manipulate it at the printer level.

10) see also

Oracle Application Server Reports Services Publishing Reports to the Web 10g (9.0.4) Part Number B13673-01
"20 Tuning Oracle Reports"

0
 
Mark GeerlingsDatabase AdministratorCommented:
You've received some good suggestions already, but I'll add a few more.

1. Use views (create them if necessary) for your reports, especially if the reports queries involve joins and/or group operators (min, max, etc.) since the database can do these operations without all of the network traffic that is required for the report to do them.

2. In the Data Model, go to the property sheet for each item being retrieved in the query and set Break Order to "None" for all columns that do not need to be included in the "order by" clause, otherwise the Report will include all of these columns in the "order by" clause of the actual SQL query it submits to the database.

3. Do not use formula columns to retrieve additional data from the database for each record - build these into your view(s) instead.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now