5 Replies Latest reply on Apr 8, 2011 3:51 PM by Mark D Powell

# Avg num of rows per blk

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
• ###### 2. Re: Avg num of rows per blk
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
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
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
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``````