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!
Comments
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.
Thank you, Mathew! It's a clear explanation.
Matt is correct Bock Count has been part of the SQL Optimizer from the very beginning when we had 2K database blocks.
In the past this number was an estimate. Now it is calculated as part of TuneTable. We did not want to change all our internal structures so we just count the number of 8K blocks on disk and divide by 4.
Thank you, Brendan! Your comment was helpful.