This discussion is archived
11 Replies Latest reply: Apr 14, 2011 1:37 AM by Hemant K Chitale RSS

blocks_gets_per_access is too high

844366 Newbie
Currently Being Moderated
hello,

our development database is oracle 10gR2..


i analyzed an index
analyze index IX_VISIT compute statistics;
analyze index IX_VISIT validate structure;
then i queried the index_stats
select 
the out it gave;
NAME    HEIGHT  BLOCKS    BLKS_GETS_PER_ACCESS          LF_BLKS      DEL_LF_ROWS            
---------- ------------  ------------ ---------------------- ----------------------  ---------------------- ---------------------- 
IX_VISIT   2          64        *110.7654320987654320*            53                     0                      

1 rows selected
since blks_gets_per_access is too hight i need to rebuild the index.hence i fired an index rebuilding query and again analyzed the statistics as
alter index IX_VISIT rebuild;
analyze index IX_VISIT compute statistics;
analyze index IX_VISIT validate structure; 
but blks_gets_per_access remained same...
NAME    HEIGHT  BLOCKS    BLKS_GETS_PER_ACCESS          LF_BLKS      DEL_LF_ROWS            
---------- ------------  ------------ ---------------------- ----------------------  ---------------------- ---------------------- 
IX_VISIT   2          64        *110.7654320987654320*             53                     0                      
why the BLKS_GETS_PER_ACCESS is not getting reduced..

i want to reduced it below 5..

guys your help would be of much appreciation..

