Re: Inserts and db file sequential reads

<oracleingres_at_yahoo.com> wrote in message news:7f18f6bf-7f42-4f93-a76d-91a79d5fdc96_at_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

Is every relevant block of every relevant index likely to be in memory before you start ? If not then you have to do some "db file sequential reads" to get them into memory before you can add the index entries.

Can every relevant block of every relevant index fit (easily) into the available memory while everything else is going on ? If not you may find that over a period of time you have to re-read the blocks (although, if the /*+ append */ hint works in this case, this should not be necessary because of the way that Oracle pre-sorts each index's entries before inserting them when doing an /*+ append */).

I note that there are no waits for direct path inserts, which is an indicator (though not proof) that the append hint was not honoured.

-- 
Regards

Jonathan Lewis
http://jonathanlewis.wordpress.com

Author: Cost Based Oracle: Fundamentals
http://www.jlcomp.demon.co.uk/cbo_book/ind_book.html

The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html