The Parse statistics are what caught my eye. The statement is being
parsed once per execution, which is puzzling because it does use bind
variables. Of more concern, though, is the difference between CPU and
elapsed times for the parsing, indicating that there is a lot of
waiting going on. I'd suggest looking at v$session_event to see what
are the top wait events for the session.
break on sid
col event format a30
set pages 99
select se.event, se.total_waits,
se.time_waited, se.average_wait
from v$session s, v$session_event se
where s.sid = se.sid
and s.username is not null
and s.sid = &sid
order by se.time_waited
/
Run this before and after executing the insert statement, and compute
the delta for each wait event seen.
HTH
Paul Baumgartel
- 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
>
>
> --
> 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).
Do you Yahoo!?
Yahoo! Web Hosting - Let the expert host your site
http://webhosting.yahoo.com
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Paul Baumgartel
INET: treegarden_at_yahoo.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).
Received on Fri Nov 15 2002 - 11:19:32 CST
