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!