Hotsos Symposium Speaker – Doug Burns

Doug Burns is a returning Hotsos Symposium presenter.

Biography

Doug BurnsDoug Burns is an independent contractor who has 20 years of experience working with Oracle in a range of industries and applications. As well as presenting at a number of conferences, he has developed, taught, and edited performance-related courses for both Oracle UK and Learning Tree International. He is a member of The Oak Table Network.

Doug is an Oracle Ace Director.

Presentation Title

Statistics on Partitioned Objects

Abstract

Optimal SQL execution plans are rooted in the quality of the statistics describing the underlying objects that are used by the Cost Based Optimizer. Statistics maintenance is a particular challenge on Data Warehouse systems holding large data volumes that support complex and flexible user reporting requirements. Such systems are likely to use partitioned tables and indexes to complicate the picture further.

This presentation is based on the problems encountered and solutions used during the implementation of several large DW systems running on Oracle 10.2 with different workload profiles and requirements and will discuss:

Core challenges and statistics fundamentals

  • The quality/performance trade off
  • Statistic levels on partitioned objects and how they are used
  • Key DBMS_STATS parameters and defaults

Global Statistics

  • Global and Approximate Global statistics
  • Statistics aggregation scenarios and pitfalls

Alternative Strategies

  • Setting statistics
  • Copying statistics
  • Dynamic Sampling

11g improvements

The presentation will discuss a variety of problems and bugs with the various approaches that should be avoided as well as offering guidelines to help you design the best statistics collection strategy for your requirements.

Presentation Materials

Presentation materials are available online to attendees only.

Schedule

The speaker schedule is as follows: