Solved

Generating a text file using Reports

Posted on 2001-08-24
21
5,447 Views
Last Modified: 2012-05-04
When I try to generate a text file (delimited) using reports 6i, the performance is very poor. The query uses 1 permanent table and 3 temporary tables with outer joins, and the number of records in the result set for this query is 15000 (which is not much). If I spool the same query in sqlplus, it takes 10 seconds, whereas in Reports it takes more than 2 hours.
0
Comment
Question by:masaimara
  • 12
  • 6
  • 3
21 Comments
 
LVL 3

Expert Comment

by:ramkb
Comment Utility

Hi,

It is hard to comment on performance without looking at what you are exactly doing in your Reports.

Here's an article from Oracle on performance tips on Reports.  Read thro' it and see if you can tune your Report.

Cheers,
Ramesh

*********************************************************

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.

*********************************************************
0
 

Author Comment

by:masaimara
Comment Utility
Hi,

This is what I'm doing:

SELECT
DW_PROJS.PROJ_NUMBER,
DW_PROJS.PROJ_NAME,
DW_PROJS.PROJ_MGR,
DW_PROJS.ORG_NAME,
AMOUNT1_TEMP.AMOUNT AMOUNT1,
AMOUNT2_TEMP.AMOUNT AMOUNT2,
AMOUNT3_TEMP.AMOUNT AMOUNT3,
(NVL(AMOUNT1_TEMP.AMOUNT,0) - NVL(AMOUNT2_TEMP.AMOUNT,0) - NVL(AMOUNT3_TEMP.AMOUNT,0)) AMOUNT4
FROM
ICDW.DW_PROJS DW_PROJS,
ICDW.AMOUNT1_TEMP,  
ICDW.AMOUNT2_TEMP,  
ICDW.AMOUNT3_TEMP
WHERE
DW_PROJS.PROJ_TYPE = 'HW Milestone COS'
AND NAPO_PROG &P_NAPO_PROG_TMP
AND OPER_UNIT_ID = :P_OPER_UNIT_ID
AND DW_PROJS.PROJ_NUMBER = AMOUNT1_TEMP.PROJ_NUMBER(+)
AND DW_PROJS.PROJ_NUMBER = AMOUNT2_TEMP.PROJ_NUMBER(+)
AND DW_PROJS.PROJ_NUMBER = AMOUNT3_TEMP.PROJ_NUMBER(+);

My report generates a text file based upon this query. All the tables with the suffix TEMP are temporary tables. The same query runs in 10 secs in sqlplus. In Reports it takes more than 2 hours to generate the text file. I don't know if it has to do with the temporary tables or not.
0
 
LVL 3

Expert Comment

by:ramkb
Comment Utility

Hi,

If the query runs in 10 secs in SQL*Plus and takes more than 2 hours in Reports, most likely the query may not be the problem.

Oracle Reports does a lot more than executing a query.  

It transforms the output of query to Groups & fields, computes user-defined values at each level, computes summary at each repeating frame, page, report and a lot more.

I would suggest you to read thro' each of the troubleshooting tip in the above document and check your report for what went wrong.  I think it is a useful and interesting document not just for troubleshooting, but in designing a new report as well.

This will help you nail down the problem much easier than making a guess/assumption.

Cheers,
Ramesh
0
 
LVL 2

Expert Comment

by:AlbertYou
Comment Utility

Hi

If the report layout is simple,
I suggest you handle the text file output yourself.

In the form or report trigger,say,the AfterReport trigger,
open an explicit cursor to retrive data, and use the
TEXT_IO build-in package
(both available in FORMS and REPORTS) to write output text.

I think this way is simple and reliable in your case.

Hope this helps.

0
 
LVL 2

Expert Comment

by:AlbertYou
Comment Utility

Hi

If you are using a cost-based database,
please also note that the default optimizer mode
in FORMS and SQL*Plus is FIRST_ROWS,
this optimizer mode let you have the best response time.
(Which is required for the interactive tools.)

However, in Reports, the default optimizer mode is
ALL_ROWS, this optimizer mode let you have the best
system through put.

In SQL*Plus, you have to set the optimizer mode to
ALL_ROWS to tune the report SQL performance:

Issue
  ALTER SESSION SET OPTIMIZER_MODE=ALL_ROWS;

or add an optimizer hint in the SQL statement

Select /*+ ALL_ROWS  */
  ....
  FROM ...
 WHERE ...
 ORDER BY ...


0
 

Author Comment

by:masaimara
Comment Utility
Ramesh, Albert, thankyou very much for your ideas. I won't be able to access my application till monday. I will give you my feedabck on monday.

