dbms_stats.gather_table_stats : dbms_stats « System Packages « Oracle PL / SQL






dbms_stats.gather_table_stats

    


SQL>
SQL> create table  myTable1
  2  as
  3  select mod(rownum,1000) id, rpad('x',300,'x') data
  4    from all_objects
  5   where rownum <= 5*1000;
SQL>
SQL> create table  myTable2
  2  as
  3  select rownum id, rpad('x',300,'x') data
  4    from all_objects
  5   where rownum <= 1000;
SQL>
SQL> create index myTable1_idx on myTable1(id);
SQL>
SQL> create index myTable2_idx on myTable2(id);
SQL>
SQL> begin
  2     dbms_stats.gather_table_stats( user, 'myTable1', method_opt => 'for all indexed columns',cascade=>true );
  3     dbms_stats.gather_table_stats( user, 'myTable2', method_opt => 'for all indexed columns',cascade=>true );
  4  end;
  5  /
SQL> set autotrace traceonly  explain
SQL>
SQL> select *
  2    from myTable1, myTable2
  3   where myTable1.id = myTable2.id
  4     and myTable2.id between 50 and 55;

SQL>
SQL> alter session set optimizer_index_caching = 50;
SQL>
SQL> select *
  2    from myTable1, myTable2
  3   where myTable1.id = myTable2.id
  4     and myTable2.id between 50 and 55;
SQL>
SQL>
SQL> set autotrace off
SQL>
SQL>
SQL> drop table myTable1;
SQL> drop table myTable2;

   
    
    
    
  








Related examples in the same category

1.dbms_stats.drop_stat_table, dbms_stats.create_stat_table,dbms_stats.delete_system_stats
2.dbms_stats.gather_schema_stats
3.DBMS_STATS.GET_SYSTEM_STATS
4.dbms_stats.import_system_stats
5.dbms_stats.objectTab
6.dbms_stats.set_table_stats( user, 'EMP', numrows => 100000 )
7.dbms_stats.set_table_stats( user, 'myTable1', numrows => 10000000, numblks => 1000000 )
8.Delete all table and index statistics in your schema:
9.multiblock readtime in ms
10.Run DBMS_STATS.GATHER_SCHEMA_STATS to collect stats for all of your tables and indexes.
11.Extract system stats from your user table or data dictionary (sys.aux_Stats$).