[Previous entry: "Marcel-Jan has an interesting tool on his site called SQL-Gotcha"] [Next entry: "A news aggregator"]
Nice paper by Jonathan on DUAL internals and intricacies
May 16th, 2005 by Pete
Post to del.icio.us
Post to Furl
I was looking for something a couple of weeks ago on Google and found by chance an interesting paper written by Jonathan Lewis on his site. I cannot remember what I was looking for at the time now but I made a note of Jonathan’s paper to go back and read it later. This evening I had a chance to do that.
I like papers that discuss internals and details that are not easily found elsewhere. The paper is called "Dual – SYS – and the not so obvious" and has a published date of April 2005.
The paper starts of with a claim from another site where someone said that in 10g that when accessing dual using the x$dual table that logical I/O can be reduced to zero CR gets from 3 and he (the other site author) then claimed that he could show how to do it in earlier versions. Jonathan said initially that this had to be wrong and decided to test the theory.
His example shows that the claim could be true as the logical I/O's do drop to zero from three on the keep pool where he placed it. But he noticed that consistent gets on the default pool had risen. He then went on to check if dual had indeed been placed in the keep pool. Jonathan shows how to check this with some great low level details. He shows that it is in the default pool. He then tries hard to get dual into the keep pool and manages to get a header block in there but the data block is still in the default pool. Jonathan postulates that it could be a bug or a design feature intending to do something clever for RAC and suggests that it looks like tables owned by SYS do not get their data blocks into the keep pool. Jonathan then finally checks to see if the logical I/O is reduced with DUAL's segment header in the keep pool. Read this excellent short paper to find out it the butler did it. This is a very interesting paper covering some great internals details and also showing some good investigative techniques. As Jonathan points out though hacking SYS objects is not a good idea even for performance reasons.
