Having accurate optimizer statistics for your database is more important than ever. Handling larger volumes of data and benefitting from the ever-increasing number of choices in execution plans generated by the Oracle Optimizer require that the optimizer knows as much as possible about your data. The only reasonable way to gather the statistics is with the dbms_stats package. But there are many options to dbms_stats, and your choice of which options to use can dramatically affect your results, both in accuracy of statistics and performance of the statistics gathering operation itself.

A few years back we examined dbms_stats in Oracle 9 and 10.1. We update this information for Oracle 11.2 (and the next generation of the Oracle database, if it is released soon enough). In this presentation we will discuss the effects of the various choices. The focus here will be on actual experience, measured performance, and detailed examples— not just on what the documentation says. Those attending the session should have some experience with gathering optimizer statistics.