So long insert ...

From: Olivier TAUPIN <olivier.taupin_at_odaxys.fr>
Date: Fri, 1 Sep 2000 11:34:14 +0200
Message-ID: <8ontpn$3b6$1@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 - 04:34:14 CDT