Database Tuning (MOSC)

MOSC Banner

Auto Stats job does not collect stats for empty tables

edited Jan 21, 2019 4:12AM in Database Tuning (MOSC) 6 commentsAnswered

Hi all,

    Auto Stats job is not collecting stats for empty tables without statistics. If I insert a row, then auto stats job does collect them.

    11.2 database

    Following the testcase:

set serveroutput on

SQL> drop table adm_bbdd.test;

Table dropped.

SQL>  create table adm_bbdd.test (a char(1));

Table created.

declare

mystaleobjs dbms_stats.objecttab;

    begin

dbms_stats.gather_schema_stats(ownname=>'ADM_BBDD', options=>'LIST AUTO',objlist=>mystaleobjs);

    for i in 1 .. mystaleobjs.count loop

dbms_output.put_line(mystaleobjs(i).objname);

end loop;

end;

TEST   <=========

PL/SQL procedure successfully completed.

declare

mystaleobjs dbms_stats.objecttab;

    begin

dbms_stats.gather_schema_stats(ownname=>'ADM_BBDD', options=>'LIST STALE',objlist=>mystaleobjs);

Howdy, Stranger!

Log In

To view full details, sign in to My Oracle Support Community.

Register

Don't have a My Oracle Support Community account? Click here to get started.

Category Leaderboard

Top contributors this month

New to My Oracle Support Community? Visit our Welcome Center

MOSC Help Center