The default value of METHOD_OPT from 10g onwards is ‘FOR ALL COLUMNS SIZE AUTO’.
The definition of AUTO as per Oracle documentation is :
AUTO: Oracle determines the columns to collect histograms based on data distribution and the workload of the columns.
This basically implies that Oracle will automatically create histograms on those columns which have skewed data distribution and there are SQL statements referencing those columns.
However, this gives rise to the problem is that Oracle generates too many unnecessary histograms .
Let’s demonstrate:
– Create a table with skewed data distribution in two columns
SQL>drop table hr.skewed purge;
create table hr.skewed
( empno number,
job_id varchar2(10),
salary number);
insert into hr.skewed select employee_id, job_id, salary
from hr.employees;
– On gathering statistics for the table using default options, it can be seen that histogram is not gathered on any column although data
distribution in columns JOB_ID and SALARY is skewed
SQL>exec dbms_stats.gather_table_stats('HR','SKEWED');
col table_name for a10
col column_name for a10
select TABLE_NAME,COLUMN_NAME,HISTOGRAM
from dba_tab_columns where table_name = 'SKEWED';
TABLE_NAME COLUMN_NAM HISTOGRAM
---------- ---------- ---------------
SKEWED SALARY NONE
SKEWED JOB_ID NONE
SKEWED EMPNO NONE
– Let’s now issue some queries querying the table based on the three columns in the table followed by statistics gathering to verify that histograms get automatically created only on columns with skewed data distribution.
– No histogram gets created if column EMPNO is queried which
has data distributed uniformly
SQL>select * from hr.skewed where empno = 100;
exec dbms_stats.gather_table_stats('HR','SKEWED');
col table_name for a10
col column_name for a10
select TABLE_NAME,COLUMN_NAME,HISTOGRAM
from dba_tab_columns where table_name = 'SKEWED';
TABLE_NAME COLUMN_NAM HISTOGRAM
---------- ---------- ---------------
SKEWED SALARY NONE
SKEWED JOB_ID NONE
SKEWED EMPNO NONE
– A histogram gets created on JOB_ID column as soon as we search for records with a JOB_ID as data distribution is non-uniform in JOB_ID column
SQL>select * from hr.skewed where job_id = 'CLERK';
exec dbms_stats.gather_table_stats('HR','SKEWED');
col table_name for a10
col column_name for a10
select TABLE_NAME,COLUMN_NAME,HISTOGRAM
from dba_tab_columns where table_name = 'SKEWED';
TABLE_NAME COLUMN_NAM HISTOGRAM
---------- ---------- ---------------
SKEWED SALARY NONE
SKEWED JOB_ID FREQUENCY
SKEWED EMPNO NONE
– A histogram gets created on SALARY column when search is made for employees drawing salary more than 10000 as data distribution is non-uniform in SALARY column.
SQL>select * from hr.skewed where salary < 10000;
exec dbms_stats.gather_table_stats('HR','SKEWED');
col table_name for a10
col column_name for a10
select TABLE_NAME,COLUMN_NAME,HISTOGRAM
from dba_tab_columns where table_name = 'SKEWED';
TABLE_NAME COLUMN_NAM HISTOGRAM
---------- ---------- ---------------
SKEWED SALARY FREQUENCY
SKEWED JOB_ID FREQUENCY
SKEWED EMPNO NONE
Thus gathering statistics using default options, manually or as part of the automatic maintenance task, might lead to creation of histograms on all such columns which have skewed data distribution and had been part of the search clause even once. That is, Oracle makes even the histograms you didn’t ask for. Some of the histograms might not be needed by the application and hence are undesirable as computing histograms is a resource intensive operation and moreover they might degrade the performance as a result of their interaction with bind peeking.
Solution
Employ FOR ALL COLUMNS SIZE REPEAT option of METHOD_OPT parameter which prevents deletion of existing histograms and collects histograms only on the columns that already have histograms.
First step is to eliminate unwanted histograms and have histograms only on the desired columns.
Well, there are two options:
OPTION-I: Delete histograms from unwanted columns and use REPEAT option henceforth which Collects histograms only on the columns that already have histograms.
– Delete unwanted histogram for SALARY column
SQL>exec dbms_stats.gather_table_stats('HR','SKEWED', -
METHOD_OPT => 'for columns salary size 1');
-- Verify that histogram for salary column has been deleted
col table_name for a10
col column_name for a10
select TABLE_NAME,COLUMN_NAME,HISTOGRAM
from dba_tab_columns where table_name = 'SKEWED';
TABLE_NAME COLUMN_NAM HISTOGRAM
---------- ---------- ---------------
SKEWED SALARY NONE
SKEWED JOB_ID FREQUENCY
SKEWED EMPNO NONE
– Issue a SQL with salary column in where clause and verify that gathering stats using repeat option retains histogram on JOB_ID column and does not cause histogram to be created on salary column.
SQL>select * from hr.skewed where salary < 10000;
exec dbms_stats.gather_table_stats('HR','SKEWED',-
METHOD_OPT => 'for columns salary size REPEAT');
col table_name for a10
col column_name for a10
select TABLE_NAME,COLUMN_NAME,HISTOGRAM
from dba_tab_columns where table_name = 'SKEWED';
TABLE_NAME COLUMN_NAM HISTOGRAM
---------- ---------- ---------------
SKEWED SALARY NONE
SKEWED JOB_ID FREQUENCY
SKEWED EMPNO NONE
OPTION-II: Wipe out all histograms and manually add only the desired ones. Use REPEAT option henceforth which Collects histograms only on the columns that already have one.
– Delete histograms on all columns
SQL>exec dbms_stats.gather_table_stats('HR','SKEWED',-
METHOD_OPT => 'for all columns size 1');
– Verify that histograms on all columns have been dropped
SQL>col table_name for a10
col column_name for a10
select TABLE_NAME,COLUMN_NAME,HISTOGRAM
from dba_tab_columns where table_name = 'SKEWED';
TABLE_NAME COLUMN_NAM HISTOGRAM
---------- ---------- ---------------
SKEWED SALARY NONE
SKEWED JOB_ID NONE
SKEWED EMPNO NONE
– Create histogram only on the desired JOB_ID column
SQL>exec dbms_stats.gather_table_stats('HR','SKEWED',-
METHOD_OPT => 'for columns JOB_ID size AUTO');
– Verify that histogram has been created on JOB_ID
SQL>col table_name for a10
col column_name for a10
select TABLE_NAME,COLUMN_NAME,HISTOGRAM
from dba_tab_columns where table_name = 'SKEWED';
TABLE_NAME COLUMN_NAM HISTOGRAM
---------- ---------- ---------------
SKEWED SALARY NONE
SKEWED JOB_ID FREQUENCY
SKEWED EMPNO NONE
- Verify that gathering stats using repeat option creates histogram only on JOB_ID column on which it already exists
SQL>exec dbms_stats.gather_table_stats('HR','SKEWED',-
METHOD_OPT => 'for columns salary size REPEAT');
SQL>col table_name for a10
col column_name for a10
select TABLE_NAME,COLUMN_NAME,HISTOGRAM
from dba_tab_columns where table_name = 'SKEWED';
TABLE_NAME COLUMN_NAM HISTOGRAM
---------- ---------- ---------------
SKEWED SALARY NONE
SKEWED JOB_ID FREQUENCY
SKEWED EMPNO NONE
That is, now Oracle will no longer make histograms you didn’t ask for.
– Finally, change the preference for METHOD_OPT parameter of automatic stats gathering job from default value of AUTO to REPEAT so that it will gather histograms only for the columns already having one.
– Get Current value –
SQL> select dbms_stats.get_prefs ('METHOD_OPT') from dual;
DBMS_STATS.GET_PREFS('METHOD_OPT')
-----------------------------------------------------------------------
FOR ALL COLUMNS SIZE AUTO
– Set preference to REPEAT–
SQL> exec dbms_stats.set_global_prefs ('METHOD_OPT','FOR ALL COLUMNS SIZE REPEAT');
– Verify –
SQL> select dbms_stats.get_prefs ('METHOD_OPT') from dual;
DBMS_STATS.GET_PREFS('METHOD_OPT')
-----------------------------------------------------------------------
FOR ALL COLUMNS SIZE REPEAT
From now onwards, gathering of statistics, manually or automatically will not create any new histograms while retaining all the existing ones.
I hope this post is useful.
Happy reading….
References:
https://blogs.oracle.com/optimizer/entry/how_does_the_method_opt
http://www.pythian.com/blog/stabilize-oracle-10gs-bind-peeking-behaviour/
https://richardfoote.wordpress.com/2008/01/04/dbms_stats-method_opt-default-behaviour-changed-in-10g-be-careful/
———————————————————————————————–
Related Links:
Home
Database Index
Tuning Index
———————————————————————————————
Like this:
Like Loading...