Statistics gathering parameters for partitioned tables

When partitioned tables get a bit large, these parameters may be suitable to set.

When tables get large, incremental collection of statistics may speed up the collection process, as well as giving more detailed statistics for the optimizer.

    ownname => null, 
    tabname => '<table_name>',
    pname => 'INCREMENTAL',
    pvalue => 'TRUE');

Manually set settings can be checked by querying the user_tab_stat_prefs-view.

select *
from user_tab_stat_prefs;

Programmatically, values can be accessed by dbms_stats.get_prefs, which would also return any default value, if none is set for that parameter:

select dbms_stats.get_prefs('INCREMENTAL', user, '<table_name>') from dual;

Note that incremental gathering takes a little bit of space in the SYSAUX-tablespace, so keep an eye on the space usage there.

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s