Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Database tuning: appending records via an ODBC connection is slow
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 application.
Questions:
1. For the given database hardware, is this a slow or normal append
time?
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
update?
Turning archiving off or on appears not to have an affect on the append
rate.
Received on Tue Feb 13 2001 - 18:52:03 CST