Re: tune for INSERT performance?

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: 1997/12/23
Message-ID: <34a0a5b3.33035572@inet16>#1/1

On Mon, 22 Dec 1997 11:31:36 -0600, Gregg Sporar <gregg_sporar_at_i2.com> wrote:

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

Actaully, there are at least 2 indexes on the table -- one on (col_a,col_b,col_c,col_d) and one on (col_f). Unique/primary key constraints implicitly create indexes.

You should look at your log files to see how large (or small) they are. You might have the default 512k log files which are way too small. Look in the file \orant\rdbms73\trace\{$ORACLE_SID}alrt.ora and see if you have any "checkpoint not complete..." type of messages. These indicate that log files are not sized appropriately for the amount of work you want to do. Try increasing the log files.

>
>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
>
>
>
 

Thomas Kyte
tkyte_at_us.oracle.com
Oracle Government
Bethesda MD  

http://govt.us.oracle.com/ -- downloadable utilities  

---

Opinions are mine and do not necessarily reflect those of Oracle Corporation  

Anti-Anti Spam Msg: if you want an answer emailed to you, you have to make it easy to get email to you. Any bounced email will be treated the same way i treat SPAM-- I delete it. Received on Tue Dec 23 1997 - 00:00:00 CST