Category Archives: Stats

ORA-38029 Object Statistics Are Locked – due to import with rows=n

Posted on 8 March 2011 by Andrew Fraser

A strange one – but it is a documented feature in oracle. If you import specifying option ‘rows=n’, then statistics for all imported tables will be locked after the import operation is finished. That is only for import – an … Continue reading →

Posted in Stats | 1 Comment

Purge and regather all optimizer stats

Posted on 13 May 2010 by Andrew Fraser

Purge and regather all optimizer stats with: exec dbms_stats.delete_database_stats exec dbms_stats.delete_system_stats exec dbms_scheduler.run_job(‘GATHER_STATS_JOB’) That works provided statistics level has not been changed to none show parameter statistics_level You can gather more detailed system stats with: exec dbms_stats.gather_system_stats(‘START’) exec dbms_stats.gather_system_stats(‘STOP’) You … Continue reading →

Posted in Stats | Leave a comment

dbms_stats export import optimizer statistics

Posted on 7 April 2010 by Andrew Fraser

Examples of syntax to export/import dbms_stats: exec dbms_stats.create_stat_table ( ownname => user , stattab => ‘temp_stats’ ) ; exec dbms_stats.export_table_stats ( ownname => user , stattab => ‘temp_stats’, tabname => ‘mytable’, statid => ‘Taken_7Apr2010′) ; exec dbms_stats.import_table_stats ( ownname => … Continue reading →

Posted in Stats | Leave a comment

Automatic gather stats job

Posted on 5 March 2010 by Andrew Fraser

check it is on with: select state, last_start_date from dba_scheduler_jobs where job_name = ‘GATHER_STATS_JOB’ ; Switch it on and off with: exec dbms_scheduler.disable(‘GATHER_STATS_JOB’) exec dbms_scheduler.enable(‘GATHER_STATS_JOB’)

Posted in Performance tuning, Stats | Leave a comment