,

Automatic Statistics Gathering and Stale Statistics

понедельник, 29 ноября 2010 г. Оставить комментарий

Automatic statistics gathering is enabled by default.
To check whether the statistics are collected automatically have to look at dba_scheduler_jobs.
To enable/disable statistics collection is necessary to use package dbms_scheduler.


select *
from dba_scheduler_jobs
where job_name = 'GATHER_STATS_JOB';

-- disable automatic statistics gathering
begin
dbms_scheduler.disable('GATHER_STATS_JOB');
end;
/

-- enable automatic statistics gathering
begin
dbms_scheduler.enable('GATHER_STATS_JOB');
end;
/

Statistics collected automatically if the property statistics_level is set to TYPICAL or ALL.




select *
from v$parameter
where v$parameter.name = 'statistics_level';

Job collects change all the tables, which included monitoring.




select *
from user_tables
where user_tables.monitoring = 'YES';


Job detects changes in the table (changing rows, adding and deleting). If the number of lines exceeds 10%, then the collection of statistics.

The information about changes of tables can be viewed in the user_tab_modifications view.


10% is not constant and can be changed using dbms_stats.set_global_prefs.

The values of all parameters of the collection of statistics can be found in the table sys.optstat_hist_control$.




begin
dbms_stats.set_global_prefs(pname => 'STALE_PERCENT',
pvalue => 12);
end;
/

select *
from sys.optstat_hist_control$;

More Automatic Statistics Gathering

0 коммент. »

Оставьте Ваш комментарий