clob欄位對於parallel dml的限制

denglt發表於2012-09-28
原文網址 : http://blog.itpub.net/195110/viewspace-745353/
Parallel
今天發現包含clob欄位的表不能parallel insert

實驗如下：
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
5 rows selected.
SQL> create table test_lock (id number);
Table created.
SQL> alter session enable parallel dml;
Session altered.
SQL> explain plan for insert /*+ parallel(test_lock,4) */ into test_lock select object_id from user_objects;
Explained.
SQL> @s_xplan
執行計劃如下：
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 640494208
----------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                            | Name         | Rows  | Bytes | Cost (%CPU)| Time     |    TQ  |IN-OUT| PQ Distrib |
----------------------------------------------------------------------------------------------------------------------------------
|   0 | INSERT STATEMENT                     |              |  5307 | 68991 |  1792   (2)| 00:00:22 |        |      |            |
|   1 |  PX COORDINATOR                      |              |       |       |            |          |        |      |            |
|   2 |   PX SEND QC (RANDOM)                | :TQ10001     |  5307 | 68991 |  1792   (2)| 00:00:22 |  Q1,01 | P->S | QC (RAND)  |
|   3 |    LOAD AS SELECT                    | TEST_LOCK    |       |       |            |          |  Q1,01 | PCWP |            |
|   4 |     BUFFER SORT                      |              |       |       |            |          |  Q1,01 | PCWC |            |
|   5 |      PX RECEIVE                      |              |  5307 | 68991 |  1792   (2)| 00:00:22 |  Q1,01 | PCWP |            |
|   6 |       PX SEND ROUND-ROBIN            | :TQ10000     |  5307 | 68991 |  1792   (2)| 00:00:22 |        | S->P | RND-ROBIN  |
|   7 |        VIEW                          | USER_OBJECTS |  5307 | 68991 |  1792   (2)| 00:00:22 |        |      |            |
|   8 |         UNION-ALL                    |              |       |       |            |          |        |      |            |
|*  9 |          FILTER                      |              |       |       |            |          |        |      |            |
|* 10 |           TABLE ACCESS FULL          | OBJ$         |  6197 |   205K|  1790   (2)| 00:00:22 |        |      |            |
|* 11 |           TABLE ACCESS BY INDEX ROWID| IND$         |     1 |     9 |     2   (0)| 00:00:01 |        |      |            |
|* 12 |            INDEX UNIQUE SCAN         | I_IND1       |     1 |       |     1   (0)| 00:00:01 |        |      |            |
|* 13 |          INDEX RANGE SCAN            | I_LINK1      |     8 |    32 |     2   (0)| 00:00:01 |        |      |            |
----------------------------------------------------------------------------------------------------------------------------------
SQL>
SQL> alter table test_lock add text clob;
Table altered.
SQL> explain plan for insert /*+ parallel(test_lock,4) */ into test_lock (id) select object_id from user_objects;
Explained.
SQL> @s_xplan
執行計劃如下：
PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 2452769860
-----------------------------------------------------------------------------------------------
| Id  | Operation                      | Name         | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------
|   0 | INSERT STATEMENT               |              |  5307 | 68991 |  1792   (2)| 00:00:22 |
|   1 |  VIEW                          | USER_OBJECTS |  5307 | 68991 |  1792   (2)| 00:00:22 |
|   2 |   UNION-ALL                    |              |       |       |            |          |
|*  3 |    FILTER                      |              |       |       |            |          |
|*  4 |     TABLE ACCESS FULL          | OBJ$         |  6197 |   205K|  1790   (2)| 00:00:22 |
|*  5 |     TABLE ACCESS BY INDEX ROWID| IND$         |     1 |     9 |     2   (0)| 00:00:01 |
|*  6 |      INDEX UNIQUE SCAN         | I_IND1       |     1 |       |     1   (0)| 00:00:01 |
|*  7 |    INDEX RANGE SCAN            | I_LINK1      |     8 |    32 |     2   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------
SQL>
計劃中沒有px了．

SQL> explain plan for update /*+ parallel(test_lock,4) */ test_lock set id = id+100;
Explained.
SQL> @s_xplan
執行計劃如下：
PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 3152912665
----------------------------------------------------------------------------------------------------------------
| Id  | Operation             | Name      | Rows  | Bytes | Cost (%CPU)| Time     |    TQ  |IN-OUT| PQ Distrib |
----------------------------------------------------------------------------------------------------------------
|   0 | UPDATE STATEMENT      |           |    30 |   390 |     3   (0)| 00:00:01 |        |      |         |
|   1 |  PX COORDINATOR       |           |       |       |            |          |        |      |         |
|   2 |   PX SEND QC (RANDOM) | :TQ10000  |    30 |   390 |     3   (0)| 00:00:01 |  Q1,00 | P->S | QC (RAND)  |
|   3 |    UPDATE             | TEST_LOCK |       |       |            |          |  Q1,00 | PCWP |         |
|   4 |     PX BLOCK ITERATOR |           |    30 |   390 |     3   (0)| 00:00:01 |  Q1,00 | PCWC |         |
|   5 |      TABLE ACCESS FULL| TEST_LOCK |    30 |   390 |     3   (0)| 00:00:01 |  Q1,00 | PCWP |         |
----------------------------------------------------------------------------------------------------------------
是可以parallel．注意這時test_lock是有clob欄位的．
 
總結：一個表是否可以進行paralle dml是有很多限制的，這種限制需要大家來總結．
　　　從上面實驗可以看到：
　　　　　對於有clob欄位的表：parallel insert 是禁止的；parallel update 確是可以的．(我想對於blob應該也時一樣的)
另外：
<>的書中提到：對於Delete\update\Merge的操作，只有操作的物件是分割槽表時，Oracle才會啟用並行操作．
通過上面的測試後，parallel update 對於普通的表也時可以並行的．

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/195110/viewspace-745353/，如需轉載，請註明出處，否則將追究法律責任。

相關文章