Call now: 252-767-6166  
Oracle Training Oracle Support Development Oracle Apps

 
 Home
 E-mail Us
 Oracle Articles
New Oracle Articles


 Oracle Training
 Oracle Tips

 Oracle Forum
 Class Catalog


 Remote DBA
 Oracle Tuning
 Emergency 911
 RAC Support
 Apps Support
 Analysis
 Design
 Implementation
 Oracle Support


 SQL Tuning
 Security

 Oracle UNIX
 Oracle Linux
 Monitoring
 Remote s
upport
 Remote plans
 Remote
services
 Application Server

 Applications
 Oracle Forms
 Oracle Portal
 App Upgrades
 SQL Server
 Oracle Concepts
 Software Support

 Remote S
upport  
 Development  

 Implementation


 Consulting Staff
 Consulting Prices
 Help Wanted!

 


 Oracle Posters
 Oracle Books

 Oracle Scripts
 Ion
 Excel-DB  

Don Burleson Blog 


 

 

 


 

 

 
 

Oracle Concurrent statistics gathering

Oracle Database Tips by Donald BurlesonFebruary 20, 2015

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 dbms_stats utility.

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 SQL workloads.

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 dbms_stats.

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.

exec dbms_stats.set_global_prefs('concurrent','true');

A new method for parallelizing CBO statistics refreshing

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 concurrent analysis.

Oracle has enhanced the dba views to allow you to monitor concurrent statistics gathering:

set lines 200;

select
   job_name,
   state,
   comments
from
   dba_scheduler_jobs
where
   job_class like 'CONC%'
and
   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.

select
   job_name,
   elapsed_time
from
   dba_scheduler_running_jobs
where
  job_name like 'ST$%'

Note:  It's important to note that the vast 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.

For complete 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 "Advanced Oracle 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 buy it for 30% off directly from the publisher.

 


 

 

Burleson is the American Team

Note: This Oracle documentation was created as a support and Oracle training reference for use by our DBA performance tuning consulting professionals.  Feel free to ask questions on our Oracle forum.

Verify experience! Anyone considering using the services of an Oracle support expert should independently investigate their credentials and experience, and not rely on advertisements and self-proclaimed expertise. All legitimate Oracle experts publish their Oracle qualifications.

Errata?  Oracle technology is changing and we strive to update our BC Oracle support information.  If you find an error or have a suggestion for improving our content, we would appreciate your feedback.  Just  e-mail:  and include the URL for the page.


                    









Burleson Consulting

The Oracle of Database Support

Oracle Performance Tuning

Remote DBA Services


 

Copyright © 1996 -  2016

All rights reserved by Burleson

Oracle ® is the registered trademark of Oracle Corporation.


 

��  
 
 
Oracle Training at Sea
 
 
 
 
oracle dba poster
 

 
Follow us on Twitter 
 
Oracle performance tuning software 
 
Oracle Linux poster