Hotsos 2012 – the benefits of a discussion with a really knowledgeable audience

March 12, 2012

               After a short conversation with Alex Gorbachev, I realized that maybe I should have added some implementation details about the “two-phase removal of data” approach for reducing table volatility.

               First, the approach does not reduce the volatility of the content of the tab view, it reduces the volatility of what the CBO sees or assumes about the tab view. But, again, that is all that matters…

               Second, please do not create a histogram on the deleted column of tab_internal table, even though the column would likely be heavily skewed. Doing so would introduce volatility because the histogram would represent the deleted/active distribution as of when the stats are gathered. What we need is the “standard” 50/50 split we get when the column does not have a histogram. Make sure that there are exactly two distinct values for the deleted column in tab_internal table.

               Third, an index (regular of function-based /covering only the “N” value/ ) on the deleted column in tab_internal would be quite useful.

               On a different note, someone from the back of the hall, I think that was Kerry Osborne, but I am not quite sure, mentioned that the JUST_STATS package should explicitly invalidate all cursors that use the table we are gathering stats on. I answered that I thought the package did invalidate cursors, but I was incorrect. Anyway, the current version does invalidate cursors (NO_INVALIDATE=> FALSE).  Thanks a lot for this observation.


Advanced Methods for Managing Statistics of Volatile Tables in Oracle (Hotsos 2012)

March 7, 2012

Thanks to all who attended my presentation at Hotsos 2012. You were a great audience with insightful questions and relevant comments.

Here is my presentation  along with the white paper.

The code for JUST_STATS package (spec and body) is available for free. Note that I cannot support the package and I do not accept any legal liability or responsibility for the content or the accuracy of the information so provided, or, for any loss or damage caused arising directly or indirectly in connection with reliance on the use of such information.

The readme and the file with examples could quite useful as well.

Hotsos is such a great conference… I had to fly back home on Tuesday morning, but I wish I could stay longer.

 

August 2014:

P.S

There is a patch for JUST_STATS package – http://wp.me/p1DHW2-9L . It allows it to work with partitions.