Gathering Statistics

Gathering stats doesn't lock the table/index, only locks the object statistics.

As of 11g, the gathering of object statistics is integrated into the automated tasks scheduled with the Scheduler:

SELECT task_name, status

FROM dba_autotask_task

WHERE client_name = 'auto optimizer stats collection';

Statistics history ,Display current value:

SELECT dbms_stats.get_stats_history_retention() AS retention from DUAL;

Set retention period to 14 days:

dbms_stats.alter_stats_history_retention(retention => 14);

When statistics were modified for a given table:

SELECT stats_update_time FROM dba_tab_stats_history

WHERE owner = 'SYS' and table_name = 'TAB$';

Restore object statistics of the schema SH to the values that were in use 1 day ago:

dbms_stats.restore_schema_stats(ownname => 'SH',as_of_timestamp => systimestamp – 1,force => TRUE)

dbms_stats log information about their execution:

SELECT operation, start_time,2 (end_time-start_time) DAY(1) TO SECOND(0) AS duration

FROM dba_optstat_operations ORDER BY start_time DESC;