Gather Stats with or without histogram in Oracle

Gather CBO statistics without Histograms (using SYS.DBMS_STATS)
Use commands below to generate a fresh set of CBO statistics for the schema objects accessed by your SQL. Histograms will be dropped with this command “method_opt => ‘FOR ALL COLUMNS SIZE 1′” in DBMS_STATS Package.

--Gather Schema Stats without Histogram (reset parameter use default value)
BEGIN
dbms_stats.gather_schema_stats(
ownname=>'HR', 
degree =>4,
method_opt => 'FOR ALL COLUMNS SIZE 1');
END;
/

--Gather Table Stats without Histogram
BEGIN
SYS.DBMS_STATS.GATHER_TABLE_STATS (
ownname => 'HR',
tabname => 'TRAN',
estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE,
method_opt => 'FOR ALL COLUMNS SIZE 1',
cascade => TRUE,
no_invalidate => FALSE);
END;
/

Gather CBO statistics with Histograms (using SYS.DBMS_STATS)
Use commands below to generate a fresh set of CBO statistics for the schema objects accessed by your SQL. Histograms will be generated for some columns or all columns.

method_opt to ‘for all columns size auto‘ which means that Oracle will decide whether or not to collect histograms for a column. (DEFAULT)

METHOD_OPT to ‘FOR ALL COLUMNS SIZE 254’ will cause Oracle to gather a histogram on every column.

--Gather Schema Stats without Histogram(AUTO option Oracle will decide)
BEGIN
dbms_stats.gather_schema_stats(
ownname=>'HR', 
degree =>4,
method_opt => 'FOR ALL COLUMNS SIZE AUTO');
END;
/

--Gather Table Stats without Histogram (254 option for all columns histogram)
BEGIN
SYS.DBMS_STATS.GATHER_TABLE_STATS (
ownname => 'HR',
tabname => 'TRAN',
estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE,
method_opt => 'FOR ALL COLUMNS SIZE 254',
cascade => TRUE,
no_invalidate => FALSE);
END;
/