Hotsos Symposium Speaker – Iordan Iotzov

Iordan Iotzov is a new Hotsos Symposium presenter.

Biography

Iordan IotzovIordan Iotzov is a senior DBA/Architect with more than ten years of database administration and development experience. His professional interests span many areas, with performance tuning one of his favorites. During his career, Iordan has worked on complex database problems across various industries, providing original and effective solutions. Currently, he is a senior DBA at News America Marketing (NewsCorp). Iordan has a MS in Computer Science and holds numerous Oracle certifications.

Iordan was a speaker at NYOUG and presented at BrainSurface VirtaThon. He participates in OTN forums and actively blogs at http://iiotzov.wordpress.com/.

Presentation Title

Advanced Methods for Managing Statistics of Volatile Tables in Oracle

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. It works fine for tables that change little over the day, but it is inadequate for tables which size or data distribution fluctuate every couple of minutes. 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. 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.

Presentation Materials

Presentation materials are available online to attendees only.

Schedule

The speaker schedule is as follows: