Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> tune for INSERT performance?
Iā€™m not new to relational databases, but I am new to administering an Oracle server. Iā€™m having problems getting decent INSERT performance using v7.3.3 (workgroup, not enterprise) on a 200MHz Pentium Pro system that is running NT4.
Specifically, with a table defined as:
COL_A VARCHAR(14) NOT NULL; COL_B VARCHAR(8) NOT NULL; COL_C DATE NOT NULL; COL_D INT NOT NULL; COL_E INT; COL_F INT UNIQUE NOT NULL;
The primary key is defined as COL_A, COL_B, COL_C, and COL_D; there are no indexes defined and no foreign keys referenced. Using SQLPlus, the following test loop takes 44 seconds:
DECLARE
col_a VARCHAR2(14); col_b VARCHAR2(8); col_c DATE; col_d NUMBER; col_e NUMBER;
col_a := 'product'; col_d := 5; col_c := TO_DATE ('20-Dec-97'); FOR i IN 1..4000 LOOP col_b := TO_CHAR (i); col_e := i; INSERT INTO test_table VALUES (col_a, col_b, col_c, col_d, col_e,i);
The same server is also running a (supposedly) lower-end relational database product called SQLBase (v6.1), and it can do the above operation in less than 30 seconds. This is consistent with larger tests that Iā€™ve done: On the same system, Oracle takes about 40% longer to accomplish the same INSERTs as SQLBase. Admittedly, the table structure in the SQLBase version is not exactly the same: COL_F does not exist in the SQLBase version because it is not needed (we use it to hold a row ID value in the Oracle version).
Iā€™ve read through Oracleā€™s tuning information, but I didnā€™t see anything that looked like it would help performance here. Is there something I can tune in the Oracle server configuration to improve INSERT performance?
TIA,
Gregg Sporar
i2 Technologies
Received on Mon Dec 22 1997 - 00:00:00 CST