Database tuning: appending records via an ODBC connection is slow

Database tuning: appending records via an ODBC connection is slow

Post by Some Bod » Thu, 15 Feb 2001 09:52:03



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.

 
 
 

Database tuning: appending records via an ODBC connection is slow

Post by Jim Kenned » Thu, 15 Feb 2001 10:43:43


Do an ODBC trace and see what the application is doing.
Jim

Quote:> 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.


 
 
 

1. append records from flatfile via SQLLDR ?

how does one append records from a flat file to an existing file that is
already populated using SQLLDR?

I know the syntax for INSERTING records into a BLANK table:

LOAD DATA
INFILE '/dbraid/ida1-00/home/applmgr/fixed_assets.dat'
INTO TABLE apps.fixed_assets
(
 dept           INTEGER EXTERNAL TERMINATED BY ',' ,
 asset_no       INTEGER EXTERNAL TERMINATED BY ',' ,
 description    CHAR TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"',
 in_service     DATE "DD-MON-RR" TERMINATED BY ',' ,
 method         CHAR TERMINATED BY ',' ,
 yr_mo          DECIMAL EXTERNAL TERMINATED BY ',' ,
 cost           DECIMAL EXTERNAL TERMINATED BY ',' ,
 amount         DECIMAL EXTERNAL TERMINATED BY ',' ,
 depreciation   DECIMAL EXTERNAL TERMINATED BY ',' ,
 reserve        DECIMAL EXTERNAL TERMINATED BY ',' ,
 percent        DECIMAL EXTERNAL TERMINATED BY ',' ,
 t_f            CHAR
)

but how can I modify this so that additional files can be processed
later?  Thanks, and sorry for the wordiness.

Sent via Deja.com http://www.deja.com/
Before you buy.

2. SYSTEM SEGMENT IS FULL - ALTERING SIZE DOESN'T WORK

3. Slow database connection with new ODBC drivers

4. Oracle Thin Driver Performance

5. Linking Access databases to SQL Server via ODBC connection

6. min( ) function.

7. Database security via a special ODBC connection possible?

8. Question : Clearing Data Control's Recordsource.

9. Database connection via sun.jdbc.odbc.JdbcOdbcDriver

10. Problems appending to Access Tables via ODBC w/ Delphi 1.0

11. Database security via a special ODBC connection possible?

12. Repost - ODBC connection deadly slow odbc only on server, over LAN it's fine

13. sql 2000 iis 5 ado database connection problem -- connections slow