<oracleingres@[EMAIL PROTECTED]
> wrote in message
news:7f18f6bf-7f42-4f93-a76d-91a79d5fdc96@[EMAIL PROTECTED]
> Hi
>
> The tkprof re****t 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
|
