如何让CTAS真正的走出parallel的执行计划
CTAS是建表初始化或者作数据迁移时的常用方法，大部分情况下会以全表扫描方式加载批量数据，所以在cpu允许的条件下开启parallel server能很大程度上提高语句的执行效率，CTAS的create部分和select部分可以同时使用parallel的执行方式，这种情况下执行效率应该是最高的。但实际操作时往往由于某些原因，比如Select 后面所跟的where条件中的某个字段上建有索引，导致无法真正开启parallel server。下面通过实验说明一下针对CTAS操作如何在create和select部分都能正常的用上parallel server
##建立测试用表
create table aabb tablespace ts_pub as select * from dba_objects;
create index ind_aabb on aabb(object_id) tablespace ts_pub;
exec dbms_stats.gather_table_stats(ownname=>'SYS',tabname=>'AABB',estimate_percent=>NULL,cascade=>TRUE);
********************
** create table as select from:select和create部分均没有任何并行，因为：create和select部分均没有使用parallel hint，select部分使用的是index range scan
********************
drop table aabb_c1;
create table aabb_c1 tablespace ts_pub as select * from aabb where object_id<1000;
select * from table(dbms_xplan.display_cursor(NULL,NULL,'advanced'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
ime |
--------------------------------------------------------------------------------
---------
| 0 | CREATE TABLE STATEMENT | | | | 5 (100)|
|
| 1 | LOAD AS SELECT | | | | |
|
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
| 2 | TABLE ACCESS BY INDEX ROWID| AABB | 32 | 3104 | 4 (0)| 0
0:00:01 |
|* 3 | INDEX RANGE SCAN | IND_AABB | 32 | | 2 (0)| 0
0:00:01 |
--------------------------------------------------------------------------------
---------
********************
** create table as select /*+ full(aabb) */ from:和上例相比仅select部分采用了FTS替代了上例的index range scan，并没有启用任何的并行
********************
drop table aabb_c1;
create table aabb_c1 tablespace ts_pub as select /*+ full(aabb) */ * from aabb where object_id<1000;
select * from table(dbms_xplan.display_cursor(NULL,NULL,'advanced'));
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------
| 0 | CREATE TABLE STATEMENT | | | | 417 (100)| |
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
| 1 | LOAD AS SELECT | | | | | |
|* 2 | TABLE ACCESS FULL | AABB | 32 | 3104 | 416 (1)| 00:00:01 |
-------------------------------------------------------------------------------
********************
** create /*+ parallel(3) */ table as select from:仅create部分采用了并行，select部分由于采用index range scan所以未能使用并行
********************
drop table aabb_c1;
create /*+ parallel(3) */ table aabb_c1 tablespace ts_pub as select * from aabb where object_id<1000;
select * from table(dbms_xplan.display_cursor(NULL,NULL,'advanced'));
| Id | Operation | Name | Rows | Bytes | Cost (%CPU
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
)| Time | TQ |IN-OUT| PQ Distrib |
--------------------------------------------------------------------------------
------------------------------------------
| 0 | CREATE TABLE STATEMENT | | | | 5 (100
)| | | | |
| 1 | PX COORDINATOR | | | |
| | | | |
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
| 2 | PX SEND QC (RANDOM) | :TQ10001 | 32 | 3104 | 4 (0
)| 00:00:01 | Q1,01 | P->S | QC (RAND) |
| 3 | LOAD AS SELECT | | | |
| | Q1,01 | PCWP | |
| 4 | PX RECEIVE | | 32 | 3104 | 4 (0
)| 00:00:01 | Q1,01 | PCWP | |
| 5 | PX SEND ROUND-ROBIN | :TQ10000 | 32 | 3104 | 4 (0
)| 00:00:01 | | S->P | RND-ROBIN |
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
| 6 | TABLE ACCESS BY INDEX ROWID| AABB | 32 | 3104 | 4 (0
)| 00:00:01 | | | |
|* 7 | INDEX RANGE SCAN | IND_AABB | 32 | | 2 (0
)| 00:00:01 | | | |
--------------------------------------------------------------------------------
------------------------------------------
********************
** create /*+ parallel(3) */ table as select /*+ full(aabb) */ from: create和select部分都采用了并行，select部分用/*+ full(aabb) */强制走FTS，使得select部分也能跟随create部分一起使用parallel方式
********************
drop table aabb_c1;
create /*+ parallel(3) */ table aabb_c1 tablespace ts_pub as select /*+ full(aabb) */ * from aabb where object_id<1000;
select * from table(dbms_xplan.display_cursor(NULL,NULL,'advanced'));
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
| TQ |IN-OUT| PQ Distrib |
--------------------------------------------------------------------------------
--------------------------------
| 0 | CREATE TABLE STATEMENT | | | | 154 (100)|
| | | |
| 1 | PX COORDINATOR | | | | |
| | | |
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
| 2 | PX SEND QC (RANDOM) | :TQ10000 | 32 | 3104 | 154 (1)| 00:00:0
1 | Q1,00 | P->S | QC (RAND) |
| 3 | LOAD AS SELECT | | | | |
| Q1,00 | PCWP | |
| 4 | PX BLOCK ITERATOR | | 32 | 3104 | 154 (1)| 00:00:0
1 | Q1,00 | PCWC | |
|* 5 | TABLE ACCESS FULL | AABB | 32 | 3104 | 154 (1)| 00:00:0
1 | Q1,00 | PCWP | |
********************
** create table as select /*+ full(aabb) parallel(3) */ from: create部分虽然没有指定parallel hint，但因select部分使用了/*+ full(aabb) parallel(3) */强制走FTS和并行方式，所以从执行计划看create也走了并行
********************
drop table aabb_c1;
create table aabb_c1 tablespace ts_pub as select /*+ full(aabb) parallel(3) */ * from aabb where object_id<1000;
select * from table(dbms_xplan.display_cursor(NULL,NULL,'advanced'));
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
| TQ |IN-OUT| PQ Distrib |
--------------------------------------------------------------------------------
--------------------------------
| 0 | CREATE TABLE STATEMENT | | | | 154 (100)|
| | | |
| 1 | PX COORDINATOR | | | | |
| | | |
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
| 2 | PX SEND QC (RANDOM) | :TQ10000 | 32 | 3104 | 154 (1)| 00:00:0
1 | Q1,00 | P->S | QC (RAND) |
| 3 | LOAD AS SELECT | | | | |
| Q1,00 | PCWP | |
| 4 | PX BLOCK ITERATOR | | 32 | 3104 | 154 (1)| 00:00:0
1 | Q1,00 | PCWC | |
|* 5 | TABLE ACCESS FULL | AABB | 32 | 3104 | 154 (1)| 00:00:0
1 | Q1,00 | PCWP | |
********************
** create /*+ parallel(5) */ table as select /*+ full(aabb) parallel(9) */ from: create和select部分均指定parallel hint的情况下，DOP以select指定的为准
********************
drop table aabb_c1;
create /*+ parallel(5) */ table aabb_c1 tablespace ts_pub as select /*+ full(aabb) parallel(9) */ * from aabb where object_id<1000;
select * from v$pq_sesstat;
STATISTIC LAST_QUERY SESSION_TOTAL
------------------------------ ---------- -------------
Queries Parallelized 0 1
DML Parallelized 0 0
DDL Parallelized 1 7
DFO Trees 1 8
Server Threads 9 0
Allocation Height 9 0
Allocation Width 1 0
Local Msgs Sent 299 831
Distr Msgs Sent 0 0
Local Msgs Recv'd 299 831
Distr Msgs Recv'd 0 0
结论：
1、select子句必须使用fts作为access path时，select部分才有可能并行，否则最多只能是create部分并行
2、在select子句能够使用FTS的前提下，如果给select子句增加了parallel hint，那么在不给create部分增加parallel hint的情况下，也能实现create和select部分同时以并行方式运行。如果create部分也加了parallel hint，那么create和select部分的并行度以select部分的DOP为准
3、执行完create table as后，v$pq_sesstat中的Queries Parallelized始终显示为0，虽然parallel query也是CTAS的组成部分
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/30811755/viewspace-2121490/ ，如需转载，请注明出处，否则将追究法律责任。
上一篇：
一条SQL建表语句的优化
下一篇：
没有了~
请登录后发表评论
登录
全部评论
