Gather Statistics

1.Gather Dictionary Stats: EXEC DBMS_STATS.gather_dictionary_stats;

2.Gather Fixed Object Stats: EXEC DBMS_STATS.gather_fixed_objects_stats;

3.Gather Full Database Stats: 

EXEC DBMS_STATS.gather_database_stats;

EXEC DBMS_STATS.gather_database_stats(

estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE,

degree => 8

);

4. Gather Schema Stats

EXEC DBMS_STATS.gather_schema_stats('SCOTT');

EXEC DBMS_STATS.gather_schema_stats(

  ownname => 'SCOTT',

  method_opt => 'FOR ALL COLUMNS SIZE 1',

  granularity => 'ALL',

  degree => 8,

  cascade => TRUE,

  estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE

);

5. Gather Table Stats

EXEC DBMS_STATS.gather_table_stats('SCOTT', 'EMP');

 

EXEC DBMS_STATS.gather_table_stats(

  ownname => 'SCOTT',

  tabname => 'EMP',

  cascade => TRUE,

  method_opt => 'FOR ALL COLUMNS SIZE 1',

  granularity => 'ALL',

  degree => 8

);

BEGIN
DBMS_STATS.GATHER_TABLE_STATS(
ownname => 'HR',
tabname => 'EMPLOYEES',
estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE,
method_opt => 'FOR ALL COLUMNS SIZE SKEWONLY',
cascade => TRUE
);
END;

6. Gather Index Stats

EXEC DBMS_STATS.gather_index_stats('SCOTT', 'EMP_PK');

7. Gather Partition Stats

BEGIN

  DBMS_STATS.gather_table_stats(

    ownname => 'SCOTT',

    tabname => 'SALES',

    partname => 'SALES_Q1',

    granularity => 'APPROX_GLOBAL AND PARTITION',

    degree => 8

  );

END;

The METHOD_OPT parameter in Oracle's DBMS_STATS procedures is a powerful and nuanced tool that controls how column statistics are gathered—especially histograms and extended statistics. Here's a breakdown to help you use it effectively

What METHOD_OPT Does

It determines:

• Which columns get base statistics (min, max, NDV, nulls, etc.)
• Whether histograms are created, and what type
• Whether extended statistics (like column groups) are gathered

🧾 Syntax Overview

METHOD_OPT => 'FOR [column_spec] SIZE [histogram_spec]'

🔹 Column Spec Options

FOR ALL COLUMNS ==> Gathers stats for all columns (default)
FOR ALL INDEXED COLUMNS ==> Only indexed columns (not recommended)
FOR ALL HIDDEN COLUMNS ==> Only virtual/hidden columns (special use case)
FOR COLUMNS (col1, col2) ==> Specific columns or column group

🔸 Histogram Spec Options

SIZE 1  ==> No histograms
SIZE AUTO ==> Oracle decides based on usage and data distribution (recommended)
SIZE SKEWONLY ==> Histograms only if data is skewed
SIZE REPEAT ==> Repeats previous histogram settings
SIZE ==> Explicit number of histogram buckets (e.g. 254)

✅ Examples

1. No Histograms

METHOD_OPT => 'FOR ALL COLUMNS SIZE 1'

2. Auto Histograms

METHOD_OPT => 'FOR ALL COLUMNS SIZE AUTO'

3. Specific Columns with Histograms

METHOD_OPT => 'FOR COLUMNS (col1, col2) SIZE 254'

4. Column Group Statistics

METHOD_OPT => 'FOR COLUMNS (col1, col2) SIZE AUTO'

This gathers extended stats for the column group.

🧠 Best Practices

• Use SIZE AUTO for most environments—it lets Oracle decide intelligently.
• Avoid FOR ALL INDEXED COLUMNS unless you have a very narrow workload.
• Use column groups for correlated columns in WHERE or JOIN clauses.

In Oracle's DBMS_STATS package, the ESTIMATE_PERCENT parameter controls how much of the data is sampled when gathering statistics. It directly affects the accuracy and performance of the stats collection process.

What Does ESTIMATE_PERCENT Do?

It specifies the percentage of rows Oracle should sample from a table (or index) to generate statistics. You can set it to:

• A specific number (e.g., 10, 50, 100)
• Or use the automatic setting: DBMS_STATS.AUTO_SAMPLE_SIZE

Recommended Setting: DBMS_STATS.AUTO_SAMPLE_SIZE

Oracle recommends using:

estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE

🔍 Why?

• Oracle uses adaptive sampling algorithms to determine the best sample size.
• It avoids full table scans unless necessary.
• It ensures accurate NDV (Number of Distinct Values) without high overhead.
• Required for incremental statistics and hybrid histograms

1. Auto Sample Size (Best Practice)

EXEC DBMS_STATS.GATHER_TABLE_STATS(

ownname => 'HR', 

tabname => 'EMPLOYEES', 

estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE, 

method_opt => 'FOR ALL COLUMNS SIZE AUTO', 

cascade => TRUE );

2. Full Table Scan (100% Sampling)

estimate_percent => 100