Script to analyze schema
beginfor x in (select table_name from dba_tables where owner='<SCHEMA_NAME>')
loop
dbms_output.put_line('TABLE : ' || x.table_name);
begin
dbms_stats.gather_table_stats(ownname => '<SCHEMA_NAME>',tabname =>
x.table_name, cascade => TRUE, estimate_percent => 35);
exception
when others then
dbms_output.put_line('### ERROR: ' || sqlerrm);
end;
end loop;
end;
/
Script to generate Analyze table script
select 'Analyze table <SCHEMA_NAME>.'|| table_name || ' estimate statistics;' fromdba_tables where owner = 'OWNER_NAME'
No comments:
Post a Comment