I have tried all the optimizer hints but they didnt do the trick. But i will try the TEXT_IO package.

One other question I have is, is there anyway I can spool a file using SRW.DO_SQL?
0
 

Author Comment

by:masaimara
Comment Utility
Ramesh, Albert, thankyou very much for your ideas. I won't be able to access my application till monday. I will give you my feedabck on monday.

I have tried all the optimizer hints but they didnt do the trick. But i will try the TEXT_IO package.

One other question I have is, is there anyway I can spool a file using SRW.DO_SQL?
0
 
LVL 2

Expert Comment

by:AlbertYou
Comment Utility

Hi

As my knowledge of SRW.DO_SQL ,this instruction
let you "do SQL" but can not perform any output.

0
 

Author Comment

by:masaimara
Comment Utility
We can use all the DML and DDL statements but it gave me an error when I tried to spool a file.
0
 

Author Comment

by:masaimara
Comment Utility
I know what the problem is: When I say delimited as the desformat it hangs on me. It takes a while before it finishes the report. I want to use the no_ff as the desformat. Can you tell me how to get
an excel output with this format.
0
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.

 
LVL 3

Expert Comment

by:ramkb
Comment Utility

Hi,

Sorry, but this looks like a bug.  Look at this article from Oracle.

See if this is what you are experiencing.

- Ramesh


Problem Description
-------------------

You are running a Developer 6.0 report using the new desformat of delimited.  This desformat is used to create a delimited text file suitable for importing into other applications (i.e. MicroSoft Excel, Access, etc.).  Generally, this works fine, but when the query returns
a large amount of records/rows, the reports tool seems to hang.  This hanging has been observed in both Reports runtime and Forms runtime when using run_product to call a report.  Generally, the hanging seems to occur when the number of rows retrieved exceeds 5000 records.
 

Solution Description
--------------------

This problem is related to Bug 974019, DELIMITED FILE GENERATION IN ORACLE REPORTS TAKES MUCH LONGER TIME WITH 100%CPU.  This is a generic issue that occurs on multiple operating systems (Unix, Windows, etc.) and in Reports versions 6.0.5.28 through 6.0.5.35.  The bug was also
present in the initial release of Developer 6i, reports version 6.0.8.8.0.

The fix for this bug was incorporated into Developer 6i, patch 1, reports version 6.0.8.10.0.


Explanation
-----------

If you are running Developer 6i, you can apply patch 1 to resolve the hanging issue for reports with approximately 5000 rows.
This patch is available via Metalink under the Patches Link.

A related bug that affects reports that return approximately 60,000 rows of data is unresolved at this writing, (reference bug 1651284).

If you are running Developer 6.0 and cannot upgrade to Developer 6i, you can use the following workaround.

Instead of running your report to the desformat=delimited, create a character mode report and use the concatenation operator in your SQL statement.  For instance, modify your select statement:

CHANGE FROM: "select ename, job, sal from emp;"

CHANGE TO: "select ename||','||job||','||sal from emp;"

This query will create a comma-delimited text file when run in Oracle Reports.

0
 
LVL 3

Expert Comment

by:ramkb
Comment Utility

Hi again,

Also i think this document might specifically help.

Cheers,
Ramesh



How can a report be designed in Reports versions 3.X or 6.X for use in Excel or other spreadsheet programs?

Solution: CREATE A CHARACTER MODE REPORT

SOLUTION DESCRIPTION:
=====================

Follow these steps to generate Excel format from Oracle Reports 3.X or 6.X:

1.  Create the report as a character mode report by opening the Reports Designer and selecting "create new report" manually.

2.  Click on report properties in the Object Navigator, and set "Design in Character Units" to Yes (under the Character Mode Heading).

3.  In the Object Navigator, expand Data Model; then expand System Parameters, and under the Mode properties, change the initial value to Character.

4.  Go to the data model and create the query.

5.  Use the report wizard and create a simple tabular report without any templates.

6.  Use the default parameter form builder, and create a parameter form that uses the destype, desname, and desformat parameters, by highlighting these fields.

7.  Run the report, and enter the following values:
      destype=file
      desname=c:\temp\test.txt
      desformat=no_ff.

NOTE:  The no_ff.prt is recommended as the desformat because it does not produce any page breaks or form feeds.  This file is located in the ORACLE_HOME/REPORT30/PRINTERS directory.  As an additional option, you could copy the dflt.prt to a new name, such as mydflt.prt, and then modify the file to remove any linefeed or after page control commands.

8.  Then launch Excel, and open the test.txt file.

9.  Do not attempt to open the test.txt file with any other program or the control codes will be embedded in the file.

