Gather Stats for schemas

set serveroutput on;

set echo on;

set timing on;

set time on;

EXECUTE dbms_stats.set_global_prefs('DEGREE', DBMS_STATS.AUTO_DEGREE);

EXECUTE DBMS_STATS.GATHER_SCHEMA_STATS(ownname => 'MERCH_BANK_DHL', estimate_percent => dbms_stats.auto_sample_size, CASCADE=> true, GRANULARITY=> 'ALL');

EXECUTE DBMS_STATS.GATHER_SCHEMA_STATS(ownname => 'MERCH_PROC_DHL', estimate_percent => dbms_stats.auto_sample_size, CASCADE=> true, GRANULARITY=> 'ALL');

EXECUTE DBMS_STATS.GATHER_SCHEMA_STATS(ownname => 'MERCH_BANK_CBD', estimate_percent => dbms_stats.auto_sample_size, CASCADE=> true, GRANULARITY=> 'ALL');

EXECUTE DBMS_STATS.GATHER_SCHEMA_STATS(ownname => 'MERCH_AA_PROC', estimate_percent => dbms_stats.auto_sample_size, CASCADE=> true, GRANULARITY=> 'ALL');

EXECUTE DBMS_STATS.GATHER_SCHEMA_STATS(ownname => 'MAPS_PG_CBD', estimate_percent => dbms_stats.auto_sample_size, CASCADE=> true, GRANULARITY=> 'ALL');

EXEC DBMS_STATS.gather_dictionary_stats;

--EXEC DBMS_STATS.gather_fixed_objects_stats;

ALTER SYSTEM FLUSH SHARED_POOL;

ALTER SYSTEM FLUSH BUFFER_CACHE;

exit;

Call above sql script through shell script and crontab entry is below the reference.

#!/bin/bash

export ORACLE_HOME=/u01/app/oracle/product/12.1.0/dbhome_2

fdate=`date '+%d-%b-%Y-%H_%M'`

/u01/app/oracle/product/12.1.0/dbhome_2/bin/sqlplus dba/dbadba098#@MERUAT2 @/orabackup/scripts/mer_pos_eee_schema_stats.sql >/orabackup/scripts/logs/mer_pos_eee_schema_stats-${fdate}-execution.log

exit;