Starting with Oracle 10gR2, a nightly maintenance job is included by default to collect statistics on the data in your database. While there have been significant improvements to the algorithms used to calculate those statistics since it's initial release, these operations have all been single threaded. Now with Oracle 12c, DBAs can enable concurrent statistics collections.
The Math, with no concurrency:
Different than Parallel
This is different than parallel collection of statistics. Parallelism allowed for multiple threads to be used to collect statistics for a single database object or partition. Concurrent statistics collection allows for multiple jobs to run against a set of objects. Concurrent stats and parallel stats work together.
The Math, with no concurrency:
total threads = 1 * parallelism
The Math, with concurrency:
total threads = # of concurrent stats jobs * parallelism
This is a potentially dangerous situation. Adjusting parallelism and concurrency to find the correct balance.
Enable Concurrency
Concurrent statistics collection is managed by the JOB_QUEUE_PROCESSES initialization parameter. Make sure this is set to a sane value before enabling concurrent collections.
In this case, up to 8 jobs can run concurrently. If there are no other jobs scheduled, all 8 could be statistics jobs.
Next, use DBMS_STATS.SET_GLOBAL_PREFS to enable CONCURRENT jobs.
From this point forward, any execution of a DBMS_STATS.GATHER_*_STATS procedure will execute with concurrency.
Source:
Comments
Post a Comment