"Sybrand Bakker" <postbus_at_sybrandb.demon.nl> wrote in message news:967802461.299.0.pluto.d4ee154e_at_news.demon.nl...
> Possible causes:
> -space management (this may well be true with your non-default pctused and
> pctincrease)
> - too many indexes.
or, not enough indexes?
does this table have a valid unique index or an enabled primary key ?
> Apparently the biggest problem here are the library cache misses, in your
> case about 1 per row inserted and I simply don't know whether that's
> standard.
> The other thing is you seem to be parsing identical statements, but as the
> statements are garbled I can't see whether they are *really* identical.
> You could do the following:
> probe in v$session_events where it is really waiting for. Right now your
> shared pool seems to small.
> You also should determine your library cache hit ratio.
> You could also repeat the operation and post a *complete* trace file, so
> including the recursive sql you now have left out.
> Space management can be distinguished easily enough.
>
> Hth,
>
> Sybrand Bakker, Oracle DBA
>
>
> "Olivier TAUPIN" <olivier.taupin_at_odaxys.fr> wrote in message
> news:8ontpn$3b6$1_at_reader1.fr.uu.net...
> > Here my (big) problem :
> >
> > Server : Sun E3500 , RAM 1 Go (low activity on the server)
> > Oracle : 816 , SGA = 180Mo
> > 400000 rows in the table TBL_UPLOAD_ORDER (PCTFREE=5 , PCTUSED=60 ,
> > PCTINCREASE=0 and no chained rows).
> >
> > The 4 following tkprof results show that my insert statement is very long
> > (especially the first one : 2 minutes for 38 rows !!!)
> > Does it mean 2 minutes of activity or waits+activity ?
> > Can block management take so long time ?
> >
> > Can anybody help me ?
> > Thanks
> >
> > Olivier ( otaupin_at_odaxys.fr )
> >
> >
> > ****
> >
> > insert into TBL_HISTORY_SALE (ID_ADH, NUM_DECL, NUM_LIGNE_DECL,
> > DATE_TRANSFERT_DECL, ID_UTIL_DECL, NUM_DOSSIER, ID_VENTE, ID_STOCK,
> > VALIDE,
> > TRAME)
> > values
> > (:1, :2, :3, :4, :5, :6, :7, :8, :9, :10)
> >
> >
> > call count cpu elapsed disk query current
> > rows
> > ------- ------ -------- ---------- ---------- ---------- ---------- ----
--
> > ----
> > Parse 1 0.14 0.15 0 0 0
> > 0
> > Execute 38 134.20 139.34 0 39 274
> > 38
> > Fetch 0 0.00 0.00 0 0 0
> > 0
> > ------- ------ -------- ---------- ---------- ---------- ---------- ----
--
> > ----
> > total 39 134.34 139.49 0 39 274
> > 38
> >
> > Misses in library cache during parse: 1
> > Misses in library cache during execute: 37
> > Optimizer goal: CHOOSE
> > Parsing user id: 62 (AGRM_CASH_RW)
> >
> > Rows Execution Plan
> > ------- ---------------------------------------------------
> > 0 INSERT STATEMENT GOAL: CHOOSE
> >
> >
> > ****
> >
> > insert into TBL_HISTORY_SALE (ID_ADH, NUM_DECL, NUM_LIGNE_DECL,
> > DATE_TRANSFERT_DECL, ID_UTIL_DECL, NUM_DOSSIER, ID_VENTE, ID_STOCK,
> > VALIDE,
> > TRAME)
> > values
> > (:1, :2, :3, :4, :5, :6, :7, :8, :9, :10)
> >
> >
> > call count cpu elapsed disk query current
> > rows
> > ------- ------ -------- ---------- ---------- ---------- ---------- ----
--
> > ----
> > Parse 1 0.04 0.04 0 0 0
> > 0
> > Execute 132 5.73 5.98 0 144 976
> > 132
> > Fetch 0 0.00 0.00 0 0 0
> > 0
> > ------- ------ -------- ---------- ---------- ---------- ---------- ----
--
> > ----
> > total 133 5.77 6.02 0 144 976
> > 132
> >
> > Misses in library cache during parse: 1
> > Misses in library cache during execute: 131
> > Optimizer goal: CHOOSE
> > Parsing user id: 62 (AGRM_CASH_RW)
> >
> > Rows Execution Plan
> > ------- ---------------------------------------------------
> > 0 INSERT STATEMENT GOAL: CHOOSE
> >
> >
> > ****
> >
> > insert into TBL_HISTORY_SALE (ID_ADH, NUM_DECL, NUM_LIGNE_DECL,
> > DATE_TRANSFERT_DECL, ID_UTIL_DECL, NUM_DOSSIER, ID_VENTE, ID_STOCK,
> > VALIDE,
> > TRAME)
> > values
> > (:1, :2, :3, :4, :5, :6, :7, :8, :9, :10)
> >
> >
> > call count cpu elapsed disk query current
> > rows
> > ------- ------ -------- ---------- ---------- ---------- ---------- ----
--
> > ----
> > Parse 0 0.00 0.00 0 0 0
> > 0
> > Execute 5 4.63 6.48 0 5 35
> > 5
> > Fetch 0 0.00 0.00 0 0 0
> > 0
> > ------- ------ -------- ---------- ---------- ---------- ---------- ----
--
> > ----
> > total 5 4.63 6.48 0 5 35
> > 5
> >
> > Misses in library cache during parse: 0
> > Misses in library cache during execute: 5
> > Optimizer goal: CHOOSE
> > Parsing user id: 62 (AGRM_CASH_RW)
> >
> > Rows Execution Plan
> > ------- ---------------------------------------------------
> > 0 INSERT STATEMENT GOAL: CHOOSE
> >
> >
> > ****
> >
> > insert into TBL_HISTORY_SALE (ID_ADH, NUM_DECL, NUM_LIGNE_DECL,
> > DATE_TRANSFERT_DECL, ID_UTIL_DECL, NUM_DOSSIER, ID_VENTE, ID_STOCK,
> > VALIDE,
> > TRAME)
> > values
> > (:1, :2, :3, :4, :5, :6, :7, :8, :9, :10)
> >
> >
> > call count cpu elapsed disk query current
> > rows
> > ------- ------ -------- ---------- ---------- ---------- ---------- ----
--
> > ----
> > Parse 0 0.00 0.00 0 0 0
> > 0
> > Execute 18 14.05 16.47 0 19 126
> > 18
> > Fetch 0 0.00 0.00 0 0 0
> > 0
> > ------- ------ -------- ---------- ---------- ---------- ---------- ----
--
> > ----
> > total 18 14.05 16.47 0 19 126
> > 18
> >
> > Misses in library cache during parse: 0
> > Misses in library cache during execute: 18
> > Optimizer goal: CHOOSE
> > Parsing user id: 62 (AGRM_CASH_RW)
> >
> > Rows Execution Plan
> > ------- ---------------------------------------------------
> > 0 INSERT STATEMENT GOAL: CHOOSE
> >
> >
> >
>
>
>
Received on Sat Sep 02 2000 - 20:28:36 CDT
