Re: Insert running long

Correct - your assumption of row by row is false.

Thinking back on previous email I've sent - I suggested your index MIGHT be running at 50% efficiency and asked you to let us know the avg_col_length and num_nulls for the column in the index: you haven't supplied that information so there's no way to tell what the effect of a rebuild might be.

You've also reported your index stats as showing 11 million leaf blocks, and most of the I/O being for the index; we've also estimated one I/O for every two rows inserted. If you rebuild the index and it drops to half the size (maybe only temporarily) then it's still 5,5M blocks and that might not be anything like low enough to make a significant dent in the I/O. Another piece of information you should dig up is the number of distinct blocks there are in the cache that belong to the index - this might give you a hint about whether a rebuilt index or a bigger cache would help.

select count(*) from (select distinct file#, block# from v$bh where objd = {data_object_id of index})

If this is one of the partitioned indexes you'll have to do this for each partition in turn

Regards
Jonathan Lewis

On Thu, Apr 9, 2020 at 5:34 PM Bhavani Dhulipalla <bhavanidba6_at_gmail.com> wrote:

> Thank you Jonathon-
>
> Since it is inserting 400 rows per call and my initial assumption that it
> is being done row by row processing is false?
>
> Also as you said , oracle is doing 8 buffer gets per row and 0.5 disk
> reads per row , Can I rebuild the index to eliminate the scattered inserts
> in the index?
>
> Thank you
> Bhavani
>
>

--
http://www.freelists.org/webpage/oracle-l