10.  If you are using Excel 97, it will go through a Text Import Wizard.
     In Step 1 of the Wizard, it asks if you want to import as Fixed Width or Delimited; choose fixed width and press next.  
     In Step 2, adjust the column width if needed.
     In Step 3, accept the default of Column data format as General.

Report data can then be viewed in a spreadsheet format.
0
 

Author Comment

by:masaimara
Comment Utility
Hi Ramesh,
Thanks for the info.

"Design in Character Units" is not working with no_ff - says invalid printer.

I'm not using "Design in Character Units" for another reason also - fixed width. The file has to be delimited.

I'm almost there but I'm struggling with the linesize now. I do not want my lines to wrap which they are. The max length of a concatenated row must be 500 characters. How can you make them not to wrap?
0
 

Author Comment

by:masaimara
Comment Utility
It's printing 80 characters on each line and wrapping the rest of the line. I tried changing the Section Width and Report width in Main Section Property palette under Layout Editor. Can you give me any ideas what to do?
0
 
LVL 3

Accepted Solution

by:
ramkb earned 100 total points
Comment Utility

Hi,

I don't have readily access to Reports to test your problem.  But i found a sample which might help you.  Can you try the following and let me know if this helps..

- Ramesh

PROBLEM DESCRIPTION:
====================
 
How do you generate a comma delimited file using Reports 3.0.5.8.0, and open it in MS Excel without blank spaces appearing between records or at the top of the page?  
 
PROBLEM EXPLANATION:
====================

The spaces appearing on top of the page are inserted due to the margin space.  The blanks between each record may be caused by:

- carriage returns
- report width not being big enough to fit the concatenated data
- field width not being wide enough

Solution: Create a character mode report with a comma concatenated between columns

SOLUTION DESCRIPTION:
=====================

To generate a comma delimited file in Reports 3.0, that you can open correctly in Excel without blank lines, you need to create a character mode report with a query having a comma concatenated between each selected column.  Some modifications need to be made to the margin and report width.

SOLUTION EXPLANATION:
=====================
 
To create this report, take the following steps:

1.  Create query:
      select empno||','||ename||','||job||','||mgr||','||hiredate||','||sal
      ||','||comm||','||deptno test
      from EMP
       (test is just an alias).

2.  System parameters:
      MODE=character
      DESFORMAT=flat

3.  In report Property Palette, set:
      Design In Character Units = YES
      Report Width = 200
      Report Height = 66

4.  Create flat.prt file in $OH\report30\printers directory.  The contents are:
      printer "flat"
      height   66
      width   200
      linefeed      control(J)

5.  Run Report Wizard and in each tab, choose:
      Style=Tabular
      Fields = test    (this is the column alias)
      Labels = 200 (make sure that the column width = 200)   <=== this is what was constricting the width.
     Template = No Template
     Click FINISH.
 
6.  Open the Layout Model, and click on the "margin" icon on top.  This is the 4th icon from the top right hand side of layout.  You will see a solid black line box around the objects.  This is the margin.  Select the top portion of the black line box and drag it all the way to the top, not allowing any margin space.  (This is what caused the blank lines at the top of your data.

7.  Now, run it to file, and open this file in Excel.

0
 

Author Comment

by:masaimara
Comment Utility
Ramesh,
Thanks for the info. I will try and let you know.

masaimara
0
 

Author Comment

by:masaimara
Comment Utility
Ramesh,
Thankyou for the solution. It worked perfect. I wonder where you get all this info.

masaimara
0
 

Author Comment

by:masaimara
Comment Utility
Ramesh,
Thankyou for the solution. It worked perfect. I wonder where you get all this info.

masaimara
0
 

Author Comment

by:masaimara
Comment Utility
Ramesh,
Thankyou for the solution. It worked perfect. I wonder where you get all this info.

masaimara
0
 
LVL 3

Expert Comment

by:ramkb
Comment Utility

Hi masaimara,

Glad that this worked.  I get most of the reference from metalink (Oracle's support site).  You need to register there using your company's license.
The website is http://metalink.oracle.com

Cheers,
Ramesh
0
 

Author Comment

by:masaimara
Comment Utility
I am registered there. I never looked for Reports stuff there. Thanks for letting me know.
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

This article started out as an Experts-Exchange question, which then grew into a quick tip to go along with an IOUG presentation for the Collaborate confernce and then later grew again into a full blown article with expanded functionality and legacy…
Working with Network Access Control Lists in Oracle 11g (part 2) Part 1: http://www.e-e.com/A_8429.html Previously, I introduced the basics of network ACL's including how to create, delete and modify entries to allow and deny access.  For many…
This video shows syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.

728 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