联系：手机/微信(+86 17813235971) QQ(107644445)
作者：惜分飞©版权所有[未经本人同意,不得以任何形式转载,否则有进一步追究法律责任的权利.]
1、CREATE INDEX 操作
SQL> conn chf/xifenfei Connected. SQL> EXPLAIN PLAN FOR create index ind_t2 on t1(object_name) online nologging PARALLEL(degree 4); Explained. SQL> SELECT * FROM table (DBMS_XPLAN.display(NULL, NULL, 'BASIC +PARALLEL')); PLAN_TABLE_OUTPUT -------------------------------------------------------------------------------- Plan hash value: 2130784087 -------------------------------------------------------------------------- | Id | Operation | Name | TQ |IN-OUT| PQ Distrib | -------------------------------------------------------------------------- | 0 | CREATE INDEX STATEMENT | | | | | | 1 | PX COORDINATOR | | | | | | 2 | PX SEND QC (ORDER) | :TQ10001 | Q1,01 | P->S | QC (ORDER) | | 3 | INDEX BUILD NON UNIQUE| IND_T2 | Q1,01 | PCWP | | | 4 | SORT CREATE INDEX | | Q1,01 | PCWP | | | 5 | PX RECEIVE | | Q1,01 | PCWP | | | 6 | PX SEND RANGE | :TQ10000 | Q1,00 | P->P | RANGE | | 7 | PX BLOCK ITERATOR | | Q1,00 | PCWC | | | 8 | TABLE ACCESS FULL| T1 | Q1,00 | PCWP | | -------------------------------------------------------------------------- SQL> conn chf/xifenfei Connected. SQL> ALTER SESSION ENABLE PARALLEL DDL; Session altered. SQL> EXPLAIN PLAN FOR create index ind_t2 on t1(object_name) online nologging PARALLEL(degree 4); Explained. SQL> SELECT * FROM table (DBMS_XPLAN.display(NULL, NULL, 'BASIC +PARALLEL')); PLAN_TABLE_OUTPUT -------------------------------------------------------------------------------- Plan hash value: 2130784087 -------------------------------------------------------------------------- | Id | Operation | Name | TQ |IN-OUT| PQ Distrib | -------------------------------------------------------------------------- | 0 | CREATE INDEX STATEMENT | | | | | | 1 | PX COORDINATOR | | | | | | 2 | PX SEND QC (ORDER) | :TQ10001 | Q1,01 | P->S | QC (ORDER) | | 3 | INDEX BUILD NON UNIQUE| IND_T2 | Q1,01 | PCWP | | | 4 | SORT CREATE INDEX | | Q1,01 | PCWP | | | 5 | PX RECEIVE | | Q1,01 | PCWP | | | 6 | PX SEND RANGE | :TQ10000 | Q1,00 | P->P | RANGE | | 7 | PX BLOCK ITERATOR | | Q1,00 | PCWC | | | 8 | TABLE ACCESS FULL| T1 | Q1,00 | PCWP | | --------------------------------------------------------------------------
2、REBUILD INDEX 操作
SQL> conn chf/xifenfei Connected. SQL> EXPLAIN PLAN FOR alter index ind_t2 rebuild online nologging PARALLEL(degree 4); Explained. SQL> SELECT * FROM table (DBMS_XPLAN.display(NULL, NULL, 'BASIC +PARALLEL')); PLAN_TABLE_OUTPUT -------------------------------------------------------------------------------- Plan hash value: 2130784087 -------------------------------------------------------------------------- | Id | Operation | Name | TQ |IN-OUT| PQ Distrib | -------------------------------------------------------------------------- | 0 | ALTER INDEX STATEMENT | | | | | | 1 | PX COORDINATOR | | | | | | 2 | PX SEND QC (ORDER) | :TQ10001 | Q1,01 | P->S | QC (ORDER) | | 3 | INDEX BUILD NON UNIQUE| IND_T2 | Q1,01 | PCWP | | | 4 | SORT CREATE INDEX | | Q1,01 | PCWP | | | 5 | PX RECEIVE | | Q1,01 | PCWP | | | 6 | PX SEND RANGE | :TQ10000 | Q1,00 | P->P | RANGE | | 7 | PX BLOCK ITERATOR | | Q1,00 | PCWC | | | 8 | TABLE ACCESS FULL| T1 | Q1,00 | PCWP | | -------------------------------------------------------------------------- SQL> conn chf/xifenfei Connected. SQL> ALTER SESSION ENABLE PARALLEL DDL; Session altered. SQL> EXPLAIN PLAN FOR alter index ind_t2 rebuild online nologging PARALLEL(degree 4); Explained. SQL> SELECT * FROM table (DBMS_XPLAN.display(NULL, NULL, 'BASIC +PARALLEL')); PLAN_TABLE_OUTPUT -------------------------------------------------------------------------------- Plan hash value: 2130784087 -------------------------------------------------------------------------- | Id | Operation | Name | TQ |IN-OUT| PQ Distrib | -------------------------------------------------------------------------- | 0 | ALTER INDEX STATEMENT | | | | | | 1 | PX COORDINATOR | | | | | | 2 | PX SEND QC (ORDER) | :TQ10001 | Q1,01 | P->S | QC (ORDER) | | 3 | INDEX BUILD NON UNIQUE| IND_T2 | Q1,01 | PCWP | | | 4 | SORT CREATE INDEX | | Q1,01 | PCWP | | | 5 | PX RECEIVE | | Q1,01 | PCWP | | | 6 | PX SEND RANGE | :TQ10000 | Q1,00 | P->P | RANGE | | 7 | PX BLOCK ITERATOR | | Q1,00 | PCWC | | | 8 | TABLE ACCESS FULL| T1 | Q1,00 | PCWP | | --------------------------------------------------------------------------
3、CREATE TABLE 操作
SQL> conn chf/xifenfei Connected. SQL> EXPLAIN PLAN FOR create table t_6 parallel (degree 4) 2 as select /*+ parallel (t1,4) */ * from t1 where rownum<5000000; Explained. SQL> SELECT * FROM table (DBMS_XPLAN.display(NULL, NULL, 'BASIC +PARALLEL')); PLAN_TABLE_OUTPUT -------------------------------------------------------------------------------- Plan hash value: 2102891290 ------------------------------------------------------------------------------ | Id | Operation | Name | TQ |IN-OUT| PQ Distrib | ------------------------------------------------------------------------------ | 0 | CREATE TABLE STATEMENT | | | | | | 1 | PX COORDINATOR | | | | | | 2 | PX SEND QC (RANDOM) | :TQ20001 | Q2,01 | P->S | QC (RAND) | | 3 | LOAD AS SELECT | T_6 | Q2,01 | PCWP | | | 4 | BUFFER SORT | | Q2,01 | PCWC | | | 5 | PX RECEIVE | | Q2,01 | PCWP | | | 6 | PX SEND ROUND-ROBIN | :TQ20000 | | S->P | RND-ROBIN | | 7 | COUNT STOPKEY | | | | | | 8 | PX COORDINATOR | | | | | | 9 | PX SEND QC (RANDOM) | :TQ10000 | Q1,00 | P->S | QC (RAND) | | 10 | COUNT STOPKEY | | Q1,00 | PCWC | | | 11 | PX BLOCK ITERATOR | | Q1,00 | PCWC | | | 12 | TABLE ACCESS FULL| T1 | Q1,00 | PCWP | | ------------------------------------------------------------------------------ SQL> conn chf/xifenfei Connected. SQL> ALTER SESSION ENABLE PARALLEL DDL; Session altered. SQL> EXPLAIN PLAN FOR create table t_6 parallel (degree 4) 2 as select /*+ parallel (t1,4) */ * from t1 where rownum<5000000; Explained. SQL> SELECT * FROM table (DBMS_XPLAN.display(NULL, NULL, 'BASIC +PARALLEL')); PLAN_TABLE_OUTPUT -------------------------------------------------------------------------------- Plan hash value: 2102891290 ------------------------------------------------------------------------------ | Id | Operation | Name | TQ |IN-OUT| PQ Distrib | ------------------------------------------------------------------------------ | 0 | CREATE TABLE STATEMENT | | | | | | 1 | PX COORDINATOR | | | | | | 2 | PX SEND QC (RANDOM) | :TQ20001 | Q2,01 | P->S | QC (RAND) | | 3 | LOAD AS SELECT | T_6 | Q2,01 | PCWP | | | 4 | BUFFER SORT | | Q2,01 | PCWC | | | 5 | PX RECEIVE | | Q2,01 | PCWP | | | 6 | PX SEND ROUND-ROBIN | :TQ20000 | | S->P | RND-ROBIN | | 7 | COUNT STOPKEY | | | | | | 8 | PX COORDINATOR | | | | | | 9 | PX SEND QC (RANDOM) | :TQ10000 | Q1,00 | P->S | QC (RAND) | | 10 | COUNT STOPKEY | | Q1,00 | PCWC | | | 11 | PX BLOCK ITERATOR | | Q1,00 | PCWC | | | 12 | TABLE ACCESS FULL| T1 | Q1,00 | PCWP | | ------------------------------------------------------------------------------
4、说明
1）本次测试的数据库版本为10.2.0.4，Linux环境，其他版本可能有差异
2）关于INDEX的并行操作，并行度可能不会和指定相同（测试为2倍）
3）操作过程中，是否指定ddl 并发，效果相同。建议指定未佳：
ALTER SESSION ENABLE PARALLEL DDL;
ALTER SESSION DISABLE PARALLEL DDL;
4）修改表结构操作，指定并发无效（待寻找方法）