Mastering Oracle Optimizer Statistics: When and How to Collect Them

Introduction

The Oracle optimizer evaluates all possible execution plans for a SQL statement and selects the one with the lowest cost, where cost reflects resource usage. Accurate cost estimation requires optimizer statistics that describe every object (tables, indexes) accessed by the SQL and the system environment.

When and How to Collect Statistics

In typical Oracle environments, statistics are gathered using the automatic statistics collection job, which runs during a predefined maintenance window. The job invokes dbms_stats.gather_database_stats_job_proc with the same default parameters as the dbms_stats.gather_*_stats procedures.

To change default parameters, use dbms_stats.set_*_pref. For example, to treat a table as stale when only 5% of rows have changed:

begin
  dbms_stats.set_table_prefs('SH','SALES','STALE_PERCENT','5');
end;
/

The PL/SQL block completes successfully, indicating the preference was set.

Manual Statistics Collection

If you have a custom collection process or need to disable automatic stats for a specific schema, you can switch the target of the auto‑stats job to collect only dictionary statistics:

begin
  dbms_stats.set_global_prefs('AUTOSTATS_TARGET','ORACLE');
end;
/

After this change, the job gathers only data‑dictionary stats. For manual collection, use the dbms_stats package, e.g.:

begin
  dbms_stats.gather_table_stats('SH','SALES');
end;
/

The two most frequently tuned parameters are ESTIMATE_PERCENT and METHOD_OPT .

ESTIMATE_PERCENT

This parameter controls the sampling percentage used to compute statistics. 100% sampling yields the most accurate stats but is slow; smaller samples are faster but less precise. Prior to Oracle 10g, the default changed from 100% to AUTO_SAMPLE_SIZE, which lets Oracle decide an appropriate sample size per table.

In Oracle 11g, a new hash‑based sampling algorithm provides near‑100% accuracy with the cost of a 10% sample. The following chart (auto‑sample‑size comparison) illustrates performance and quality:

From Oracle 11g onward, it is recommended to keep the default ESTIMATE_PERCENT (AUTO_SAMPLE_SIZE). Manually setting it to 100% still uses the old algorithm.

METHOD_OPT and Histograms

The METHOD_OPT parameter determines whether histograms are created. Histograms give detailed column value distribution but increase collection time and resource usage. They also interact with bind peeking and affect cardinality estimates for “near‑popular” values.

To avoid the overhead, you can delete existing histograms:

begin
  dbms_stats.delete_table_stats('SH','SALES');
end;
/

and change the default METHOD_OPT to prevent future histogram creation:

begin
  dbms_stats.set_param(pname=>'METHOD_OPT',pval=>'FOR ALL COLUMNS SIZE 1');
end;
/

After adjusting, re‑gather statistics for the affected objects.

Bind Peeking and Histograms

Before Oracle 11g, the optimizer performed bind peeking during the first hard parse, using the sampled bind values to choose a plan. Subsequent executions reused that plan even if bind values changed, causing sub‑optimal performance when histograms were present.

Two ways to mitigate this are:

Remove histograms and disable their creation in future collections.

Disable bind peeking by setting the hidden parameter _optim_peek_user_binds to FALSE.

Near‑Popular Values

When a predicate value appears in a histogram bucket endpoint, the optimizer uses a formula based on the number of bucket endpoints. For non‑endpoint (near‑popular) values, it falls back to density‑based estimates, which can be inaccurate.

Example: For CUST_CITY_ID = 51806 (an endpoint) the estimated rows are 874, matching the actual count. For CUST_CITY_ID = 52500 (non‑endpoint) the estimate is 66 rows, far from the actual 121 rows.

Dynamic Sampling

Dynamic sampling collects statement‑specific statistics at execution time, improving cardinality estimates for near‑popular values. Example hint:

select /*+ dynamic_sampling(a 2) */ count(a.cust_id)
from sh.customers a
where a.cust_city_id = 52114;

With the hint, the execution plan shows a higher row estimate (246) that aligns better with the true count.

Pending Statistics

Before changing default DBMS_STATS parameters in production, use pending statistics to test the impact safely. Set PUBLISH to FALSE for the target object, gather stats, and query the *_TAB_PENDING_STATS view. Activate the pending stats in a session with:

alter session set optimizer_use_pending_stats = true;

After validation, publish them with dbms_stats.publish_pending_stats.

Conclusion

Effective management of Oracle optimizer statistics involves using the automatic collection job, fine‑tuning ESTIMATE_PERCENT and METHOD_OPT, handling histograms and bind peeking, applying dynamic sampling for near‑popular values, and leveraging pending statistics to test changes without affecting production workloads.