Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> Re:Please help resolving report generation performance probl

Re:Please help resolving report generation performance probl

From: <dgoulet_at_vicr.com>
Date: Mon, 08 Apr 2002 06:18:22 -0800
Message-ID: <F001.0043E3ED.20020408061822@fatcity.com>


Three words: TUNE THE SQL.

Database performance is 80% SQL Tuning and 20% database tuning. It would sound like you've got a statement or two that is doing a whole bunch of full table scans, probably somewhere inside a nested loop.

Take a look inside $ORACLE_HOME\sqlplus\admin for the plustrce.sql file. If you've no tuning tools it will help.

Dick Goulet

____________________Reply Separator____________________
Author: "Denmark Weatherburne" <denmark_weatherburne_at_hotmail.com>
Date:       4/7/2002 10:18 AM

Hi DBA's,

I've been trying to isolate the bottleneck with our Oracle database. I work as an Oracle DBA for the Government of a developing country (Belize). Recently, as it is income tax time, the department has to reconcile all witholdings by the employer with their payment receipt records. This involves some data entry and a report generation by employer (witholder) which lists all witholdings by each employee. This report can generate lots of pages depending on the number of employees. In some cases, the report has to be run overnight, as it takes too long (several hours) to generate.
I've tried giving more resources to Oracle. I've tried creating a copy of the production database on another machine to use only for generating reports. I've increased the size and number of rollback segments. I've tuned some parameters. However, I have not observed any significant improvement in the report generation performance. I know tuning the SQL might be required, however, I don't have much experience in this area. The SQL statemements were written by consultants who have long left. We do have the source code though.

We are running Oracle 8.0.5.2.1 on NT 4.0 The NT server is a Dell 4400 with Dual CPU and 1GB RAM We are using hardware RAID 5.
Our database is OLTP with reporting.
It is a small database (exported data is about 150 MB).

I would appreciate your recommendations and advice.

Thanks in advance,

Denmark Weatherburne



Chat with friends online, try MSN Messenger: http://messenger.msn.com

The previous attachment was filtered out by the ListGuru mailing software at fatcity.com because binary attachments are not appropriate for mailing lists. If you want a copy of the attachment which was removed, contact the sender directly and ask for it to be sent to you by private E-mail.

This warning is inserted into all messages containing binary attachments which have been removed by ListGuru. If you have questions about this message, contact Postmaster_at_fatcity.com for clarification.   


The previous attachment was filtered out by the ListGuru mailing software at fatcity.com because binary attachments are not appropriate for mailing lists. If you want a copy of the attachment which was removed, contact the sender directly and ask for it to be sent to you by private E-mail.

This warning is inserted into all messages containing binary attachments which have been removed by ListGuru. If you have questions about this message, contact Postmaster_at_fatcity.com for clarification.


Content-Type: text/plain; name="ReadMe.txt"; charset="us-ascii" Content-Transfer-Encoding: 7bit

The previous attachment was filtered out by the ListGuru mailing software at fatcity.com because binary attachments are not appropriate for mailing lists. If you want a copy of the attachment which was removed, contact the sender directly and ask for it to be sent to you by private E-mail.

This warning is inserted into all messages containing binary attachments which have been removed by ListGuru. If you have questions about this message, contact Postmaster_at_fatcity.com for clarification.

--IMA.Boundary.38917281010--

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: 
  INET: dgoulet_at_vicr.com

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

Received on Mon Apr 08 2002 - 09:18:22 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US