G
George Potemkin
Guest
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?
Continue reading...
Continue reading...