Parallel操作測試

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 預設是關閉的