sitemap

RSS地图

收藏本站

设为首页

Oracle研究中心

当前位置:Oracle研究中心 > 运维DBA >

【学习笔记】Oracle 11gR2新特性之In-Memory Parallel execution深入研究

时间:2016-11-21 19:44   来源:Oracle研究中心   作者:网络   点击:

天萃荷净 Oracle研究中心学习笔记:分享一篇关于Oracle数据库11.2.0.1版本的新特性研究笔记,详细介绍11gR2新特性之In-Memory Parallel execution深入研究。

本站文章除注明转载外,均为本站原创: 转载自love wife & love life —Roger 的Oracle技术博客
本文链接地址: 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中 auto dop的操作过程,做出如下的简单总结:

1. 根据系统默认dop进行计算计算出一个dop;
2. 获取当前实例的负载信息(rac环境会获取所有节点的负载信息);
3. 根据负载信息,结合前面的dop进行计算,得出一个dop值;
4. 多次更新实例负载信息,这里应该还会参考过去的负载信息进行多次计算。
从上面来看,这里计算了2次,我猜测此时这2分实例负载信息类似该实例过去的一份awr快照一样;
5. 最后计算出一个合理的dop值,然后生成执行计划;
6. 执行sql语句(执行之前会进行salve进程的分配);
7. 执行完毕以后,释放salve进程;
8. 更新实例负载信息(我猜测这次的负载信息会被下次计算作为参考).

但是在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,这里不多说。
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信息:

*** 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(*)
----------
225824

SQL> ALTER SESSION SET "_px_trace"="none";
SESSION altered.

再次来看看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: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的限制。######
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 信息如下:

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

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的情况下效率最高。

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为默认值的情况下进行的测试,下面更改该值。

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

SQL> SELECT COUNT(*) FROM ht02;http://www.oracleplus.net

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. 更新实例负载信息(我猜测这次的负载信息会被下次计算作为参考).
个人见解,希望能够抛砖引玉!

--------------------------------------ORACLE-DBA----------------------------------------

最权威、专业的Oracle案例资源汇总之【学习笔记】Oracle 11gR2新特性之In-Memory Parallel execution深入研究

本文由大师惜分飞原创分享,网址:http://www.oracleplus.net/arch/1305.html

Oracle研究中心

关键词:

In-Memory Parallel execution新特性

Oracle In-Memory Parallel execution测试笔记