Pokazywanie postów oznaczonych etykietą GATHER table statistics. Pokaż wszystkie posty
Pokazywanie postów oznaczonych etykietą GATHER table statistics. Pokaż wszystkie posty

piątek, 30 listopada 2012

Incremental statistics

In 11g Oracle introduce incremental gathering statistics for partitioned tables. For huge tables gathering statistics takes a lot of time, but if incremental statistic is enabled on table, of course gathering will be faster.
Below is a query when we can check which  tables don't have enabled incremental statistics:

SELECT UNIQUE TABLE_OWNER,TABLE_NAME,
  SUM(NUM_ROWS)
FROM DBA_TAB_PARTITIONS
WHERE TABLE_OWNER not in ('SYS','SYSTEM')
AND table_name NOT IN
  (SELECT table_name
  FROM DBA_TAB_STAT_PREFS
  WHERE PREFERENCE_NAME='INCREMENTAL'
  AND PREFERENCE_VALUE ='TRUE'
  )
GROUP BY TABLE_OWNER,TABLE_NAME
ORDER BY 3;


and here is script to enable incremental statistcs for table:


EXEC dbms_stats.set_table_prefs('SCHEMA1', 'TABLE1', 'INCREMENTAL', 'TRUE');
EXEC DBMS_STATS.set_table_prefs('SCHEMA1', 'TABLE1', 'ESTIMATE_PERCENT', DBMS_STATS.AUTO_SAMPLE_SIZE);
EXEC dbms_stats.set_table_prefs('SCHEMA1', 'TABLE1', 'GRANULARITY', 'AUTO');
EXEC DBMS_STATS.GATHER_TABLE_STATS('SCHEMA1', 'TABLE1');