Tuesday, April 4, 2017

How to enable incremental statistics gathering for a large partitioned table

What is incremental statistics?

It's a way to improve performance of global statisics gathering on large partitioned tables.

Incremental statistics allows the database to scan the table once to gather partition statistics and from there to derive global statistics by aggregating partition-level statistics.

During later statistics gathering, the database only needs to scan stale partitions and then update their statistics.
From there the database can derive global statistics from the fresh partition statistics, which in turn saves a full table scan.

To view the current settings:

SQL> select dbms_Stats.get_prefs('INCREMENTAL','SCOTT','EMP') "paramvalue" from dual;

paramvalue
--------------------
FALSE

To change the setting:
SQL> exec dbms_stats.set_table_prefs(ownname=>'SCOTT',tabname=>'EMP',pname=>'INCREMENTAL',pvalue=>'TRUE');

Verify:
SQL> select dbms_Stats.get_prefs('INCREMENTAL','SCOTT','EMP') "paramvalue" from dual;

paramvalue
--------------------
TRUE

For more information about incremental statistics and how it can benefit large table, read this from the Oracle Documentation

No comments:

Post a Comment