Solved

Oracle report

Posted on 2004-08-11
8
1,212 Views
Last Modified: 2008-01-09
What is the best practices to make oracle reports faster

0
Comment
Question by:alialyosofy
8 Comments
 
LVL 7

Accepted Solution

by:
bvanderveen earned 100 total points
ID: 11779143
1. Make sure your underlying query is optimized.
2. Make sure you have enough resources on the report server.
0
 
LVL 8

Assisted Solution

by:annamalai77
annamalai77 earned 100 total points
ID: 11780417
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
 
LVL 47

Assisted Solution

by:schwertner
schwertner earned 100 total points
ID: 11781059
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
 
LVL 3

Assisted Solution

by:rkogelhe
rkogelhe earned 100 total points
ID: 11783114
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
 
LVL 34

Assisted Solution

by:Mark Geerlings
Mark Geerlings earned 100 total points
ID: 11783401
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

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Suggested Solutions

Why doesn't the Oracle optimizer use my index? Querying too much data Most Oracle developers know that an index is useful when you can use it to restrict your result set to a small number of the total rows in a table. So, the obvious sideā€¦
Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
Via a live example, show how to take different types of Oracle backups using RMAN.
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.

705 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now