tune for INSERT performance?

From: Gregg Sporar <gregg_sporar_at_i2.com>
Date: 1997/12/22
Message-ID: <349EA3F8.4C1220F1@i2.com>#1/1

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;

BEGIN

  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);
  END LOOP; END;
.

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