G
George Potemkin
Guest
I had researched a bit father. Progress V11.6. It looks like dbanalys uses the following formula to calculate a scatter factor: ASSIGN MaxRPB = (DbBlockSize - 52) / MeanRecSize MaxRPB = MIN(MaxRPB, AreaRPB) RealRPB = RecCount / UsedBlocks ScatterFactor = 1 + LOG(MaxRPB / RealRPB, 10) . /* ASSIGN */ The tests confirmed that a scatter factor does not take into account a create limit (expansion space constant). I guess dbanalys incorrectly thinks the size of the data block headers is 52 bytes. But a size of standard block header (bkHeaderSize) is 64 bytes or even 80 bytes for the borderline blocks in data cluster. The results per area reported by message 3922 are also affected. For example: 508 RM block(s) found in the storage area. 1.27% of the RM block space is used. RM CHAIN ANALYSIS --------------------------- LIST OF RM CHAIN BLOCKS free # free dbkey space slots hold 132096 4008 254 0 132352 4028 255 0 ... 261632 4028 255 0 261888 4012 255 0 The used space = 4096 - 4028 = 68 bytes (for the most blocks) = 64 bytes (bkHeaderSize) + 4 bytes (No of Recs, Free Slots, No Bytes Free Space). Record offset directory is empty in these blocks. 1.27% of 4096 bytes = 52 bytes So dbanalys also does not count the size of a RM block header (4 bytes + record offset directory). But it's a minor issue. A bigger problem is a definition of the blocks used by a table. It can cause the funny results like: -Record Size (B)- ---Fragments--- Scatter Table Records Size Min Max Mean Count Factor Factor PUB.tbl 1 19.0B 19 19 19 1 1.0 3.7 Dbanalys says that a /single/ record is "scattered" in the are with a huge factor (3.7): it uses 501 times = EXP(10, 2.7) more space than it could. Do we need a dump and load for this record? ;-) A simple test: Area: d "Table Area":7,256;512 . Db block size: 4096 At first I created only one record: -Record Size (B)- ---Fragments--- Scatter Table Records Size Min Max Mean Count Factor Factor PUB.tbl 1 19.0B 19 19 19 1 1.0 1.0 So far so good. Then I created 79111 records to fill all 508 blocks in the table's data cluster (the first 4 blocks in the first data cluster are not RM blocks). Then I deleted all records and again created only one record. And now the scatter factor is 3.7. Table owned all 508 data blocks from beginning. Now dbanalys threats 507 blocks as "used" because each block stores one recid lock. How many blocks are "used" by the table in my case? One or 508 blocks? Both answers are correct: The queries that used the indexes will retrieve only one data block from disk - the one that stores the only record in the table. The queries with TABLE-SCAN option will retrieve all 508 blocks. The quiz at the end ;-) What scatter factor table will have if I'll delete its last record? Scroll right to see the answer: -> 0.0 for the tabel and 1.0 as average: -> -> -Record Size (B)- ---Fragments--- Scatter -> Table Records Size Min Max Mean Count Factor Factor -> PUB.tbl 0 0.0B 0 0 0 0 0.0 0.0 -> -> ----------------------------------------------------------- -> Subtotals: 0 0.0B 0 0 0 0 0.0 1.0 Best regards, George
Continue reading...
Continue reading...