Gathering Table statistics in Oracle - DBMS_STATS.GATHER_TABLE_STATS

DECLARE

 CURSOR CUR_PARTITION IS

        SELECT TABLE_NAME FROM DBA_TABLES WHERE OWNER = 'SUVIN' AND PARTITIONED ='YES';

CURSOR CUR_NONPARTITION IS

        SELECT OWNER, TABLE_NAME FROM DBA_TABLES WHERE OWNER IN ('SUVIN','RETAIL') AND PARTITIONED != 'YES' AND TABLE_NAME NOT IN ('RETAIL_TRANS', 'WHOLETRANS');

BEGIN

        FOR REC_PARTITION IN CUR_PARTITION LOOP

                DBMS_STATS.GATHER_TABLE_STATS(

                          ownname => 'SUVIN',

                          tabname         => REC_PARTITION.table_name,

                          partname        => NULL,

                          estimate_percent        => 100,

                          block_sample    => FALSE,

                          method_opt      => 'FOR ALL COLUMNS SIZE 1', -- including column stats, without histograms

                          degree          => 4,                          -- running parallel for partitioned tables with degree of 4

                          granularity     => 'ALL',                     -- global, partition, subpartition stats

                          cascade         => TRUE,                  -- including index stats

                          stattab         => NULL,

                          statid          => NULL,

                          force           => TRUE,

                          statown => NULL);

        END LOOP;

        FOR REC_NONPARTITION IN CUR_NONPARTITION LOOP

                DBMS_STATS.GATHER_TABLE_STATS(

                          ownname => rec_nopart_tab.owner,

                          tabname         => rec_nopart_tab.table_name,

                          partname        => NULL,

                          estimate_percent        => 100,

                          block_sample    => FALSE,

                          method_opt      => 'FOR ALL COLUMNS SIZE 1',  -- including column stats, without histograms

                          degree          => NULL,

                          granularity     => 'DEFAULT',

                          cascade         => TRUE,                                -- including index stats

                          stattab         => NULL,

                          statid          => NULL,

                          force           => TRUE,

                          statown => NULL);

        END LOOP;

END;

/