dbms_space.free_blocks does not report correctly ?
Hi list, I'm checking space usage in some objects. Type Name TBlocks Unused Used FBlocks HWM TABLE FND_CONCURRENT_REQUESTS 21,075 70 3,557 20,225 21,005 TABLE GL_BALANCES 126,233 220 125,987 4 126,013 I've used the following query to calculate Used blocks (I mean blocks where there're rows, at least one). SELECT COUNT(DISTINCT SUBSTR(rowid,15,4)||SUBSTR(rowid,1,8)) FROM segment; ---> "Used" And I've used dbms_space.free_blocks to calculate "FBlocks". And dbms_space.unused_blocks to calculate "Unused" (blocks above HWM), "TBlocks" (Total blocks of the object). And HWM=TBlocks-Unused So, check this out Table GL_BALANCES, I've got 126233 blocks and 220 blocks unused, so we can say that HWM is 126013 (126233-220). And I've got 125987 blocks with some data in them, so 126013-125987= 26 blocks. This means that these 26 blocks were used some time ago (because they are below HWM) but they're not holding any rows now. right? So here's my question: why does dbms_space.free_blocks report only 4 blocks ? that means that below HWM there are 4 blocks that are candidate for inserts, what happened with the 26 blocks !?? shouldn't it be reporting at least 26 blocks ? Please help me out with this. Thanks
RE: dbms_space.free_blocks does not report correctly ?
Frankly, I am guessing, but all I could find in the documentation is that user must have analyze privilege for running dbms_space package, so my guess is it uses "estimate statistics" (sampling ) internally. Hence, the inaccuracy? -Original Message- Sent: Friday, April 20, 2001 4:46 PM To: Multiple recipients of list ORACLE-L Hi list, I'm checking space usage in some objects. TypeName TBlocks Unused Used FBlocks HWM TABLE FND_CONCURRENT_REQUESTS 21,075 703,557 20,225 21,005 TABLE GL_BALANCES 126,233 220 125,9874 126,013 I've used the following query to calculate Used blocks (I mean blocks where there're rows, at least one). SELECT COUNT(DISTINCT SUBSTR(rowid,15,4)||SUBSTR(rowid,1,8)) FROM segment; ---> "Used" And I've used dbms_space.free_blocks to calculate "FBlocks". And dbms_space.unused_blocks to calculate "Unused" (blocks above HWM), "TBlocks" (Total blocks of the object). And HWM=TBlocks-Unused So, check this out Table GL_BALANCES, I've got 126233 blocks and 220 blocks unused, so we can say that HWM is 126013 (126233-220). And I've got 125987 blocks with some data in them, so 126013-125987= 26 blocks. This means that these 26 blocks were used some time ago (because they are below HWM) but they're not holding any rows now. right? So here's my question: why does dbms_space.free_blocks report only 4 blocks ? that means that below HWM there are 4 blocks that are candidate for inserts, what happened with the 26 blocks !?? shouldn't it be reporting at least 26 blocks ? Please help me out with this. Thanks -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Trivedi, Hitarth INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: dbms_space.free_blocks does not report correctly ?
I can't answer your question directly but I would like to point out a few things. Your select count on rowid will miss chained blocks, the first block of the first segment which is used for freelist chains, and as I learned some time ago that oracle used more blocks to keep track of the extra freelists that became available with the "maxextents unlimited" option. Remember years ago when we where bound by 121 extents for 2k block, and 225 for 4k, and 505 for 8k (something like that), anyway, oracle had to put the extra info somewhere, so that take extra blocks to store it all. Lastly, I just can't see spending time looking for a few blocks. 1000's maybe. That's just my opinion, I could be wrong. -Original Message-From: Diego Cutrone [mailto:[EMAIL PROTECTED]]Sent: Friday, April 20, 2001 1:46 PMTo: Multiple recipients of list ORACLE-LSubject: dbms_space.free_blocks does not report correctly ? Hi list, I'm checking space usage in some objects. Type Name TBlocks Unused Used FBlocks HWM TABLE FND_CONCURRENT_REQUESTS 21,075 70 3,557 20,225 21,005 TABLE GL_BALANCES 126,233 220 125,987 4 126,013 I've used the following query to calculate Used blocks (I mean blocks where there're rows, at least one). SELECT COUNT(DISTINCT SUBSTR(rowid,15,4)||SUBSTR(rowid,1,8)) FROM segment; ---> "Used" And I've used dbms_space.free_blocks to calculate "FBlocks". And dbms_space.unused_blocks to calculate "Unused" (blocks above HWM), "TBlocks" (Total blocks of the object). And HWM=TBlocks-Unused So, check this out Table GL_BALANCES, I've got 126233 blocks and 220 blocks unused, so we can say that HWM is 126013 (126233-220). And I've got 125987 blocks with some data in them, so 126013-125987= 26 blocks. This means that these 26 blocks were used some time ago (because they are below HWM) but they're not holding any rows now. right? So here's my question: why does dbms_space.free_blocks report only 4 blocks ? that means that below HWM there are 4 blocks that are candidate for inserts, what happened with the 26 blocks !?? shouldn't it be reporting at least 26 blocks ? Please help me out with this. Thanks
RE: dbms_space.free_blocks does not report correctly ?
Thanks Trivedi and Brian, I think that you may be right about chained blocks, I'll check this out. - Original Message - From: Brian MacLean To: '[EMAIL PROTECTED]' ; 'Diego Cutrone' Sent: Friday, April 20, 2001 4:34 PM Subject: RE: dbms_space.free_blocks does not report correctly ? I can't answer your question directly but I would like to point out a few things. Your select count on rowid will miss chained blocks, the first block of the first segment which is used for freelist chains, and as I learned some time ago that oracle used more blocks to keep track of the extra freelists that became available with the "maxextents unlimited" option. Remember years ago when we where bound by 121 extents for 2k block, and 225 for 4k, and 505 for 8k (something like that), anyway, oracle had to put the extra info somewhere, so that take extra blocks to store it all. Lastly, I just can't see spending time looking for a few blocks. 1000's maybe. That's just my opinion, I could be wrong. -Original Message-From: Diego Cutrone [mailto:[EMAIL PROTECTED]]Sent: Friday, April 20, 2001 1:46 PMTo: Multiple recipients of list ORACLE-LSubject: dbms_space.free_blocks does not report correctly ? Hi list, I'm checking space usage in some objects. Type Name TBlocks Unused Used FBlocks HWM TABLE FND_CONCURRENT_REQUESTS 21,075 70 3,557 20,225 21,005 TABLE GL_BALANCES 126,233 220 125,987 4 126,013 I've used the following query to calculate Used blocks (I mean blocks where there're rows, at least one). SELECT COUNT(DISTINCT SUBSTR(rowid,15,4)||SUBSTR(rowid,1,8)) FROM segment; ---> "Used" And I've used dbms_space.free_blocks to calculate "FBlocks". And dbms_space.unused_blocks to calculate "Unused" (blocks above HWM), "TBlocks" (Total blocks of the object). And HWM=TBlocks-Unused So, check this out Table GL_BALANCES, I've got 126233 blocks and 220 blocks unused, so we can say that HWM is 126013 (126233-220). And I've got 125987 blocks with some data in them, so 126013-125987= 26 blocks. This means that these 26 blocks were used some time ago (because they are below HWM) but they're not holding any rows now. right? So here's my question: why does dbms_space.free_blocks report only 4 blocks ? that means that below HWM there are 4 blocks that are candidate for inserts, what happened with the 26 blocks !?? shouldn't it be reporting at least 26 blocks ? Please help me out with this. Thanks
