11gR2 新特性之—In-Memory Parallel execution

原帖地址

该特性是在oracle 11gR2 引入，其目的不言而喻。在11gR2之前，也就是在11gR1中， 如果当你发出/*+parallel(16) */ 时，可能会出现下面两种情形：1. The SQL can run with reduced DOP (be downgraded) -该特性我在11gR1中测试过 2. The SQL can run in serial mode (be serialized) * "ORA-12827: insufficient parallel query slaves available" (If PARALLEL_MIN_PERCENT was specified)但是在11gR2中，彻底发生了改变，首先我们来看看新引入的几个paralle相关的参数： parallel_degree_policy 该参数属性为manual、auto、limited，11gR2中默认为manual。 ---manual Disables automatic degree of parallelism,statement queuing,in-memory parallel execution。 ---auto Enables automatic degree of parallelism,statement queuing,in-memory parallel execution.---limited 当设置为该属性时，该特性将关闭，部分sql语句仍然可用使用，如表和索引的degree大于1的情况。parallel_min_time_threshold sql语句执行的最小时间（在使用了该特性时），换句话说，也就是只有当parallel_degree_policy 参数设置为auto或limited时，该参数默认值为auto，即是默认为10s。parallel_degree_limit 该参数属性为CPU、IO、integer。默认值为CPU ---cpu 意为最大的DOP会根据系统cpu负载来进行自动调节 ---io 意为最大的DOP会根据系统IO能力来进行自动调节 ---integer 即可以在system或session级别指定为某个具体的数值 该参数是动态参数，可用在session级别进行更改。parallel_force_local 顾名思义，该参数主要用于RAC环境，控制parallel server processes 是否能够跨节点， 其属性为true、false，默认值为false。该参数为动态参数。 parallel_servers_target 该参数的含义是可用的parallel server processes 该参数值=4 x CPU_COUNT x PARALLEL_THREADS_PER_CPU x ACTIVE_INSTANCES parallel_max_servers 最大的parallel进程，parallel_servers_target select n.name, s.value 2 from v$mystat s, v$statname n 3 where s.statistic# = n.statistic# 4 and n.name like 'Parallel%' 5 /NAME VALUE ---------------------------------------------------------------- ---------- Parallel operations not downgraded 0 Parallel operations downgraded to serial 0 Parallel operations downgraded 75 to 99 pct 0 Parallel operations downgraded 50 to 75 pct 0 Parallel operations downgraded 25 to 50 pct 0 Parallel operations downgraded 1 to 25 pct 06 rows selected.SQL> alter session set "_px_trace"="none";Session altered.SQL> alter session set "_px_trace"=all;Session altered.SQL> SELECT /*+ parallel(4) */ count(*) from ht02;COUNT(*) ---------- 225824SQL> alter session set "_px_trace"="none";Session altered.我们来看下trace信息： *** 2011-09-03 00:51:23.921 kxfrDefaultDOP DOP Trace -- compute default DOP # CPU = 1 Threads/CPU = 2 ("parallel_threads_per_cpu") default DOP = 2 (# CPU * Threads/CPU) default DOP = 2 (DOP * # instance) Default DOP = 2 kxfxqOnOrderQueue KXFXQQUEUABLE() is TRUE. pgadep: 0, pgatopsql: 1, pgapls 0. SlaveSQL?: NO, Parallized?: YES, DOP: 4. kxfxqOnOrderQueue Admitting Parallel Statement (dop:4): ----- Current SQL Statement for this session (sql_id=432y0dmm1qdzk) ----- SELECT /*+ parallel(4) */ count(*) from ht02 kxfxqUpdateLoad snapshot of RAC load before update: [ total queued PQ: 0, total running( admitted) PQ: 0, total granted slaves in RAC: 0, total target in RAC: 0 ] kxfpiinfo inst[cpus:mxslv] 1[1:20] ---表示1个cpu，parallel_max_servers参数值为20. kxfpGetNumActiveSlaves number of active slaves on the instance: 0 kxfpGetDefInstTarget default inst target is 8, defDOP: 2, mxu: 2, cpus: 1 kxfpGetInstTarget (default: 1) inst target is 8 --这里是parallel_servers_target值 kxfpclinfo inst(load:user:pct:fact:queued:started:granted:active)aff 1 (1:0:100:100:0:0:0) kxfpGetDefInstTarget default inst target is 8, defDOP: 2, mxu: 2, cpus: 1 kxfpGetInstTarget (default: 1) inst target is 8 kxfpMarkRACLoadStat RAC load statistics is marked as valid. ........Sending parse to slave set 1: User sqllen sent from QC = 45 SELECT /*+ parallel(4) */ count(*) from ht02 kxfxpf [ 1430/ 60] MSG( -->, KXFXOparse, DIALOG_HINT, slv=0 ) kxfxpf [ 1440/ 10] MSG( -->, KXFXOparse, DIALOG_HINT, slv=1 ) kxfxpf [ 1440/ 0] MSG( -->, KXFXOparse, DIALOG_HINT, slv=2 ) kxfxpf [ 1440/ 0] MSG( -->, KXFXOparse, DIALOG_HINT, slv=3 ) kxfxgs [ 1640/ 200] MSG( <--, KXFXORokcurs, ss#=1 slv=3 ok=yes ) kxfxgs [ 1640/ 0] MSG( <--, KXFXORokcurs, ss#=1 slv=2 ok=yes ) kxfxgs [ 1640/ 0] MSG( <--, KXFXORokcurs, ss#=1 slv=1 ok=yes ) kxfxgs [ 1640/ 0] MSG( <--, KXFXORokcurs, ss#=1 slv=0 ok=yes ) qerpxSendParse [ 1640/ 0]kxfpgsg                                                        [    1640/     0]         Freeing Memory: il=0x4835d0 iload=0x48357c ilist=(nil) slist=(nil)         set1_pids=0x4835ec set2_pids=(nil) kxfrAllocSlaves                                                [    1640/     0]         actual num slaves alloc'd = 4 (kxfpqcthr)        ----实际分配的slave进程个数 kxfrialo                                                       [    1640/     0]         Finish: allocated actual 4 slaves for non-GV query .............. *** 2011-09-03 00:51:25.390 kxfpg1sg                                                       [    1280/    30]           received reply from qref 0x24f811b0 kxfpg1sg                                                       [    1280/     0]           got 4 servers (sync), errors=0x0 returning GROUP GET                                                      [    1280/     0]           Acquired 4 slaves on 1 instances avg height=4 in 1 set q serial:513           P000 inst 1 spid 4291  --这里是分配的4个salve进程spid           P001 inst 1 spid 4293           P002 inst 1 spid 4295           P003 inst 1 spid 4297           Insts   1           Svrs    4     ---4个salve进程 当前虚拟机测试，我分配了4个parallel，现在加大该值为30，看看最后的DOP会是多少。 SQL> alter session set parallel_degree_policy = auto; Session altered.SQL> alter session set "_px_trace"=all;Session altered.SQL> SELECT /*+ parallel(30) */ count(*) from ht02;COUNT(*) ---------- 225824SQL> alter session set "_px_trace"="none";Session altered.SQL>再次来看看trace。 kxfpg1sg [ 4470/ 0] got 20 servers (sync), errors=0x0 returning GROUP GET [ 4490/ 20] Acquired 20 slaves on 1 instances avg height=20 in 1 set q serial:1025P000 inst 1 spid 4354 P001 inst 1 spid 4356 P002 inst 1 spid 4358 P003 inst 1 spid 4360 P004 inst 1 spid 4362 P005 inst 1 spid 4364 P006 inst 1 spid 4366 P007 inst 1 spid 4368 P008 inst 1 spid 4370 P009 inst 1 spid 4372 P010 inst 1 spid 4374 P011 inst 1 spid 4376 P012 inst 1 spid 4378 P013 inst 1 spid 4380 P014 inst 1 spid 4382 P015 inst 1 spid 4384 P016 inst 1 spid 4386 P017 inst 1 spid 4388 P018 inst 1 spid 4390 P019 inst 1 spid 4392 Insts 1 Svrs 20 可以看到只分配了20分slave进程，为啥呢？因为这里受到参数parallel_max_servers的限制。 SQL> show parameter parallel_max_serversNAME TYPE VALUE ------------------------------------ ----------- ------------------------------ parallel_max_servers integer 100 SQL> conn roger/roger SQL> alter session set parallel_degree_policy = auto;Session altered.SQL> alter session set "_px_trace"=all;Session altered.SQL> SELECT /*+ parallel(100) */ count(*) from ht02;COUNT(*) ---------- 225824SQL> alter session set "_px_trace"="none";Session altered. 此时trace 信息如下： grep -i inst 1 spid roger_ora_4507.trc roger_ora_4507.trc: Acquired 100 slaves on 1 instances avg height=100 in 1 set q serial:51 roger_ora_4507.trc: P000 inst 1 spid 4509 roger_ora_4507.trc: P001 inst 1 spid 4511 roger_ora_4507.trc: P002 inst 1 spid 4513 roger_ora_4507.trc: P003 inst 1 spid 4515 ....... roger_ora_4507.trc: P094 inst 1 spid 4698 roger_ora_4507.trc: P095 inst 1 spid 4700 roger_ora_4507.trc: P096 inst 1 spid 4702 roger_ora_4507.trc: P097 inst 1 spid 4704 roger_ora_4507.trc: P098 inst 1 spid 4706 roger_ora_4507.trc: P099 inst 1 spid 4708下面来进行auto DOP的测试。 首先说明下DOP的计算公式： 单实例： DOP = PARALLEL_THREADS_PER_CPU x CPU_COUNTRAC： DOP = PARALLEL_THREADS_PER_CPU x CPU_COUNT x INSTANCE_COUNTSQL> alter session set parallel_degree_policy = auto;Session altered.SQL> alter session set "_px_trace"=all;Session altered. SQL> select count(*) from ht02;Execution Plan ---------------------------------------------------------- Plan hash value: 583574080------------------------------------------------------------------- | Id | Operation | Name | Rows | Cost (%CPU)| Time | ------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 446 (1)| 00:00:06 | | 1 | SORT AGGREGATE | | 1 | | | | 2 | TABLE ACCESS FULL| HT02 | 225K| 446 (1)| 00:00:06 | -------------------------------------------------------------------Note ----- - automatic DOP: Computed Degree of Parallelism is 1 because of parallel threshold此时自动计算出来的DOP 为1,说明当前sql语句在parallel为1的情况下效率最高。 SQL> set autot off SQL> set timing on SQL> select count(*) from ht02;COUNT(*) ---------- 225824Elapsed: 00:00:00.08 SQL> SELECT /*+ parallel(2) */ count(*) from ht02;COUNT(*) ---------- 225824Elapsed: 00:00:00.22 SQL> SELECT /*+ parallel(4) */ count(*) from ht02;COUNT(*) ---------- 225824Elapsed: 00:00:00.74 SQL> SELECT /*+ parallel(6) */ count(*) from ht02;COUNT(*) ---------- 225824Elapsed: 00:00:00.81 SQL> SELECT /*+ parallel(8) */ count(*) from ht02;COUNT(*) ---------- 225824Elapsed: 00:00:00.92 SQL> SELECT /*+ parallel(10) */ count(*) from ht02;COUNT(*) ---------- 225824Elapsed: 00:00:01.15 SQL> SELECT /*+ parallel(20) */ count(*) from ht02;COUNT(*) ---------- 225824Elapsed: 00:00:03.24 SQL> SELECT /*+ parallel(40) */ count(*) from ht02;COUNT(*) ---------- 225824Elapsed: 00:00:09.11 SQL> SELECT /*+ parallel(60) */ count(*) from ht02;COUNT(*) ---------- 225824Elapsed: 00:00:23.75 SQL> SELECT /*+ parallel(80) */ count(*) from ht02;COUNT(*) ---------- 225824Elapsed: 00:00:29.80 根据上述数据制作图表如下：我们知道，当前测试是在parallel_min_time_threshold为默认值的情况下进行的测试， 下面更改该值。 SQL> alter session set parallel_min_time_threshold=3;Session altered. Elapsed: 00:00:00.02 SQL> alter system flush shared_pool; 此时的情况如下：从上面来看，当该值修改为3以后，Dop为2时效率是最高的，下面来看看起执行计划是否如此。 SQL> set autot traceonly SQL> alter system flush shared_pool;System altered.Elapsed: 00:00:00.55SQL> select count(*) from ht02; Elapsed: 00:00:01.50 Execution Plan ---------------------------------------------------------- Plan hash value: 2508058984 -------------------------------------------------------------------------------------------------------- | Id  | Operation              | Name     | Rows  | Cost (%CPU)| Time     |    TQ  |IN-OUT| PQ Distrib | -------------------------------------------------------------------------------------------------------- |   0 | SELECT STATEMENT       |          |     1 |   248   (1)| 00:00:03 |        |      |            | |   1 |  SORT AGGREGATE        |          |     1 |            |          |        |      |            | |   2 |   PX COORDINATOR       |          |       |            |          |        |      |            | |   3 |    PX SEND QC (RANDOM) | :TQ10000 |     1 |            |          |  Q1,00 | P->S | QC (RAND)  | |   4 |     SORT AGGREGATE     |          |     1 |            |          |  Q1,00 | PCWP |            | |   5 |      PX BLOCK ITERATOR |          |   225K|   248   (1)| 00:00:03 |  Q1,00 | PCWC |            | |   6 |       TABLE ACCESS FULL| HT02     |   225K|   248   (1)| 00:00:03 |  Q1,00 | PCWP |            | -------------------------------------------------------------------------------------------------------- Note -----    - automatic DOP: Computed Degree of Parallelism is 2 Statistics ----------------------------------------------------------         377  recursive calls          12  db block gets        1444  consistent gets        1335  physical reads           0  redo size         424  bytes sent via SQL*Net to client         415  bytes received via SQL*Net from client           2  SQL*Net roundtrips to/from client           6  sorts (memory)           0  sorts (disk) 1 rows processed 这里补充一点是，如果参数parallel_degree_policy为manual时，我们可以使用parallel hint来使用该特性，如下：SQL> SELECT /*+ parallel(auto) */ count(*) from ht02; Execution Plan ---------------------------------------------------------- Plan hash value: 583574080 ------------------------------------------------------------------- | Id  | Operation          | Name | Rows  | Cost (%CPU)| Time     | ------------------------------------------------------------------- |   0 | SELECT STATEMENT   |      |     1 |   446   (1)| 00:00:06 | |   1 |  SORT AGGREGATE    |      |     1 |            |          | |   2 |   TABLE ACCESS FULL| HT02 |   225K|   446   (1)| 00:00:06 | ------------------------------------------------------------------- Note -----    - automatic DOP: Computed Degree of Parallelism is 1 because of parallel threshold Statistics ----------------------------------------------------------           0  recursive calls           0  db block gets        1338  consistent gets        1335  physical reads           0  redo size         424  bytes sent via SQL*Net to client         415  bytes received via SQL*Net from client           2  SQL*Net roundtrips to/from client           0  sorts (memory)           0  sorts (disk)           1  rows processed SQL> alter session set parallel_min_time_threshold=5; Session altered. SQL> SELECT /*+ parallel(auto) */ count(*) from ht02; Execution Plan ---------------------------------------------------------- Plan hash value: 2508058984 -------------------------------------------------------------------------------------------------------- | Id  | Operation              | Name     | Rows  | Cost (%CPU)| Time     |    TQ  |IN-OUT| PQ Distrib | -------------------------------------------------------------------------------------------------------- |   0 | SELECT STATEMENT       |          |     1 |   248   (1)| 00:00:03 |        |      |            | |   1 |  SORT AGGREGATE        |          |     1 |            |          |        |      |            | |   2 |   PX COORDINATOR       |          |       |            |          |        |      |            | |   3 |    PX SEND QC (RANDOM) | :TQ10000 |     1 |            |          |  Q1,00 | P->S | QC (RAND)  | |   4 |     SORT AGGREGATE     |          |     1 |            |          |  Q1,00 | PCWP |            | |   5 |      PX BLOCK ITERATOR |          |   225K|   248   (1)| 00:00:03 |  Q1,00 | PCWC |            | |   6 |       TABLE ACCESS FULL| HT02     |   225K|   248   (1)| 00:00:03 |  Q1,00 | PCWP |            | -------------------------------------------------------------------------------------------------------- Note -----    - automatic DOP: Computed Degree of Parallelism is 2 Statistics ----------------------------------------------------------           8  recursive calls           4  db block gets        1394  consistent gets        1335  physical reads           0  redo size         424  bytes sent via SQL*Net to client         415  bytes received via SQL*Net from client           2  SQL*Net roundtrips to/from client           0  sorts (memory)           0  sorts (disk)           1  rows processed 关于该特性，是针对DW环境的，auto dop的计算，其实在11gR1就有了，不过计算的算法不太合理，存在一定的缺陷。最后我们来看下11gR2中auto dop是如何计算的。 kxfrDefaultDOP DOP Trace -- compute default DOP # CPU = 1 Threads/CPU = 2 ("parallel_threads_per_cpu") default DOP = 2 (# CPU * Threads/CPU) default DOP = 2 (DOP * # instance) Default DOP = 2 kxfrDefaultDOP DOP Trace -- compute default DOP # CPU = 1 Threads/CPU = 2 ("parallel_threads_per_cpu") default DOP = 2 (# CPU * Threads/CPU) default DOP = 2 (DOP * # instance) Default DOP = 2 ----- 系统默认的DOP kxfxqOnOrderQueue KXFXQQUEUABLE() is TRUE. pgadep: 0, pgatopsql: 1, pgapls 0. SlaveSQL?: NO, Parallized?: YES, DOP: 2. kxfxqOnOrderQueue Admitting Parallel Statement (dop:2): ----- Current SQL Statement for this session (sql_id=b4npv3kz33xb5) ----- select count(*) from ht02 kxfxqUpdateLoad ---更新负载情况 snapshot of RAC load before update: [ total queued PQ: 0, total running( admitted) PQ: 0, total granted slaves in RAC: 0, total target in RAC: 8 ] kxfpuqpq ---更新queue PQ instance load stat of queued PQ updated. number of queued PQ is incremented from 0 to 1. load stat(queued PQ) on all RAC has been updated. snapshot of RAC load stat [ total queued PQ: 1, total running(admitted) PQ: 0, total granted slaves in RAC: 0, total target in RAC: 8 ] kxfxqsoc ----创建state object(应该就是一个SQL语句的标示类似SQL_ID) state object created [stmt id: 16777230, exetime: 1315063063, queued? tr ue, starting? false, granted slaves: 0] kxfxqInstInfo ---检查实例信息 inst[cpus:mxslv] 1[1:100] ---1 描述为1个节点 100描述parallel_max_servers为100 kxfpGetNumActiveSlaves ---获得当前处于活动的savle进程 number of active slaves on the instance: 0 kxfpGetDefInstTarget ---获取实例默认parallel_target_servers参数值 default inst target is 8, defDOP: 2, mxu: 2, cpus: 1 kxfpGetInstTarget (default: 1) inst target is 8 kxfxqLocalInstLoad ---获取本地节点的实例负载 local inst(load:user:pct:fact:queued:admitted:started:granted:active) 1 (0:0:100:0:1:0:0:0:0) kxfxqInstLoad ---获取实例的负载信息(如果是RAC的话，应该跟上面的不同) inst(load:user:pct:fact:queued:admitted:started:granted:active) 1(local) (0:0:100:0:1:0:0:0:0) kxfxqInstList ---初始化实例负载信息 load information of 1 instances (single inst) initialized kxfxqGrantedDOP ---根据系统负载情况计算合理的DOP值 Computing granted DOP. kxfxqGrantedDOP ---根据前面的计算结果分配dop即是分配salve进程 RequestedDOP=2 GrantedDOP=2 Target=8 Load=0 GrantedSlv=0 AdmittedPQ=0 De faultDOP=0 users=0 sets=1 force_admit=false kxfxqUpdateLoad ---再次更新实例负载信息 snapshot of RAC load before update: [ total queued PQ: 1, total running( admitted) PQ: 0, total granted slaves in RAC: 0, total target in RAC: 8 ] kxfpuqpq ---再次更新queue PQ instance load stat of queued PQ updated. number of queued PQ is decremented from 1 to 0. load stat(queued PQ) on all RAC has been updated. snapshot of RAC load stat [ total queued PQ: 0, total running(admitted) PQ: 0, total granted slaves in RAC: 0, total target in RAC: 8 ] kxfxqUpdateLoad ----第3次更新实例负载信息 snapshot of RAC load before update: [ total queued PQ: 0, total running( admitted) PQ: 0, total granted slaves in RAC: 0, total target in RAC: 8 ] kxfpAdjustGrantedSlaves ---根据前面的多次调整，决定是否需要调整dop即salve进程 gslv is not adjusted.sga total gslv: 0, glsv: 2, adjusted gslv 2. kxfpurpq instance load stat of admitted PQ updated. number of admitted PQ is incremented from 0 to 1, total granted slaves is incremented from 0 to 2. load stat(running(admitted) PQ) on all RAC has been updated. snapshot of RAC load stat [ total queued PQ: 0, total running(admitted) PQ: 1, total granted slaves in RAC: 2, total target in RAC: 8 ] kxfxqsou state object updated [stmt id: 16777230, exe time: 1315063063, queued? f alse, starting? true, granted slaves: 2, remove state obj? false kxfxqOnOrderQueue Statement bypasses the queue. Starting parallelizer rwsid:2 pxid:1 qerpxStart [ 0/ 0] Start: Starting SQL statement dump SQL Information user_id=85 user_name=ROGER module=SQL*Plus action= sql_id=b4npv3kz33xb5 plan_hash_value=-1786908312 problem_type=3 ----- Current SQL Statement for this session (sql_id=b4npv3kz33xb5) ----- select count(*) from ht02 sql_text_length=26 sql=select count(*) from ht02----- Explain Plan Dump ----- ----- Plan Table -----   ============ Plan Table ============ ------------------------------------------+-----------------------------------+-------------------------+ | Id  | Operation               | Name    | Rows  | Bytes | Cost  | Time      |  TQ  |IN-OUT|PQ Distrib | ------------------------------------------+-----------------------------------+-------------------------+ | 0   | SELECT STATEMENT        |         |       |       |   248 |           |      |      |           | | 1   |  SORT AGGREGATE         |         |     1 |       |       |           |      |      |           | | 2   |   PX COORDINATOR        |         |       |       |       |           |      |      |           | | 3   |    PX SEND QC (RANDOM)  | :TQ10000|     1 |       |       |           |:Q1000| P->S |QC (RANDOM)| | 4   |     SORT AGGREGATE      |         |     1 |       |       |           |:Q1000| PCWP |           | | 5   |      PX BLOCK ITERATOR  |         |  221K |       |   248 |  00:00:03 |:Q1000| PCWC |           | | 6   |       TABLE ACCESS FULL | HT02    |  221K |       |   248 |  00:00:03 |:Q1000| PCWP |           | ------------------------------------------+-----------------------------------+-------------------------+ ............... GROUP GET [ 470/ 0] Acquired 2 slaves on 1 instances avg height=2 in 1 set q serial:23041 P000 inst 1 spid 7835 P001 inst 1 spid 7837 Insts 1 Svrs 2 kxfpValidateSlaveGroup [ 470/ 0] qcq:0x2292fc94 flg:0 qerpxSendParse [ 470/ 0] qcq=0x2292fc94 pxid=1 mflg=0x0 #slaves=2 kxfxcp1 [ 470/ 0] Sending parse to nprocs:2 slave_set:1 kxfxcPutSession [ 520/ 50] ................ kxfpqsrls [ 820/ 10] Release Slave q=0x2292fc94 qr=0x2247e860 action=1 slave=1 inst=1 ----这里是释放salve进程 kxfpqsrls [ 820/ 0] Release Slave q=0x2292fc94 qr=0x22481710 action=1 slave=0 inst=1 GROUP RELEASE [ 820/ 0] all slaves released q serial 23041 kxfpqsod_qc_sod [ 820/ 0] clean up of q=0x2292fc94 completed kxfrfir [ 820/ 0] cbk fired: 0x9d8b24 kxfxqsou [ 820/ 0] state object removed [stmt id: 16777230, exe time: 1315063063] ---删除创建的state object kxfrfir [ 820/ 0] cbk fired: 0x9d8b14 kxfxqRPQcbk [ 820/ 0] Decr admitted parallel statement load kxfxqUpdateLoad [ 820/ 0]--更新负载信息 snapshot of RAC load before update: [ total queued PQ: 0, total running( admitted) PQ: 1, total granted slaves in RAC: 2, total target in RAC: 8 ] kxfpAdjustGrantedSlaves [ 820/ 0] gslv is not adjusted.sga total gslv: 2, glsv: 2, adjusted gslv 2. kxfpurpq [ 820/ 0] instance load stat of admitted PQ updated. ---更新queue PQ number of admitted PQ is decremented from 1 to 0, total granted slaves is decremented from 2 to 0. load stat(running(admitted) PQ) on all RAC has been updated. snapshot of RAC load stat [ total queued PQ: 0, total running(admitted) PQ: 0, total granted slaves in RAC: 0, total target in RAC: 8 ]根据前面的实验我们可以清楚的看到11gR2中 auto dop的操作过程，做出如下的简单总结： 1. 根据系统默认dop进行计算计算出一个dop; 2. 获取当前实例的负载信息(rac环境会获取所有节点的负载信息); 3. 根据负载信息，结合前面的dop进行计算，得出一个dop值; 4. 多次更新实例负载信息，这里应该还会参考过去的负载信息进行多次计算。 从上面来看，这里计算了2次，我猜测此时这2分实例负载信息类似该实例过去的一份awr快照一样； 5. 最后计算出一个合理的dop值，然后生成执行计划； 6. 执行sql语句(执行之前会进行salve进程的分配）； 7. 执行完毕以后，释放salve进程; 8. 更新实例负载信息（我猜测这次的负载信息会被下次计算作为参考).个人见解，希望能够抛砖引玉!