Gathering Oracle Database Statistics

Gathering Oracle Database Statistics

It is important to gather as much information as possible about your data in order for the optimiser to make accurate decisions on how to create optimal explain plans.

For this reason it is highly recommended to collect statistics on 100% of your data; for example:

-- script to gather database statistics

begin
DBMS_STATS.GATHER_DATABASE_STATS (
estimate_percent =>100,
block_sample=>FALSE,
method_opt=>'for all columns size auto',
degree=>null,
cascade=>true,
no_invalidate=>false,
options=>'GATHER STALE',
gather_sys=>FALSE);

DBMS_STATS.GATHER_DATABASE_STATS (
estimate_percent =>100,
block_sample=>FALSE,
method_opt=>'for all columns size auto',
degree=>null,
cascade=>true,
no_invalidate=>false,
options=>'GATHER EMPTY',
gather_sys=>FALSE);
end;

-- script to gather dictionary statistics

begin
DBMS_STATS.GATHER_DICTIONARY_STATS (
estimate_percent =>100,
block_sample=>FALSE,
method_opt=>'for all columns size auto',
degree=>null,
cascade=>true,
no_invalidate=>false,
options=>'GATHER STALE'
);

DBMS_STATS.GATHER_DICTIONARY_STATS (
estimate_percent =>100,
block_sample=>FALSE,
method_opt=>'for all columns size auto',
degree=>null,
cascade=>true,
no_invalidate=>false,
options=>'GATHER EMPTY'
);
end;

It is recommended to create a job to update your database on a frequent basis (at least once a week).  In 10g you can use the new dbms_scheduler to create jobs; for example the below script will create a job to gather database statistics (doesn't include dictionary stats) on a nightly basis:

begin
sys.dbms_scheduler.create_job(job_name => '"SYS"."ESTIMATE100_GATHERAUTO"',
job_type => 'PLSQL_BLOCK',
job_action =>
'begin
DBMS_STATS.GATHER_DATABASE_STATS (
estimate_percent =>100,
block_sample=>FALSE,
method_opt=>''for all columns size auto'',
degree=>null,
cascade=>true,
no_invalidate=>false,
options=>''GATHER STALE'',
gather_sys=>FALSE);

DBMS_STATS.GATHER_DATABASE_STATS (
estimate_percent =>100,
block_sample=>FALSE,
method_opt=>''for all columns size auto'',
degree=>null,
cascade=>true,
no_invalidate=>false,
options=>''GATHER EMPTY'',
gather_sys=>FALSE);
end;',
repeat_interval => 'FREQ=DAILY;BYHOUR=2;BYMINUTE=0;BYSECOND=0',
start_date => trunc(sysdate+1) + 2/24,
job_class => 'DEFAULT_JOB_CLASS',
comments => 'Gather auto stats on every table with 100% sampling',
auto_drop => FALSE,
enabled => FALSE);

sys.dbms_scheduler.set_attribute(name => '"SYS"."ESTIMATE100_GATHERAUTO"',
attribute => 'job_priority',
value => 4);

sys.dbms_scheduler.enable('"SYS"."ESTIMATE100_GATHERAUTO"');
end;