Billy Verreynne wrote:"Yes", and "No". DB is quoting his father, George H.W. Bush.burleson wrote:Is on one of the "+most hated idiots in the world+" not your own George W. Bush? And er.. are you not quoting him?
Do you really think that quoting one of the most hated idiots in the world will help your argument?
Don't you recall, you were corrected on your own blog for misunderstanding basic science concepts?Your concept of the scientific approach is also somewhat exotic.
No. Sorry . . .(Actually, even if it's not documented by Metalink, wouldn't a few block dumps be fairly convincing anyway ?)
burleson wrote:Such a wide sweeping statement ... one in which you overstep your authority. I take offense that you presume to speak for me, being in one of the above categories.
I don't believe that you didn't understand how your post offends all Christians, Jews and Muslims alike.
burleson wrote:It's not a bug, it's simply the expected behaviour - as documented on Metalink and demonstrable with some simple programming.
If what you say is true, then simply get Oracle to issue a bug number. I'm sure that you can convince them with your "proof".
burleson wrote:Dear Mr. Burleson,
We used a script like this to monitor the fragmentation and loading of the individual freelists, since free blocks cannot be shared between freelist groups:
1) "Multiple freelists allow multiple transactions to grab free blocks from the segment header without causing buffer busy waits or segment header contention".I think you intended to start that sentence with "Multiple freelist groups", although if you are using multiple freelist groups then the free blocks are not grabbed from the segment header, they are grabbed from the freelist group blocks - which is why multiple freelist groups eliminate segment header contention.
2) "As the single task deletes the rows, only one freelist is re-populated."I hesitate to suggest that you might want to mention the distinction between transaction freelists, process freelists, and the master freelist as I don't know if your note is aimed at the novice or the expert; but for the novice you might want to simplify the explanation by suggesting that only one freelist group acquires the free blocks and that the free blocks will not be automatically shared across multiple freelist groups.
3) ".. a table that continues to extend, even though the dba_segments view shows lot's of free blocks."I think you meant the dba_tables view, not the dba_segments view.
4) "The following script will walk the freelists chains on the tables and provide you with the relative number of free blocks on each freelist chain."Again I don't know if you want to say anything to distinguish between the three types of freelists, but your code is trying to report the number of free blocks associated with each freelist group. Possibly all you need do here is delete the word "chains" in the first part of the sentence, and change the word "chain" to "group" in the second part.
5) "If you see a serious imbalance, you should reorganize the table to coalesce the freelists".It would probably be more sensible to use the dbms_repair.rebuild_freelists procedure to re-distribute the free blocks evenly across all the freelist groups - taking note of the bug that means you may have to re-run the procedure under a different process if it doesn't do the job perfectly first time.
6) The script raises a few questions:
Regardsa) the name of a table does not need to match the name of its data segment so the join is not appropriate. The script will not treat partitioned tables nicely, nor will it handle systems where two different schemas own objects with the same name.
b) You reference column empty_blocks. This column is not populated unless you use the deprecated analyze command to gather statistics. Moreover, it shows you the number of blocks that have been allocated to the segment but are still above the high water mark, not the number of blocks on the freelists (*num_freelist_blocks*, also populated only by the analyze command) - which is presumably what you should be looking at to decide if you need to do something about an uneven distribution of free blocks across the freelist groups.
c) If this note is aimed at the novice, you might want to point out that it assumes they are using a 4KB block size for their database.
d) I am a little puzzled that you check only the first two freelist groups when a small change to the driving cursor would allow you to check all of them and make the entire procedure a little shorter and tidier.
Sure, but ONLY for that exact release, on that exact OS with those exact init.ora parms . . .Of course is it possible to prove something with test cases
But in practice it isn't, that's the problem!it should be reproducible for others as well
Yup. Test cases can be abused by unethical people to cheat . . .And that is where you and Mr. Lewis (and also the other members of the Oak Table) differ so fundamentally.