-- AWR Table partitions with STALE/MISSING stats
select STALE_STATS, last_analyzed,
'exec dbms_stats.gather_table_stats(''' || owner || ''',''' || table_name || ''',partname=>''' || partition_name|| ''',GRANULARITY=>''PARTITION'',DEGREE=>16,CASCADE =>TRUE);'
from DBA_TAB_STATISTICS
where owner = 'SYS'
and table_name like 'WRH$%'
and (STALE_STATS = 'YES' OR last_analyzed is null)
and partition_name is not null
ORDER BY owner, table_name, partition_name
;

-- AWR Table with STALE/MISSING stats
select STALE_STATS, last_analyzed,
'exec dbms_stats.gather_table_stats(''' || owner || ''',''' || table_name || ''',DEGREE=>16,CASCADE =>TRUE);'
from DBA_TAB_STATISTICS
where owner = 'SYS'
and table_name like 'WRH$%'
and (STALE_STATS = 'YES' OR last_analyzed is null)
and partition_name is null
order by num_rows asc
;




-- Gather Fixed Object Stats ( Dynamic performance tables, eg. X$ table which are referenced in GV$ views)
BEGIN
dbms_stats.gather_fixed_objects_stats( no_invalidate => dbms_stats.auto_invalidate);
END;
/

-- Gather dictionary stats 
BEGIN
dbms_stats.gather_dictionary_stats(estimate_percent => dbms_stats.auto_sample_size
                                 , block_sample => false
                                 , method_opt => 'FOR ALL COLUMNS SIZE AUTO'
                                 , degree => dbms_stats.auto_degree
                                 , granularity => 'AUTO'
                                 , cascade => dbms_stats.auto_cascade
                                 , options => 'GATHER EMPTY'
                                 , no_invalidate => dbms_stats.auto_invalidate
                                );
END;
/


BEGIN
dbms_stats.gather_dictionary_stats(comp_id =>   -- select comp_id from dba_registry order by comp_id;
                                , estimate_percent => dbms_stats.auto_sample_size
                                , block_sample => false
                                , method_opt => 'FOR ALL COLUMNS SIZE AUTO'
                                , degree => dbms_stats.auto_degree
                                , granularity => 'AUTO'
                                , cascade => dbms_stats.auto_cascade
                                , options => 'GATHER EMPTY'
                                , no_invalidate => dbms_stats.auto_invalidate
                               );
END;
/


BEGIN
dbms_stats.gather_database_stats(estimate_percent => dbms_stats.auto_sample_size
                               , block_sample => false
                               , method_opt => 'FOR ALL COLUMNS SIZE 1'
                               , degree => dbms_stats.auto_degree
                               , granularity => 'AUTO'
                               , cascade => dbms_stats.auto_cascade
                               , options => 'GATHER EMPTY'
                               , gather_sys => TRUE
                               , no_invalidate => dbms_stats.auto_invalidate
                               );
END;
/

BEGIN                               
dbms_stats.gather_database_stats(estimate_percent => dbms_stats.auto_sample_size
                               , block_sample => false
                               , method_opt => 'FOR ALL COLUMNS SIZE 1'
                               , degree => dbms_stats.auto_degree
                               , granularity => 'AUTO'
                               , cascade => dbms_stats.auto_cascade
                               , options => 'GATHER STALE'
                               , gather_sys => TRUE
                               , no_invalidate => dbms_stats.auto_invalidate
                               );
END;
/


BEGIN
dbms_stats.gather_schema_stats(ownname => 'MUREX_MX_OWNER'
                               , block_sample => false
                               , estimate_percent => dbms_stats.auto_sample_size
                               , method_opt => 'FOR ALL COLUMNS SIZE 1'
                               , degree => dbms_stats.auto_degree
                               , granularity => 'AUTO'
                               , cascade => dbms_stats.auto_cascade
                               , options => 'GATHER EMPTY'
                               , no_invalidate => dbms_stats.auto_invalidate
                               , force => false
                               );
END;
/

BEGIN                               
dbms_stats.gather_schema_stats(ownname => 'MUREX_MX_OWNER'
                               , block_sample => false
                               , estimate_percent => dbms_stats.auto_sample_size
                               , method_opt => 'FOR ALL COLUMNS SIZE 1'
                               , degree => dbms_stats.auto_degree
                               , granularity => 'AUTO'
                               , cascade => dbms_stats.auto_cascade
                               , options => 'GATHER STALE'
                               , no_invalidate => dbms_stats.auto_invalidate
                               , force => false
                               );
END;
/


--****************************
-- Gather Stale Table stats
--****************************
set serveroutput on size unlimited
BEGIN
FOR i in (
select owner, table_name
      , 'BEGIN dbms_stats.gather_table_stats(force=>false'
       || ',ownname=>''' || owner || '''' 
       || ',tabname=>''' || table_name || '''' 
       || NVL2(partition_name,',partname=>''' || partition_name || '''' ,'')
       || ',degree=>8' 
       || ',granularity =>''PARTITION''' 
       || ',no_invalidate => dbms_stats.auto_invalidate'
       || '); END;' sqltext      
