DBMS_STATS Problems

From: MacGregor, Ian A. <ian_at_SLAC.Stanford.EDU>
Date: Tue, 27 Jun 2000 13:16:31 -0700
Message-Id: <10541.110582@fatcity.com>

I am testing the following package:

---

---

create or replace package nightly_stats as

   procedure gather_stale_stats_nightly(schema_owner in varchar2);
   procedure gather_empty_stats_nightly(schema_owner in varchar2);
   procedure gather_NIGHTLY_STATS_schemas;

end nightly_stats;
/

create or replace package body nightly_stats as

    procedure gather_stale_stats_nightly(schema_owner in varchar2) as     BEGIN

        DBMS_STATS.GATHER_SCHEMA_STATS
            (OWNNAME => schema_owner,
             CASCADE => TRUE,
             options => 'GATHER STALE');

    END gather_stale_stats_nightly;
    procedure gather_empty_STATS_nightly(schema_owner in varchar2) as     BEGIN

        DBMS_STATS.GATHER_SCHEMA_STATS
            (OWNNAME => schema_owner,
             CASCADE => TRUE,
             options => 'GATHER EMPTY');

    END gather_empty_stats_nightly;

    procedure gather_NIGHTLY_STATS_schemas as

        schema_owner varchar2(30);
        cursor get_schema is
            select distinct owner from dba_tables where owner != 'SYS';
    BEGIN
        open get_schema;
        loop
            fetch get_schema into schema_owner;
        Exit when get_schema%notfound;
            gather_empty_stats_nightly(schema_owner);
            gather_stale_stats_nightly(schema_owner);
        end loop;
        close get_schema;

    end gather_NIGHTLY_STATS_schemas;
end nightly_stats;
/

---

---

N.B. this code will bomb if you have any tables where IOT overflow tables; you will have at least one such table if you setup Intermedia. This code is running on three separate databases on one of them I received the following errors:

ERROR at line 1:

ORA-06502: PL/SQL: numeric or value error: character string buffer too small
ORA-06512: at "SYS.DBMS_STATS", line 4560
ORA-06512: at "SYS.DBMS_STATS", line 4683
ORA-06512: at "SYS.DBMS_STATS", line 4655
ORA-06512: at "ORACLE.NIGHTLY_STATS", line 11
ORA-06512: at "ORACLE.NIGHTLY_STATS", line 27
ORA-06512: at line 1

All this is pointing to the gather_empty_stats_nightly procedure. However if I change the CASCADE parameter from TRUE to FALSE the program runs to completion. Of course after that CASCADE => TRUE works as the object causing the problem has statistics. The fact that CASCADE => FALSE worked and CASCADE = TRUE did not makes me think that DBMS_STATS is not bug free.

Metalink did have a note on this in one of the forums, but Oracle wanted a reproducible case. Has anybody else run into this?

Ian MacGregor Received on Tue Jun 27 2000 - 15:16:31 CDT