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).

How to create the gathering statistics job

The gathering statistics job consists of 2 parts: a log table where gathering history is stored and the job itself.

1. To create the log table you should either:
   • Upgrade codeBeamer to 20.11-final, OR
   • If you don't want to upgrade codebeamer, download the log table creation script from here: oracle_create_gather_stats_log_table.sql and run the script as CBROOT, e.g
     

     sqlplus CBROOT/password@<your connection> @./oracle_create_gather_stats_log_table.sql

2. Download the job creation script: oracle_gather_schema_stats_job.sql and run the script as Oracle DBA. The script uses one parameter: codeBeamer's schema name which should be passed from sqlplus
   

   sqlplus sys/<your_connection> as sysdba @./oracle_gather_schema_stats_job.sql <your_schema_name>

e.g

sqlplus sys/Oradoc_db1@ORCLCDB as sysdba @./oracle_gather_schema_stats_job.sql CBROOT

How to run the job manually

By default the job runs once a week, on Sunday at 8:00PM (database time). However it can be started manually at any time .

• Log in to your schema with the codeBeamer Oracle account
• Run the following script to start the job asynchronously:
  

  BEGIN
    DBMS_SCHEDULER.RUN_JOB(
      JOB_NAME            => 'GATHER_MANUAL_STATS_FOR_USER',
      USE_CURRENT_SESSION => FALSE);
  END;
  /

• Running the job can take a few minutes depending on the database size. To see the job result check the GATHER_STATS_LOG table.

How to alter the job

You can alter the job's attributes, e.g change the time when it runs to every Saturday, 6PM:

• Log in to your schema with the codeBeamer Oracle account
• Run the following script to change the job's appropriate attributes :
  

  BEGIN
    DBMS_SCHEDULER.SET_ATTRIBUTE (
     name         =>  'GATHER_MANUAL_STATS_FOR_USER',
     attribute    =>  'repeat_interval',
     value        =>  'Freq=Weekly;ByDay=Sat;ByHour=18');
  END;
  /

How check fragmentation

select
   'alter table '||t.owner||'.'||t.table_name||' enable ROW movement;' as enable_row_movement,
   'alter table '||t.owner||'.'||t.table_name||' shrink SPACE;' as shirk_query,
   'alter table '||t.owner||'.'||t.table_name||' move;' as move,
   t.table_name,
   round((t.blocks*8),2) as "size (kb)" ,
   round((t.num_rows*t.avg_row_len/1024),2) as "actual_data (kb)",
   round((t.blocks*8),2) - round((t.num_rows*t.avg_row_len/1024),2) as "wasted_space (kb)",
   case when (t.num_rows*t.avg_row_len/1024) = 0 then 100 else round( (1-((t.num_rows*t.avg_row_len)/(t.blocks*8192)))*100 ) end as "ratio"
from
   dba_tables t
where 1=1
  and round((t.blocks*8),2) > round((t.num_rows*t.avg_row_len/1024),2)
  and t.owner = ?
order by "wasted_space (kb)" desc;