Gather stats

DECLARE
   v_stmt          VARCHAR2 (200);
   v_cnt           NUMBER;
   v_deviation     NUMBER DEFAULT 10;
   v_est_percent   NUMBER DEFAULT 20;
BEGIN

   BEGIN
      EXECUTE IMMEDIATE 'DROP TABLE tabstat';
   EXCEPTION
      WHEN OTHERS
      THEN
         NULL;
   END;

   BEGIN
      EXECUTE IMMEDIATE 'CREATE TABLE tabstat (
   tabname     VARCHAR2 (30),
   cnt         NUMBER,
   nrows       NUMBER,
   deviation   NUMBER,
   last_analyzed DATE
)';
   EXCEPTION
      WHEN OTHERS
      THEN
         DBMS_OUTPUT.put_line ('error');
   END;

   EXECUTE IMMEDIATE
      ' INSERT INTO tabstat (tabname,
                        cnt,
                        nrows,
                        last_analyzed)
      (SELECT ut.table_name,
              TO_NUMBER (
                 EXTRACTVALUE (
                    xmltype (
                       DBMS_XMLGEN.getxml (
                             ''select /*+ PARALLEL */ count(*) c from ''
                          || ut.table_name)),
                    ''/ROWSET/ROW/C''))
                 COUNT,
              num_rows,
              last_analyzed
         FROM user_tables ut,
              (SELECT DISTINCT
                      table_name, NVL (stattype_locked, ''N'') stattype_locked
                 FROM user_tab_statistics) uts
        WHERE     ut.table_name = uts.table_name
              AND ut.temporary = ''N''
              AND uts.stattype_locked = ''N''
              AND ut.table_name NOT IN (''TABSTAT'')
     AND ut.table_name NOT LIKE ''PROCESS%'')';


   EXECUTE IMMEDIATE
      ' UPDATE tabstat
      SET deviation = ROUND (ABS ( (cnt - nrows) / cnt) * 100, 2)
      WHERE cnt !=0';

   EXECUTE IMMEDIATE 'UPDATE tabstat
      SET deviation = 100
      WHERE cnt =0 AND nrows !=0 ';

EXECUTE IMMEDIATE 'UPDATE tabstat
      SET deviation = 0
      WHERE cnt =0 AND nrows =0 ';


   EXECUTE IMMEDIATE
      '
    BEGIN
    FOR tab_rec IN (  SELECT tabname
                       FROM tabstat
                      WHERE deviation > NVL (&1, 0)
                   ORDER BY tabname)
   LOOP
      DBMS_STATS.GATHER_TABLE_STATS (
         ownname            => USER,
         tabname            => tab_rec.tabname,
         estimate_percent   => DBMS_STATS.AUTO_SAMPLE_SIZE,
         degree             => 8,
         method_opt         => ''FOR ALL COLUMNS'',
         cascade            => TRUE,
no_invalidate    => FALSE);

    UPDATE tabstat SET last_analyzed = SYSDATE WHERE tabname=tab_rec.tabname;
   END LOOP;
   END ;';

COMMIT ;

END;
/


Query to find out table name and actual number of rows in each table:

1.
SELECT ut.table_name,
       TO_NUMBER (
          EXTRACTVALUE (
             xmltype (
                DBMS_XMLGEN.getxml (
                   'select /*+ PARALLEL */ count(*) c from ' || ut.table_name)),
             '/ROWSET/ROW/C'))
          COUNT,
       num_rows,
       last_analyzed
  FROM user_tables ut ;

2.
SELECT table_name, COLUMN_VALUE cnt
  FROM user_tables, XMLTABLE ( ('count(ora:view("' || table_name || '"))'))
 WHERE table_name IN ('EMP', 'DEPT', 'BONUS');

3.
SELECT table_name,
       DBMS_XMLGEN.getxmltype ('select count(*) c from ' || table_name).EXTRACT (
          '//text()').getnumberval ()
          tot_rows
  FROM user_tables
 WHERE iot_type IS NULL OR iot_type != 'IOT_OVERFLOW';