Presentation Title: Statistics on Partitioned Objects
Presentation 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 and Approximate Global statistics
- Statistics aggregation scenarios and pitfalls
- Setting statistics
- Copying statistics
- Dynamic Sampling
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.
Biography: Doug 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.
If you have registered yet, spots are going fast! Sign up today!