ANBOB™

Using the code below one can find the tables/indexes that have stale statistics in a schema, when options=>’GATHER AUTO’ is used oracle gathers statistics analyzes the tables/indexes that have stale statistics. Table monitoring is enabled by default in 10g to find table which is used to find table statistics, when STATISTICS_LEVEL is set to “BASIC” table monitoring is disabled. In 10g when the value of statistics_level is “typical” or “all” table monitoring is turned enabled. In 9i one can enable/disable table monitoring by calling DBMS_STATS.ALTER_SCHEMA_TABLE_MONITORING,and in 11g  [no]MONITORING get the bounce,default is monitoring.

create or replace procedure show_stale(p_owner varchar2 default user)
is
v_objs dbms_stats.ObjectTab;
BEGIN
dbms_stats.gather_schema_stats(ownname=>upper(p_owner),objlist=>v_objs, options=>'LIST STALE');
FOR i in v_objs.FIRST..v_objs.LAST
LOOP
   dbms_output.put_line(v_objs(i).ownname || '.' || v_objs(i).ObjName || ' ' || v_objs(i).ObjType || ' ' || v_objs(i).partname);
END LOOP;
END;
SQL> conn sys/ as sysdba
Enter password:
Connected.

SQL> set serveroutput on
SQL> exec show_stale;
SYS.APPLY$_SOURCE_SCHEMA TABLE
SYS.AQ$_ALERT_QT_H TABLE
SYS.AQ$_KUPC$DATAPUMP_QUETAB_S TABLE
SYS.ARGUMENT$ TABLE
SYS.CCOL$ TABLE
SYS.CDEF$ TABLE
SYS.COL$ TABLE

...

SQL> exec show_stale('ANBOB');
ANBOB.TOPBOX_ORG_SC_M_VIEW TABLE

PL/SQL procedure successfully completed.

参考oraspin

打赏

微信扫一扫，打赏作者吧～