STATISTICS dbms_stats

- gather statistics – SYS , system, dictionary , fixed_objects  

 

 exec dbms_stats.gather_system_stats('interval', interval=>180);

 exec dbms_stats.gather_dictionary_stats;

 exec dbms_stats.gather_fixed_objects_stats;

 exec dbms_stats.gather_schema_stats('SYS',degree=>30,-

   estimate_percent=>100,cascade=>TRUE);

  

-- gather database statistics

   exec DBMS_STATS.gather_database_stats(estimate_percent => 15);    

        

-- gather schema statistics

exec dbms_stats.delete_schema_stats('SCOTT');

exec dbms_stats.gather_schema_stats(USER,  cascade => TRUE);

exec dbms_stats.gather_schema_stats(ownname =>'SCOTT,   options =>'GATHER',method_opt=>'FOR ALL COLUMNS SIZE AUTO',estimate_percent => dbms_stats.auto_sample_size,granularity=>'AUTO',cascade=>TRUE);

exec dbms_stats.gather_schema_stats(ownname =>'SCOTT', -

     method_opt=>'FOR ALL INDEXES FOR ALL INDEXED COLUMNS SIZE -   1',estimate_percent => 15,granularity=>'ALL',cascade=>TRUE);

   

-- gather table, index  stats

exec dbms_stats.delete_table_stats(ownname =>'SCOTT',-

tabname =>'EMPLOYEE');

exec dbms_stats.gather_table_stats(ownname =>'SCOTT',tabname =>'EMPLOYEE',method_opt=>'for all columns size auto',estimate_percent => dbms_stats.auto_sample_size,granularity=>'AUTO',cascade=>true);

 exec dbms_stats.gather_table_stats(ownname =>'SCOTT',tabname =>'EMPLOYEE',method_opt=>'FOR ALL INDEXES FOR ALL INDEXED COLUMNS SIZE 1',estimate_percent => 15,cascade => TRUE );      

   with histogram on columns

     exec dbms_stats.gather_table_stats(ownname =>'SCOTT',tabname =>'EMPLOYEE',method_opt=>'FOR ALL INDEXES FOR ALL INDEXED COLUMNS SIZE 1',estimate_percent => dbms_stats.auto_sample_size,granularity=>'ALL',cascade=>TRUE);

   

   with no histogram on columns

     exec dbms_stats.gather_table_stats(ownname =>'SCOTT',tabname =>'EMPLOYEE',method_opt=>'FOR ALL COLUMNS SIZE 1',estimate_percent => dbms_stats.auto_sample_size,granularity=>'ALL',cascade=>TRUE);

   for skewed columns        

     execute dbms_stats.gather_table_stats(ownname =>'SCOTT',tabname =>'EMPLOYEE',method_opt=>'for all columns size skewonly',estimate_percent => dbms_stats.auto_sample_size,granularity=>'AUTO',cascade=>TRUE);

using shell scipt

set time on

declare

cursor tab_cur IS select table_name from dba_tables where owner='SCOTT' order by table_name;

begin

  for tab_rec in tab_cur loop

    exit when tab_cur%NOTFOUND;

    dbms_stats.gather_table_stats(ownname =>'SCOTT',options=>'GATHER',tabname =>tab_rec.table_name,method_opt=>'for all columns size auto',estimate_percent => dbms_stats.auto_sample_size,granularity=>'AUTO',cascade=>true);

  end loop;

end;

/

exit

  

-- gather index stats

   exec dbms_stats.gather_index_stats( ownname=>'SCOTT',indname=>'ITEM_NDX1',-

granularity=>'ALL',estimate_percent=> 10,degree=>1);

using shell scipt
set time on
declare
cursor tab_cur IS select index_name from dba_indexes where owner='SCOTT' and last_analyzed < sysdate - 1  order by table_name;
begin
  for tab_rec in tab_cur loop
    exit when tab_cur%NOTFOUND;
    dbms_stats.gather_index_stats( ownname=>'MAXIMO',indname=>tab_rec.index_name,granularity=>'AUTO',estimate_percent=> dbms_stats.auto_sample_size,degree=>1);
end loop;
end;
/
exit

Hope this helps! Rupam