Important Notice: Our web hosting provider recently started charging us for additional visits, which was unexpected. In response, we're seeking donations. Depending on the situation, we may explore different monetization options for our Community and Expert Contributors. It's crucial to provide more returns for their expertise and offer more Expert Validated Answers or AI Validated Answers. Learn more about our hosting issue here.

Whats Up with dbms_stats?

April 26, 2017dbms_stats Oracle

0

Posted

Whats Up with dbms_stats?

1 Answer

0

Posted

For years Oracle has been telling us to use the dbms_stats package instead of the analyze command to gather statistics to be used by the Oracle optimizer. 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. Further confusing the situation are additional features and automation that Oracle has added to dbms_stats over the years. 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 on what the documentation says. We will examine the behavior in both Oracle 9i and Oracle 10g. While, by default, statistics are gathered automatically in Oracle 10g, often the default choices are not the best route to take. Those attending the session should have some experience with gathering optimizer statistics, and they will get more ou