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)

Little example 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 [collapse]

PS. Also there is a typo in the example from oracle documentation: ‘gather_stale’ instead of ‘gather stale’ – underline instead of blank space.