This discussion is archived
5 Replies Latest reply: Apr 8, 2011 8:51 AM by Mark D Powell RSS

Avg num of rows per blk

Chilakamarthi Newbie
Currently Being Moderated
HI Can any one please point to me a document or a way to calculate the average number of rows per block in oralce 10.2.0.3
  • 1. Re: Avg num of rows per blk
    sb92075 Guru
    Currently Being Moderated
    http://download.oracle.com/docs/cd/B19306_01/appdev.102/b14258/d_stats.htm
  • 2. Re: Avg num of rows per blk
    Aman.... Oracle ACE
    Currently Being Moderated
    What exactly is the reason that you are trying to find out? The average number of rows would depend on number of things like row length, block size etc.

    HTH
    Aman....
  • 3. Re: Avg num of rows per blk
    Mark D Powell Guru
    Currently Being Moderated
    I think Aman asked a good question but here is one method for determing rows per block for a table with current statistics:
    SQL> select num_rows/blocks "Rows Per Block" from dba_tables
      2  where table_name = 'XXXX_XXXXXX';
    
    Rows Per Block
    --------------
        10.8336414
    If you are trying to estimate the rows per block before the table is created or at least populated then take what you expect to be the average row size and divide the block size minus overhead by this figure. For overhead you might just want to use an average of 150 rather than calculate one out. (8192 - 150) / est row length = rows per block


    HTH -- Mark D Powell --
  • 4. Re: Avg num of rows per blk
    orawiss Oracle ACE
    Currently Being Moderated
    Jonathan Lewis has written a post about that :

    http://jonathanlewis.wordpress.com/2011/04/05/rows-per-block/
  • 5. Re: Avg num of rows per blk
    Mark D Powell Guru
    Currently Being Moderated
    This is a little late but for anyone interested here are some results from Jonathan's query plus for comparison purposes I added a query to do the blocks/num_rows calculation and I added a compute avg on avg_rows even though this is not 'proper' way to calculate the average rows per block. The target table is 4.2G in size.
    Enter value for tbl_name: xxx_xxxxx
    
     TWENTIETH   MIN_ROWS   MAX_ROWS TOT_BLOCKS   TOT_ROWS AVG_ROWS
    ---------- ---------- ---------- ---------- ---------- --------
             1          1          2       9439      10133     1.07
             2          3          4        972       3426     3.52
             3          5          6       1449       8101     5.59
             4          7          8       2425      18349     7.57
             5          9         10       4194      40204     9.59
             6         11         12       9946     115661    11.63
             7         13         14      28703     390941    13.62
             8         15         16      70452    1100064    15.61
             9         17         17      47960     815320    17.00
            10         18         18      52212     939816    18.00
            11         19         19      85585    1626115    19.00
            12         20         20      70143    1402860    20.00
            13         21         21      29570     620970    21.00
            14         22         22      23561     518342    22.00
            15         23         23      28524     656052    23.00
            16         24         24      20786     498864    24.00
            17         25         25       2291      57275    25.00
            18         26         26       1679      43654    26.00
            19         27         27       1897      51219    27.00
            20         28         28        992      27776    28.00
                                     ---------- ---------- --------
    avg                                                       16.91
    sum                                  492780    8945142
    
    20 rows selected.
    
    
    AVG ROWS/BLK
    ------------
              17

Legend

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