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" <[EMAIL PROTECTED]>
Sent by: [EMAIL PROTECTED]
04/07/2002 10:18 AM PST
Please respond to ORACLE-L
To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]>
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
Report_SQL.zip
Description: Zip archive
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 [EMAIL PROTECTED] 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 [EMAIL PROTECTED] for clarification.
Hourly_Bstat_Estat_Reports.zip
Description: Zip archive
