Thursday, September 18, 2008

DBMS_STATS

# find the latest partition
select partition_name from user_tab_partitions where table_name = 'ORDERS';

# create a stats backup table
exec dbms_stats.create_stat_table('BOBJ_LOGIN','STATS_BACKUP');

# export partition stats
exec dbms_stats.export_table_stats('BOBJ_LOGIN','ORDERS','ORDERS_WE210908','STATS_BACKUP',NULL,TRUE);

# collect new partition stats
begin
dbms_stats.gather_table_stats(ownname=> 'bobj_login'
, tabname=> 'ORDERS'
, estimate_percent => 10
, partname=>'ORDERS_WE210908'
,granularity =>'PARTITION'
,cascade =>TRUE
,degree=>4
,no_invalidate =>false);
end;
/

# gather system stats

dbms_stats.create_stat_table(ownname => 'system', stattab => 'system_stats', tblspace => 'datafix_dat');

dbms_stats.gather_system_stats(gathering_mode => 'START',stattab => 'system_stats', statid => 'systats_25062007' ,statown => 'system')

dbms_stats.gather_system_stats(gathering_mode => 'STOP',stattab => 'system_stats', statid => 'systats_25062007' ,statown => 'system');

# export system stats

dbms_stats.export_system_stats(stattab => 'systats_apn_5_1_2006', statid => 'systats_before_new_cpus', statown => 'datafix');

# delete system stats

dbms_stats.delete_system_stats;

# import system stats

dbms_stats.import_system_stats(stattab => 'system_stats', statid => 'systats_25062007', statown => 'system');

# export schema stats

dbms_stats.export_system_stats(stattab => 'systats_apn_5_1_2006', statid => 'systats_before_new_cpus', statown => 'datafix');

# gather schema stats

DBMS_STATS.GATHER_SCHEMA_STATS
(
OwnName => 'bobj_login',
Estimate_Percent => 2,
Block_sample => FALSE,
Method_Opt => 'FOR ALL COLUMNS SIZE 100',
Degree => 4,
Cascade => TRUE,
GRANULARITY => 'ALL'
);