/*
  Creates a job to gather statistics for all tables and indexes in the given schema
  - run the script as sys user. The script uses one input parameter, the schema name.
  sqlplus sys/<your_connection> as sysdba @./oracle_gather_schema_stats_job.sql <schema_name>
*/
alter session set current_schema = &1;

create or replace procedure gather_stats_manually(schema_name varchar2) is
   v_schema varchar2(128) := schema_name;
   e_code  number;
   e_msg  varchar2(255);
   v_log CLOB;
   v_started_at TIMESTAMP;
   v_estimate_percent NUMBER;
begin
  v_started_at := current_timestamp;
  delete from GATHER_STATS_LOG where started_at < add_months(v_started_at, -12);
  for i in (select t.owner, t.table_name from all_tables t where t.owner = v_schema)
  loop
    if (lower(i.table_name)='object_revision') then
      v_estimate_percent := 15;
    else
      v_estimate_percent := dbms_stats.AUTO_SAMPLE_SIZE;
    end if;
    dbms_stats.gather_table_stats(ownname => i.owner,
                                  tabname => i.table_name,
                                  estimate_percent => v_estimate_percent,
                                  cascade => true,
                                  options => 'GATHER AUTO');
    v_log := v_log||'Stats have been gathered for table '||i.table_name||' in the '||i.owner||' schema.'||chr(10);
  end loop;
  for i in (select t.owner, t.index_name from all_indexes t where t.owner = v_schema)
  loop
    dbms_stats.gather_index_stats(ownname => i.owner,
                                  indname => i.index_name,
                                  estimate_percent => dbms_stats.AUTO_SAMPLE_SIZE);
    v_log := v_log||'Stats have been gathered for index '||i.index_name||' in the '||i.owner||' schema.'||chr(10);
  end loop;
  insert into GATHER_STATS_LOG(ID, STATUS, OUTPUT, STARTED_AT, FINISHED_AT)
  values (GATHER_STATS_LOG_S.NEXTVAL, 'SUCCEEDED', v_log, v_started_at, current_timestamp);
  commit;

exception
  when others then
    e_code := SQLCODE; e_msg := SUBSTR(SQLERRM, 1, 255);
    insert into GATHER_STATS_LOG(ID, STATUS, OUTPUT, ERROR_CODE, ERROR_MSG, STARTED_AT, FINISHED_AT)
    values (GATHER_STATS_LOG_S.NEXTVAL, 'FAILED', v_log, e_code, e_msg, v_started_at, current_timestamp);
    commit;
end gather_stats_manually;
/


begin
  sys.dbms_scheduler.create_job(job_name            => 'GATHER_MANUAL_STATS_FOR_USER',
                                job_type            => 'STORED_PROCEDURE',
                                job_action          => 'gather_stats_manually',
                                number_of_arguments => 1,
                                start_date          => to_date('20200802','YYYYMMDD'),
                                repeat_interval     => 'Freq=Weekly;ByDay=Sun;ByHour=20',
                                end_date            => to_date(null),
                                job_class           => 'DEFAULT_JOB_CLASS',
                                enabled             => false,
                                auto_drop           => false,
                                comments            => '');
  sys.dbms_scheduler.set_job_argument_value(job_name          => 'GATHER_MANUAL_STATS_FOR_USER',
                                            argument_position => 1,
                                            argument_value    => SYS_CONTEXT('USERENV','CURRENT_SCHEMA'));
  sys.dbms_scheduler.enable(name => 'GATHER_MANUAL_STATS_FOR_USER');
end;
/
