Oracle-L: Please help resolving report generation performance problem...

From: Denmark Weatherburne <denmark_weatherburne_at_hotmail.com>
Date: Sun, 07 Apr 2002 10:18:27 -0800
Message-ID: <F001.0043DFC3.20020407101827@fatcity.com>

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.

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

------=_NextPart_000_56a0_2bd7_120d--

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Denmark Weatherburne
  INET: denmark_weatherburne_at_hotmail.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).



application/x-zip-compressed attachment: Report_SQL.zip



application/x-zip-compressed attachment: Hourly_Bstat_Estat_Reports.zip

Received on Sun Apr 07 2002 - 13:18:27 CDT