Tag Archives: Histogram

Understand Gather Stats job and default behavior in Oracle

Understand Gather Stats job and default behavior in Oracle

Gather Stats job is the default job running in Oracle Database for collecting the most used tables stats automatically. Oracle monitor the most DML tables with help of DBA_TAB_MODIFICATIONS view which stores information about the inserts, deletes, and updates to a table. Gather Stats analyzed stats for those tables having more than 10% rows done DML Operations.

Check the Job Status

SQL> select client_name,status from dba_autotask_client;
------------------------------- --------
auto optimizer stats collection DISABLED
auto space advisor ENABLED
sql tuning advisor ENABLED

Check the job timing with following commands

select operation,target,start_time,end_time from dba_optstat_operations where operation='gather_database_stats(auto)';

Enable and disable the gather stats job

-- Disable
exec dbms_auto_task_admin.disable(client_name=> 'auto optimizer stats collection');
-- Enable
exec dbms_auto_task_admin.enable(client_name=>'auto optimizer stats collection');

Two ways to change the default preferences for Statistics Gather Job

1. Manually used the Parameter setting while executing the DBMS_STATS.GATHER_*_STATS procedure for gather stats.


exec dbms_stats.gather_table_stats(
estimate_percent=> DBMS_STATS.AUTO_SAMPLE_SIZE,
degree=> null,
granularity=> 'AUTO',
method_opt=> 'FOR ALL COLUMNS SIZE AUTO');

2. Oracle defines default values for preferences, you can change it with procedure DBMS_STATS.SET_*_PREFS.
Following are the procedures to change preferences at different level:

DBMS_STATS.SET_*_PREFS Procedure is used to change the default values for of parameters for control statistics collection.
SET_TABLE_PREFS: Lets you specify default parameters to be used by the DBMS_STATS.GATHER_*_STATS procedures for a specific table
SET_SCHEMA_PREFS: Lets you change the default parameters to be used by the DBMS_STATS.GATHER_*_STATS procedures for all objects in a specific schema
SET_DATABASE_PREFS: Lets you change the default parameters to be used by the DBMS_STATS.GATHER_*_STATS procedures for the entire database, including all user schema’s and system schema’s such as SYS and SYSTEM
SET_GLOBAL_PREFS: Sets global statistics preferences; this procedure lets you change the default statistic collection parameters for any object in the database that doesn’t have an existing preference at the table level. If you don’t set table level preferences or you don’t set any parameter explicitly in the DBMS_STATS.GATHER_*_STATS procedure, the parameters default to their global settings.

Example for Check and Change the Default preferences:

--at the database level by using the SET_DATABASE_PREFS procedure:
SQL> execute dbms_stats.set_database_prefs('ESTIMATE_PERCENT','20');

--check default value set in database with get_prefs procedure:
select dbms_stats.get_prefs ('STALE_PERCENT','SH') stale_percent from dual;

Following are the list of preferences used by gather stats:

Used for database may or may not collect index statistics along with the table statistics. By default,(cascade=true) it collect stats for all of a tables plus indexes.

Means to degree of parallelism the database use for gather the statistics.The default setting is DBMS_STATS.AUTO_DEGREE.

ESTIMATE_PERCENT: specifies the percentage of rows the database must use to estimate for the Statistics. By Default DBMS_STATS.AUTO_SAMPLE_SIZE. It’s value from 0% to 100%. 100% means all rows scanned for statistics.
METHOD_OPT: Two values you can used
1. Columns for which the database will collect statistics.
2. Columns on which the database will create histograms.

Means Database must collect statistics for all columns or only for the indexed columns. If you used the FOR ALL INDEXED COLUMNS option, the database will collect statistics only for those columns that have an index on them.

FOR COLUMNS [size_clause] column [size_clause] [,COLUMN [size_clause]…]
Means one or more columns on which the database must gather statistics.
column stands for column_name or extension name or expression used for gather the stats on the specific column or expression for tables.

size_clause used in both cases which tell database to collect histograms for a column.
One option is to supply an integer value indicating the number of histogram buckets in range 1 through 254.
Value 254 : Create the histograms on all the column mentioned in for or all options above.
Value 1 : then it will not create histograms on the column because of single bucket. Setting value 1 remove the histogram if already present
REPEAT : Histogram is collected on those columns which already have histograms.
AUTO : Database determine for the columns for collect histograms based on column usage plus data distribution.
SKEWONLY : Database determine for which columns collect histograms based on column data distribution.
Default value for the METHOD_OPT parameter is FOR ALL COLUMNS SIZE AUTO.

For Example:

SQL> exec dbms_stats.gather_table_stats('HR','EMPLOYEES',method_opt=> 'for columns size 254 job_id')
PL/SQL procedure successfully completed.

Default value for the NO_INVALIDATE parameter.TRUE means database doesn’t invalidate the dependent cursors of the table.FALSE means database immediately invalidates the dependent cursors. DBMS_STATS.AUTO_INVALIDATE is auto.

Basically used for statistics gathering for partitioned tables. Having following options to set:
ALL: Gathers subpartition-, partition-, and global-level statistics; this setting provides a very accurate set of table statistics but consuming lot of resources and time.
GLOBAL: Gathers global statistics for a table.
PARTITION: Gathers only partition statistics.
GLOBAL AND PARTITION: Gathers the global and partition statistics, but not the subpartition level statistics.
SUBPARTITION: Gathers only subpartition statistics.
AUTO: Default value for the GRANULARITY parameter and determines the granularity based on the partitioning type.


Create and delete Histogram in Oracle

Histogram in Oracle

Histogram used by SQL Query optimizer to make better execution plan.

A Histogram is a column statistic that provides more detailed information about the data distribution in a table column.

By Default optimizer use the uniform distribution of rows across the distinct value of columns.

Suppose if table has 3000 rows and one column Country has India has 2500 rows, USA has 300 rows and England has 200 rows. By default without histogram, Optimizer considers 1000 rows each for India, USA and England. To make and provide more relevant data to optimizer for choose better plan for SQL Queries we use histogram.

Col column_name for a30
Select column_name , notes , histogram from user_tab_col_statistics where table_name = 'K1' and column_name = 'OWNER';

------------    -------------   -------------
OWNER                           NONE
Elapsed: 00:00:00.01

For Creation of histogram on column you need to gather the stats as follows

Gather Stats with histogram

Select column_name , notes , histogram from user_tab_col_statistics where table_name = 'K1' and column_name = 'OWNER';

------------     -----------    -------------
OWNER                           FREQUENCY

Delete the Histogram:

Exec  dbms_stats.delete_column_stats(ownname=>'',

Select column_name,notes,histogram from user_tab_col_statistics where table_name = 'K1' and column_name = 'OWNER';

---------------    ---------   -------------
OWNER                          NONE