Gather statistics for a schema
Posted by sraghav on March 13, 2007
-- recreate all statisticsDECLARECURSOR cOwner IS SELECT DISTINCT owner FROM dba_tables WHERE owner NOT IN ('SYS','SYSTEM','DBSNMP',);BEGIN -- analyze sOwner,compute FOR rOwner IN cOwner LOOP DBMS_STATS.GATHER_SCHEMA_STATS (ownname => rOwner.owner, estimate_percent => 99, block_sample => FALSE, method_opt => 'FOR ALL COLUMNS SIZE 150', degree => NULL, granularity => 'DEFAULT', cascade => TRUE); END LOOP;END;/
select 'END_DATE: '||to_char(sysdate,'dd.mm.yyyy hh24:mi:ss') as end_date from dual;exit
