Dangerous viewB2 utility

Since V11.6 the viewB2 utility became the fast way to estimate the sizes of all database objects in type II storage areas. But it’s turned out not to be fast and it might lock a database.

Story: the customer has deleted the unused index (its size was 100 GB). After that the viewB2 utility runs on this database for 13-15 hours. At the end a lot of processes that tried to update the table were blocked by viewB2. Fortunately, it was a test environment.

Result before the index was deleted:

Object Enablement    Size     Type   Object Name
-----------------  -------- -------  ------------
Default                   7  Master   Area.Control-Object:0
Default           109264768   Index   PUB.table.index1:8
Default            21103776   Index   PUB.table.index2:9
Default            11941256   Index   PUB.table.index3:10
Default            26215864   Index   PUB.table.index4:11
                   --------
                  168525671

After the index was deleted:

Object Enablement    Size     Type   Object Name
-----------------  -------- -------  ------------
Default            26215735  Master   Area.Control-Object:0
Default           109264920   Index   PUB.table.index1:8
Default            21103840   Index   PUB.table.index2:9
Default            11941304   Index   PUB.table.index3:10
                   --------
                  168525783

First of all, viewB2 scans the free cluster chains. The utility adds the blocks on the chain to the number of blocks owned by Master object. The reason seems to be obvious - the Area Control Object (ACO) block contains the firstFreeCluster and lastFreeCluster fields but it does not store the number of the number of clusters (or blocks) on the free cluster chain. Though the utility can easy get the number: the last row in the viewB2 output is the hiWaterBlock value stored in ACO block. The sizes of the objects are the totalBlocks values stored in the correspondent object blocks. So the number of the blocks owned by Master block can be got as the difference of these values.

By the way, the utility also scans the free chain owned by Master block but this chain used to be short - shorter than a cluster size.

Second, the viewB2 holds SHARE lock on ACO block while the utility is scanning the free cluster chain. ACO blocks are the most frequently updated blocks in each area (the largest value in bk_updctr). In our case the lock was hold during 13-15 hours. So be careful if you’re going to run viewB2 when there were the large objects deleted in database.

Finally, the utility seems to scan the free cluster chains too slowly. Disk I/O is 5 times less than the sequential disk reads. The viewB2 utility scans the chains exactly as chanalys does: they read only one block per cluster - the last block in each cluster. The extended header of this block contains the nextCluster and prevCluster fields. In our case the clusters on the chain were mainly sequential. Cluster size in this area is 8. Disk I/O is 5 times slower than expected. Is it a coincidence?