Permanently deleting histograms

By Brian Fitzgerald

“A histogram is a special type of column statistic that provides more detailed information about the data distribution in a table column.” Without histograms, the optimizer could underestimate the cost of a table access for a nonselective predicate, and as a result, choose an inefficient index access path.

There are times when the DBA will want to delete histograms, including:

  • Issues with bind peeking.
  • Issues with adaptive cursor sharing.
  • Limit SYSAUX growth.
  • High number of child cursors resulting from adaptive cursor sharing while using a database resident connection pool (DRCP).
  • Plan instability caused by histograms.
  • Troubleshooting any of these.
  • Temporary workaround for any of these.
  • Permanent workaround for any of these.

You can delete a histogram, but that has obvious problems. In the first place, if you delete a histogram, you haven’t prevented it from coming back. You can lock statistics, but locking statistics has its drawbacks. You can only lock statistics at the table level, not the column level. If you table lock stats, other critical statistics, such as num_rows, num_nulls, and num_distinct will not get gathered. The optimizer will not keep up with changes in table population. Finally, to prevent reappearance of histograms, some DBAs decide to disable automatic statistics gathering. I usually do not agree with disabling automatic statistics gathering.

A more flexible way to delete a histogram is to set table preference method_opt to for columns size 1 for the desired column or columns. This blog posting demonstrates how this technique can be scripted.

Scripts for optimizer statistics

gather.table.stats.sql

Gather stats with the default options (no method_opt):

define ownname=&&1
define tabname=&&2
whenever oserror exit 1
@ sqlerx.sql
set verify off
set serveroutput on

declare
 l_fmt clob := q'{Gathered stats on %s.%s with default options.}';
begin
 dbms_stats.gather_table_stats (
 ownname => '&&ownname',
 tabname => '&&tabname'
 );
 out_pkg.printf(l_fmt, '&&ownname', '&&tabname' );
end;
/

Note, at this juncture, that every sql file that reads other sql files (with”@”) needs “whenever oserror exit 1”

dba.tab.col.statistics.sql

Display column statistics and show whether there is a histogram.

define owner=&&1
define tablename=&&2
whenever oserror exit 1
@ l32k.p50k.sql
@ columnformat.sql
set verify off

select column_name, num_distinct, histogram, num_buckets
from dba_tab_col_statistics
where owner = '&&owner'
and table_name = '&&tablename'
order by column_name;

rpt.col.usage.sql

Oracle decides whether to gather a histogram base on workload on skewed columns, as recorded in col_usage$. You can run report_col_usage to check on what workload Oracle has recorded. report_col_usage will not work unless you set long.

define ownname=&&1
define tabname=&&2
whenever oserror exit 1
@ sqlerx.sql
@ l32k.p50k.sql

set verify off

set long 2000000000
exec dbms_stats.flush_database_monitoring_info;
column rpt format a160
select dbms_stats.report_col_usage('&&ownname','&&tabname') rpt from dual;

prefer.delete.histo.sql

The delete histogram script that is the subject of this blog post. The script does not actually delete the histogram. It simply arranges that it will be delete on the next gather stats. You can either delete the histogram right away by calling dbms_stats.delete_column_stats, or you can gather stats with the default options (no method_opt).

define ownname=&&1
define tabname=&&2
define colname=&&3
whenever oserror exit 1
@ sqlerx.sql
set verify off
set serveroutput on

declare
 l_fmt clob := q'{Preference for no histogram on %s.%s is set.}';
begin
 dbms_stats.set_table_prefs (
 ownname => '&&ownname',
 tabname => '&&tabname',
 pname => 'method_opt',
 pvalue => 'for columns size 1 &&colname'
 );
 out_pkg.printf(l_fmt, '&&ownname', '&&tabname' );
end;
/

dba.tab.stat.prefs.sql

Use this script for reporting table stat prefs

define owner=&&1
whenever oserror exit 1
@ sqlerx.sql
@ l32k.p50k.sql
@ columnformat.sql
set verify off

