Re: Slow Inserts

JApplewhite_at_austin.isd.tenet.edu wrote:
>
> 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

Any idea why you have as many parses as executes ? That's where all the elapsed time is, parsing.

-- 
Regards,

Stephane Faroult
Oriole Software
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Stephane Faroult
  INET: sfaroult_at_oriole.com

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