048.【实验：测试PARALLEL提示】

最新推荐文章于 2021-03-20 08:54:51 发布

cuihu2560

最新推荐文章于 2021-03-20 08:54:51 发布

阅读量169

点赞数

【测试1】单表非并行
SQL> select *
2 from scott.dept b;

43105 rows selected.

Execution Plan
----------------------------------------------------------
Plan hash value: 3383998547

--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 43105 | 1557K| 87 (3)| 00:00:02 |
| 1 | TABLE ACCESS FULL| DEPT | 43105 | 1557K| 87 (3)| 00:00:02 |
--------------------------------------------------------------------------

Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
3240 consistent gets
0 physical reads
0 redo size
1896823 bytes sent via SQL*Net to client
31988 bytes received via SQL*Net from client
2875 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
43105 rows processed
【测试2】单表并表
SQL> select /*+ parallel(b) */*
2 from scott.dept b;

43105 rows selected.

Execution Plan
----------------------------------------------------------
Plan hash value: 587379989

--------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | TQ |IN-OUT| PQ Distrib |
--------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 43105 | 1557K| 48 (3)| 00:00:01 | | | |
| 1 | PX COORDINATOR | | | | | | | | |
| 2 | PX SEND QC (RANDOM)| :TQ10000 | 43105 | 1557K| 48 (3)| 00:00:01 | Q1,00 | P->S | QC (RAND) |
| 3 | PX BLOCK ITERATOR | | 43105 | 1557K| 48 (3)| 00:00:01 | Q1,00 | PCWC | |
| 4 | TABLE ACCESS FULL| DEPT | 43105 | 1557K| 48 (3)| 00:00:01 | Q1,00 | PCWP | |
--------------------------------------------------------------------------------------------------------------

Statistics
----------------------------------------------------------
6 recursive calls
0 db block gets
509 consistent gets
400 physical reads
0 redo size
1894955 bytes sent via SQL*Net to client
31988 bytes received via SQL*Net from client
2875 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
43105 rows processed

【测试3】两表连接，其中一张表并行
SQL> select /*+parallel(b)*/ *
2 from scott.dept b,scott.dept_sort c
3 where b.deptno=c.deptno;

43105 rows selected.

Execution Plan
----------------------------------------------------------
Plan hash value: 2776900878

------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | TQ |IN-OUT| PQ Distrib |
------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 43105 | 3115K| 104 (4)| 00:00:02 | | | |
| 1 | PX COORDINATOR | | | | | | | | |
| 2 | PX SEND QC (RANDOM) | :TQ10001 | 43105 | 3115K| 104 (4)| 00:00:02 | Q1,01 | P->S | QC (RAND) |
|* 3 | HASH JOIN | | 43105 | 3115K| 104 (4)| 00:00:02 | Q1,01 | PCWP | |
| 4 | PX BLOCK ITERATOR | | 43105 | 1557K| 48 (3)| 00:00:01 | Q1,01 | PCWC | |
| 5 | TABLE ACCESS FULL | DEPT | 43105 | 1557K| 48 (3)| 00:00:01 | Q1,01 | PCWP | |
| 6 | BUFFER SORT | | | | | | Q1,01 | PCWC | |
| 7 | PX RECEIVE | | 43105 | 1557K| 55 (4)| 00:00:01 | Q1,01 | PCWP | |
| 8 | PX SEND BROADCAST | :TQ10000 | 43105 | 1557K| 55 (4)| 00:00:01 | | S->P | BROADCAST |
| 9 | TABLE ACCESS FULL| DEPT_SORT | 43105 | 1557K| 55 (4)| 00:00:01 | | | |
------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

3 - access("B"."DEPTNO"="C"."DEPTNO")

Statistics
----------------------------------------------------------
41 recursive calls
7 db block gets
768 consistent gets
1206 physical reads
888 redo size
3166716 bytes sent via SQL*Net to client
31988 bytes received via SQL*Net from client
2875 SQL*Net roundtrips to/from client
1 sorts (memory)
2 sorts (disk)
43105 rows processed

【测试4】两张表连接，两张表都使用并行
SQL> select /*+parallel(b) parallel(c)*/ *
2 from scott.dept b,scott.dept_sort c
3 where b.deptno=c.deptno;

43105 rows selected.

Execution Plan
----------------------------------------------------------
Plan hash value: 4208745840

