Gather Stats with or without histogram in Oracle

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 autowhich 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;
/
This entry was posted in Oracle on by .

About SandeepSingh DBA

Hi, I am working in IT industry with having more than 10 year of experience, worked as an Oracle DBA with a Company and handling different databases like Oracle, SQL Server , DB2 etc Worked as a Development and Database Administrator.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.