Updating Oracle schema statistics periodically
Oracle Execution plans strongly depend on Oracle statistics that must be regularly updated otherwise Oracle might generate slow execution plans. The SQL commands below can be used to check Oracle statistics data. The statements require Oracle DBA permissions:
• select d.OWNER, d.TABLE_NAME, d.LAST_ANALYZED, d.STALE_STATS, d.SAMPLE_SIZE, d.NUM_ROWS from dba_tab_statistics d ORDER BY d.OWNER asc, d.TABLE_NAME asc;
• select d.OWNER, d.TABLE_NAME, d.INDEX_NAME, d.LAST_ANALYZED, d.STALE_STATS, d.NUM_ROWS, d.SAMPLE_SIZE from dba_ind_statistics d ORDER BY d.OWNER asc, d.TABLE_NAME asc, d.INDEX_NAME asc;
• select d.OWNER, d.TABLE_NAME, d.STATS_UPDATE_TIME from dba_tab_stats_history d ORDER BY d.OWNER asc, d.TABLE_NAME asc, d.STATS_UPDATE_TIME desc;
Schema statistics gathering job
To improve Oracle database performance starting from 20.11-final, Codebeamer uses an Oracle Scheduler job to gather statistics for it's schemas tables and indexes. The job runs weekly, by default on Sunday at 8:00PM (database time).