1.给出一个2表关联的并行查询执行计划,并画出并行数据流图。<br>
2.就自己本机的硬件情况,通过SQL示例,来找到最优的并行度。<br>
3.针对PARALLEL_DEGREE_POLICY的三个值,分别演示它们的效果。<br>
4.用一个并行的SQL示例,比较10391事件和 V$PQ_TQSTAT结果的异同。<br>
5.分别演示一个DDL和DML操作的并行执行示例。<br>
===============================================================
1.给出一个2表关联的并行查询执行计划,并画出并行数据流图。<br>
答:
SQL> set linesize 8000;
SQL> set autotrace traceonly;
SQL> select /*+ PARALLEL(d 2) PARALLEL(e 2) */ d.dname,e.* from scott.dept d inner join scott.emp e on d.deptno=e.deptno;
14 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 197865614
-----------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | TQ |IN-OUT| PQ Distrib |
-----------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 14 | 714 | 5 (20)| 00:00:01 | | | |
| 1 | PX COORDINATOR | | | | | | | | |
| 2 | PX SEND QC (RANDOM) | :TQ10001 | 14 | 714 | 5 (20)| 00:00:01 | Q1,01 | P->S | QC (RAND) |
|* 3 | HASH JOIN | | 14 | 714 | 5 (20)| 00:00:01 | Q1,01 | PCWP | |
| 4 | PX RECEIVE | | 4 | 52 | 2 (0)| 00:00:01 | Q1,01 | PCWP | |
| 5 | PX SEND BROADCAST | :TQ10000 | 4 | 52 | 2 (0)| 00:00:01 | Q1,00 | P->P | BROADCAST |
| 6 | PX BLOCK ITERATOR | | 4 | 52 | 2 (0)| 00:00:01 | Q1,00 | PCWC | |
| 7 | TABLE ACCESS FULL| DEPT | 4 | 52 | 2 (0)| 00:00:01 | Q1,00 | PCWP | |
| 8 | PX BLOCK ITERATOR | | 14 | 532 | 2 (0)| 00:00:01 | Q1,01 | PCWC | |
| 9 | TABLE ACCESS FULL | EMP | 14 | 532 | 2 (0)| 00:00:01 | Q1,01 | PCWP | |
-----------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("D"."DEPTNO"="E"."DEPTNO")
Statistics
----------------------------------------------------------
176 recursive calls
2 db block gets
116 consistent gets
10 physical reads
0 redo size
1758 bytes sent via SQL*Net to client
524 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
16 sorts (memory)
0 sorts (disk)
14 rows processed
(并行数据流示意图请看附件)
-----------------------------------------------------------------------
2.就自己本机的硬件情况,通过SQL示例,来找到最优的并行度。<br>
使用了公司内部的测试服务器,性能上用来做一般的测试服务器应该还算可以:
配置:
CPU: INTER XEON 3.33 GHZ,2处理器,
MEM:16G
OS:windows server ENTERPRISE 64位;
进行以下测试时没有其它业务在运行。
2.1 设置使用5个并行进行查询统计,看到COST=608
SQL> select /*+ PARALLEL(5) */ c_id,c_name from mcard.cards ;
155048 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 542627410
--------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | TQ |IN-OUT| PQ Distrib |
--------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 154K| 2114K| 608 (0)| 00:00:02 | | | |
| 1 | PX COORDINATOR | | | | | | | | |
| 2 | PX SEND QC (RANDOM)| :TQ10000 | 154K| 2114K| 608 (0)| 00:00:02 | Q1,00 | P->S | QC (RAND) |
| 3 | PX BLOCK ITERATOR | | 154K| 2114K| 608 (0)| 00:00:02 | Q1,00 | PCWC | |
| 4 | TABLE ACCESS FULL| cards | 154K| 2114K| 608 (0)| 00:00:02 | Q1,00 | PCWP | |
--------------------------------------------------------------------------------------------------------------
2.2 设置使用10个并行进行查询统计,看到COST=304,减少了一半
SQL>
SQL> select /*+ PARALLEL(10) */ c_id,c_name from mcard.cards ;
155048 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 542627410
--------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | TQ |IN-OUT| PQ Distrib |
--------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 154K| 2114K| 304 (0)| 00:00:01 | | | |
| 1 | PX COORDINATOR | | | | | | | | |
| 2 | PX SEND QC (RANDOM)| :TQ10000 | 154K| 2114K| 304 (0)| 00:00:01 | Q1,00 | P->S | QC (RAND) |
| 3 | PX BLOCK ITERATOR | | 154K| 2114K| 304 (0)| 00:00:01 | Q1,00 | PCWC | |
| 4 | TABLE ACCESS FULL| cards | 154K| 2114K| 304 (0)| 00:00:01 | Q1,00 | PCWP | |
--------------------------------------------------------------------------------------------------------------
Note
-----
- Degree of Parallelism is 10 because of hint
2.3 设置使用30个并行进行查询统计,看到COST=101,减少到1/3
SQL> select /*+ PARALLEL(30) */ c_id,c_name from mcard.cards ;
155048 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 542627410
--------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | TQ |IN-OUT| PQ Distrib |
--------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 154K| 2114K| 101 (0)| 00:00:01 | | | |
| 1 | PX COORDINATOR | | | | | | | | |
| 2 | PX SEND QC (RANDOM)| :TQ10000 | 154K| 2114K| 101 (0)| 00:00:01 | Q1,00 | P->S | QC (RAND) |
| 3 | PX BLOCK ITERATOR | | 154K| 2114K| 101 (0)| 00:00:01 | Q1,00 | PCWC | |
| 4 | TABLE ACCESS FULL| cards | 154K| 2114K| 101 (0)| 00:00:01 | Q1,00 | PCWP | |
--------------------------------------------------------------------------------------------------------------
Note
-----
- Degree of Parallelism is 30 because of hint
2.4 设置使用30个并行进行查询统计,看到COST=76,减少到并行度为10 时的1/4
SQL> select /*+ PARALLEL(40) */ c_id,c_name from mcard.cards ;
155048 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 542627410
--------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | TQ |IN-OUT| PQ Distrib |
--------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 154K| 2114K| 76 (0)| 00:00:01 | | | |
| 1 | PX COORDINATOR | | | | | | | | |
| 2 | PX SEND QC (RANDOM)| :TQ10000 | 154K| 2114K| 76 (0)| 00:00:01 | Q1,00 | P->S | QC (RAND) |
| 3 | PX BLOCK ITERATOR | | 154K| 2114K| 76 (0)| 00:00:01 | Q1,00 | PCWC | |
| 4 | TABLE ACCESS FULL| cards | 154K| 2114K| 76 (0)| 00:00:01 | Q1,00 | PCWP | |
--------------------------------------------------------------------------------------------------------------
Note
-----
- Degree of Parallelism is 40 because of hint
我经过测试,一直测试到120个并行度时,都还是正常的。看来在此服务器上,并不能测试出
此数据查询的合适并行度是多少。因为服务器为空闲。并且性能也不错。
直到测试出错误:ORA-00020:maximum number of processes (150) exceeded 错误
我的参数 processes=150,后来修改成300,再测试下面内容
下面使用了更大的表进行测试:直到测试到25-30个并行度时,COST才没有按比例下降。
所以我认为多少个最优的并行度,并不适合不同的表。并且也不能做为优化查询的一个指标。
因为在正式的产品服务器中,性能是在变化的。只要能满足当前业务需求就好。
SQL> set autotrance onlytrace
SQL> set autotrace traceonly;
SQL> set linesize 800;
SQL> select /*+ PARALLEL(1) */ count(0) from persion p;
Execution Plan
----------------------------------------------------------
Plan hash value: 1994127684
----------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
----------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 75474 (1)| 00:15:06 |
| 1 | SORT AGGREGATE | | 1 | | |
| 2 | TABLE ACCESS FULL| PERSION | 20M| 75474 (1)| 00:15:06 |
----------------------------------------------------------------------
Statistics
----------------------------------------------------------
1 recursive calls
1 db block gets
278268 consistent gets
277516 physical reads
0 redo size
529 bytes sent via SQL*Net to client
524 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
SQL> select /*+ PARALLEL(2) */ count(0) from persion p;
Execution Plan
----------------------------------------------------------
Plan hash value: 1284080732
--------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time | TQ |IN-OUT| PQ Distrib |
--------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 41876 (1)| 00:01:24 | | | |
| 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 | | 20M| 41876 (1)| 00:01:24 | Q1,00 | PCWC | |
| 6 | TABLE ACCESS FULL| PERSION | 20M| 41876 (1)| 00:01:24 | Q1,00 | PCWP | |
--------------------------------------------------------------------------------------------------------
Note
-----
- Degree of Parallelism is 2 because of hint
Statistics
----------------------------------------------------------
7 recursive calls
220 db block gets
442739 consistent gets
277516 physical reads
0 redo size
529 bytes sent via SQL*Net to client
524 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
SQL> select /*+ PARALLEL(3) */ count(0) from persion p;
Execution Plan
----------------------------------------------------------
Plan hash value: 1284080732
--------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time | TQ |IN-OUT| PQ Distrib |
--------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 27918 (1)| 00:00:56 | | | |
| 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 | | 20M| 27918 (1)| 00:00:56 | Q1,00 | PCWC | |
| 6 | TABLE ACCESS FULL| PERSION | 20M| 27918 (1)| 00:00:56 | Q1,00 | PCWP | |
--------------------------------------------------------------------------------------------------------
Note
-----
- Degree of Parallelism is 3 because of hint
Statistics
----------------------------------------------------------
12 recursive calls
237 db block gets
455506 consistent gets
277516 physical reads
0 redo size
529 bytes sent via SQL*Net to client
524 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
SQL> select /*+ PARALLEL(4) */ count(0) from persion p;
Execution Plan
----------------------------------------------------------
Plan hash value: 1284080732
--------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time | TQ |IN-OUT| PQ Distrib |
--------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 20938 (1)| 00:00:42 | | | |
| 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 | | 20M| 20938 (1)| 00:00:42 | Q1,00 | PCWC | |
| 6 | TABLE ACCESS FULL| PERSION | 20M| 20938 (1)| 00:00:42 | Q1,00 | PCWP | |
--------------------------------------------------------------------------------------------------------
Note
-----
- Degree of Parallelism is 4 because of hint
Statistics
----------------------------------------------------------
15 recursive calls
238 db block gets
456257 consistent gets
277516 physical reads
0 redo size
529 bytes sent via SQL*Net to client
524 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
SQL> select /*+ PARALLEL(5) */ count(0) from persion p;
Execution Plan
----------------------------------------------------------
Plan hash value: 1284080732
--------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time | TQ |IN-OUT| PQ Distrib |
--------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 16751 (1)| 00:00:34 | | | |
| 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 | | 20M| 16751 (1)| 00:00:34 | Q1,00 | PCWC | |
| 6 | TABLE ACCESS FULL| PERSION | 20M| 16751 (1)| 00:00:34 | Q1,00 | PCWP | |
--------------------------------------------------------------------------------------------------------
Note
-----
- Degree of Parallelism is 5 because of hint
Statistics
----------------------------------------------------------
18 recursive calls
238 db block gets
456257 consistent gets
277516 physical reads
0 redo size
529 bytes sent via SQL*Net to client
524 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
SQL> select /*+ PARALLEL(6) */ count(0) from persion p;
Execution Plan
----------------------------------------------------------
Plan hash value: 1284080732
--------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time | TQ |IN-OUT| PQ Distrib |
--------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 13959 (1)| 00:00:28 | | | |
| 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 | | 20M| 13959 (1)| 00:00:28 | Q1,00 | PCWC | |
| 6 | TABLE ACCESS FULL| PERSION | 20M| 13959 (1)| 00:00:28 | Q1,00 | PCWP | |
--------------------------------------------------------------------------------------------------------
Note
-----
- Degree of Parallelism is 6 because of hint
Statistics
----------------------------------------------------------
21 recursive calls
255 db block gets
469024 consistent gets
277516 physical reads
0 redo size
529 bytes sent via SQL*Net to client
524 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
SQL> select /*+ PARALLEL(7) */ count(0) from persion p;
Execution Plan
----------------------------------------------------------
Plan hash value: 1284080732
--------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time | TQ |IN-OUT| PQ Distrib |
--------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 11965 (1)| 00:00:24 | | | |
| 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 | | 20M| 11965 (1)| 00:00:24 | Q1,00 | PCWC | |
| 6 | TABLE ACCESS FULL| PERSION | 20M| 11965 (1)| 00:00:24 | Q1,00 | PCWP | |
--------------------------------------------------------------------------------------------------------
Note
-----
- Degree of Parallelism is 7 because of hint
SQL> select /*+ PARALLEL(20) */ count(0) from persion p;
Execution Plan
----------------------------------------------------------
Plan hash value: 1284080732
--------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time | TQ |IN-OUT| PQ Distrib |
--------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 4188 (1)| 00:00:09 | | | |
| 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 | | 20M| 4188 (1)| 00:00:09 | Q1,00 | PCWC | |
| 6 | TABLE ACCESS FULL| PERSION | 20M| 4188 (1)| 00:00:09 | Q1,00 | PCWP | |
--------------------------------------------------------------------------------------------------------
Note
-----
- Degree of Parallelism is 20 because of hint
SQL> select /*+ PARALLEL(30) */ count(0) from persion p;
Execution Plan
----------------------------------------------------------
Plan hash value: 1284080732
--------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time | TQ |IN-OUT| PQ Distrib |
--------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 2792 (1)| 00:00:06 | | | |
| 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 | | 20M| 2792 (1)| 00:00:06 | Q1,00 | PCWC | |
| 6 | TABLE ACCESS FULL| PERSION | 20M| 2792 (1)| 00:00:06 | Q1,00 | PCWP | |
--------------------------------------------------------------------------------------------------------
Note
-----
- Degree of Parallelism is 30 because of hint
SQL> select /*+ PARALLEL(25) */ count(0) from persion p;
Execution Plan
----------------------------------------------------------
Plan hash value: 1284080732
--------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time | TQ |IN-OUT| PQ Distrib |
--------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 3350 (1)| 00:00:07 | | | |
| 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 | | 20M| 3350 (1)| 00:00:07 | Q1,00 | PCWC | |
| 6 | TABLE ACCESS FULL| PERSION | 20M| 3350 (1)| 00:00:07 | Q1,00 | PCWP | |
--------------------------------------------------------------------------------------------------------
Note
-----
- Degree of Parallelism is 25 because of hint
Statistics
----------------------------------------------------------
76 recursive calls
501 db block gets
653770 consistent gets
277516 physical reads
0 redo size
529 bytes sent via SQL*Net to client
524 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
SQL>
-----------------------------------------------------------------------
3.针对PARALLEL_DEGREE_POLICY的三个值,分别演示它们的效果。<br>
答: PARALLEL_DEGREE_POLICY 三个值为:
MANUAL: 手动方式,也是默认方式,ORACLE不会擅自去调整并行度
LIMITED: 限制方式 如果为限制方式,即使你指定并行度大于此
auto: 自动方式
3.1.1 查询当前参数值
SQL> set linesize 800;
SQL> show parameter parallel_degree;
NAME TYPE VALUE
---------------------- -------------- ---------------
parallel_degree_limit string CPU
parallel_degree_policy string MANUAL
SQL>
3.1.2 建立测试表,使用HINT 指定并行并查询,
SQL> drop table t purge;
Table dropped.
SQL> create table t as select * from dba_objects;
Table created.
看到这时ORACLE使用了我指定的并行度进行查询数据;
3.1.3 指定表默认并行度,再进行查询
SQL> alter table t parallel(degree 4);
Table altered.
select table_name,degree from user_tables t where t.TABLE_NAME='T'
TABLE_NAME DEGREE
--------------------
1 T 4
SQL> set autotrace off;
SQL> SELECT * FROM V$PQ_SESSTAT WHERE STATISTIC='Allocation Height';
STATISTIC LAST_QUERY SESSION_TOTAL
------------------- ---------- -------------
Allocation Height 0 0
SQL> select /*+ PARALLEL(t 4) */ count(0) from t;
COUNT(0)
----------
76386
SQL> SELECT * FROM V$PQ_SESSTAT WHERE STATISTIC='Allocation Height';
STATISTIC LAST_QUERY SESSION_TOTAL
------------- ---------- -------------
Allocation Height 4 0
SQL>
SQL> SELECT * FROM V$PQ_SESSTAT WHERE STATISTIC='Allocation Height';
STATISTIC LAST_QUERY SESSION_TOTAL
--------------- ---------- -------------
Allocation Height 4 0
SQL> select /*+ PARALLEL(t 6) */ count(0) from t;
COUNT(0)
----------
76386
SQL> SELECT * FROM V$PQ_SESSTAT WHERE STATISTIC='Allocation Height';
STATISTIC LAST_QUERY SESSION_TOTAL
------------ ---------- -------------
Allocation Height 6 0
从上面的3次查询可以看出,不管是我指定并行度或是使用默认的并行度,真正在查询时,
ORACLE都是按我指定的或是按默认的并行度进行查询,并没有干预。
3.2.1 把参数 PARALLEL_DEGREE_POLICY 设置成LIMITED
SQL> ALTER SESSION SET PARALLEL_DEGREE_POLICY=LIMITED;
SQL> select count(0) from t;
COUNT(0)
----------
76386
SQL> SELECT * FROM V$PQ_SESSTAT WHERE STATISTIC='Allocation Height';
STATISTIC LAST_QUERY SESSION_TOTAL
----------- ---------- -------------
Allocation Height 4 0
SQL> select /*+ PARALLEL(t 60) */ count(0) from t;
COUNT(0)
----------
76386
SQL> SELECT * FROM V$PQ_SESSTAT WHERE STATISTIC='Allocation Height';
STATISTIC LAST_QUERY SESSION_TOTAL
----------- ---------- -------------
Allocation Height 60 0
SQL> select /*+ PARALLEL(t 100) */ count(0) from t;
COUNT(0)
----------
76386
SQL> SELECT * FROM V$PQ_SESSTAT WHERE STATISTIC='Allocation Height';
STATISTIC LAST_QUERY SESSION_TOTAL
----------- ---------- -------------
Allocation Height 100 0
SQL> select /*+ PARALLEL(t 200) */ count(0) from t;
COUNT(0)
----------
76386
SQL> SELECT * FROM V$PQ_SESSTAT WHERE STATISTIC='Allocation Height';
STATISTIC LAST_QUERY SESSION_TOTAL
----------- ---------- -------------
Allocation Height 200 0
SQL> select /*+ PARALLEL(t 2900) */ count(0) from t;
COUNT(0)
----------
76386
STATISTIC LAST_QUERY SESSION_TOTAL
----------- ---------- -------------
Allocation Height 48 0
SQL>
因为服务器性能不错的原因,上面我的测试,开始一直加大并行度,ORACLE都没有干预,但我加大到 2900时(这时大于参数PROCESSES)
ORACLE开始干预了,系统认为 48 是最好的。
3.3.1 把参数 PARALLEL_DEGREE_POLICY 设置成AUTO
ALTER SESSION SET PARALLEL_DEGREE_POLICY=AUTO;
SQL> ALTER SESSION SET PARALLEL_DEGREE_POLICY=AUTO;
Session altered.
SQL> select count(0) from t;
COUNT(0)
----------
76386
SQL> SELECT * FROM V$PQ_SESSTAT WHERE STATISTIC='Allocation Height';
STATISTIC LAST_QUERY SESSION_TOTAL
-------- ---------- -------------
Allocation Height 4 0
SQL> select /*+ PARALLEL(t 20) */ count(0) from t;
COUNT(0)
----------
76386
SQL> SELECT * FROM V$PQ_SESSTAT WHERE STATISTIC='Allocation Height';
STATISTIC LAST_QUERY SESSION_TOTAL
-------- ---------- -------------
Allocation Height 20 0
SQL> select /*+ PARALLEL(t 100) */ count(0) from t;
COUNT(0)
----------
76386
SQL> SELECT * FROM V$PQ_SESSTAT WHERE STATISTIC='Allocation Height';
STATISTIC LAST_QUERY SESSION_TOTAL
-------- ---------- -------------
Allocation Height 100 0
SQL> select /*+ PARALLEL(t 2900) */ count(0) from t;
COUNT(0)
----------
76386
SQL> SELECT * FROM V$PQ_SESSTAT WHERE STATISTIC='Allocation Height';
STATISTIC LAST_QUERY SESSION_TOTAL
-------- ---------- -------------
Allocation Height 263 0
在参数设置成AUTO时,在不指定并行度时,ORACLE 使用了4个,说明它认为4个并行就够了。
在我指定并行度为20,100时,ORACLE也没有干预。但我指定为2900时,ORACLE干预了。
这时使用了263个并行度进行查询。
-----------------------------------------------------------------------
4.用一个并行的SQL示例,比较10391事件和 V$PQ_TQSTAT结果的异同。<br>
4.1 设置测试环境
SQL> alter session set sql_trace=true;
Session altered.
SQL> alter session set tracefile_identifier='tang'
2 ;
Session altered.
SQL>
SQL> alter session set events '10391 trace name context forever,level 128';
Session altered.
分别用不同的并行度,做3次查询
SQL> select /*+parallel(t,1) */ count(0) from t;
COUNT(0)
----------
76386
SQL> select /*+parallel(t,8) */ count(0) from t;
COUNT(0)
----------
76386
SQL> select /*+parallel(t,10) */ count(0) from t;
COUNT(0)
----------
76386
SQL>
SQL> alter session set events '10391 trace name context off';
Session altered.
SQL> select name,value from v$diag_info where name='Default Trace File';
D:\APP\ORACLE\diag\rdbms\orcl\orcl\trace\orcl_ora_5368.trc
4.2 跟踪文件日志内容
----------------------
Trace file D:\APP\ORACLE\diag\rdbms\orcl\orcl\trace\orcl_p005_3736_tang.trc
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Windows NT Version V6.0 Service Pack 2
CPU : 24 - type 8664, 12 Physical Cores
Process Affinity : 0x0x0000000000000000
Memory (Avail/Total): Ph:4637M/16370M, Ph+PgF:24304M/32827M
Instance name: orcl
Redo thread mounted by this instance: 1
Oracle process number: 41
Windows thread id: 3736, image: ORACLE.EXE (P005)
*** 2013-11-30 14:05:08.454
*** SESSION ID:(350.17) 2013-11-30 14:05:08.454
*** CLIENT ID:() 2013-11-30 14:05:08.454
*** SERVICE NAME:(orcl) 2013-11-30 14:05:08.454
*** MODULE NAME:(SQL*Plus) 2013-11-30 14:05:08.454
*** ACTION NAME:() 2013-11-30 14:05:08.454
=====================
PARSING IN CURSOR #509548072 len=42 dep=1 uid=84 oct=3 lid=84 tim=7871753515351 hv=1066695699 ad='2af83f3f8' sqlid='dvwddqnzt8z0m'
select /*+parallel(t,6) */ count(0) from t
END OF STMT
PARSE #509548072:c=0,e=52,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=1,plh=3126468333,tim=7871753515349
EXEC #509548072:c=0,e=6166,p=0,cr=234,cu=0,mis=0,r=0,dep=1,og=1,plh=3126468333,tim=7871753525406
STAT #509548072 id=1 cnt=0 pid=0 pos=1 obj=0 op='SORT AGGREGATE (cr=0 pr=0 pw=0 time=0 us)'
STAT #509548072 id=2 cnt=0 pid=1 pos=1 obj=0 op='PX COORDINATOR (cr=0 pr=0 pw=0 time=0 us)'
STAT #509548072 id=3 cnt=0 pid=2 pos=1 obj=0 op='PX SEND QC (RANDOM) :TQ10000 (cr=0 pr=0 pw=0 time=0 us)'
STAT #509548072 id=4 cnt=1 pid=3 pos=1 obj=0 op='SORT AGGREGATE (cr=234 pr=0 pw=0 time=5559 us)'
STAT #509548072 id=5 cnt=12988 pid=4 pos=1 obj=0 op='PX BLOCK ITERATOR (cr=234 pr=0 pw=0 time=41304 us cost=56 size=0 card=76386)'
STAT #509548072 id=6 cnt=12988 pid=5 pos=1 obj=96047 op='TABLE ACCESS FULL T (cr=234 pr=0 pw=0 time=14657 us cost=56 size=0 card=76386)'
CLOSE #509548072:c=0,e=223,dep=1,type=1,tim=7871753525884
*** 2013-11-30 14:05:20.326
*** SESSION ID:(350.19) 2013-11-30 14:05:20.326
*** SERVICE NAME:(orcl) 2013-11-30 14:05:20.326
*** MODULE NAME:(SQL*Plus) 2013-11-30 14:05:20.326
*** ACTION NAME:() 2013-11-30 14:05:20.326
=====================
PARSING IN CURSOR #509548072 len=42 dep=1 uid=84 oct=3 lid=84 tim=7871765390288 hv=2567766901 ad='2ad8b95d8' sqlid='4dgjnm6chu0vp'
select /*+parallel(t,8) */ count(0) from t
END OF STMT
PARSE #509548072:c=0,e=64,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=1,plh=3126468333,tim=7871765390286
EXEC #509548072:c=0,e=9483,p=0,cr=150,cu=0,mis=0,r=0,dep=1,og=1,plh=3126468333,tim=7871765400080
STAT #509548072 id=1 cnt=0 pid=0 pos=1 obj=0 op='SORT AGGREGATE (cr=0 pr=0 pw=0 time=0 us)'
STAT #509548072 id=2 cnt=0 pid=1 pos=1 obj=0 op='PX COORDINATOR (cr=0 pr=0 pw=0 time=0 us)'
STAT #509548072 id=3 cnt=0 pid=2 pos=1 obj=0 op='PX SEND QC (RANDOM) :TQ10000 (cr=0 pr=0 pw=0 time=0 us)'
STAT #509548072 id=4 cnt=1 pid=3 pos=1 obj=0 op='SORT AGGREGATE (cr=150 pr=0 pw=0 time=5034 us)'
STAT #509548072 id=5 cnt=7876 pid=4 pos=1 obj=0 op='PX BLOCK ITERATOR (cr=150 pr=0 pw=0 time=31296 us cost=42 size=0 card=76386)'
STAT #509548072 id=6 cnt=7876 pid=5 pos=1 obj=96047 op='TABLE ACCESS FULL T (cr=150 pr=0 pw=0 time=10265 us cost=42 size=0 card=76386)'
CLOSE #509548072:c=0,e=216,dep=1,type=1,tim=7871765400981
*** 2013-11-30 14:05:24.195
*** SESSION ID:(350.21) 2013-11-30 14:05:24.195
*** SERVICE NAME:(orcl) 2013-11-30 14:05:24.195
*** MODULE NAME:(SQL*Plus) 2013-11-30 14:05:24.195
*** ACTION NAME:() 2013-11-30 14:05:24.195
=====================
PARSING IN CURSOR #509548072 len=43 dep=1 uid=84 oct=3 lid=84 tim=7871769259701 hv=3631007758 ad='2b095bdf8' sqlid='cw04bdmc6tk0f'
select /*+parallel(t,10) */ count(0) from t
END OF STMT
PARSE #509548072:c=0,e=63,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=1,plh=3126468333,tim=7871769259699
EXEC #509548072:c=0,e=9216,p=0,cr=180,cu=0,mis=0,r=0,dep=1,og=1,plh=3126468333,tim=7871769269208
STAT #509548072 id=1 cnt=0 pid=0 pos=1 obj=0 op='SORT AGGREGATE (cr=0 pr=0 pw=0 time=0 us)'
STAT #509548072 id=2 cnt=0 pid=1 pos=1 obj=0 op='PX COORDINATOR (cr=0 pr=0 pw=0 time=0 us)'
STAT #509548072 id=3 cnt=0 pid=2 pos=1 obj=0 op='PX SEND QC (RANDOM) :TQ10000 (cr=0 pr=0 pw=0 time=0 us)'
STAT #509548072 id=4 cnt=1 pid=3 pos=1 obj=0 op='SORT AGGREGATE (cr=180 pr=0 pw=0 time=5288 us)'
STAT #509548072 id=5 cnt=8383 pid=4 pos=1 obj=0 op='PX BLOCK ITERATOR (cr=180 pr=0 pw=0 time=33455 us cost=34 size=0 card=76386)'
STAT #509548072 id=6 cnt=8383 pid=5 pos=1 obj=96047 op='TABLE ACCESS FULL T (cr=180 pr=0 pw=0 time=11851 us cost=34 size=0 card=76386)'
CLOSE #509548072:c=0,e=345,dep=1,type=1,tim=7871769270106
4.2 使用日志分析工具处理后的跟踪文件日志内容
tkprof D:\APP\ORACLE\diag\rdbms\orcl\orcl\trace\orcl_p005_3736_tang.trc
SQL ID: cw04bdmc6tk0f Plan Hash: 3126468333
select /*+parallel(t,10) */ count(0)
from
t
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.01 0 3 0 0
Fetch 2 0.00 0.00 0 0 0 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 0.00 0.01 0 3 0 1
Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 84
Number of plan statistics captured: 1
Rows (1st) Rows (avg) Rows (max) Row Source Operation
---------- ---------- ---------- ---------------------------------------------------
1 1 1 SORT AGGREGATE (cr=3 pr=0 pw=0 time=13765 us)
10 10 10 PX COORDINATOR (cr=3 pr=0 pw=0 time=13057 us)
0 0 0 PX SEND QC (RANDOM) :TQ10000 (cr=0 pr=0 pw=0 time=0 us)
0 0 0 SORT AGGREGATE (cr=0 pr=0 pw=0 time=0 us)
0 0 0 PX BLOCK ITERATOR (cr=0 pr=0 pw=0 time=0 us cost=2 size=0 card=2)
0 0 0 TABLE ACCESS FULL T (cr=0 pr=0 pw=0 time=0 us cost=2 size=0 card=2)
********************************************************************************
查询V$PQ_TQSTAT
SQL> SELECT dfo_number,tq_id,server_type,num_rows,bytes,process FROM V$PQ_tqstat;
DFO_NUMBER TQ_ID SERVER_TYPE NUM_ROWS BYTES PROCESS
---------- ---------- ------------- ---------- ---------- ------------------------------------
1 0 Producer 1 36 P005
1 0 Producer 1 36 P007
1 0 Producer 1 36 P003
1 0 Producer 1 36 P009
1 0 Producer 1 36 P004
1 0 Producer 1 36 P008
1 0 Producer 1 36 P006
1 0 Producer 1 36 P000
1 0 Producer 1 36 P001
1 0 Producer 1 36 P002
1 0 Consumer 10 360 QC
11 rows selected.
SQL>
在性能视图中可以看到,使用了10个并发进程。
-----------------------------------------------------------------------
5.分别演示一个DDL和DML操作的并行执行示例。<br>
5.1 测试使用DDL
SQL> alter session set events '10046 trace name context forever,level 12';
Session altered.
SQL> drop table t5 purge;
Table dropped.
SQL> create table t5 parallel 4 as select * from dba_objects;
Table created.
SQL> create index idx_t5_obj_id on t5(object_id) parallel 4;
Index created.
SQL> alter session set events '10046 trace name context off';
Session altered.
SQL> select name,value from v$diag_info where name='Default Trace File';
NAME
--------------------------------------------------------------------------------
VALUE
--------------------------------------------------------------------------------
Default Trace File
D:\APP\ORACLE\diag\rdbms\orcl\orcl\trace\orcl_ora_5708.trc
SQL>
5.2 从跟踪文件中,生成文件
C:\Users\Administrator>tkprof D:\app\oracle\diag\rdbms\orcl\orcl\trace\orcl_ora_
5708.trc d:\tang.prof
TKPROF: Release 11.2.0.3.0 - Development on 星期六 11月 30 16:07:58 2013
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
C:\Users\Administrator>
文件内容如下:
从跟踪文件中的内容可以看到,使用了4个并行:
create table t5 parallel 4 as select * from dba_objects
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.01 0.02 0 0 0 0
Execute 1 0.42 0.90 0 51 408 76386
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2 0.43 0.92 0 51 408 76386
Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 84
Number of plan statistics captured: 1
Rows (1st) Rows (avg) Rows (max) Row Source Operation
---------- ---------- ---------- ---------------------------------------------------
4 4 4 PX COORDINATOR (cr=14 pr=0 pw=0 time=697718 us)
0 0 0 PX SEND QC (RANDOM) :TQ20001 (cr=0 pr=0 pw=0 time=0 us cost=137 size=15800310 card=76330)
0 0 0 LOAD AS SELECT (cr=0 pr=0 pw=0 time=0 us)
0 0 0 VIEW DBA_OBJECTS (cr=0 pr=0 pw=0 time=0 us cost=137 size=15800310 card=76330)
0 0 0 UNION-ALL (cr=0 pr=0 pw=0 time=0 us)
0 0 0 TABLE ACCESS BY INDEX ROWID SUM$ (cr=2 pr=0 pw=0 time=29 us cost=1 size=11 card=1)
1 1 1 INDEX UNIQUE SCAN I_SUM$_1 (cr=1 pr=0 pw=0 time=15 us cost=0 size=0 card=1)(object id 1002)
0 0 0 TABLE ACCESS BY INDEX ROWID OBJ$ (cr=0 pr=0 pw=0 time=0 us cost=3 size=30 card=1)
0 0 0 INDEX RANGE SCAN I_OBJ1 (cr=0 pr=0 pw=0 time=0 us cost=2 size=0 card=1)(object id 36)
0 0 0 BUFFER SORT (cr=0 pr=0 pw=0 time=0 us)
0 0 0 PX RECEIVE (cr=0 pr=0 pw=0 time=0 us)
0 0 0 PX SEND ROUND-ROBIN :TQ20000 (cr=0 pr=0 pw=0 time=0 us)
76379 76379 76379 FILTER (cr=9 pr=0 pw=0 time=461741 us)
76379 76379 76379 PX COORDINATOR (cr=9 pr=0 pw=0 time=289402 us)
0 0 0 PX SEND QC (RANDOM) :TQ10002 (cr=0 pr=0 pw=0 time=0 us cost=78 size=9006114 card=76323)
0 0 0 HASH JOIN (cr=0 pr=0 pw=0 time=0 us cost=78 size=9006114 card=76323)
0 0 0 PX RECEIVE (cr=0 pr=0 pw=0 time=0 us cost=2 size=1768 card=104)
0 0 0 PX SEND BROADCAST :TQ10001 (cr=0 pr=0 pw=0 time=0 us cost=2 size=1768 card=104)
0 0 0 PX BLOCK ITERATOR (cr=0 pr=0 pw=0 time=0 us cost=2 size=1768 card=104)
0 0 0 TABLE ACCESS FULL USER$ (cr=0 pr=0 pw=0 time=0 us cost=2 size=1768 card=104)
0 0 0 HASH JOIN (cr=0 pr=0 pw=0 time=0 us cost=75 size=7708623 card=76323)
0 0 0 BUFFER SORT (cr=0 pr=0 pw=0 time=0 us)
0 0 0 PX RECEIVE (cr=0 pr=0 pw=0 time=0 us cost=1 size=2288 card=104)
0 0 0 PX SEND BROADCAST :TQ10000 (cr=0 pr=0 pw=0 time=0 us cost=1 size=2288 card=104)
104 104 104 INDEX FULL SCAN I_USER2 (cr=1 pr=0 pw=0 time=125 us cost=1 size=2288 card=104)(object id 47)
0 0 0 PX BLOCK ITERATOR (cr=0 pr=0 pw=0 time=0 us cost=73 size=6029517 card=76323)
0 0 0 TABLE ACCESS FULL OBJ$ (cr=0 pr=0 pw=0 time=0 us cost=73 size=6029517 card=76323)
0 0 0 NESTED LOOPS (cr=0 pr=0 pw=0 time=0 us cost=1 size=29 card=1)
0 0 0 INDEX SKIP SCAN I_USER2 (cr=0 pr=0 pw=0 time=0 us cost=1 size=20 card=1)(object id 47)
0 0 0 INDEX RANGE SCAN I_OBJ4 (cr=0 pr=0 pw=0 time=0 us cost=0 size=9 card=1)(object id 39)
0 0 0 NESTED LOOPS (cr=0 pr=0 pw=0 time=0 us cost=4 size=273 card=7)
0 0 0 PX BLOCK ITERATOR (cr=0 pr=0 pw=0 time=0 us)
0 0 0 TABLE ACCESS FULL LINK$ (cr=0 pr=0 pw=0 time=0 us cost=2 size=154 card=7)
0 0 0 TABLE ACCESS CLUSTER USER$ (cr=0 pr=0 pw=0 time=0 us cost=0 size=17 card=1)
0 0 0 INDEX UNIQUE SCAN I_USER# (cr=0 pr=0 pw=0 time=0 us cost=0 size=0 card=1)(object id 11)
......
Rows (1st) Rows (avg) Rows (max) Row Source Operation
---------- ---------- ---------- ---------------------------------------------------
4 4 4 PX COORDINATOR (cr=5 pr=0 pw=0 time=73817 us)
0 0 0 PX SEND QC (ORDER) :TQ10001 (cr=0 pr=0 pw=0 time=0 us)
0 0 0 INDEX BUILD NON UNIQUE IDX_T5_OBJ_ID (cr=0 pr=0 pw=0 time=0 us)(object id 0)
0 0 0 SORT CREATE INDEX (cr=0 pr=0 pw=0 time=0 us)
0 0 0 PX RECEIVE (cr=0 pr=0 pw=0 time=0 us cost=88 size=1231737 card=94749)
0 0 0 PX SEND RANGE :TQ10000 (cr=0 pr=0 pw=0 time=0 us cost=88 size=1231737 card=94749)
0 0 0 PX BLOCK ITERATOR (cr=0 pr=0 pw=0 time=0 us cost=88 size=1231737 card=94749)
0 0 0 TABLE ACCESS FULL T5 (cr=0 pr=0 pw=0 time=0 us cost=88 size=1231737 card=94749)
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
PX Deq: Join ACK 8 0.00 0.00
PX Deq: Parse Reply 8 0.00 0.00
PX Deq: Execute Reply 82 0.01 0.06
PX Deq: Table Q qref 4 0.00 0.00
db file scattered read 6 0.00 0.00
reliable message 7 0.00 0.00
enq: CR - block range reuse ckpt 3 0.00 0.00
log file sync 1 0.00 0.00
PX Deq: Signal ACK EXT 8 0.00 0.00
PX Deq: Slave Session Stats 8 0.00 0.00
enq: PS - contention 1 0.00 0.00
SQL*Net message to client 1 0.00 0.00
SQL*Net message from client 1 20.17 20.17
********************************************************************************
create index idx_t5_obj_id on t5(object_id) parallel 4
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.01 0.00 0 1 0 0
Execute 1 0.01 0.11 23 9 965 0
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2 0.03 0.11 23 10 965 0
Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 84
Number of plan statistics captured: 1
Rows (1st) Rows (avg) Rows (max) Row Source Operation
---------- ---------- ---------- ---------------------------------------------------
4 4 4 PX COORDINATOR (cr=5 pr=0 pw=0 time=73817 us)
0 0 0 PX SEND QC (ORDER) :TQ10001 (cr=0 pr=0 pw=0 time=0 us)
0 0 0 INDEX BUILD NON UNIQUE IDX_T5_OBJ_ID (cr=0 pr=0 pw=0 time=0 us)(object id 0)
0 0 0 SORT CREATE INDEX (cr=0 pr=0 pw=0 time=0 us)
0 0 0 PX RECEIVE (cr=0 pr=0 pw=0 time=0 us cost=88 size=1231737 card=94749)
0 0 0 PX SEND RANGE :TQ10000 (cr=0 pr=0 pw=0 time=0 us cost=88 size=1231737 card=94749)
0 0 0 PX BLOCK ITERATOR (cr=0 pr=0 pw=0 time=0 us cost=88 size=1231737 card=94749)
0 0 0 TABLE ACCESS FULL T5 (cr=0 pr=0 pw=0 time=0 us cost=88 size=1231737 card=94749)
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
PX Deq: Join ACK 8 0.00 0.00
PX Deq: Parse Reply 8 0.00 0.00
PX Deq: Execute Reply 82 0.01 0.06
PX Deq: Table Q qref 4 0.00 0.00
db file scattered read 6 0.00 0.00
reliable message 7 0.00 0.00
enq: CR - block range reuse ckpt 3 0.00 0.00
log file sync 1 0.00 0.00
PX Deq: Signal ACK EXT 8 0.00 0.00
PX Deq: Slave Session Stats 8 0.00 0.00
enq: PS - contention 1 0.00 0.00
SQL*Net message to client 1 0.00 0.00
SQL*Net message from client 1 20.17 20.17
********************************************************************************
做DML 操作时使用并行,进行一个并行插入的测试:
SQL> explain plan for insert /*+ parallel(t5 10) */ into t5 select /*+ parallel(t 4) */ * from t;
Explained.
SQL> select * from table(dbms_xplan.display);
SQL> set linesize 100;
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
Plan hash value: 3050126167
----------------------------------------------------------------------------------------------------
--------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | TQ |IN-OUT
| PQ Distrib |
----------------------------------------------------------------------------------------------------
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
| 0 | INSERT STATEMENT | | 76386 | 7310K| 85 (2)| 00:00:02 | |
| |
| 1 | LOAD TABLE CONVENTIONAL | T5 | | | | | |
| |
| 2 | PX COORDINATOR | | | | | | |
| |
| 3 | PX SEND QC (RANDOM) | :TQ10000 | 76386 | 7310K| 85 (2)| 00:00:02 | Q1,00 | P->S
| QC (RAND) |
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
| 4 | PX BLOCK ITERATOR | | 76386 | 7310K| 85 (2)| 00:00:02 | Q1,00 | PCWC
| |
| 5 | TABLE ACCESS FULL | T | 76386 | 7310K| 85 (2)| 00:00:02 | Q1,00 | PCWP
| |
----------------------------------------------------------------------------------------------------
12 rows selected.
SQL>
从上面的执行计划中看到,从表T中查询,及插入T5表,都用到了 并行处理。
2.就自己本机的硬件情况,通过SQL示例,来找到最优的并行度。<br>
3.针对PARALLEL_DEGREE_POLICY的三个值,分别演示它们的效果。<br>
4.用一个并行的SQL示例,比较10391事件和 V$PQ_TQSTAT结果的异同。<br>
5.分别演示一个DDL和DML操作的并行执行示例。<br>
===============================================================
1.给出一个2表关联的并行查询执行计划,并画出并行数据流图。<br>
答:
SQL> set linesize 8000;
SQL> set autotrace traceonly;
SQL> select /*+ PARALLEL(d 2) PARALLEL(e 2) */ d.dname,e.* from scott.dept d inner join scott.emp e on d.deptno=e.deptno;
14 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 197865614
-----------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | TQ |IN-OUT| PQ Distrib |
-----------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 14 | 714 | 5 (20)| 00:00:01 | | | |
| 1 | PX COORDINATOR | | | | | | | | |
| 2 | PX SEND QC (RANDOM) | :TQ10001 | 14 | 714 | 5 (20)| 00:00:01 | Q1,01 | P->S | QC (RAND) |
|* 3 | HASH JOIN | | 14 | 714 | 5 (20)| 00:00:01 | Q1,01 | PCWP | |
| 4 | PX RECEIVE | | 4 | 52 | 2 (0)| 00:00:01 | Q1,01 | PCWP | |
| 5 | PX SEND BROADCAST | :TQ10000 | 4 | 52 | 2 (0)| 00:00:01 | Q1,00 | P->P | BROADCAST |
| 6 | PX BLOCK ITERATOR | | 4 | 52 | 2 (0)| 00:00:01 | Q1,00 | PCWC | |
| 7 | TABLE ACCESS FULL| DEPT | 4 | 52 | 2 (0)| 00:00:01 | Q1,00 | PCWP | |
| 8 | PX BLOCK ITERATOR | | 14 | 532 | 2 (0)| 00:00:01 | Q1,01 | PCWC | |
| 9 | TABLE ACCESS FULL | EMP | 14 | 532 | 2 (0)| 00:00:01 | Q1,01 | PCWP | |
-----------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("D"."DEPTNO"="E"."DEPTNO")
Statistics
----------------------------------------------------------
176 recursive calls
2 db block gets
116 consistent gets
10 physical reads
0 redo size
1758 bytes sent via SQL*Net to client
524 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
16 sorts (memory)
0 sorts (disk)
14 rows processed
(并行数据流示意图请看附件)
-----------------------------------------------------------------------
2.就自己本机的硬件情况,通过SQL示例,来找到最优的并行度。<br>
使用了公司内部的测试服务器,性能上用来做一般的测试服务器应该还算可以:
配置:
CPU: INTER XEON 3.33 GHZ,2处理器,
MEM:16G
OS:windows server ENTERPRISE 64位;
进行以下测试时没有其它业务在运行。
2.1 设置使用5个并行进行查询统计,看到COST=608
SQL> select /*+ PARALLEL(5) */ c_id,c_name from mcard.cards ;
155048 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 542627410
--------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | TQ |IN-OUT| PQ Distrib |
--------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 154K| 2114K| 608 (0)| 00:00:02 | | | |
| 1 | PX COORDINATOR | | | | | | | | |
| 2 | PX SEND QC (RANDOM)| :TQ10000 | 154K| 2114K| 608 (0)| 00:00:02 | Q1,00 | P->S | QC (RAND) |
| 3 | PX BLOCK ITERATOR | | 154K| 2114K| 608 (0)| 00:00:02 | Q1,00 | PCWC | |
| 4 | TABLE ACCESS FULL| cards | 154K| 2114K| 608 (0)| 00:00:02 | Q1,00 | PCWP | |
--------------------------------------------------------------------------------------------------------------
2.2 设置使用10个并行进行查询统计,看到COST=304,减少了一半
SQL>
SQL> select /*+ PARALLEL(10) */ c_id,c_name from mcard.cards ;
155048 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 542627410
--------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | TQ |IN-OUT| PQ Distrib |
--------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 154K| 2114K| 304 (0)| 00:00:01 | | | |
| 1 | PX COORDINATOR | | | | | | | | |
| 2 | PX SEND QC (RANDOM)| :TQ10000 | 154K| 2114K| 304 (0)| 00:00:01 | Q1,00 | P->S | QC (RAND) |
| 3 | PX BLOCK ITERATOR | | 154K| 2114K| 304 (0)| 00:00:01 | Q1,00 | PCWC | |
| 4 | TABLE ACCESS FULL| cards | 154K| 2114K| 304 (0)| 00:00:01 | Q1,00 | PCWP | |
--------------------------------------------------------------------------------------------------------------
Note
-----
- Degree of Parallelism is 10 because of hint
2.3 设置使用30个并行进行查询统计,看到COST=101,减少到1/3
SQL> select /*+ PARALLEL(30) */ c_id,c_name from mcard.cards ;
155048 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 542627410
--------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | TQ |IN-OUT| PQ Distrib |
--------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 154K| 2114K| 101 (0)| 00:00:01 | | | |
| 1 | PX COORDINATOR | | | | | | | | |
| 2 | PX SEND QC (RANDOM)| :TQ10000 | 154K| 2114K| 101 (0)| 00:00:01 | Q1,00 | P->S | QC (RAND) |
| 3 | PX BLOCK ITERATOR | | 154K| 2114K| 101 (0)| 00:00:01 | Q1,00 | PCWC | |
| 4 | TABLE ACCESS FULL| cards | 154K| 2114K| 101 (0)| 00:00:01 | Q1,00 | PCWP | |
--------------------------------------------------------------------------------------------------------------
Note
-----
- Degree of Parallelism is 30 because of hint
2.4 设置使用30个并行进行查询统计,看到COST=76,减少到并行度为10 时的1/4
SQL> select /*+ PARALLEL(40) */ c_id,c_name from mcard.cards ;
155048 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 542627410
--------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | TQ |IN-OUT| PQ Distrib |
--------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 154K| 2114K| 76 (0)| 00:00:01 | | | |
| 1 | PX COORDINATOR | | | | | | | | |
| 2 | PX SEND QC (RANDOM)| :TQ10000 | 154K| 2114K| 76 (0)| 00:00:01 | Q1,00 | P->S | QC (RAND) |
| 3 | PX BLOCK ITERATOR | | 154K| 2114K| 76 (0)| 00:00:01 | Q1,00 | PCWC | |
| 4 | TABLE ACCESS FULL| cards | 154K| 2114K| 76 (0)| 00:00:01 | Q1,00 | PCWP | |
--------------------------------------------------------------------------------------------------------------
Note
-----
- Degree of Parallelism is 40 because of hint
我经过测试,一直测试到120个并行度时,都还是正常的。看来在此服务器上,并不能测试出
此数据查询的合适并行度是多少。因为服务器为空闲。并且性能也不错。
直到测试出错误:ORA-00020:maximum number of processes (150) exceeded 错误
我的参数 processes=150,后来修改成300,再测试下面内容
下面使用了更大的表进行测试:直到测试到25-30个并行度时,COST才没有按比例下降。
所以我认为多少个最优的并行度,并不适合不同的表。并且也不能做为优化查询的一个指标。
因为在正式的产品服务器中,性能是在变化的。只要能满足当前业务需求就好。
SQL> set autotrance onlytrace
SQL> set autotrace traceonly;
SQL> set linesize 800;
SQL> select /*+ PARALLEL(1) */ count(0) from persion p;
Execution Plan
----------------------------------------------------------
Plan hash value: 1994127684
----------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
----------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 75474 (1)| 00:15:06 |
| 1 | SORT AGGREGATE | | 1 | | |
| 2 | TABLE ACCESS FULL| PERSION | 20M| 75474 (1)| 00:15:06 |
----------------------------------------------------------------------
Statistics
----------------------------------------------------------
1 recursive calls
1 db block gets
278268 consistent gets
277516 physical reads
0 redo size
529 bytes sent via SQL*Net to client
524 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
SQL> select /*+ PARALLEL(2) */ count(0) from persion p;
Execution Plan
----------------------------------------------------------
Plan hash value: 1284080732
--------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time | TQ |IN-OUT| PQ Distrib |
--------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 41876 (1)| 00:01:24 | | | |
| 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 | | 20M| 41876 (1)| 00:01:24 | Q1,00 | PCWC | |
| 6 | TABLE ACCESS FULL| PERSION | 20M| 41876 (1)| 00:01:24 | Q1,00 | PCWP | |
--------------------------------------------------------------------------------------------------------
Note
-----
- Degree of Parallelism is 2 because of hint
Statistics
----------------------------------------------------------
7 recursive calls
220 db block gets
442739 consistent gets
277516 physical reads
0 redo size
529 bytes sent via SQL*Net to client
524 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
SQL> select /*+ PARALLEL(3) */ count(0) from persion p;
Execution Plan
----------------------------------------------------------
Plan hash value: 1284080732
--------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time | TQ |IN-OUT| PQ Distrib |
--------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 27918 (1)| 00:00:56 | | | |
| 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 | | 20M| 27918 (1)| 00:00:56 | Q1,00 | PCWC | |
| 6 | TABLE ACCESS FULL| PERSION | 20M| 27918 (1)| 00:00:56 | Q1,00 | PCWP | |
--------------------------------------------------------------------------------------------------------
Note
-----
- Degree of Parallelism is 3 because of hint
Statistics
----------------------------------------------------------
12 recursive calls
237 db block gets
455506 consistent gets
277516 physical reads
0 redo size
529 bytes sent via SQL*Net to client
524 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
SQL> select /*+ PARALLEL(4) */ count(0) from persion p;
Execution Plan
----------------------------------------------------------
Plan hash value: 1284080732
--------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time | TQ |IN-OUT| PQ Distrib |
--------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 20938 (1)| 00:00:42 | | | |
| 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 | | 20M| 20938 (1)| 00:00:42 | Q1,00 | PCWC | |
| 6 | TABLE ACCESS FULL| PERSION | 20M| 20938 (1)| 00:00:42 | Q1,00 | PCWP | |
--------------------------------------------------------------------------------------------------------
Note
-----
- Degree of Parallelism is 4 because of hint
Statistics
----------------------------------------------------------
15 recursive calls
238 db block gets
456257 consistent gets
277516 physical reads
0 redo size
529 bytes sent via SQL*Net to client
524 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
SQL> select /*+ PARALLEL(5) */ count(0) from persion p;
Execution Plan
----------------------------------------------------------
Plan hash value: 1284080732
--------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time | TQ |IN-OUT| PQ Distrib |
--------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 16751 (1)| 00:00:34 | | | |
| 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 | | 20M| 16751 (1)| 00:00:34 | Q1,00 | PCWC | |
| 6 | TABLE ACCESS FULL| PERSION | 20M| 16751 (1)| 00:00:34 | Q1,00 | PCWP | |
--------------------------------------------------------------------------------------------------------
Note
-----
- Degree of Parallelism is 5 because of hint
Statistics
----------------------------------------------------------
18 recursive calls
238 db block gets
456257 consistent gets
277516 physical reads
0 redo size
529 bytes sent via SQL*Net to client
524 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
SQL> select /*+ PARALLEL(6) */ count(0) from persion p;
Execution Plan
----------------------------------------------------------
Plan hash value: 1284080732
--------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time | TQ |IN-OUT| PQ Distrib |
--------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 13959 (1)| 00:00:28 | | | |
| 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 | | 20M| 13959 (1)| 00:00:28 | Q1,00 | PCWC | |
| 6 | TABLE ACCESS FULL| PERSION | 20M| 13959 (1)| 00:00:28 | Q1,00 | PCWP | |
--------------------------------------------------------------------------------------------------------
Note
-----
- Degree of Parallelism is 6 because of hint
Statistics
----------------------------------------------------------
21 recursive calls
255 db block gets
469024 consistent gets
277516 physical reads
0 redo size
529 bytes sent via SQL*Net to client
524 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
SQL> select /*+ PARALLEL(7) */ count(0) from persion p;
Execution Plan
----------------------------------------------------------
Plan hash value: 1284080732
--------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time | TQ |IN-OUT| PQ Distrib |
--------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 11965 (1)| 00:00:24 | | | |
| 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 | | 20M| 11965 (1)| 00:00:24 | Q1,00 | PCWC | |
| 6 | TABLE ACCESS FULL| PERSION | 20M| 11965 (1)| 00:00:24 | Q1,00 | PCWP | |
--------------------------------------------------------------------------------------------------------
Note
-----
- Degree of Parallelism is 7 because of hint
SQL> select /*+ PARALLEL(20) */ count(0) from persion p;
Execution Plan
----------------------------------------------------------
Plan hash value: 1284080732
--------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time | TQ |IN-OUT| PQ Distrib |
--------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 4188 (1)| 00:00:09 | | | |
| 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 | | 20M| 4188 (1)| 00:00:09 | Q1,00 | PCWC | |
| 6 | TABLE ACCESS FULL| PERSION | 20M| 4188 (1)| 00:00:09 | Q1,00 | PCWP | |
--------------------------------------------------------------------------------------------------------
Note
-----
- Degree of Parallelism is 20 because of hint
SQL> select /*+ PARALLEL(30) */ count(0) from persion p;
Execution Plan
----------------------------------------------------------
Plan hash value: 1284080732
--------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time | TQ |IN-OUT| PQ Distrib |
--------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 2792 (1)| 00:00:06 | | | |
| 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 | | 20M| 2792 (1)| 00:00:06 | Q1,00 | PCWC | |
| 6 | TABLE ACCESS FULL| PERSION | 20M| 2792 (1)| 00:00:06 | Q1,00 | PCWP | |
--------------------------------------------------------------------------------------------------------
Note
-----
- Degree of Parallelism is 30 because of hint
SQL> select /*+ PARALLEL(25) */ count(0) from persion p;
Execution Plan
----------------------------------------------------------
Plan hash value: 1284080732
--------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time | TQ |IN-OUT| PQ Distrib |
--------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 3350 (1)| 00:00:07 | | | |
| 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 | | 20M| 3350 (1)| 00:00:07 | Q1,00 | PCWC | |
| 6 | TABLE ACCESS FULL| PERSION | 20M| 3350 (1)| 00:00:07 | Q1,00 | PCWP | |
--------------------------------------------------------------------------------------------------------
Note
-----
- Degree of Parallelism is 25 because of hint
Statistics
----------------------------------------------------------
76 recursive calls
501 db block gets
653770 consistent gets
277516 physical reads
0 redo size
529 bytes sent via SQL*Net to client
524 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
SQL>
-----------------------------------------------------------------------
3.针对PARALLEL_DEGREE_POLICY的三个值,分别演示它们的效果。<br>
答: PARALLEL_DEGREE_POLICY 三个值为:
MANUAL: 手动方式,也是默认方式,ORACLE不会擅自去调整并行度
LIMITED: 限制方式 如果为限制方式,即使你指定并行度大于此
auto: 自动方式
3.1.1 查询当前参数值
SQL> set linesize 800;
SQL> show parameter parallel_degree;
NAME TYPE VALUE
---------------------- -------------- ---------------
parallel_degree_limit string CPU
parallel_degree_policy string MANUAL
SQL>
3.1.2 建立测试表,使用HINT 指定并行并查询,
SQL> drop table t purge;
Table dropped.
SQL> create table t as select * from dba_objects;
Table created.
看到这时ORACLE使用了我指定的并行度进行查询数据;
3.1.3 指定表默认并行度,再进行查询
SQL> alter table t parallel(degree 4);
Table altered.
select table_name,degree from user_tables t where t.TABLE_NAME='T'
TABLE_NAME DEGREE
--------------------
1 T 4
SQL> set autotrace off;
SQL> SELECT * FROM V$PQ_SESSTAT WHERE STATISTIC='Allocation Height';
STATISTIC LAST_QUERY SESSION_TOTAL
------------------- ---------- -------------
Allocation Height 0 0
SQL> select /*+ PARALLEL(t 4) */ count(0) from t;
COUNT(0)
----------
76386
SQL> SELECT * FROM V$PQ_SESSTAT WHERE STATISTIC='Allocation Height';
STATISTIC LAST_QUERY SESSION_TOTAL
------------- ---------- -------------
Allocation Height 4 0
SQL>
SQL> SELECT * FROM V$PQ_SESSTAT WHERE STATISTIC='Allocation Height';
STATISTIC LAST_QUERY SESSION_TOTAL
--------------- ---------- -------------
Allocation Height 4 0
SQL> select /*+ PARALLEL(t 6) */ count(0) from t;
COUNT(0)
----------
76386
SQL> SELECT * FROM V$PQ_SESSTAT WHERE STATISTIC='Allocation Height';
STATISTIC LAST_QUERY SESSION_TOTAL
------------ ---------- -------------
Allocation Height 6 0
从上面的3次查询可以看出,不管是我指定并行度或是使用默认的并行度,真正在查询时,
ORACLE都是按我指定的或是按默认的并行度进行查询,并没有干预。
3.2.1 把参数 PARALLEL_DEGREE_POLICY 设置成LIMITED
SQL> ALTER SESSION SET PARALLEL_DEGREE_POLICY=LIMITED;
SQL> select count(0) from t;
COUNT(0)
----------
76386
SQL> SELECT * FROM V$PQ_SESSTAT WHERE STATISTIC='Allocation Height';
STATISTIC LAST_QUERY SESSION_TOTAL
----------- ---------- -------------
Allocation Height 4 0
SQL> select /*+ PARALLEL(t 60) */ count(0) from t;
COUNT(0)
----------
76386
SQL> SELECT * FROM V$PQ_SESSTAT WHERE STATISTIC='Allocation Height';
STATISTIC LAST_QUERY SESSION_TOTAL
----------- ---------- -------------
Allocation Height 60 0
SQL> select /*+ PARALLEL(t 100) */ count(0) from t;
COUNT(0)
----------
76386
SQL> SELECT * FROM V$PQ_SESSTAT WHERE STATISTIC='Allocation Height';
STATISTIC LAST_QUERY SESSION_TOTAL
----------- ---------- -------------
Allocation Height 100 0
SQL> select /*+ PARALLEL(t 200) */ count(0) from t;
COUNT(0)
----------
76386
SQL> SELECT * FROM V$PQ_SESSTAT WHERE STATISTIC='Allocation Height';
STATISTIC LAST_QUERY SESSION_TOTAL
----------- ---------- -------------
Allocation Height 200 0
SQL> select /*+ PARALLEL(t 2900) */ count(0) from t;
COUNT(0)
----------
76386
STATISTIC LAST_QUERY SESSION_TOTAL
----------- ---------- -------------
Allocation Height 48 0
SQL>
因为服务器性能不错的原因,上面我的测试,开始一直加大并行度,ORACLE都没有干预,但我加大到 2900时(这时大于参数PROCESSES)
ORACLE开始干预了,系统认为 48 是最好的。
3.3.1 把参数 PARALLEL_DEGREE_POLICY 设置成AUTO
ALTER SESSION SET PARALLEL_DEGREE_POLICY=AUTO;
SQL> ALTER SESSION SET PARALLEL_DEGREE_POLICY=AUTO;
Session altered.
SQL> select count(0) from t;
COUNT(0)
----------
76386
SQL> SELECT * FROM V$PQ_SESSTAT WHERE STATISTIC='Allocation Height';
STATISTIC LAST_QUERY SESSION_TOTAL
-------- ---------- -------------
Allocation Height 4 0
SQL> select /*+ PARALLEL(t 20) */ count(0) from t;
COUNT(0)
----------
76386
SQL> SELECT * FROM V$PQ_SESSTAT WHERE STATISTIC='Allocation Height';
STATISTIC LAST_QUERY SESSION_TOTAL
-------- ---------- -------------
Allocation Height 20 0
SQL> select /*+ PARALLEL(t 100) */ count(0) from t;
COUNT(0)
----------
76386
SQL> SELECT * FROM V$PQ_SESSTAT WHERE STATISTIC='Allocation Height';
STATISTIC LAST_QUERY SESSION_TOTAL
-------- ---------- -------------
Allocation Height 100 0
SQL> select /*+ PARALLEL(t 2900) */ count(0) from t;
COUNT(0)
----------
76386
SQL> SELECT * FROM V$PQ_SESSTAT WHERE STATISTIC='Allocation Height';
STATISTIC LAST_QUERY SESSION_TOTAL
-------- ---------- -------------
Allocation Height 263 0
在参数设置成AUTO时,在不指定并行度时,ORACLE 使用了4个,说明它认为4个并行就够了。
在我指定并行度为20,100时,ORACLE也没有干预。但我指定为2900时,ORACLE干预了。
这时使用了263个并行度进行查询。
-----------------------------------------------------------------------
4.用一个并行的SQL示例,比较10391事件和 V$PQ_TQSTAT结果的异同。<br>
4.1 设置测试环境
SQL> alter session set sql_trace=true;
Session altered.
SQL> alter session set tracefile_identifier='tang'
2 ;
Session altered.
SQL>
SQL> alter session set events '10391 trace name context forever,level 128';
Session altered.
分别用不同的并行度,做3次查询
SQL> select /*+parallel(t,1) */ count(0) from t;
COUNT(0)
----------
76386
SQL> select /*+parallel(t,8) */ count(0) from t;
COUNT(0)
----------
76386
SQL> select /*+parallel(t,10) */ count(0) from t;
COUNT(0)
----------
76386
SQL>
SQL> alter session set events '10391 trace name context off';
Session altered.
SQL> select name,value from v$diag_info where name='Default Trace File';
D:\APP\ORACLE\diag\rdbms\orcl\orcl\trace\orcl_ora_5368.trc
4.2 跟踪文件日志内容
----------------------
Trace file D:\APP\ORACLE\diag\rdbms\orcl\orcl\trace\orcl_p005_3736_tang.trc
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Windows NT Version V6.0 Service Pack 2
CPU : 24 - type 8664, 12 Physical Cores
Process Affinity : 0x0x0000000000000000
Memory (Avail/Total): Ph:4637M/16370M, Ph+PgF:24304M/32827M
Instance name: orcl
Redo thread mounted by this instance: 1
Oracle process number: 41
Windows thread id: 3736, image: ORACLE.EXE (P005)
*** 2013-11-30 14:05:08.454
*** SESSION ID:(350.17) 2013-11-30 14:05:08.454
*** CLIENT ID:() 2013-11-30 14:05:08.454
*** SERVICE NAME:(orcl) 2013-11-30 14:05:08.454
*** MODULE NAME:(SQL*Plus) 2013-11-30 14:05:08.454
*** ACTION NAME:() 2013-11-30 14:05:08.454
=====================
PARSING IN CURSOR #509548072 len=42 dep=1 uid=84 oct=3 lid=84 tim=7871753515351 hv=1066695699 ad='2af83f3f8' sqlid='dvwddqnzt8z0m'
select /*+parallel(t,6) */ count(0) from t
END OF STMT
PARSE #509548072:c=0,e=52,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=1,plh=3126468333,tim=7871753515349
EXEC #509548072:c=0,e=6166,p=0,cr=234,cu=0,mis=0,r=0,dep=1,og=1,plh=3126468333,tim=7871753525406
STAT #509548072 id=1 cnt=0 pid=0 pos=1 obj=0 op='SORT AGGREGATE (cr=0 pr=0 pw=0 time=0 us)'
STAT #509548072 id=2 cnt=0 pid=1 pos=1 obj=0 op='PX COORDINATOR (cr=0 pr=0 pw=0 time=0 us)'
STAT #509548072 id=3 cnt=0 pid=2 pos=1 obj=0 op='PX SEND QC (RANDOM) :TQ10000 (cr=0 pr=0 pw=0 time=0 us)'
STAT #509548072 id=4 cnt=1 pid=3 pos=1 obj=0 op='SORT AGGREGATE (cr=234 pr=0 pw=0 time=5559 us)'
STAT #509548072 id=5 cnt=12988 pid=4 pos=1 obj=0 op='PX BLOCK ITERATOR (cr=234 pr=0 pw=0 time=41304 us cost=56 size=0 card=76386)'
STAT #509548072 id=6 cnt=12988 pid=5 pos=1 obj=96047 op='TABLE ACCESS FULL T (cr=234 pr=0 pw=0 time=14657 us cost=56 size=0 card=76386)'
CLOSE #509548072:c=0,e=223,dep=1,type=1,tim=7871753525884
*** 2013-11-30 14:05:20.326
*** SESSION ID:(350.19) 2013-11-30 14:05:20.326
*** SERVICE NAME:(orcl) 2013-11-30 14:05:20.326
*** MODULE NAME:(SQL*Plus) 2013-11-30 14:05:20.326
*** ACTION NAME:() 2013-11-30 14:05:20.326
=====================
PARSING IN CURSOR #509548072 len=42 dep=1 uid=84 oct=3 lid=84 tim=7871765390288 hv=2567766901 ad='2ad8b95d8' sqlid='4dgjnm6chu0vp'
select /*+parallel(t,8) */ count(0) from t
END OF STMT
PARSE #509548072:c=0,e=64,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=1,plh=3126468333,tim=7871765390286
EXEC #509548072:c=0,e=9483,p=0,cr=150,cu=0,mis=0,r=0,dep=1,og=1,plh=3126468333,tim=7871765400080
STAT #509548072 id=1 cnt=0 pid=0 pos=1 obj=0 op='SORT AGGREGATE (cr=0 pr=0 pw=0 time=0 us)'
STAT #509548072 id=2 cnt=0 pid=1 pos=1 obj=0 op='PX COORDINATOR (cr=0 pr=0 pw=0 time=0 us)'
STAT #509548072 id=3 cnt=0 pid=2 pos=1 obj=0 op='PX SEND QC (RANDOM) :TQ10000 (cr=0 pr=0 pw=0 time=0 us)'
STAT #509548072 id=4 cnt=1 pid=3 pos=1 obj=0 op='SORT AGGREGATE (cr=150 pr=0 pw=0 time=5034 us)'
STAT #509548072 id=5 cnt=7876 pid=4 pos=1 obj=0 op='PX BLOCK ITERATOR (cr=150 pr=0 pw=0 time=31296 us cost=42 size=0 card=76386)'
STAT #509548072 id=6 cnt=7876 pid=5 pos=1 obj=96047 op='TABLE ACCESS FULL T (cr=150 pr=0 pw=0 time=10265 us cost=42 size=0 card=76386)'
CLOSE #509548072:c=0,e=216,dep=1,type=1,tim=7871765400981
*** 2013-11-30 14:05:24.195
*** SESSION ID:(350.21) 2013-11-30 14:05:24.195
*** SERVICE NAME:(orcl) 2013-11-30 14:05:24.195
*** MODULE NAME:(SQL*Plus) 2013-11-30 14:05:24.195
*** ACTION NAME:() 2013-11-30 14:05:24.195
=====================
PARSING IN CURSOR #509548072 len=43 dep=1 uid=84 oct=3 lid=84 tim=7871769259701 hv=3631007758 ad='2b095bdf8' sqlid='cw04bdmc6tk0f'
select /*+parallel(t,10) */ count(0) from t
END OF STMT
PARSE #509548072:c=0,e=63,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=1,plh=3126468333,tim=7871769259699
EXEC #509548072:c=0,e=9216,p=0,cr=180,cu=0,mis=0,r=0,dep=1,og=1,plh=3126468333,tim=7871769269208
STAT #509548072 id=1 cnt=0 pid=0 pos=1 obj=0 op='SORT AGGREGATE (cr=0 pr=0 pw=0 time=0 us)'
STAT #509548072 id=2 cnt=0 pid=1 pos=1 obj=0 op='PX COORDINATOR (cr=0 pr=0 pw=0 time=0 us)'
STAT #509548072 id=3 cnt=0 pid=2 pos=1 obj=0 op='PX SEND QC (RANDOM) :TQ10000 (cr=0 pr=0 pw=0 time=0 us)'
STAT #509548072 id=4 cnt=1 pid=3 pos=1 obj=0 op='SORT AGGREGATE (cr=180 pr=0 pw=0 time=5288 us)'
STAT #509548072 id=5 cnt=8383 pid=4 pos=1 obj=0 op='PX BLOCK ITERATOR (cr=180 pr=0 pw=0 time=33455 us cost=34 size=0 card=76386)'
STAT #509548072 id=6 cnt=8383 pid=5 pos=1 obj=96047 op='TABLE ACCESS FULL T (cr=180 pr=0 pw=0 time=11851 us cost=34 size=0 card=76386)'
CLOSE #509548072:c=0,e=345,dep=1,type=1,tim=7871769270106
4.2 使用日志分析工具处理后的跟踪文件日志内容
tkprof D:\APP\ORACLE\diag\rdbms\orcl\orcl\trace\orcl_p005_3736_tang.trc
SQL ID: cw04bdmc6tk0f Plan Hash: 3126468333
select /*+parallel(t,10) */ count(0)
from
t
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.01 0 3 0 0
Fetch 2 0.00 0.00 0 0 0 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 0.00 0.01 0 3 0 1
Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 84
Number of plan statistics captured: 1
Rows (1st) Rows (avg) Rows (max) Row Source Operation
---------- ---------- ---------- ---------------------------------------------------
1 1 1 SORT AGGREGATE (cr=3 pr=0 pw=0 time=13765 us)
10 10 10 PX COORDINATOR (cr=3 pr=0 pw=0 time=13057 us)
0 0 0 PX SEND QC (RANDOM) :TQ10000 (cr=0 pr=0 pw=0 time=0 us)
0 0 0 SORT AGGREGATE (cr=0 pr=0 pw=0 time=0 us)
0 0 0 PX BLOCK ITERATOR (cr=0 pr=0 pw=0 time=0 us cost=2 size=0 card=2)
0 0 0 TABLE ACCESS FULL T (cr=0 pr=0 pw=0 time=0 us cost=2 size=0 card=2)
********************************************************************************
查询V$PQ_TQSTAT
SQL> SELECT dfo_number,tq_id,server_type,num_rows,bytes,process FROM V$PQ_tqstat;
DFO_NUMBER TQ_ID SERVER_TYPE NUM_ROWS BYTES PROCESS
---------- ---------- ------------- ---------- ---------- ------------------------------------
1 0 Producer 1 36 P005
1 0 Producer 1 36 P007
1 0 Producer 1 36 P003
1 0 Producer 1 36 P009
1 0 Producer 1 36 P004
1 0 Producer 1 36 P008
1 0 Producer 1 36 P006
1 0 Producer 1 36 P000
1 0 Producer 1 36 P001
1 0 Producer 1 36 P002
1 0 Consumer 10 360 QC
11 rows selected.
SQL>
在性能视图中可以看到,使用了10个并发进程。
-----------------------------------------------------------------------
5.分别演示一个DDL和DML操作的并行执行示例。<br>
5.1 测试使用DDL
SQL> alter session set events '10046 trace name context forever,level 12';
Session altered.
SQL> drop table t5 purge;
Table dropped.
SQL> create table t5 parallel 4 as select * from dba_objects;
Table created.
SQL> create index idx_t5_obj_id on t5(object_id) parallel 4;
Index created.
SQL> alter session set events '10046 trace name context off';
Session altered.
SQL> select name,value from v$diag_info where name='Default Trace File';
NAME
--------------------------------------------------------------------------------
VALUE
--------------------------------------------------------------------------------
Default Trace File
D:\APP\ORACLE\diag\rdbms\orcl\orcl\trace\orcl_ora_5708.trc
SQL>
5.2 从跟踪文件中,生成文件
C:\Users\Administrator>tkprof D:\app\oracle\diag\rdbms\orcl\orcl\trace\orcl_ora_
5708.trc d:\tang.prof
TKPROF: Release 11.2.0.3.0 - Development on 星期六 11月 30 16:07:58 2013
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
C:\Users\Administrator>
文件内容如下:
从跟踪文件中的内容可以看到,使用了4个并行:
create table t5 parallel 4 as select * from dba_objects
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.01 0.02 0 0 0 0
Execute 1 0.42 0.90 0 51 408 76386
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2 0.43 0.92 0 51 408 76386
Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 84
Number of plan statistics captured: 1
Rows (1st) Rows (avg) Rows (max) Row Source Operation
---------- ---------- ---------- ---------------------------------------------------
4 4 4 PX COORDINATOR (cr=14 pr=0 pw=0 time=697718 us)
0 0 0 PX SEND QC (RANDOM) :TQ20001 (cr=0 pr=0 pw=0 time=0 us cost=137 size=15800310 card=76330)
0 0 0 LOAD AS SELECT (cr=0 pr=0 pw=0 time=0 us)
0 0 0 VIEW DBA_OBJECTS (cr=0 pr=0 pw=0 time=0 us cost=137 size=15800310 card=76330)
0 0 0 UNION-ALL (cr=0 pr=0 pw=0 time=0 us)
0 0 0 TABLE ACCESS BY INDEX ROWID SUM$ (cr=2 pr=0 pw=0 time=29 us cost=1 size=11 card=1)
1 1 1 INDEX UNIQUE SCAN I_SUM$_1 (cr=1 pr=0 pw=0 time=15 us cost=0 size=0 card=1)(object id 1002)
0 0 0 TABLE ACCESS BY INDEX ROWID OBJ$ (cr=0 pr=0 pw=0 time=0 us cost=3 size=30 card=1)
0 0 0 INDEX RANGE SCAN I_OBJ1 (cr=0 pr=0 pw=0 time=0 us cost=2 size=0 card=1)(object id 36)
0 0 0 BUFFER SORT (cr=0 pr=0 pw=0 time=0 us)
0 0 0 PX RECEIVE (cr=0 pr=0 pw=0 time=0 us)
0 0 0 PX SEND ROUND-ROBIN :TQ20000 (cr=0 pr=0 pw=0 time=0 us)
76379 76379 76379 FILTER (cr=9 pr=0 pw=0 time=461741 us)
76379 76379 76379 PX COORDINATOR (cr=9 pr=0 pw=0 time=289402 us)
0 0 0 PX SEND QC (RANDOM) :TQ10002 (cr=0 pr=0 pw=0 time=0 us cost=78 size=9006114 card=76323)
0 0 0 HASH JOIN (cr=0 pr=0 pw=0 time=0 us cost=78 size=9006114 card=76323)
0 0 0 PX RECEIVE (cr=0 pr=0 pw=0 time=0 us cost=2 size=1768 card=104)
0 0 0 PX SEND BROADCAST :TQ10001 (cr=0 pr=0 pw=0 time=0 us cost=2 size=1768 card=104)
0 0 0 PX BLOCK ITERATOR (cr=0 pr=0 pw=0 time=0 us cost=2 size=1768 card=104)
0 0 0 TABLE ACCESS FULL USER$ (cr=0 pr=0 pw=0 time=0 us cost=2 size=1768 card=104)
0 0 0 HASH JOIN (cr=0 pr=0 pw=0 time=0 us cost=75 size=7708623 card=76323)
0 0 0 BUFFER SORT (cr=0 pr=0 pw=0 time=0 us)
0 0 0 PX RECEIVE (cr=0 pr=0 pw=0 time=0 us cost=1 size=2288 card=104)
0 0 0 PX SEND BROADCAST :TQ10000 (cr=0 pr=0 pw=0 time=0 us cost=1 size=2288 card=104)
104 104 104 INDEX FULL SCAN I_USER2 (cr=1 pr=0 pw=0 time=125 us cost=1 size=2288 card=104)(object id 47)
0 0 0 PX BLOCK ITERATOR (cr=0 pr=0 pw=0 time=0 us cost=73 size=6029517 card=76323)
0 0 0 TABLE ACCESS FULL OBJ$ (cr=0 pr=0 pw=0 time=0 us cost=73 size=6029517 card=76323)
0 0 0 NESTED LOOPS (cr=0 pr=0 pw=0 time=0 us cost=1 size=29 card=1)
0 0 0 INDEX SKIP SCAN I_USER2 (cr=0 pr=0 pw=0 time=0 us cost=1 size=20 card=1)(object id 47)
0 0 0 INDEX RANGE SCAN I_OBJ4 (cr=0 pr=0 pw=0 time=0 us cost=0 size=9 card=1)(object id 39)
0 0 0 NESTED LOOPS (cr=0 pr=0 pw=0 time=0 us cost=4 size=273 card=7)
0 0 0 PX BLOCK ITERATOR (cr=0 pr=0 pw=0 time=0 us)
0 0 0 TABLE ACCESS FULL LINK$ (cr=0 pr=0 pw=0 time=0 us cost=2 size=154 card=7)
0 0 0 TABLE ACCESS CLUSTER USER$ (cr=0 pr=0 pw=0 time=0 us cost=0 size=17 card=1)
0 0 0 INDEX UNIQUE SCAN I_USER# (cr=0 pr=0 pw=0 time=0 us cost=0 size=0 card=1)(object id 11)
......
Rows (1st) Rows (avg) Rows (max) Row Source Operation
---------- ---------- ---------- ---------------------------------------------------
4 4 4 PX COORDINATOR (cr=5 pr=0 pw=0 time=73817 us)
0 0 0 PX SEND QC (ORDER) :TQ10001 (cr=0 pr=0 pw=0 time=0 us)
0 0 0 INDEX BUILD NON UNIQUE IDX_T5_OBJ_ID (cr=0 pr=0 pw=0 time=0 us)(object id 0)
0 0 0 SORT CREATE INDEX (cr=0 pr=0 pw=0 time=0 us)
0 0 0 PX RECEIVE (cr=0 pr=0 pw=0 time=0 us cost=88 size=1231737 card=94749)
0 0 0 PX SEND RANGE :TQ10000 (cr=0 pr=0 pw=0 time=0 us cost=88 size=1231737 card=94749)
0 0 0 PX BLOCK ITERATOR (cr=0 pr=0 pw=0 time=0 us cost=88 size=1231737 card=94749)
0 0 0 TABLE ACCESS FULL T5 (cr=0 pr=0 pw=0 time=0 us cost=88 size=1231737 card=94749)
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
PX Deq: Join ACK 8 0.00 0.00
PX Deq: Parse Reply 8 0.00 0.00
PX Deq: Execute Reply 82 0.01 0.06
PX Deq: Table Q qref 4 0.00 0.00
db file scattered read 6 0.00 0.00
reliable message 7 0.00 0.00
enq: CR - block range reuse ckpt 3 0.00 0.00
log file sync 1 0.00 0.00
PX Deq: Signal ACK EXT 8 0.00 0.00
PX Deq: Slave Session Stats 8 0.00 0.00
enq: PS - contention 1 0.00 0.00
SQL*Net message to client 1 0.00 0.00
SQL*Net message from client 1 20.17 20.17
********************************************************************************
create index idx_t5_obj_id on t5(object_id) parallel 4
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.01 0.00 0 1 0 0
Execute 1 0.01 0.11 23 9 965 0
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2 0.03 0.11 23 10 965 0
Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 84
Number of plan statistics captured: 1
Rows (1st) Rows (avg) Rows (max) Row Source Operation
---------- ---------- ---------- ---------------------------------------------------
4 4 4 PX COORDINATOR (cr=5 pr=0 pw=0 time=73817 us)
0 0 0 PX SEND QC (ORDER) :TQ10001 (cr=0 pr=0 pw=0 time=0 us)
0 0 0 INDEX BUILD NON UNIQUE IDX_T5_OBJ_ID (cr=0 pr=0 pw=0 time=0 us)(object id 0)
0 0 0 SORT CREATE INDEX (cr=0 pr=0 pw=0 time=0 us)
0 0 0 PX RECEIVE (cr=0 pr=0 pw=0 time=0 us cost=88 size=1231737 card=94749)
0 0 0 PX SEND RANGE :TQ10000 (cr=0 pr=0 pw=0 time=0 us cost=88 size=1231737 card=94749)
0 0 0 PX BLOCK ITERATOR (cr=0 pr=0 pw=0 time=0 us cost=88 size=1231737 card=94749)
0 0 0 TABLE ACCESS FULL T5 (cr=0 pr=0 pw=0 time=0 us cost=88 size=1231737 card=94749)
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
PX Deq: Join ACK 8 0.00 0.00
PX Deq: Parse Reply 8 0.00 0.00
PX Deq: Execute Reply 82 0.01 0.06
PX Deq: Table Q qref 4 0.00 0.00
db file scattered read 6 0.00 0.00
reliable message 7 0.00 0.00
enq: CR - block range reuse ckpt 3 0.00 0.00
log file sync 1 0.00 0.00
PX Deq: Signal ACK EXT 8 0.00 0.00
PX Deq: Slave Session Stats 8 0.00 0.00
enq: PS - contention 1 0.00 0.00
SQL*Net message to client 1 0.00 0.00
SQL*Net message from client 1 20.17 20.17
********************************************************************************
做DML 操作时使用并行,进行一个并行插入的测试:
SQL> explain plan for insert /*+ parallel(t5 10) */ into t5 select /*+ parallel(t 4) */ * from t;
Explained.
SQL> select * from table(dbms_xplan.display);
SQL> set linesize 100;
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
Plan hash value: 3050126167
----------------------------------------------------------------------------------------------------
--------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | TQ |IN-OUT
| PQ Distrib |
----------------------------------------------------------------------------------------------------
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
| 0 | INSERT STATEMENT | | 76386 | 7310K| 85 (2)| 00:00:02 | |
| |
| 1 | LOAD TABLE CONVENTIONAL | T5 | | | | | |
| |
| 2 | PX COORDINATOR | | | | | | |
| |
| 3 | PX SEND QC (RANDOM) | :TQ10000 | 76386 | 7310K| 85 (2)| 00:00:02 | Q1,00 | P->S
| QC (RAND) |
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
| 4 | PX BLOCK ITERATOR | | 76386 | 7310K| 85 (2)| 00:00:02 | Q1,00 | PCWC
| |
| 5 | TABLE ACCESS FULL | T | 76386 | 7310K| 85 (2)| 00:00:02 | Q1,00 | PCWP
| |
----------------------------------------------------------------------------------------------------
12 rows selected.
SQL>
从上面的执行计划中看到,从表T中查询,及插入T5表,都用到了 并行处理。