Category: Stats

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

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

Posted in Stats

Purge and regather all optimizer stats

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

Posted in Stats

dbms_stats export import optimizer statistics

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

Posted in Stats

Automatic gather stats job

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