SQL> select * from v$version;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bi
PL/SQL Release 10.2.0.4.0 - Production
CORE 10.2.0.4.0 Production
TNS for IBM/AIX RISC System/6000: Version 10.2.0.4.0 - Productio
NLSRTL Version 10.2.0.4.0 - Production

Elapsed: 00:00:00.03
SQL>
SQL> select degree from user_tables where table_name = upper('t_service_route');

DEGREE
--------------------
1

SQL> set timing on

SQL> set line 1000
SQL> explain plan for
2 select count(1) from t_service_route;

Explained.

Elapsed: 00:00:00.01
SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------
Plan hash value: 572639962

------------------------------------------------------------------------------------
| Id | Operation             | Name               | Rows | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |                    |     1 | 23766   (2)| 00:05:33 |
|   1 | SORT AGGREGATE       |                    |     1 |            |          |
|   2 |   INDEX FAST FULL SCAN| PK_T_SERVICE_ROUTE |    33M| 23766   (2)| 00:05:33 |
------------------------------------------------------------------------------------

9 rows selected.

Elapsed: 00:00:00.03
SQL>
SQL> alter table t_service_route parallel; --启用parallel

Table altered.

Elapsed: 00:00:00.02
SQL> select degree from user_tables where table_name = upper('t_service_route');

DEGREE
--------------------
DEFAULT

Elapsed: 00:00:00.02
SQL>
SQL> explain plan for
2 select count(1) from t_service_route;

Explained.

Elapsed: 00:00:00.01
SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------
Plan hash value: 213106263

---------------------------------------------------------------------------------------------------------------
| Id | Operation              | Name            | Rows | Cost (%CPU)| Time     |    TQ |IN-OUT| PQ Distrib |
---------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT       |                 |     1 | 3210   (1)| 00:00:45 |        |      |         |
|   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 |                 |    33M| 3210   (1)| 00:00:45 | Q1,00 | PCWC |         |

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------
| 6 | TABLE ACCESS FULL| T_SERVICE_ROUTE | 33M| 3210 (1)| 00:00:45 | Q1,00 | PCWP | |
---------------------------------------------------------------------------------------------------------------

13 rows selected.

Elapsed: 00:00:00.08
SQL>

---一个并行建立索引的例子：

SQL> drop index grade_name;

Index dropped.

Elapsed: 00:00:00.08
SQL> create index grade_name on addr(grade,name);

Index created.

Elapsed: 00:00:36.28
SQL> drop index grade_name;

Index dropped.

Elapsed: 00:00:00.04
SQL> create index grade_name on addr(grade,name) parallel;

Index created.

Elapsed: 00:00:15.83 --速度快了一倍
SQL>

SQL> select degree from user_indexes where index_name='GRADE_NAME';

DEGREE
----------------------------------------
DEFAULT

Elapsed: 00:00:00.03
SQL>

并行的相关view：

gv$px_process
gv$px_session
gv$px_sesstat

SQL> select PDML_ENABLED,PDML_STATUS,PDDL_STATUS from v$session where rownum=1;

PDM PDML_STA PDDL_STA
--- -------- --------
NO DISABLED ENABLED

Elapsed: 00:00:00.02

默认PDDL是开启的

PDML 默认是关闭的(alter session enable parallel dml; alter session disable parallel dml;)

delete、update、merge的parallel执行仅只能在分区表上。

Parallel Query 默认是关闭的

ITPub博客

Parallel操作测试

最新文章