thank you...
  • 1. Re: blocks_gets_per_access is too high
    Nicolas.Gasparotto Oracle ACE
    Currently Being Moderated
    841363 wrote:
    hello,

    our development database is oracle 10gR2..


    i analyzed an index
    analyze index IX_VISIT compute statistics;
    analyze index IX_VISIT validate structure;
    What about DBMS_STATS.GATHER_INDEX_STATS instead of the first obsolete command line ?
    i want to reduced it below 5..
    Where that number 5 is coming from ?

    Nicolas.
  • 2. Re: blocks_gets_per_access is too high
    sybrand_b Guru
    Currently Being Moderated
    First of all:
    analyze index compute statistics is deprecated, and needs to be replaced by dbms_stats.

    Secondly:
    Your index is a non-unique index, which can be seen from the fact blocks_get_per_access isn't 4 (or 3, in your case as the height of this index is 2)
    The blocks_gets_per_access column signifies the number of blocks Oracle has to read to get all the records with that key.
    Apparently the index is on a column with low cardinality, and likely it isn't even used because of this reason (the table must be small as the height of the index is only 2).

    Evidently, rebuilding the index won't reduce the blocks_get_per_access, as rebuilding an index doesn't reduce the number of keys.
    The idea you want it to be reduced below 5 is very strange: that is not going to happen, as the value is determined by the number of records for that key.

    -----------------
    Sybrand Bakker
    Senior Oracle DBA
  • 3. Re: blocks_gets_per_access is too high
    311441 Employee ACE
    Currently Being Moderated
    Unfortunately, you're learning the hard way not to believe everything you read ...

    As others have stated, rebuilding an index because it has a blks_gets_per_access greater than 5 and hoping that the rebuild will reduce this to less than 5 is simply nonsensical, as it signifies how many blocks are required to read all the rows with a particular index key (on average), which for a non-unique index could be anything. And as you've now seen, rebuilding the index will likely do precisely nothing to change things ...

    Therefore, any rebuild criteria that suggests otherwise is just plain dumb.

    Unfortunately, I think I know where you've got this awful advice:

    http://www.remote-dba.net/t_tuning_index_rebuilding.htm

    where it states:

    "We might want to rebuild an index if the block gets per access is greater than 5, since excessive block gets indicate a fragmented B-tree structure."

    Don Burleson is nothing if not consistent, although note sometimes the so-called magical number is 10:

    http://www.dba-oracle.com/art_index1.htm or http://www.hardline.ru/2/14/1016/ or in page 727 of his Oracle Tuning: The Definitive Reference book (http://books.google.com/books?id=hiOhVSO-EFcC&pg=PA727#v=onepage&q&f=false)

    where it states:

    "The number of "gets" per access refers to the amount of logical I/O that is required to fetch a row with the index. As you may know, a logical "get" is not necessarily a physical I/O since much of the index may reside in the Oracle buffer cache. However, any SAP index with a number greater than 10 would probably benefit from an index rebuild."


    Regardless, 5, 10, 42, it's all rubbish.

    Cheers

    Richard Foote
    http://richardfoote.wordpress.com/
  • 4. Re: blocks_gets_per_access is too high
    844366 Newbie
    Currently Being Moderated
    thank you sir.

    yes you are correct..

    i referred to the same link as you mentioned...



    since i am new to the performance tuning , i was just googling around and found that criteria for rebuilding that index..



    so any reliable link on performance tuning would be of good help

    thanks all
  • 5. Re: blocks_gets_per_access is too high
    Hemant K Chitale Oracle ACE
    Currently Being Moderated
    Even the standard documentation isn't clear yet.

    http://download.oracle.com/docs/cd/E11882_01/server.112/e17110/statviews_5138.htm

    "+Expected number of consistent mode block reads per row, assuming that a randomly chosen row is accessed using the index. Used to calculate the number of consistent reads that will occur during an index scan.+"

    ".. reads per row ..." it says and not "... reads per key value".


    Hemant K Chitale
  • 6. Re: blocks_gets_per_access is too high
    311441 Employee ACE
    Currently Being Moderated
    Hi Hemant

    I agree it's certainly not clear and something a newbie might get wrong to start with.

    However, the OP now has a clearer understanding of what it really means and won't make the same mistake of rebuilding indexes just because the blks_gets_per_access is greater than some arbitrary value.

    Doesn't really excuse someone though from making the same mistake again and again and again, year after year after year, unnecessarily rebuilding the same indexes week after week after week ...

    Cheers

    Richard Foote
    http://richardfoote.wordpress.com/
  • 7. Re: blocks_gets_per_access is too high
    Jonathan Lewis Oracle ACE Director
    Currently Being Moderated
    Hemant K Chitale wrote:
    Even the standard documentation isn't clear yet.

    http://download.oracle.com/docs/cd/E11882_01/server.112/e17110/statviews_5138.htm

    "+Expected number of consistent mode block reads per row, assuming that a randomly chosen row is accessed using the index. Used to calculate the number of consistent reads that will occur during an index scan.+"

    ".. reads per row ..." it says and not "... reads per key value".
    Hemant,

    On one hand the documentation is close to correct, on the other hand it is completely wrong.
    As I pointed out in "Practical Oracle 8i" this column is:

    <ul>
    An indication of how much work will have to be done to get a single specific row from the table using this index.  This is basically "Height (rows_per_key + 1)/2".+ </ul>

    So the "expected number of consistent block reads per row" is roughly correct - though not intuitively obvious. If you want to pick a row at random when the average number of rows per key is N then, on average, the number of rows you will visit for a given key value before finding the right one blevel plus N/2 before you find the right one - hence the result.

    On the other hand, index_stats is NOT used for any calculation of cost - so that comment is rubbish.

    Regards
    Jonathan Lewis
  • 8. Re: blocks_gets_per_access is too high
    jgarry Guru
    Currently Being Moderated
    Regardless, 5, 10, 42, it's all rubbish.
    [url http://42opus.com/v6n2/42reasons]42??? Blasphemy!
  • 9. Re: blocks_gets_per_access is too high
    Hemant K Chitale Oracle ACE
    Currently Being Moderated
    If you want to pick a row at random when the average number of rows per key is N then, on average, the number of rows you will visit for a given key value before finding the right one blevel plus N/2 before you find the right one - hence the result.
    How do I interpret the "BLKS_GETS_PER_ACCESS" of 1315 here :
    SQL> create table my_obj_list as select owner, object_id, object_name from dba_objects;
    
    Table created.
    
    SQL> create index mhy_obj_list_owner_ndx on my_obj_list(owner);
    
    Index created.
    
    SQL> select count(*) from my_obj_list where owner = 'SYS';
    
      COUNT(*)
    ----------
         22459
    
    SQL> /
    
      COUNT(*)
    ----------
         22459
    
    SQL> exec dbms_stats.gather_table_stats('','MY_OBJ_LIST',estimate_percent=>100);
    
    PL/SQL procedure successfully completed.
    
    SQL> select num_rows from user_tables where table_name  ='MY_OBJ_LIST';
    
      NUM_ROWS
    ----------
         47271
    
    SQL> select num_distinct from user_tab_columns where table_name = 'MY_OBJ_LIST' and column_name = 'OWNER';
    
    NUM_DISTINCT
    ------------
              18
    
    SQL> select num_rows from user_indexes where index_name = upper('mhy_obj_list_owner_ndx');
    
      NUM_ROWS
    ----------
         47271
    
    SQL> analyze index mhy_obj_list_owner_ndx validate structure;
    
    Index analyzed.
    
    SQL> select * from index_stats;
    
        HEIGHT     BLOCKS NAME
    ---------- ---------- ------------------------------
    PARTITION_NAME                    LF_ROWS    LF_BLKS LF_ROWS_LEN LF_BLK_LEN
    ------------------------------ ---------- ---------- ----------- ----------
       BR_ROWS    BR_BLKS BR_ROWS_LEN BR_BLK_LEN DEL_LF_ROWS DEL_LF_ROWS_LEN
    ---------- ---------- ----------- ---------- ----------- ---------------
    DISTINCT_KEYS MOST_REPEATED_KEY BTREE_SPACE USED_SPACE   PCT_USED ROWS_PER_KEY
    ------------- ----------------- ----------- ---------- ---------- ------------
    BLKS_GETS_PER_ACCESS   PRE_ROWS PRE_ROWS_LEN OPT_CMPR_COUNT OPT_CMPR_PCTSAVE
    -------------------- ---------- ------------ -------------- ----------------
             2        120 MHY_OBJ_LIST_OWNER_NDX
                                        47271        109      780143       7996
           108          1        2011       8028           0               0
               18             22459      879592     782154         89   2626.16667
              1315.58333          0            0              1               33
    
    
    SQL>
    Is 1315 supposedly the number of blocks that I will visit to retrieve a single row (any row -- e.g. one where owner is "HEMANT" or one where owner is "SYS") ?
    And if I were to use the index to do an Index Range Scan to retrieve all 22,459 owner='SYS' rows, how many blocks do I expect to access -- whether I read only the Index alone -- e.g. in this case below :
    SQL> explain plan for select count(*) from my_obj_list where owner = 'SYS';
    
    Explained.
    
    SQL> select * from table(dbms_xplan.display);
    
    PLAN_TABLE_OUTPUT
    ------------------------------------------------------------------------------------------------------------------------------------
    Plan hash value: 1240843314
    
    ------------------------------------------------------------------------------------------------
    | Id  | Operation             | Name                   | Rows  | Bytes | Cost (%CPU)| Time     |
    ------------------------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT      |                        |     1 |     6 |    17   (0)| 00:00:01 |
    |   1 |  SORT AGGREGATE       |                        |     1 |     6 |            |          |
    |*  2 |   INDEX FAST FULL SCAN| MHY_OBJ_LIST_OWNER_NDX | 22459 |   131K|    17   (0)| 00:00:01 |
    ------------------------------------------------------------------------------------------------
    
    Predicate Information (identified by operation id):
    ---------------------------------------------------
    
       2 - filter("OWNER"='SYS')
    
    14 rows selected.
    
    SQL>
    Although the number of index blocks for a key value can wildly vary :
    SQL> select count(*) from my_obj_list where owner = 'SYS';
    
      COUNT(*)
    ----------
         22459
    
    
    Execution Plan
    ----------------------------------------------------------
    Plan hash value: 1240843314
    
    ------------------------------------------------------------------------------------------------
    | Id  | Operation             | Name                   | Rows  | Bytes | Cost (%CPU)| Time     |
    ------------------------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT      |                        |     1 |     6 |    17   (0)| 00:00:01 |
    |   1 |  SORT AGGREGATE       |                        |     1 |     6 |            |          |
    |*  2 |   INDEX FAST FULL SCAN| MHY_OBJ_LIST_OWNER_NDX | 22459 |   131K|    17   (0)| 00:00:01 |
    ------------------------------------------------------------------------------------------------
    
    Predicate Information (identified by operation id):
    ---------------------------------------------------
    
       2 - filter("OWNER"='SYS')
    
    
    Statistics
    ----------------------------------------------------------
              0  recursive calls
              0  db block gets
            116  consistent gets
              0  physical reads
              0  redo size
            413  bytes sent via SQL*Net to client
            385  bytes received via SQL*Net from client
              2  SQL*Net roundtrips to/from client
              0  sorts (memory)
              0  sorts (disk)
              1  rows processed
    
    SQL>SQL> select count(*) from my_obj_list where owner = 'HEMANT';
    
      COUNT(*)
    ----------
           485
    
    
    Execution Plan
    ----------------------------------------------------------
    Plan hash value: 2630941097
    
    --------------------------------------------------------------------------------------------
    | Id  | Operation         | Name                   | Rows  | Bytes | Cost (%CPU)| Time     |
    --------------------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT  |                        |     1 |     6 |     2   (0)| 00:00:01 |
    |   1 |  SORT AGGREGATE   |                        |     1 |     6 |            |          |
    |*  2 |   INDEX RANGE SCAN| MHY_OBJ_LIST_OWNER_NDX |   485 |  2910 |     2   (0)| 00:00:01 |
    --------------------------------------------------------------------------------------------
    
    Predicate Information (identified by operation id):
    ---------------------------------------------------
    
       2 - access("OWNER"='HEMANT')
    
    
    Statistics
    ----------------------------------------------------------
              0  recursive calls
              0  db block gets
              3  consistent gets
              0  physical reads
              0  redo size
            412  bytes sent via SQL*Net to client
            385  bytes received via SQL*Net from client
              2  SQL*Net roundtrips to/from client
              0  sorts (memory)
              0  sorts (disk)
              1  rows processed
    
    SQL>
    Therefore :
    On the other hand, index_stats is NOT used for any calculation of cost - so that comment is rubbish.
    because the columns in index_stats don't necessarily appear in USER_TAB_COL_STATISTICS and USER_INDEXES and USER_IND_STATISTICS -- which are what the Optimizer uses when costing for a row.

    Hemant K Chitale

    Edited by: Hemant K Chitale on Apr 14, 2011 9:46 AM

    Edited by: Hemant K Chitale on Apr 14, 2011 9:49 AM

    Edited by: Hemant K Chitale on Apr 14, 2011 9:51 AM
  • 10. Re: blocks_gets_per_access is too high
    311441 Employee ACE
    Currently Being Moderated
    Hemant K Chitale wrote:
    If you want to pick a row at random when the average number of rows per key is N then, on average, the number of rows you will visit for a given key value before finding the right one blevel plus N/2 before you find the right one - hence the result.
    How do I interpret the "BLKS_GETS_PER_ACCESS" of 1315 here :
    Hi Hemant

    You have 47271 index row entries and you have 18 distinct key values.

    Therefore, if you want to know how much work needs to be performed to get a specific row of interest:

    You have 47271/18 = 2626.1667 index entries on average per indexed key value.

    So Oracle makes a few very broad, easy to calculate, general assumptions with regard the blocks you need to visit to get a specific row of interest. It says you need to read the index (height) + an average 1/2 of these rows (+1) to get the specific row you might want. So that's 2 + (2626.1667 + 1)/2 = 1315.5833.

    So on average, you need to access 1315.5833 blocks to get to the actual row that is of interest.

    However, this number is just a very very rough and potentially grossly wrong estimate, as are many averages that try to summarise an index with a single number.

    It of course assumes even distribution of your 18 distinct values. For some index values, this figure could be way too small or too large.

    It assumes you only need to read 1 index leaf block when for larger scans it could be many.

    And most important of all, it doesn't take into consideration the Clustering Factor of the index (as this is beyond of the scope of the validate structure to calculate), which means you could potentially access and perform far fewer consistent gets to get to a specific row of interest.

    I've always kinda interpreted BLKS_GETS_PER_ACCESS as being the very rough number of blocks that need to be accessed for a specific key value, assuming an average Clustering Factor (which would be approximately 1/2 the number of index entries within the index as well).

    The numbers work out practically the same. The total index entries is 47271 so an average CF would be 47271/2 = 23635.5. You have 18 distinct values so to get all rows for a given index entry on average would be 23635.5/18 + 2 for index height which equates to 1315.0833, which is only out fractionally due to the additional 1 that is added in the previous formula.

    But of course if the CF is way different, then the actual blocks that need to be visited will likewise be way different for a given indexed value. BLKS_GETS_PER_ACCESS is therefore just a simple guesstimate or guide.

    However (back to the OP), an index rebuild will make no difference (except in the very unlikely event of it reducing the index height when it might go down by 1), so any requirement that it should be less than 5 or 10 or whatever else rebuild the index is plain dumb and wrong. It's similar to saying rebuild the index if the CF is too high.

    Cheers

    Richard Foote
    http://richardfoote.wordpress.com/
  • 11. Re: blocks_gets_per_access is too high
    Hemant K Chitale Oracle ACE
    Currently Being Moderated
    I think that I am beginning to understand how Oracle calculates this figure.

    I might access 1 block (if I am lucky enough to go for the right value) OR I might access 2626 blocks (if I am very unlucky). So, the average is 1313.

    I agree with you and Jonathan. It is unreal , silly or rubbish depending on how you look at it !

    Thanks.


    Hemant K Chitale

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points