11gR2 新特性之—In-Memory Parallel execution
本站文章除注明转载外，均为本站原创： 转载自love wife love life —Roger的Oracle/MySQL/PostgreSQL数据恢复博客
该特性是在oracle 11gR2 引入，其目的不言而喻。在11gR2之前，也就是在11gR1中，
如果当你发出/*+parallel(16) */ 时，可能会出现下面两种情形：
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 |
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, and in-memory parallel execution。 ---auto Enables automatic degree of parallelism, statement queuing, and 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 < parallel_max_servers. 11gR2还引入了一个新的包DBMS_PARALLEL_EXECUTE，这里不多说。 |
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 |
SQL> 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 0 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(*) ---------- 225824 SQL> alter session set "_px_trace"="none"; Session altered. |
我们来看下trace信息：
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 |
*** 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会是多少。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
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(*) ---------- 225824 SQL> alter session set "_px_trace"="none"; Session altered. |
再次来看看trace。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 |
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:1025 P000 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的限制。###### |
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 |
SQL> show parameter parallel_max_servers NAME 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(*) ---------- 225824 SQL> alter session set "_px_trace"="none"; Session altered. |
此时trace 信息如下：
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
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_COUNT
RAC： DOP = PARALLEL_THREADS_PER_CPU x CPU_COUNT x INSTANCE_COUNT
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 |
SQL> 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的情况下效率最高。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 |
SQL> set autot off SQL> set timing on SQL> select count(*) from ht02; COUNT(*) ---------- 225824 Elapsed: 00:00:00.08 SQL> SELECT /*+ parallel(2) */ count(*) from ht02; COUNT(*) ---------- 225824 Elapsed: 00:00:00.22 SQL> SELECT /*+ parallel(4) */ count(*) from ht02; COUNT(*) ---------- 225824 Elapsed: 00:00:00.74 SQL> SELECT /*+ parallel(6) */ count(*) from ht02; COUNT(*) ---------- 225824 Elapsed: 00:00:00.81 SQL> SELECT /*+ parallel(8) */ count(*) from ht02; COUNT(*) ---------- 225824 Elapsed: 00:00:00.92 SQL> SELECT /*+ parallel(10) */ count(*) from ht02; COUNT(*) ---------- 225824 Elapsed: 00:00:01.15 SQL> SELECT /*+ parallel(20) */ count(*) from ht02; COUNT(*) ---------- 225824 Elapsed: 00:00:03.24 SQL> SELECT /*+ parallel(40) */ count(*) from ht02; COUNT(*) ---------- 225824 Elapsed: 00:00:09.11 SQL> SELECT /*+ parallel(60) */ count(*) from ht02; COUNT(*) ---------- 225824 Elapsed: 00:00:23.75 SQL> SELECT /*+ parallel(80) */ count(*) from ht02; COUNT(*) ---------- 225824 Elapsed: 00:00:29.80 |
根据上述数据制作图表如下：
我们知道，当前测试是在parallel_min_time_threshold为默认值的情况下进行的测试，
下面更改该值。
|
1 2 3 4 5 6 |
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时效率是最高的，下面来看看起执行计划是否如此。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 |
SQL> set autot traceonly SQL> alter system flush shared_pool; System altered. Elapsed: 00:00:00.55 SQL> 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来使用该特性，如下：
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 |
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是如何计算的。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 |
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. 更新实例负载信息（我猜测这次的负载信息会被下次计算作为参考).
个人见解，希望能够抛砖引玉!
2 Responses to “11gR2 新特性之—In-Memory Parallel execution”
You have a great website! I would love to build backlinks for you.
[…] http://www.killdb.com/?p=348 该特性是在oracle 11gR2 […]
Leave a Reply
You must be logged in to post a comment.