how to gather statistics for all the tables

Gathering statistics for schemas, tables , indexes is a very important part of database maintaining tasks. Without statistics a growing database can have really big performance issues.

Here is a procedure that gathers statistics for every table except system ones for Oracle 10g

create or replace procedure analyze_tables
is

err_msg varchar2(200);
v_table varchar2(100);
v_owner varchar2(100);
v_sql varchar2(800);

cursor c_tabela is
select TABLE_NAME , owner
from dba_tables
where owner not in ('CTXSYS','SYS','SYSTEM','WMSYS','OUTLN','XDB','OWNER','DBSNMP','DMSYS','EXFSYS','SYSMAN','TSMSYS')
;

begin
open c_tabela;
fetch c_tabela into v_table,v_owner;
while c_tabela%found loop
v_sql:= 'begin DBMS_STATS.gather_table_stats ('''||v_owner||''','''||v_table||''',ESTIMATE_PERCENT=>50,METHOD_OPT=>''for all indexed columns size auto'', degree => 4, CASCADE=>True); end;';
execute immediate v_sql;
fetch c_tabela into v_table,v_owner;
end loop;

exception
when others then
err_msg := TO_CHAR(SQLCODE) || ':' || SQLERRM;
insert into log_rebuid (data, mesg_err) values (sysdate, 'SCHEMA '||err_msg );

end analyze_tables;

It is good to see on a production system a weekly job that gathers statistics, either fort tables, or schemas. Another interesting procedure is this one:

create or replace procedure rebuild_index (v_index in varchar2)
is
err_msg varchar2(200);
begin
execute immediate 'alter index '||v_index||' rebuild';
exception
when others then
err_msg := TO_CHAR(SQLCODE) || ':' || SQLERRM;
insert into log_rebuid (data, mesg_err) values (sysdate, 'INDEX '||err_msg );
end ;

It rebuilds the index on a table. Be careful because on a 24/7 database, it puts a lock on a table until the index is rebuilt. If it's a big index then you might have some locks on the database.