from dba_tab_statistics s
where 1=1
and owner LIKE 'TRAF_%'  
and STALE_STATS = 'YES'
and STATTYPE_LOCKED IS NULL
)
LOOP
   dbms_output.put_line(i.sqltext);
   execute immediate i.sqltext;
END LOOP;
END;
/

--****************************
-- Gather Stale Index stats
--****************************
set serveroutput on size unlimited
BEGIN
FOR i in (
select owner, index_name, table_owner, table_name, partition_name
      , 'BEGIN dbms_stats.gather_index_stats(force=>false'
       || ',ownname=>''' || owner || '''' 
       || ',indname=>''' || index_name || '''' 
       || NVL2(partition_name,',partname=>''' || partition_name || '''' ,'')
       || ',degree=>8' 
       || ',granularity =>''PARTITION''' 
       || ',no_invalidate => dbms_stats.auto_invalidate'
       || '); END;' sqltext      
from dba_ind_statistics i
where 1=1
and owner LIKE 'TRAF_%'  
and STALE_STATS = 'YES'
and STATTYPE_LOCKED IS NULL
)
LOOP
   dbms_output.put_line(i.sqltext);
   execute immediate i.sqltext;
END LOOP;
END;
/


exec DBMS_STATS.GATHER_DATABASE_STATS( METHOD_OPT => 'FOR ALL COLUMNS SIZE 1' , ESTIMATE_PERCENT => 5, DEGREE => 16, OPTIONS => 'GATHER EMPTY' , CASCADE => TRUE );
exec DBMS_STATS.GATHER_SCHEMA_STATS( ownname => 'schemaname', METHOD_OPT => 'FOR ALL COLUMNS SIZE 1'  , ESTIMATE_PERCENT => 5, DEGREE => 16 , OPTIONS => 'GATHER EMPTY'  , CASCADE => TRUE );
exec DBMS_STATS.GATHER_TABLE_STATS(ownname => 'HISTORY_OWN', tabname =>  'PROVIDER_PRICE' ,  partname => 'PROVIDER_PRICE_2012_01', METHOD_OPT => 'FOR ALL COLUMNS SIZE 1'  , ESTIMATE_PERCENT => 5  , DEGREE => 8 ,  CASCADE => TRUE );
exec DBMS_STATS.GATHER_INDEX_STATS(ownname => 'PM_OWN', indname =>  'PMA_A_IDX1' ,  partname => 'PMA_A_0117_2013', ESTIMATE_PERCENT => 5  , DEGREE => 8 );



--****************************
-- List objects without stats
--****************************
set serveroutput on
DECLARE
objlist DBMS_STATS.OBJECTTAB ;
BEGIN
DBMS_STATS.GATHER_DATABASE_STATS( METHOD_OPT => 'FOR ALL COLUMNS SIZE 1' , ESTIMATE_PERCENT => 5, DEGREE => 16, OPTIONS => 'LIST EMPTY' , objlist => objlist, CASCADE => TRUE );
  for  i in 1 ..  objlist.COUNT
  LOOP
    dbms_output.put_line (objlist(i).objname);
  END LOOP;  
END;
/


--****************************
-- Incremental Stats
--****************************
-- Enable
exec dbms_stats.set_database_prefs( pname => 'INCREMENTAL' , pvalue = 'TRUE', add_sys => 'TRUE');
exec dbms_stats.set_schema_prefs( ownname => 'schemaname', pname => 'INCREMENTAL' , pvalue => 'TRUE');
exec dbms_stats.set_table_prefs( ownname => 'schemaname', tabname => 'tablename', pname => 'INCREMENTAL' , pvalue => 'TRUE');

-- Disable
exec dbms_stats.set_database_prefs( pname => 'INCREMENTAL' , pvalue = 'FALSE', add_sys => 'TRUE');
exec dbms_stats.set_schema_prefs( ownname => 'schemaname', pname => 'INCREMENTAL' , pvalue => 'FALSE');
exec dbms_stats.set_table_prefs( ownname => 'schemaname', tabname => 'tablename', pname => 'INCREMENTAL' , pvalue => 'FALSE');

-- Disable Table Level
BEGIN
 FOR i in (select * from dba_tab_stat_prefs where preference_name = 'INCREMENTAL' and preference_value = 'TRUE')
 LOOP
    dbms_stats.set_table_prefs( ownname => i.owner, tabname => i.table_name, pname => 'INCREMENTAL' , pvalue => 'FALSE');
 END LOOP;
END;
/


--****************************
-- Lock Stats
--****************************

-- Lock table stats to skip daily gather stats
BEGIN
dbms_stats.lock_table_stats('PM_OWN', 'SEC_RISK_MEASURES_HIST');
END ;
/

-- Unlock table stats to include daily gather stats
BEGIN
dbms_stats.unlock_table_stats('PM_OWN', 'SEC_RISK_MEASURES_HIST');
END ;
/

-- How to find out tables with locked stats
SELECT DISTINCT OWNER, TABLE_NAME, STATTYPE_LOCKED  --, S.LAST_ANALYZED
FROM DBA_TAB_STATISTICS S
WHERE STATTYPE_LOCKED IS NOT NULL 
  AND OWNER NOT IN ('SYS','SYSTEM','SYSMAN','WMSYS','SQLTXPLAIN')
ORDER BY OWNER, TABLE_NAME ;