Oracle 11g release 2 has introduced a new feature titled
"concurrent statistics gathering. This "concurrent statistics
gathering" is recognition of "server
consolidation", an industry move to large monolithic servers
that contain 32 and 64 CPU's, enough processing horsepower to
perform parallelized full-scan operations that are required by the
Concurrent statistics gathering uses
dbms_scheduler and advanced queuing in order to achieve high
concurrency. Oracle has consistently enhanced
optimizer statistics gathering, introducing new algorithms in 11g to
improve the sampling efficiency to increase the quality of the
optimizer statistics, thereby improving the execution plans for the
The dbms_stats utility has had a
"degree" parameter for several releases to enable parallelized
full-table and full-index scans, plus, dbms_stats has a way
to analyze "cascade" an analyze to dependent tables, and analyze an
entire schema or database with a single dbms_stats command.
However, this new high-concurrency model allows for dbms_stats to
fully-saturate a large server, which is a typical goal during
scheduled DBA downtime windows. The goal is to keep all 64
processors running at full capacity to maximize throughput, and
traditionally a DBA would perform "parallel parallelism" with
Traditional parallel parallelism was done by
nohupping multiple dbms_stats jobs for concurrent execution
while each dbms_stats job contained the "degree" argument,
used to have Oracle invoke parallel query on the target object.
Now with 11g release 2, we have this parallel parallelism
functionality built-in to dbms_stats. Oracle now has a global
dbms_stats configuration parameter concurrent=true which
enables concurrent optimizer statistics gathering.
A new method for parallelizing CBO
Instead of submitting parallel jobs at the OS level (e.g. the
nohup command), 11gr2 concurrent optimizer statistics uses the
dbms_scheduler mechanism to submit parallel jobs to perform the
Oracle has enhanced the dba views to
allow you to monitor concurrent statistics gathering:
set lines 200;
job_class like 'CONC%'
state = 'RUNNING";
The dba_scheduler_running_jobs view has a new
"elapsed time" column that allows you to monitor the elapsed time
for all concurrent statistics gathering jobs.
job_name like 'ST$%'
Note: It's important to note that
majority of OLTP databases will see no major benefits from frequent
re-analyze of optimizer statistics because these databases are
relative static. Large tables remain large and the
distribution of values within columns remains such that new
statistics will not make measurable improvements in SQL performance.
It's also important to note that most shops run multiple
workloads, processing short OLTP transactions during the day and
running batch-oriented jobs during off-hours. For these
databases, Oracle allows you to
import and export optimizer statistics.
information on improving SQL performance with optimizer statistics
collection, see the book
Advanced Oracle SQL Tuning: The Definitive Reference.
Get the Complete
Oracle SQL Tuning Information
The landmark book
SQL Tuning The Definitive Reference" is
filled with valuable information on Oracle SQL Tuning.
This book includes scripts and tools to hypercharge Oracle 11g
performance and you can
for 30% off directly from the publisher.