Oracle-L: Slow Inserts

From: <JApplewhite_at_austin.isd.tenet.edu>
Date: Fri, 15 Nov 2002 08:28:52 -0800
Message-ID: <F001.00504545.20021115082852@fatcity.com>

Oracle 8.1.7.0.0 on Win2k (4 CPU, 4GB RAM)

A 3rd Party app. is experiencing very slow performance on one of our databases. I think I've nailed it down to slow, row-at-a-time inserts. The same app. performs very fast on another DB with LMTs. After switching the tables and indexes in the slow DB to LMT, we still have slow performance.

The extract from the SQL_Trace below is the slow statement. It actually takes about an hour to insert a few hundred rows. You can watch the trace file slowly grow with executions of this statement.

There is only one User hitting this table (with its single index). The table is initially empty, so it's not extending.

Anybody have any ideas as to the cause of this slow Insert activity?

BTW, I ran BStat and EStat during this time and nothing jumps out at me. Also, we ran it with CHOOSE (and fresh statistics) - same slowness.

INSERT INTO "SASI"."W_ENROLL" ("SCHOOLNUMBER" ,"STULINK" ,"FROMDATE" ,
"TODATE" ,"GRADE" ,"TRACK" ,"PEIMSTRACK" ,"PEIMSSCHOOLNUMBER" ,
"ADAELIGIBILITYCODE" ,"ISENTERDATE" )

VALUES
(:1, :2, :3, :4, :5, :6, :7, :8, :9, :10)

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ----------

Parse 2735 5.88 30.00 0 0 0 0
Execute 2735 1.16 1.24 3 2779 8571 2735
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ----------

total 5470 7.04 31.24 3 2779 8571 2735

Misses in library cache during parse: 0
Optimizer goal: RULE
Parsing user id: 399 (TXSRC)

Rows Execution Plan

-------  ---------------------------------------------------
      0  INSERT STATEMENT   GOAL: RULE
----------------------------------------------------------------------------------------------------------------------------------------

TIA. Jack C. Applewhite
Database Administrator
Austin Independent School District
Austin, Texas
512.414.9715 (wk)
512.935.5929 (pager)
JApplewhite_at_austin.isd.tenet.edu

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: 
  INET: JApplewhite_at_austin.isd.tenet.edu

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

Received on Fri Nov 15 2002 - 10:28:52 CST