并行查询(OLAP)

并行查询(一般在OLAP中使用)

条件：多个CPU并且数据跨多个磁盘驱动器存储.

SQL> explain plan for select /*+parallel(c,5)*/ * from sh.customers c order by c
ust_last_name;

已解释。

已用时间: 00: 00: 00.00
SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------

Plan hash value: 2648474980

--------------------------------------------------------------------------------

------------------------------------------

| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU

)| Time | TQ |IN-OUT| PQ Distrib |

--------------------------------------------------------------------------------

------------------------------------------

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 55500 | 9755K| | 76 (4)| 00:00:01 | | | |

| 1 | PX COORDINATOR | | | | |
| | | | |

| 2 | PX SEND QC (ORDER) | :TQ10001 | 55500 | 9755K| | 76 (4)| 00:00:01 | Q1,01 | P->S | QC (ORDER) |

| 3 | SORT ORDER BY | | 55500 | 9755K| 24M| 76 (4)| 00:00:01 | Q1,01 | PCWP | |

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------

| 4 | PX RECEIVE | | 55500 | 9755K| | 74 (2

)| 00:00:01 | Q1,01 | PCWP | |

| 5 | PX SEND RANGE | :TQ10000 | 55500 | 9755K| | 74 (2)| 00:00:01 | Q1,00 | P->P | RANGE |

| 6 | PX BLOCK ITERATOR | | 55500 | 9755K| | 74 (2)| 00:00:01 | Q1,00 | PCWC | |

| 7 | TABLE ACCESS FULL| CUSTOMERS | 55500 | 9755K| | 74 (2
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------

)| 00:00:01 | Q1,00 | PCWP | |

--------------------------------------------------------------------------------

------------------------------------------

已选择14行。

已用时间: 00: 00: 00.03
SQL> desc v$pq_tqstat
名称 是否为空? 类型
----------------------------------------- -------- ----------------------------

DFO_NUMBER NUMBER
TQ_ID NUMBER
SERVER_TYPE VARCHAR2(10)
NUM_ROWS NUMBER
BYTES NUMBER
OPEN_TIME NUMBER
AVG_LATENCY NUMBER
WAITS NUMBER
TIMEOUTS NUMBER
PROCESS VARCHAR2(10)
INSTANCE NUMBER

alter table test parallel(degree 5); --修改表的并行度(DOP,Degree Of Parallelism)

[@more@]

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/16298743/viewspace-1046686/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/16298743/viewspace-1046686/