Gathering Statistics

Gathering Table Statistics -
exec dbms_stats.gather_table_stats('<SCHEMA_NAME>','<TABLE_NAME>',cascade=>true,estimate_percent=>10,granularity=>'partition', degree=>4);


exec dbms_stats.gather_table_stats(ownname=>'<SCHEMA_NAME>', tabname=>'<TABLE_NAME>',degree=>8,estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE,CASCADE=>DBMS_STATS.AUTO_CASCADE);

Gathering Schema Statistics -

EXEC DBMS_STATS.gather_schema_stats (ownname => '<SCHEMA_NAME>', cascade =>true,estimate_percent => dbms_stats.auto_sample_size,degree=>8);

GATHER SCHEMA STATS USING DBMS_SCHEDULER -

BEGIN

       DBMS_SCHEDULER.create_job (

        job_name        => 'schema_full_stats',

        job_type        => 'PLSQL_BLOCK',

        job_action      => 'BEGIN DBMS_STATS.gather_schema_stats(OWNNAME => ''NORKOM56'',CASCADE => TRUE,ESTIMATE_PERCENT=>DBMS_STATS.AUTO_SAMP_SIZE, DEGREE =>4); END;',

        repeat_interval => 'freq=weekly;byday=sat;byhour=22; byminute=00',

        end_date        => NULL,

        enabled         => TRUE,

        comments        => 'Schema norkom56 stats.');

END;

TO DROP THE SCHEDULER JOB -

BEGIN

   DBMS_SCHEDULER.DROP_JOB(JOB_NAME => 'SCHEMA_FULL_STATS');

END;