Re: Please help resolving report generation performance problem...

You mentioned a number of things you have tried to tune the database, so it just may (probably is) the SQL.  Rewriting might be the best solution (depending), but you need your stuff now.  If you can't tune the SQL, try tuning the execution.  If you can run a trace of the report session, use tkprof to get an explain plan, perhaps the judicious addition of indexes might help you in the short term.

David A. Barbour
Oracle DBA, OCP
AISD
512-414-1002

 
"Denmark Weatherburne" <denmark_weatherburne@hotmail.com>
Sent by: root@fatcity.com
04/07/2002 10:18 AM PST
Please respond to ORACLE-L

To: Multiple recipients of list ORACLE-L <ORACLE-L@fatcity.com>
cc:
bcc:
Subject: Please help resolving report generation performance problem...

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

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.

--=_mixed 0069A04386256B94_=--

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: 
  INET: DBarbour_at_austin.isd.tenet.edu

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/zip attachment: Report_SQL.zip



text/plain attachment: ReadMe.txt



application/zip attachment: Hourly_Bstat_Estat_Reports.zip