Skip to Main Content

Breadcrumb

Question and Answer

Chris Saxon

Thanks for the question, Rajeshwaran.

Asked: March 22, 2016 - 1:52 pm UTC

Last updated: March 22, 2016 - 4:44 pm UTC

Version: 12.1.0.2

Viewed 1000+ times

You Asked

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 ;

and we said...

1. As stated in MOS note 865933.1, you need to execute this package as sysdba for it to work.

2. We'll follow up with the relevant people regarding the documentation of this.

Is this answer out of date? If it is, please let us know via a Comment