oracle stalestats_sql script: 找出stale statistics的对象

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(‘topbox_xj’);

TOPBOX_XJ.TOPBOX_ORG_SC_M_VIEW TABLE

PL/SQL procedure successfully completed.

打赏

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