Automatic Statistics Gathering and Stale Statistics
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 коммент. »
Оставьте Ваш комментарий