dbms_stats.gather_schema_stats : DBMS_STATS « System Packages « Oracle PL/SQL Tutorial

SQL>
SQL> declare
  2    olist  dbms_stats.objectTab;
  3  begin
  4    dbms_stats.gather_schema_stats(user,cascade=>TRUE,method_opt=> 'FOR ALL INDEXED COLUMNS',options=>'GATHER STALE',objlist=>olist);
  5
  6    dbms_output.put_line('Objects Analyzed: ' || olist.COUNT );
  7    if olist.COUNT > 0 then
  8       FOR x in 1..olist.COUNT LOOP
  9           dbms_output.put_line('Object Name: ' || olist(x).objname );
 10       END LOOP;
 11    end if;
 12
 13  end;
 14  /
Objects Analyzed: 2
Object Name: MYSTATS
Object Name: P1

PL/SQL procedure successfully completed.

SQL>
SQL>

31.27.DBMS_STATS
	31.27.1.	execute SYS.DBMS_STATS.gather_table_stats(USER, 'employee_job');
	31.27.2.	Check table status with SYS.DBMS_STATS.gather_table_stats after adding index
	31.27.3.	Create a procedure to collect statistics on all my objects
	31.27.4.	dbms_stats.drop_stat_table, dbms_stats.create_stat_table,dbms_stats.delete_system_stats
	31.27.5.	dbms_stats.gather_schema_stats
	31.27.6.	dbms_stats.gather_table_stats
	31.27.7.	dbms_stats.import_system_stats
	31.27.8.	dbms_stats.objectTab
	31.27.9.	dbms_stats.set_table_stats( user, 'EMP', numrows => 100000 )
	31.27.10.	dbms_stats.set_table_stats( user, 'myTable1', numrows => 10000000, numblks => 1000000 )