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@ebisprd2>@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
bdhulipa@ebisprd2>SELECT AVG_COL_LEN,NUM_NULLS FROM DBA_TAB_COL_STATISTICS
WHERE OWNER='UM_DWH_NXT' AND TABLE_NAME='MEMBER_BANK_BAL' AND
COLUMN_NAME='INTERNAL_ACCT_ID';
AVG_COL_LEN NUM_NULLS
----------- ----------
7 0
Also it seems like one block is there in the cache
bdhulipa@ebisprd2>SELECT DATA_OBJECT_ID FROM DBA_OBJECTS WHERE
OWNER='UM_DWH_NXT' AND OBJECT_nAME='XIF3MEMBER_BANK_BALANCE';
DATA_OBJECT_ID
--------------
2181616
bdhulipa@ebisprd1>select count(*) from (select /*+ PARALLEL(8) */ distinct
file#, block# from gv$bh where objd=2181616);
COUNT(*)
----------
1
On Thu, Apr 9, 2020 at 1:42 PM Jonathan Lewis <jlewisoracle@xxxxxxxxx>
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@xxxxxxxxx>
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
