ITPub博客

首页 > Linux操作系统 > Linux操作系统 > Parallel操作测试

Parallel操作测试

原创 Linux操作系统 作者:denglt 时间:2011-01-21 18:04:21 0 删除 编辑
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博客 ” ,链接:http://blog.itpub.net/195110/viewspace-684457/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2010-11-04

  • 博文量
    118
  • 访问量
    710566