Recently I had to gather pending stats with Object Filter List by several objects, so I copied example from documentation and was surprised: instead of gather stats by specified filter list, oracle started to gather stats for all tables in current_schema! And ‘filter list’ applies only with dbms_stats.GATHER_DATABASE_STATS
UPD: Jared Still gave link to registered bug id, which i couldn’t find in MOS before:
Bug 12754926 – DBMS_STATS.gather_schema_stats does not work with an obj_filter_list parameter specified [ID 12754926.8]
Modified:04-Jan-2012 Type:PATCH Status:PUBLISHED
This issue is fixed in 11.2.0.4 (Future Patch Set)
SQL> exec dbms_stats.delete_schema_stats('HR');
PL/SQL procedure successfully completed.
Elapsed: 00:00:01.31
SQL> begin
2 for r in (select table_name
3 from dba_tables t
4 where t.owner='XTENDER'
5 and table_name like 'TMP%'
6 )loop
7 dbms_stats.delete_table_stats('XTENDER',r.TABLE_NAME);
8 end loop;
9 end;
10 /
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.31
SQL> select
2 owner
3 ,table_name
4 ,num_rows
5 ,last_analyzed
6 from dba_tab_statistics t
7 where owner='XTENDER' and table_name like 'TMP%'
8 or owner='HR';
OWNER TABLE_NAME NUM_ROWS LAST_ANALYZED
------------------------------ ------------------------------ ---------- -------------------
HR COUNTRIES
HR DEPARTMENTS
HR EMPLOYEES
HR JOBS
HR JOB_HISTORY
HR LOCATIONS
HR REGIONS
XTENDER TMP
XTENDER TMP1
XTENDER TMP_CONTENT
XTENDER TMP_DATA
XTENDER TMP_DOCUMENT
XTENDER TMP_EXCH
13 rows selected.
Elapsed: 00:00:00.11
SQL> col dt new_value dt
SQL> select sysdate dt from dual;
DT
-------------------
2013-01-27 00:30:21
1 row selected.
Elapsed: 00:00:00.00
SQL> DECLARE
2 filter_lst DBMS_STATS.OBJECTTAB := DBMS_STATS.OBJECTTAB();
3 BEGIN
4 filter_lst.extend(2);
5 filter_lst(1).ownname := 'XTENDER';
6 filter_lst(1).objname := 'TMP%';
7 filter_lst(2).ownname := 'HR';
8 DBMS_STATS.GATHER_SCHEMA_STATS(NULL, obj_filter_list => filter_lst,
9 options => 'gather');
10 END;
11 /
PL/SQL procedure successfully completed.
Elapsed: 00:03:16.89
SQL> select owner,table_name
2 from dba_tables t
3 where t.LAST_ANALYZED>timestamp'&dt'+0;
OWNER TABLE_NAME
------------------------------ ------------------------------
XTENDER TT
XTENDER TR_LOG
XTENDER IOT1
...[skipped 171 rows]...
XTENDER DEPARTMENTS
175 rows selected.
Elapsed: 00:00:01.04
PS. Also there is a typo in the example from oracle documentation: ‘gather_stale’ instead of ‘gather stale’ – underline instead of blank space.