· Nov 10, 2016

What is Block Count in TuneTable results?

Running TuneTable accounts among other the parameter named Block Count. In documentation, we see that this is an approximate numbers of 2K-blocks in which SQL-maps are stored. Databases in recent Cache doesn't support 2K-physical blocks so SQL-blocks are not physical blocks as it seems. So two questions:

- what are these blocks?

- how knowledge about count of blocks can help in SQL optimization?

Thanks for intelligent answer to stupid questions!

Discussion (4)2
Log in or sign up to continue

Hello Mikhail, Good question!

I think of block count as a rough estimate of the disk size of a table or index (an "SQLmap").  The SQL optimizer uses this to get an estimate of how much disk I/O could be involved in scanning that map.  Block count mostly matters in proportion to other block counts (similar to ExtentSize).  

I am not certain how to interpret 2K reference in the documentation, perhaps someone else will chime in - my guess is that the "units" don't matter, so the original 2K block size is still used as a base unit for measuring the disk size of a storage map.

Block Count can be extremely important for tables that have child tables, or that otherwise share a storage global with other classes. The row count might be relatively small, but because the global nodes are spaced out, more disk I/O is required.  With the block count taken into consideration, the SQL optimizer may be pushed toward an index or different starting table.