Schema_Stats_Job

 oracleao@gorastdprm01(gpromp02_cdb6335): cat stats.sql

set timing on

spool stats_new.log

select name from v$database;

select name from v$pdbs;

set echo on

set time on

exec dbms_stats.GATHER_SCHEMA_STATS(ownname => 'VOSPCQ_TEST_R70', estimate_percent =>DBMS_STATS.AUTO_SAMPLE_SIZE,method_opt => 'FOR ALL COLUMNS SIZE AUTO', degree => 4);

SPOOL off

exit

PDB:

nohup sqlplus a406771/Volvo1234@GIMPST02 @stats1.sql &

Non-CDB:

nohup sqlplus "/ as sysdba" @stats_new.sql &

Table stats:

exec dbms_stats.GATHER_SCHEMA_STATS(ownname => 'PROMPT01', estimate_percent =>DBMS_STATS.AUTO_SAMPLE_SIZE,method_opt => 'FOR ALL COLUMNS SIZE AUTO', degree => 4);

exec dbms_stats.gather_table_stats (ownname => 'PROMPT01', tabname => 'PMT_DOCUMENTS', estimate_percent => dbms_stats.auto_sample_size, method_opt => ‘for all columns size auto’, cascade => true, degree => 4);