-------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | TQ |IN-OUT| PQ Distrib |
-------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 43105 | 3115K| 80 (4)| 00:00:01 | | | |
| 1 | PX COORDINATOR | | | | | | | | |
| 2 | PX SEND QC (RANDOM) | :TQ10001 | 43105 | 3115K| 80 (4)| 00:00:01 | Q1,01 | P->S | QC (RAND) |
|* 3 | HASH JOIN | | 43105 | 3115K| 80 (4)| 00:00:01 | Q1,01 | PCWP | |
| 4 | PX BLOCK ITERATOR | | 43105 | 1557K| 48 (3)| 00:00:01 | Q1,01 | PCWC | |
| 5 | TABLE ACCESS FULL | DEPT | 43105 | 1557K| 48 (3)| 00:00:01 | Q1,01 | PCWP | |
| 6 | BUFFER SORT | | | | | | Q1,01 | PCWC | |
| 7 | PX RECEIVE | | 43105 | 1557K| 30 (4)| 00:00:01 | Q1,01 | PCWP | |
| 8 | PX SEND BROADCAST | :TQ10000 | 43105 | 1557K| 30 (4)| 00:00:01 | Q1,00 | P->P | BROADCAST |
| 9 | PX BLOCK ITERATOR | | 43105 | 1557K| 30 (4)| 00:00:01 | Q1,00 | PCWC | |
| 10 | TABLE ACCESS FULL| DEPT_SORT | 43105 | 1557K| 30 (4)| 00:00:01 | Q1,00 | PCWP | |
-------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

3 - access("B"."DEPTNO"="C"."DEPTNO")

Statistics
----------------------------------------------------------
47 recursive calls
7 db block gets
871 consistent gets
1311 physical reads
816 redo size
3109250 bytes sent via SQL*Net to client
31988 bytes received via SQL*Net from client
2875 SQL*Net roundtrips to/from client
2 sorts (memory)
2 sorts (disk)
43105 rows processed

【测试5】修改并行度
SQL> select /*+parallel(b 32) parallel(c 32)*/ *
2 from scott.dept b,scott.dept_sort c
3 where b.deptno=c.deptno;

43105 rows selected.

Execution Plan
----------------------------------------------------------
Plan hash value: 316312321

------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | TQ |IN-OUT| PQ Distrib |
------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 43105 | 3115K| 6 (17)| 00:00:01 | | | |
| 1 | PX COORDINATOR | | | | | | | | |
| 2 | PX SEND QC (RANDOM) | :TQ10002 | 43105 | 3115K| 6 (17)| 00:00:01 | Q1,02 | P->S | QC (RAND) |
|* 3 | HASH JOIN BUFFERED | | 43105 | 3115K| 6 (17)| 00:00:01 | Q1,02 | PCWP | |
| 4 | PX RECEIVE | | 43105 | 1557K| 3 (0)| 00:00:01 | Q1,02 | PCWP | |
| 5 | PX SEND HASH | :TQ10000 | 43105 | 1557K| 3 (0)| 00:00:01 | Q1,00 | P->P | HASH |
| 6 | PX BLOCK ITERATOR | | 43105 | 1557K| 3 (0)| 00:00:01 | Q1,00 | PCWC | |
| 7 | TABLE ACCESS FULL| DEPT | 43105 | 1557K| 3 (0)| 00:00:01 | Q1,00 | PCWP | |
| 8 | PX RECEIVE | | 43105 | 1557K| 2 (0)| 00:00:01 | Q1,02 | PCWP | |
| 9 | PX SEND HASH | :TQ10001 | 43105 | 1557K| 2 (0)| 00:00:01 | Q1,01 | P->P | HASH |
| 10 | PX BLOCK ITERATOR | | 43105 | 1557K| 2 (0)| 00:00:01 | Q1,01 | PCWC | |
| 11 | TABLE ACCESS FULL| DEPT_SORT | 43105 | 1557K| 2 (0)| 00:00:01 | Q1,01 | PCWP | |
------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

3 - access("B"."DEPTNO"="C"."DEPTNO")

Statistics
----------------------------------------------------------
41 recursive calls
3 db block gets
871 consistent gets
918 physical reads
808 redo size
3077240 bytes sent via SQL*Net to client
31988 bytes received via SQL*Net from client
2875 SQL*Net roundtrips to/from client
2 sorts (memory)
0 sorts (disk)
43105 rows processed

【小结】
1.指定并行度：alter table emp parallel 4;
2.iterate:重复
3.COORDINATE:vt.使协调，使调和；整合；使（身体各部份）动作协调；（衣服、布料等）搭配
vi.协调；协同；成为同等；被归入同一类别
4.最好使用ALTER TABLE 修改并行度。这样当SQL从开发环境移植到生产环境后，优化并行度时不用修改SQL。

[@more@]

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/7901922/viewspace-1060031/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/7901922/viewspace-1060031/