Purging histograms

Oracle will automatically create histograms during night maintenance window. This might impact a query performance, so use dbms_stats to purge it. You can also change or disable the automatic collection statistics job.

The following command will purge a column histogram:

exec dbms_stats.delete_column_stats( ownname=>'&OWNER', tabname=>'&TABLE', colname=>'&COLUMN', col_stat_type=>'HISTOGRAM');

Or, if you need to purge all histograms from a table, use this one:

select 'exec dbms_stats.delete_column_stats('''||owner||''','''||table_name||''','''||column_name||''', col_stat_type=> ''HISTOGRAM''); '
from dba_tab_columns
where owner = '&OWNER' and table_name = '&TABLE_NAME' and histogram not in ('NONE')
/

Another good info at:

https://blogs.oracle.com/optimizer/how-do-i-drop-an-existing-histogram-on-a-column-and-stop-the-auto-stats-gathering-job-from-creating-it-in-the-future

https://blogs.oracle.com/optimizer/how-does-the-methodopt-parameter-work

http://docs.oracle.com/database/121/TGSQL/tgsql_histo.htm

Author: reguchi

Oracle ACE, OCP 12c, OCE 12c MAA, OCE Exadata, RHCE, AWS CSAA, OCI CSAA. Technology geek, homebrewer, dad.

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