Inserts and db file sequential reads

From: <oracleingres_at_yahoo.com>
Date: Fri, 2 May 2008 15:52:55 -0700 (PDT)
Message-ID: <7f18f6bf-7f42-4f93-a76d-91a79d5fdc96@f24g2000prh.googlegroups.com>

Hi

The tkprof report shows that the following insert operation was waited substantially for db file sequential read. from the raw trace file it shows that the blocks it waited for are related to couple of Index segments one is a unique key.

The table is partitioned on oprn_dt column and the indexes are all local prefixed indexes.

I am not very sure how the insert operation is waiting for index reads. There are no Referential integrity constraints on this table. The procedure is doing a bulk fetch from a Global Temp Table into this target table.

INSERT /*+ append */ INTO OPERATION_DTLS VALUES  

(:B1 ,:B2 ,:B3 ,:B4 ,:B5 ,:B6 ,:B7 ,:B8 ,:B9 ,:B10 ,:B11 ,:B12 ,:B13 ,:B14 ,


  :B15 ,:B16 ,:B17 ,:B18 ,:B19 ,:B20 ,:B21 ,:B22 ,:B23 ,:B24 ,:B25 ,:B26 ,

:B27 ,:B28 ,:B29 ,:B30 ,:B31 ,:B32 ,:B33 ,:B34 ,:B35 ,:B36 ,:B37 ,:B38 ,
:B39 ,:B40 ,:B41 ,:B42 ,:B43 ,:B44 ,:B45 ,:B46 ,:B47 )

call     count       cpu    elapsed       disk      query
current        rows

------- ------ -------- ---------- ---------- ---------- ----------

---

Parse        0      0.00       0.00          0          0
0           0
Execute     19     39.90     184.88      11833      36926
1102079       92524
Fetch        0      0.00       0.00          0          0
0           0

------- ------ -------- ---------- ---------- ---------- ----------

---

total       19     39.90     184.88      11833      36926
1102079       92524

Misses in library cache during parse: 0
Misses in library cache during execute: 2 Optimizer mode: ALL_ROWS
Parsing user id: 228 (recursive depth: 1)

Elapsed times include waiting on following events:

  Event waited on                             Times   Max. Wait  Total

Waited

• Waited ----------

  ---

  library cache lock 1 1.86 1.86 db file sequential read 11835 0.53 142.44 log file switch completion 7 0.13 0.35 control file sequential read 13 0.00 0.02 KSV master wait 7 0.06 0.12 Data file init write 188 0.03 0.44 db file single write 1 0.00 0.00 control file parallel write 3 0.00 0.00 rdbms ipc reply 1 0.02 0.02 latch: cache buffers chains 1 0.00 0.00

Thanks for your insight,

Sam Received on Fri May 02 2008 - 17:52:55 CDT