241 lines
9.4 KiB
SQL
241 lines
9.4 KiB
SQL
|
|
|
|
-- 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
|
|
);
|
|
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
|
|
);
|
|
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 ;
|