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');

Brak komentarzy:

Prześlij komentarz