Concurrent Statistics Gathering
Introduction
Oracle Database 11g Release 2 (11.2.0.2) introduces a new statistics gathering mode, 'concurrent statistics gathering'. The goal of this new mode is to enable a user to gather statistics on multiple tables in a schema (or database), and multiple (sub)partitions within a table concurrently. Gathering statistics on multiple tables and (sub)partitions concurrently can reduce the overall time it takes to gather statistics by allowing Oracle to fully utilize a multi-processor environment.
Concurrent statistics gathering is controlled by a global preference, CONCURRENT, in the DBMS_STATS package. The CONCURRENT preference is of type boolean, so it can be set to TRUE or FALSE. By default it is set to FALSE. When CONCURRENT is set to TRUE, Oracle employs Oracle Job Scheduler and Advanced Queuing components to create and manage multiple statistics gathering jobs concurrently.
If you call dbms_stats.gather_table_stats on a partitioned table when CONCURRENT is set to true, Oracle will create a separate statistics gathering job for each (sub)partition in the table. The Job Scheduler will decide how many of these jobs will execute concurrently, and how many will be queued based on available system resources. As the currently running jobs complete, more jobs will be dequeued and executed until all (sub)partitions have had their statistics gathered on them.
If you gather statistics using dbms_stats.gather_database_stats, dbms_stats.gather_schema_stats, or dbms_stats.gather_dictionary_stats, then Oracle will create a separate statistics gathering job for each non-partitioned table, and each (sub)partition for the partitioned tables. Each partitioned table will also have a coordinator job that manages its (sub)partition jobs. The database will then run as many concurrent jobs as possible, and queue the remaining jobs until the executing jobs complete. However, multiple partitioned tables are not allowed to be processed simultaneously to prevent possible deadlock cases. Hence, if there are some jobs running for a partitioned table, other partitioned tables in a schema (or database or dictionary) will be queued until the current one completes. There is no such restriction for non-partitioned tables. The maximum number of concurrent statistics gathering jobs is bounded by the job_queue_processes initialization parameter (per node on a RAC environment) and the available system resources.
As another example, assume that the parameter job_queue_processes is set to 32, and you issued a dbms_stats.gather_schema_stats on the SH schema. Oracle would create a statistics gathering job (Level 1 in Figure 1) for each of the non-partitioned tables;
- SUPPLEMENTARY_DEMOGRAPHICS
- COUNTRIES
- CUSTOMERS
- PROMOTIONS
- CHANNELS
- PRODUCTS
- TIMES
And, a coordinator job for each partitioned table, i.e., SALES and COSTS, in turn creates a statistics gathering job for each of partition in SALES and COSTS tables, respectively (Level 2 in Figure 1). Then, the Oracle Job Scheduler would allow 32 statistics gathering jobs to start, and would queue the rest (assuming that there are sufficient resources for 32 concurrent jobs). Suppose that 29 jobs (one for each partition + a coordinator job) for the SALES table get started, then three non-partitioned table statistics gathering jobs would also be started. The statistics gathering jobs for the COSTS table will be automatically queued, because only for one partitioned table can be processed at any one time. As each job finishes, another job will be dequeued and started, until all 63 jobs have been completed.
Configuration and Settings
Begin
DBMS_STATS.SET_GLOBAL_PREFS('CONCURRENT','TRUE');
End;
/
You will also need some additional privileges above and beyond the regular privileges required to gather statistics. The user must have the following Job Scheduler and AQ privileges:- CREATE JOB
- MANAGE SCHEDULER
- MANAGE ANY QUEUE
Finally the job_queue_processes parameter should be set to at least 4. If you want to fully utilize all of the system resources during the statistics gathering process but you don't plan to use parallel execution you should set the job_queue_processes to 2* total number of CPU cores (this is a per node parameter in a RAC environment).Please make sure that you set this parameter system-wise (i.e., ALTER SYSTEM ... or in init.ora file) rather than at the session level (i.e., ALTER SESSION).
Using Concurrent Statistics Gathering with Parallel Execution
When using concurrent statistics gathering it is still possible to have each individual statistics gather job execute in parallel. This combination is normally used when the objects (tables or (sub)partitions) being analyzed are large. If you plan to execute the concurrent statistics gathering jobs in parallel you should disable the parallel adaptive multi-user initialization parameter. That is;
Alter system set parallel_adaptive_multi_user=false;
It is also recommended that you enable parallel statement queuing. This requires Resource Manager to be activated (if not already), and the creation of a temporary resource plan where the consumer group "OTHER_GROUPS" should have queuing enabled. By default, Resource Manager is activated only during the maintenance windows. The following script illustrates one way of creating a temporary resource plan (pqq_test), and enabling the Resource Manager with this plan.-- connect as a user with dba privileges
begin
dbms_resource_manager.create_pending_area();
dbms_resource_manager.create_plan('pqq_test', 'pqq_test');
dbms_resource_manager.create_plan_directive(
'pqq_test',
'OTHER_GROUPS',
'OTHER_GROUPS directive for pqq',
parallel_target_percentage => 90);
dbms_resource_manager.submit_pending_area();
end;
/
ALTER SYSTEM SET RESOURCE_MANAGER_PLAN = 'pqq_test' SID='*';
You can use the standard database scheduler views to monitor the concurrent statistics gathering jobs. The comments field of a job in dba_scheduler_jobs shows the target object for that statistics gathering job in the form of owner.table.(sub)partition. All statistics collection job names start with 'ST$' for easy identification. The jobs whose name start with ST$T###_### are created by a coordinator job for a partitioned table, and works on a partition or subpartition of the table.
The jobs whose name begin with ST$SD###_### are created for a table in a schema or database, and either works as a coordinator for its partition level jobs (if the table is partitioned), or directly performs the statistics collection for the table (if the table is not partitioned).
Finally, those with ST$D###_### in their naming are created for dictionary level tasks (when gather_dictionary_stats is used), and jobs does similar tasks as those with SD in their names.
Using the following query you can see all of the concurrent statistics gathering jobs that have been created:
select job_name, state, comments from dba_scheduler_jobs where job_class like 'CONC%' |
To only see the currently running jobs, filter by the job state:
select job_name, state, comments from dba_scheduler_jobs where job_class like 'CONC%' and state = 'RUNNING'; |
select job_name, state, comments from dba_scheduler_jobs where job_class like 'CONC%' and state = 'SCHEDULED'; |
select job_name, elapsed_time from dba_scheduler_running_jobs where job_name like 'ST$%'; |
Concurrent statistics gathering does not use any automated way to determine the maximum number of jobs that can concurrently run based on the system load and resources. Concurrent statistics gathering solely relies on the Job Scheduler and the value of the job_queue_processes parameter. Session-wide parameter settings and events are not transferred to the newly created jobs (scheduler limitation). Indexes are not gathered concurrently.
参考至:https://blogs.oracle.com/optimizer/entry/gathering_optimizer_statistics_is_one
如有错误,欢迎指正
邮箱:czmcj@163.com
相关推荐
Inside the Oracle Concurrent Manager
这个工程是为了学习guava concurrent中的AbstractFuture而建立的,里面有可以运行的例子,再配合我的博客:https://blog.csdn.net/o1101574955/article/details/82889851,可以看明白guava concurrent的基本设计思路...
Expert Oracle Database Architecture: Oracle Database Programming 9i, 10g, and 11g Techniques and Solutions, Second Edition Now in its second edition, this best-selling book by Tom Kyte of Ask Tom ...
Oracle EBS 11i ERD 文档 Table of Contents Function Security 3 Concurrent Processing 3 Standard Request Submission 3 Concurrent Managers 3 Login Security 3 User Profiles 3 Attachments 3 Language 3 ...
backport-util-concurrent.jarbackport-util-concurrent.jarbackport-util-concurrent.jar
concurrent 源代码concurrent 源代码
concurrent.jar web开发工具包
concurrent-1.3.4.jar
Concurrent.Thread.js 一个用来让javascript也进行多线程开发的包,感兴趣的快来下吧。
11. 并发导航映射 ConcurrentNavigableMap 12. 闭锁 CountDownLatch 13. 栅栏 CyclicBarrier 14. 交换机 Exchanger 15. 信号量 Semaphore 16. 执行器服务 ExecutorService 17. 线程池执行者 ThreadPoolExecutor 18. ...
concurrent-1.3.2.jar concurrent-1.3.2.jar
concurrent.jar 里面有,使用Apache JCS 时候需要的EDU/oswego/cs/dl/util/concurrent/Channel ,编译 jcaptcha时候需要第EDU.oswego.cs.dl.util.concurrent.ClockDaemon类, concurrent-1.3.3.jar
extensions to the purely-functional language haskell that allows it to express explicitly concurrent applications
Concurrent Programming in Java Design Principles and Pattern英文版 2.48M Java并发编程设计原则与模式_第二版(原书中文版) 19.4M Concurrent_Programming_in_Java_Design_Principles_Lecture DougLea
Concurrent下的例子,涵盖了大部分常用类 例如BlockingQueue、CountDownLatch、CyclicBarrier、Exchanger、ExecuteService、lock、timeutil等
Concurrent Programming in Java - Design Principles and Patterns
JavaEE源代码 concurrent-1.3.2JavaEE源代码 concurrent-1.3.2JavaEE源代码 concurrent-1.3.2JavaEE源代码 concurrent-1.3.2JavaEE源代码 concurrent-1.3.2JavaEE源代码 concurrent-1.3.2JavaEE源代码 concurrent-...
结合本人的开发经验,对线程池的实现原理和技术进行分析,并对concurrent线程池源码和算法进行分析。
java concurrent 阻塞队列 线程 里面有详细的例子,下载后请认真阅读里面的内容,可能有点难以理解,请耐心
concurrent.jar