GATHERING STATISTICS CONCURRENTLY ON MULTIPLE TABLES

GATHERING  STATISTICS CONCURRENTLY ON MULTIPLE TABLES

In previous Oracle database editions, whenever you execute a DBMS_STATS procedure to gather table, index, schema or database level statistics, Oracle used to collect stats one table at a time. If the table is big enough, then increasing the parallelism was recommended. With 12c R1, you can now collect stats on multiple tables, partitions and sub partitions concurrently.  Before you start using it, you must set the following at the database level to enable the feature

SQL> ALTER SYSTEM SET RESOURCE_MANAGER_PLAN='DEFAULT_MAIN';

SQL> ALTER SYSTEM SET JOB_QUEUE_PROCESSES=4;

SQL> EXEC DBMS_STATS.GATHER_SCHEMA_STATS('SCENT', 'ALL');

SQL> EXEC DBMS_STATS.SET_GLOBAL_PREFS('CONCURROTT');