Oracle DDL 并行(10G)

联系:手机(+86 13429648788) QQ(107644445)QQ咨询惜分飞

标题:Oracle DDL 并行(10G)

作者:惜分飞©版权所有[未经本人同意,不得以任何形式转载,否则有进一步追究法律责任的权利.]

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)修改表结构操作,指定并发无效(待寻找方法)

此条目发表在 Oracle性能优化 分类目录。将固定链接加入收藏夹。

发表评论

电子邮件地址不会被公开。 必填项已用 * 标注

您可以使用这些 HTML 标签和属性: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>