Team,

Read about automatic stats gathering from SQL Tuning Guide.

https://docs.oracle.com/database/121/TGSQL/tgsql_stats.htm#TGSQL-GUID-E4EFD512-EAF9-4AF3-943F-FDEC7E47B23C
<quote>
When gathering statistics manually, you can reproduce the object prioritization of automatic collection by using the DBMS_AUTO_TASK_IMMEDIATE package. This package runs the same statistics gathering job that is executed during the automatic nightly statistics gathering job.
</quote>

demo@ORA12C> select table_name,last_analyzed,stale_stats
  2  from user_tab_statistics ;

TABLE_NAME LAST_ANALYZED           STA
---------- ----------------------- ---
T1         22-MAR-2016 06:52:16 pm NO
T2         22-MAR-2016 06:51:19 pm YES
T3

3 rows selected.

demo@ORA12C> exec dbms_auto_task_immediate.gather_optimizer_stats;

PL/SQL procedure successfully completed.

demo@ORA12C> select table_name,last_analyzed,stale_stats
  2  from user_tab_statistics ;

TABLE_NAME LAST_ANALYZED           STA
---------- ----------------------- ---
T1         22-MAR-2016 06:52:16 pm NO
T2         22-MAR-2016 06:51:19 pm YES
T3

3 rows selected.

Kindly help me with the below Questions:

1) Why no changes to stats after running "dbms_auto_task_immediate.gather_optimizer_stats"
2) looked into "PL/SQL Packages and Types Reference" Guide, but don't find this package "dbms_auto_task_immediate" documented there, is that got missed out? are we not supposed to use that?

https://blogs.oracle.com/UPGRADE/entry/automatic_statistics_gathering_job_preferences

Incase for a complete test case make use of this script.

drop user demo cascade;
create user demo identified by demo
default tablespace users
quota unlimited on users;
grant connect,resource,dba to demo;
conn demo/demo@ora12c
alter session set nls_date_format='DD-MON-YYYY hh:mi:ss am';
create table t1 as select * from all_objects where rownum <=50000;
create table t2 as select * from all_objects where rownum <=50000;
create table t3 as select * from all_objects where rownum <=50000;
begin 
 dbms_stats.delete_table_stats(user,'T1');
 dbms_stats.delete_table_stats(user,'T2');
 dbms_stats.delete_table_stats(user,'T3');
end;
/ 
begin 
 dbms_stats.gather_table_stats(user,'T1');
 dbms_stats.gather_table_stats(user,'T2');
end;
/
column table_name format a10
select table_name,last_analyzed,stale_stats
from user_tab_statistics;
update t1 set object_id = object_id + 0;
update t2 set object_id = object_id + 1;
commit;
exec dbms_stats.flush_database_monitoring_info;
select table_name,last_analyzed,stale_stats
from user_tab_statistics ;
exec dbms_stats.gather_table_stats(user,'T1');
select table_name,last_analyzed,stale_stats
from user_tab_statistics ;
exec dbms_auto_task_immediate.gather_optimizer_stats;
select table_name,last_analyzed,stale_stats
from user_tab_statistics ;
exec dbms_stats.gather_schema_stats('demo');
select table_name,last_analyzed,stale_stats
from user_tab_statistics ;