column preference_name format a30
column preference_value format a50

select p.owner,
 p.table_name,
 p.preference_name,
 p.preference_value
from dba_tab_stat_prefs p
where owner = '&&owner'
order by
p.owner,
p.table_name,
p.preference_name;

Script in SQLPATH

Scripts mentioned in this blog post that I keep in a common directory for reuse.

sqlerx.sql

whenever sqlerror exit 1

l32k.p50k.sql

set linesize 32767
set pagesize 50000
set trimspool on

columnformat.sql

A very long, and ever growing, file of catalog column names.

column histogram format a20
column account_status format a20
column column_name format a30
column constraint_name format a30
column consumer_group format a30
etc.

The demo script

demo.delete.histo.sql

whenever oserror exit 1
@ l32k.p50k.sql
@ columnformat.sql
set verify off

drop table t purge;
drop sequence s;
@ sqlerx.sql
create table t
(
k number,
g varchar2(1)
);
create sequence s;

prompt Generate skewed data in column G
insert into T (k, g ) values ( s.nextval, 'M' );
insert into T (k, g ) values ( s.nextval, 'F' );
insert into T (k, g ) values ( s.nextval, 'M' );

commit;

@ gather.table.stats.sql U T
@ dba.tab.col.statistics.sql

prompt generating workload on column G
select count(*) from t where g = 'Z';

prompt checking col_usage$
@ rpt.col.usage.sql U T

@ gather.table.stats.sql U T
@ dba.tab.col.statistics.sql
prompt Yes, there is a histogram.

@ prefer.delete.histo.sql U T G

@ gather.table.stats.sql U T

prompt check existence of histogram on column G
@ dba.tab.col.statistics.sql
prompt The histogram is gone!

Demo script output

[oracle@stormking db12201 stats]$ sqlplus u/u @ demo.delete.histo.sql
SQL*Plus: Release 12.2.0.1.0 Production on Sun Feb 4 20:20:33 2018
Copyright (c) 1982, 2016, Oracle. All rights reserved.
Last Successful login time: Sun Feb 04 2018 20:16:14 -05:00
Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
Table dropped.
Sequence dropped.
Table created.
Sequence created.
Generate skewed data in column G
1 row created.
1 row created.
1 row created.
Commit complete.
Gathered stats on U.T with default options.
PL/SQL procedure successfully completed.
COLUMN_NAME NUM_DISTINCT HISTOGRAM NUM_BUCKETS
------------------------------ ------------ -------------------- -----------
G 2 NONE 1
K 3 NONE 1
generating workload on column G
COUNT(*)
----------
 0
checking col_usage$
PL/SQL procedure successfully completed.
RPT
---------------------------------------------------------------------------
LEGEND:
.......
EQ : Used in single table EQuality predicate
RANGE : Used in single table RANGE predicate
LIKE : Used in single table LIKE predicate
NULL : Used in single table is (not) NULL predicate
EQ_JOIN : Used in EQuality JOIN predicate
NONEQ_JOIN : Used in NON EQuality JOIN predicate
FILTER : Used in single table FILTER predicate
JOIN : Used in JOIN predicate
GROUP_BY : Used in GROUP BY expression
...........................................................................
###########################################################################
COLUMN USAGE REPORT FOR U.T
...........................
1. G : EQ
###########################################################################
Gathered stats on U.T with default options.
PL/SQL procedure successfully completed.
COLUMN_NAME NUM_DISTINCT HISTOGRAM NUM_BUCKETS
------------------------------ ------------ -------------------- -----------
G 2 FREQUENCY 2
K 3 NONE 1
Yes, there is a histogram.
Preference for no histogram on U.T is set.
PL/SQL procedure successfully completed.
Gathered stats on U.T with default options.
PL/SQL procedure successfully completed.
check existence of histogram on column G
COLUMN_NAME NUM_DISTINCT HISTOGRAM NUM_BUCKETS
------------------------------ ------------ -------------------- ----------
G 2 NONE 1
K 3 NONE 1
The histogram is gone!
Disconnected from Oracle Database 12c Enterprise Edition 
Release 12.2.0.1.0 - 64bit Production

