Possible causes:
-space management (this may well be true with your non-default pctused and
pctincrease)
- too many indexes.
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 Fri Sep 01 2000 - 05:02:51 CDT
