Automated Stats gather on schema is not gathering Index stats

by TJ

Hi. We have automated stats gather on the schema in a database. This job runs everyday. when i check last_analysed date for tables in the schema, it has has the date of the last run of the automated job. But the indexes on the tables have this date as 1st of the month when monthly gather stats run. the only difference between daily and monthly is that monthly deletes the stats and gather new ones, but daily just gathers the stats.

Comments for Automated Stats gather on schema is not gathering Index stats

Average Rating starstarstarstarstar

Click here to add your own comments

Sep 19, 2016
Automatic Statistics Collection is controlled at many levels
by: Anonymous

With Oracle 12c statistics are by default collected automatically on objects when the statistics are deemed to be stale which is dependent on the number of changes made to the object. For a table this is the number of inserts, updates and deletes made.

Auto,atic statistics collection is controlled at the highest level by the database intialisation parameter STATISTICS_LEVEL which must be set to TYPICAL for automatic statistics collection to occur. If this parameter is set to BASIC then statistics will have to be gathered manually by use of the DBMS_STATS.GATHER_SYSTEM_STATS, DBMS_STATS.GATHER_SCHEMA_STATS or DBMS_STATS.GATHER_TABLE_STATS procedures.

Automatic statistics collection is also controlled by the DBMS_AUTO_TASK_ADMIN.ENABLE and DBMS_AUTO_TASK_ADMIN.DISABLE PL/SQL procedures which are used to allow or prevent the execution of the specified cleint or operation. (Clients are specified in the DBA_AUTOTASK_CLIENT view and operations in the DBA_AUTOTASK_OPERATION view).

The running and scheduling of automatic statiscs collection can also be controlled at a more fundamental level by the use of the various procedurfes in the DBMS_SCHEDULER package,

Finally there is the DBMS_STATS package itself which can be used to sepcify preferences for statistics collection at the glocal, database, schema and table level. One of the preferences that can be set is CASCADE which specifies whether or not index statisitics should be gathered at the same time as table statistics.

To return to the question then the problem could be that the wrong job is scheduled or that the CASCADE preference is not set to AUTO_CASCADE at the glocal, database, schema or table level. This can be checked by using the DBMS_STATS.GET_PREFS function.

For more information on automatic statisics collection in Oracle 12c see the href="">Database
Administrator's Guide
and the Oracle PL?SQL Packages and Types Reference for details on the use of the DBMS_SCHEDULER, DBMS_STATS and DBMS_AUTO_TASK_ADMIN packages.

Click here to add your own comments

Join in and write your own page! It's easy to do. How? Simply click here to return to Oracle Questions.