Translate

Search This Blog

gather and restore system statistics in oracle

First query system statistics from sys schema:

select * from aux_stats$
or
select  pname, pval1  from sys.aux_stats$

--now before starting work load
execute dbms_stats.gather_system_stats('Start');
-- run the work load
--after work load has run for representative and significant time
execute dbms_stats.gather_system_stats('Stop');

--to delete system statistics
execute dbms_stats.delete_system_stats

--to restore system statistics

--first find the time to which you want system statistics to restore to
select * from DBA_OPTSTAT_OPERATIONS order by start_time

exec dbms_stats.restore_system_stats(as_of_timestamp=>to_timestamp('27-APR-13 02.42.59'))
execute dbms_stats.restore_table_stats ('SCOTT','EMP','25-JUL-07 12.01.20.766591 PM +02:00');