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:


There is a patch for JUST_STATS package – . It allows it to work with partitions.


Presenting at Hotsos 2012

February 17, 2012

I am very excited to present at this great symposium. My presentation will start at 11 am on Monday (March 5, 2012) at Salon B.

This is my abstract:

               Adequate, up-to-date table and index statistics are of utmost importance for achieving optimal database performance. Unlike profiles, hints, and outlines, which can only help tune a narrow set of queries, database statistics assist the optimizer, a very sophisticated program, to deliver excellent results for every query every time.  

               Oracle’s default statistics gathering process collects statistics every night and over the weekend. The process works well for tables that follow a traditional growth pattern or have low volatility; however, it is inadequate for tables which experience fluctuations in size or data distribution on a frequent basis. The challenge is even greater when we are not allowed to change the application design and queries, an increasingly common situation thanks to the growing use of off-the-shelf solutions.

               After a definition of volume and distribution volatility, methods and consequences of reducing it are explored. Tradeoffs between statistics management simplicity and resource utilization are discussed.

               Following a review of the benefits and the pitfalls of using dynamic sampling and locking statistics to manage the statistics of volatile tables, a robust algorithm that delivers both plan stability and system flexibility is proposed. The algorithm works by allowing the statistics to change only under specific circumstances.

               Since handing volatile table statistics often involves directly invoking DBMS_STATS procedures, the missed opportunities due to the fact that Oracle’s DBMS_STATS package issues an implicit commit are discussed. Transaction consistency and easiness to recover after a failure, need for functional testing, and inability to gather statistics in triggers are covered. JUST_STATS, a novel custom PL/SQL package for collecting table and index statistics, is proposed and explained. The JUST_STATS package is functionally equivalent to a subset of DBMS_STATS package, except that it does not issue a commit. Examples illustrating the use of JUST_STATS in batch processing and off-the-shelf applications, including statistics gathering in table triggers, are shown.

Hope to see you there!