Oracle automatic stats gathering job

To check if auto job enabled:
SELECT CLIENT_NAME, STATUS FROM DBA_AUTOTASK_CLIENT WHERE CLIENT_NAME='auto optimizer stats collection';

To check parameters used by auto job:

SET ECHO OFF
SET TERMOUT ON
SET SERVEROUTPUT ON
SET TIMING OFF
DECLARE
   v1  varchar2(100);
   v2  varchar2(100);
   v3  varchar2(100);
   v4  varchar2(100);
   v5  varchar2(100);
   v6  varchar2(100);
   v7  varchar2(100);
   v8  varchar2(100);
   v9  varchar2(100);
   v10 varchar2(100);      
BEGIN
   dbms_output.put_line('Automatic Stats Gathering Job - Parameters');
   dbms_output.put_line('==========================================');
   v1 := dbms_stats.get_prefs('AUTOSTATS_TARGET');
   dbms_output.put_line(' AUTOSTATS_TARGET:  ' || v1);
   v2 := dbms_stats.get_prefs('CASCADE');
   dbms_output.put_line(' CASCADE:           ' || v2);
   v3 := dbms_stats.get_prefs('DEGREE');
   dbms_output.put_line(' DEGREE:            ' || v3);
   v4 := dbms_stats.get_prefs('ESTIMATE_PERCENT');
   dbms_output.put_line(' ESTIMATE_PERCENT:  ' || v4);
   v5 := dbms_stats.get_prefs('METHOD_OPT');
   dbms_output.put_line(' METHOD_OPT:        ' || v5);
   v6 := dbms_stats.get_prefs('NO_INVALIDATE');
   dbms_output.put_line(' NO_INVALIDATE:     ' || v6);
   v7 := dbms_stats.get_prefs('GRANULARITY');
   dbms_output.put_line(' GRANULARITY:       ' || v7);
   v8 := dbms_stats.get_prefs('PUBLISH');
   dbms_output.put_line(' PUBLISH:           ' || v8);
   v9 := dbms_stats.get_prefs('INCREMENTAL');
   dbms_output.put_line(' INCREMENTAL:       ' || v9);
   v10:= dbms_stats.get_prefs('STALE_PERCENT');
   dbms_output.put_line(' STALE_PERCENT:     ' || v10);
END;
/

More convenient way to list parameters:
select
dbms_stats.get_prefs('AUTOSTATS_TARGET' ) AUTOSTATS_TARGET,
dbms_stats.get_prefs('CASCADE' ) CASCADE,
dbms_stats.get_prefs('DEGREE' ) DEGREE,
dbms_stats.get_prefs('ESTIMATE_PERCENT' ) ESTIMATE_PERCENT,
dbms_stats.get_prefs('METHOD_OPT' ) METHOD_OPT,
dbms_stats.get_prefs('NO_INVALIDATE' ) NO_INVALIDATE,
dbms_stats.get_prefs('GRANULARITY' ) GRANULARITY,
dbms_stats.get_prefs('PUBLISH' ) PUBLISH,
dbms_stats.get_prefs('INCREMENTAL' ) INCREMENTAL,
dbms_stats.get_prefs('STALE_PERCENT' ) STALE_PERCENT
from DUAL;

What is the difference between auto stats gathering job and gather_schema_stats?

Both activities use the same parameters. So the stats will look the same - IF they get created. The real difference between the Automatic Statistics Gathering job and a manual invocation of GATHER_SCHEMA_STATS is that the latter will refresh ALL statistics whereas the Automatic Statistics Gathering job will refresh only statistics on objects where statistics are missing or marked as STALE.

The same behavior appears when you compare the recommendation to gather dictionary statistics before the upgrade by using DBMS_STATS.GATHER_DICTIONARY_STATS versus a DBMS_STATS.GATHER_SCHMEA_STATS('SYS')call. The latter will refresh all statistics whereas the first one will take less resources but refresh only STALE and missing statistics.

• The Automatic Statistics Gathering job prioritizes objects with NO statistics over objects with STALE statistics
• The Automatic Statistics Gathering job may get interrupted or skip objects leaving them with NO statistics gathered. You can force this by locking statistics - so the Auto job will skip those completely

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';