Increased Write Speed with Oracle 8i

[duelist]

Hi,

I am a Visual C++ programmer. Recently the company started a project which required us to use an Oracle Database as a data backup. My requirement is to write around 25,000 records per minute into an Oracle 8 database running on a Windows 2000 medium order machine. At the same time we have to run at least five fairly intense queries on the database.

I have attempted to optimise the database for reading and writing as best I can but the best I have managed is ~17,000 writes per minute without the queries.

This is also very resource intensive.

I am also having to use the Oracle ODBC driver to handle the database communication.

Does anyone have any hints as to how to reduce the disk write time or the processor use for Oracle 8 on a Windows machine.

Thanks.

 

[sem]

Try to use raw devices and also use OCI instead of ODBC.

 

[jcote]

First of all, is the program running on the same machine as the DB. If not, you will have a tremendous gain of performance by putting them on the same machine and getting rid of the network.

If your program is in fact running on the DB server, does it issues 25,000 inserts? If that is the case, you could greatly improve performance by providing a stored procedure the data you need to store (in an array for example), and call it. That stored procedure would take the entire data (the array for example), and then do the inserts. That way, all the insertion will be done within Oracle itself, and you'll save all the SQLNEL tchit-tchatting (limiting the calls to SQLNET to the call to the stored procedure).

Of course, there are other issues. For instance, do you have indexes on the table you are inserting to? That will impact greatly the insertion process. Have you consider using SQLLOADER instead, with the unrecoverable options?
Your C program could create the file that SQLLOADER you load. Just a suggestion.

I hope this helps...

 

[raygg]

1. put index tablespace on a separate drive, and at minimum, put the indexes in a separate tablespace
2. consider bitmapping indexes for indexes with low cardinality
3. consider reverse indexes to eliminate hot spots on the btree if the primary key is serial
4. increase size of redo logs to reduce log switches
5. consider multiple listeners and funnelling in the messages oriented to each of the listeners - this is easier if the source might be branch office numbers
6. partition the indexes to reduce full scans of the entire table
7. increase db_block_size to 8k from the oracle 8 default of 2k (this means exporting your entire database, dropping the db, and setting up a new db, and importing the old db)
to reduce I/O.
8. run explain plan for all the queries and updates to see where their performance can be improved
9. rebuild the indexes frequently