I'm faced with the following problem:
A commercial application is appending records into an Oracle database
via ODBC. the database consists of one master and one detail table.
The app is running on WinNT and connects to an Oracle 8.1.6 running on
Sun Solaris 2.6. The Sun has 256 MBytes of RAM and one physical disk.
I'm getting very slow response times: it takes about 0.25 seconds to
append each master record and an average of five detail records to be
written. The master record is rather large - 27 fields taking about 2
KBytes, but each detail is small, about 500 bytes per record in four
fields. I have two sequences and two triggers to generate the primary
keys for each table as the records are inserted. Since this is a
commercial application, I can't change this structure.
No other users are using the database. The Sun is dedicated to this
1. For the given database hardware, is this a slow or normal append
2. How can I speed up appends?
I want to make the best use of memory since I can't add any. Can I tune
the the SGA for better performance?
Is there a way (perhaps via 'explain plan') that I could investigate how
appends are being done that might help me troubleshoot this problem?
Could the ODBC append be somehow doing a full table scan before each
Turning archiving off or on appears not to have an affect on the append