ESTIMATE_PERCENT in DBMS_STATS

Let's break down 

estimate_percent => dbms_stats.auto_sample_size 

in simple terms and explain what each part means.

1. What is a "sample" in Oracle?

A sample is a small portion of the data from a table. Instead of looking at every row in a table, Oracle can analyze a smaller set of rows (a "sample") to gather information about the data. This is similar to tasting a few spoons of soup to guess the flavor rather than drinking the whole pot.

---

2. What is being sampled?

Oracle samples the data in your tables to collect statistics, which are summaries of your data. These statistics help the Oracle Optimizer decide the best way to execute a query.

For example:

• How many rows are in the table?
• What is the average size of data in a column?
• How many distinct values are in a column?

---

3. What is estimate_percent?

• estimate_percent tells Oracle how much data to sample when collecting statistics.
  For example:
  • estimate_percent = 10: Use 10% of the table's data.
  • estimate_percent = 50: Use 50% of the table's data.
  • estimate_percent = 100: Use all the data (also called a "full scan").

The larger the percentage:

• The more accurate the statistics.
• But it takes more time and resources to collect.

---

4. What is dbms_stats.auto_sample_size?

• auto_sample_size is Oracle's way of deciding automatically how much data to sample.
• Oracle analyzes the table and chooses the sample size dynamically.
• It tries to balance speed and accuracy.
• In most cases, this is the recommended setting because it simplifies the process.

---

5. What is "manual" vs. "automatic"?

• Automatic (auto_sample_size):
  • Oracle decides the sample size based on the table's data.
  • You don’t need to specify the exact percentage.
  • Good for most use cases.
• Manual (estimate_percent = X):
• You specify the exact percentage of data to sample.
• Useful when you have specific needs (e.g., you know the data is unevenly distributed).

---

6. Why is estimate_percent used?

Oracle uses this setting when you collect statistics for your database tables using the DBMS_STATS package. These statistics guide the Optimizer in making decisions to:

• Improve query performance.
• Avoid unnecessary full table scans.

---

Example:

Imagine a table with 10 million rows:

1. Manual:
   estimate_percent = 10: Oracle analyzes 1 million rows (10% of the data).
   You control the percentage.

2. Automatic:
   estimate_percent = dbms_stats.auto_sample_size:
   Oracle might analyze 5% or 15%, depending on its internal calculations. You don't need to worry about it.

---

Key Takeaways:

• Sample: A portion of the data used to collect statistics.
• estimate_percent: Specifies how much data to sample.
• dbms_stats.auto_sample_size: Lets Oracle decide the best sample size automatically.
• Manual vs. Automatic: Use automatic unless you have a specific reason to control it manually.

Click on below link to watch video:-

Stop using ESTIMATE_PERCENT in DBMS_STATS