Re: Insert running long

 Hi Jonathan

Thank you for your help - The Average column length for this column is 7 bytes and there are no null values in this column

bdhulipa_at_ebisprd2>_at_IND UM_DWH_NXT.MEMBER_BANK_BAL

Display indexes where table or index name matches %UM_DWH_NXT.MEMBER_BANK_BAL%...

TABLE_OWNER          TABLE_NAME
INDEX_NAME                                                 POS# COLUMN_NAME

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

UM_DWH_NXT           MEMBER_BANK_BAL
XIF3MEMBER_BANK_BALANCE           1 INTERNAL_ACCT_ID

  COUNT(*)

         1

On Thu, Apr 9, 2020 at 1:42 PM Jonathan Lewis <jlewisoracle_at_gmail.com> wrote:

>
> 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