Показаны сообщения с ярлыком statistics. Показать все сообщения
Показаны сообщения с ярлыком statistics. Показать все сообщения
,

Automatic Statistics Gathering and Stale Statistics

понедельник, 29 ноября 2010 г. 0 коммент.

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 Читать полностью