Statistics - User Object, System, Fixed Object, and Dictionary Statistics Topics
User Object Statistics
http://www.dba-oracle.com/t_gather_stats_job.htm
Improvement of AUTO sampling statistics gathering on 11g - The Oracle Optimizer Development Group
Statistics on Partitioned Tables
Managing Statistics on Large Partitioned Tables - The Oracle Optimizer Development Group
Oracle 11g Incremental Global Statistics on Partitioned Tables - Greg Rahn
Data Warehousing and Statistics in Oracle 11g - Robin Moffatt
11g Incremental Partition Statistics Review - Randolf Geist
Managing Optimizer Statistics in Oracle Database 11g - Maria Colgan
-- Turn on incremental stats for all partitioned tables > 500mb
SELECT OWNER, SEGMENT_NAME, SUM (BYTES) / 1024 / 1024 / 1024 AS GB,
DBMS_STATS.GET_PREFS('INCREMENTAL', OWNER, segment_name) AS INCREMENTAL
FROM DBA_SEGMENTS
WHERE SEGMENT_TYPE = 'TABLE PARTITION' AND SEGMENT_NAME NOT LIKE '%$%'
GROUP BY OWNER, SEGMENT_NAME
HAVING SUM (BYTES) / 1024 / 1024 / 1024 >= 0.5
ORDER BY 3 DESC;
BEGIN
FOR cur IN (
SELECT OWNER, SEGMENT_NAME, SUM (BYTES) / 1024 / 1024 / 1024 AS GB
FROM DBA_SEGMENTS
WHERE SEGMENT_TYPE = 'TABLE PARTITION'
AND SEGMENT_NAME NOT LIKE '%$%'
GROUP BY OWNER, SEGMENT_NAME
HAVING SUM (BYTES) / 1024 / 1024 / 1024 >= 0.5)
LOOP
DBMS_OUTPUT.PUT_LINE('Processing '||cur.SEGMENT_NAME);
DBMS_STATS.set_table_prefs(cur.owner, cur.segment_name, 'INCREMENTAL', 'TRUE');
END LOOP;
END;
/
System Statistics
Recording Oracle System Stats for Historical Analysis
Fixed Object Statistics
Oracle Optimizer Blog - Fixed Object Statistics and Why They Are Important
Dictionary Statistics
exec dbms_stats.gather_dictionary_stats;
More Statistics Topics...