I have tested this script on 11.2, 12.1, and 12.2.

To summarize, I have deleted the histogram on table U.T, column G. I have done this not directly, by issuing delete_column_stats, but indirectly, by issuing set_table_prefs.

Notes

  1. Gather stats option “force” overrides locked statistics but does not override table preference.
SQL> exec dbms_stats.gather_table_stats(ownname=>'U',tabname=>'T',
force=>true)
PL/SQL procedure successfully completed.
SQL> @ dba.tab.col.statistics.sql U T
COLUMN_NAME NUM_DISTINCT HISTOGRAM NUM_BUCKETS
------------------------------ ------------ -------------------- -----------
G 2 NONE 1
K 3 NONE 1

The histogram remains deleted even though option “force” was used.

2. Gathering stats with non-default options can re-create the histogram.

SQL> exec dbms_stats.gather_table_stats(ownname=>'U',tabname=>'T',
method_opt=>'for all columns size 254')
PL/SQL procedure successfully completed.
SQL> @ dba.tab.col.statistics.sql U T
COLUMN_NAME NUM_DISTINCT HISTOGRAM NUM_BUCKETS
------------------------------ ------------ -------------------- -----------
G 2 FREQUENCY 2
K 3 FREQUENCY 3

I want to point out a bad practice here. Routinely gathering histograms on all columns is inadvisable. It is better to start off by gathering stats by using the default options, and then let Oracle decide, based on col_usage$, whether to gather a histogram.

3. The earliest reference I found on this technique of using set_table_prefs to make sure that histograms stay deleted is a 2011 article in Oracle’s Optimizer Blog.

4. Whether table T needs a histogram on column G is a matter unto itself. The answer might be different depending on whether G, M, and F represent “gender”, “male”, and “female” or “granularity”, “medium” and “fine”, whether the distribution of M and F is skewed, whether G appears as a predicate in the application query, whether there is an index on G, and whether a histogram is causing query plan stability issues, or other issues.

5. Others have demonstrated data skew using a high number of rows. I demonstrate skew with only 3 rows.

6. Likewise, others have demonstrated workload by running a high number of statements. I run only one query. Note that a query for a nonexistent value, g = ‘Z’, serves the purpose.

Multiple columns per table

You cannot use script prefer.delete.histo.sql to delete the histogram on more than one column per table. If you try, you get this result.

SQL> @ prefer.delete.histo.sql U T K
Preference for no histogram on U.T is set.
PL/SQL procedure successfully completed.
SQL> @ dba.tab.stat.prefs.sql U
OWNER TABLE_NAME PREFERENCE_NAME PREFERENCE_VALUE
------------------------------ ------------------------------ ------------
U T METHOD_OPT FOR COLUMNS SIZE 1 K

You must state the prefs in a single command, such as:

prefer.delete.histo.sq2

begin
 dbms_stats.set_table_prefs (
 ownname => 'U',
 tabname => 'T',
 pname => 'method_opt',
 pvalue => 'for columns size 1 G size 1 K'
 );
end;
/

Result:

SQL> @ prefer.delete.histo.sq2
PL/SQL procedure successfully completed.
SQL> @ dba.tab.stat.prefs.sql U
OWNER TABLE_NAME PREFERENCE_NAME PREFERENCE_VALUE
------------------------------ ------------------------------ -------------
U T METHOD_OPT FOR COLUMNS SIZE 1 G SIZE 1 K

Use of table prefs in a DBA organization

In a DBA group, members have different opinions based on different experiences. Some DBAs want to lock table stats. Others want to disable the stats gathering job. Sometimes non-DBAs, like developers and data center managers, get into the act with their own opinions.

Using set_table_prefs is offered here as an alternative. dba.tab.stat.prefs.sql can be used to report on tables that have the preference for no histogram set.

The best thing to do is come up with a consensus that is based on principles, current recommendations from Oracle, and rigorous testing.

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