【筆記】oracle 並行原理深入解析及案例精粹

leonarding發表於2012-08-10
筆記Oracle並行
引言：首先說明並行技術屬於大資料範疇，適合OLAP系統，在任務分割、資料塊分割、資源充裕的場合應用較廣，本次分享主要概括並行原理、實際應用、效能對比、並行直接載入、索引屬性、特點小結等六個小點去重點闡述。下面的測試是我的筆記，這些筆記也參考了《讓oracle跑的更快2》作者：譚懷遠 一書的引導，在此向譚總表示感謝，向幫助過我們的人表示感謝 zhixiang yangqiaojie等好友，下面我們就開始快樂的旅途！
一、簡單介紹OLTP和OLAP系統的特點小結
答：OLTP和OLAP是我們大家在日常生產庫中最常用到的2種系統，簡單的說OLTP是基於多事務短時間片的系統，記憶體的效率決定了資料庫的效率。
OLAP是基於大資料集長時間片的系統，SQL執行效率決定了資料庫的效率。因此說“並行parallel”技術屬於OLAP系統範疇
二、並行技術實現機制和場合
答：並行是相對於序列而言的，一個大的資料塊分割成n個小的資料塊，同時啟動n個程式分別處理n個資料塊，最後由並行協調器coordinater整合結果返回給使用者。實際上在一個並行執行的過程中還存在著並行程式之間的通訊問題（並行間的互動操作）。上面也說過並行是屬於大資料處理的技術適合OLAP，並不適合OLTP，因為OLTP系統中的sql執行效率通常都是非常高的。
三、測試並行技術在實際中的應用和規則
（1)在有索引的表leo_t上使用並行技術，但沒有起作用的情況
建立一張表
> create table leo_t as select rownum id ,object_name,object_type from dba_objects;
在表id列上建立索引
> create index leo_t_idx on leo_t(id);
收集表leo_t統計資訊
> execute dbms_stats.gather_table_stats(ownname=>'LS',tabname=>'LEO_T',method_opt=>'for all indexed columns size
2',cascade=>TRUE);
為表啟動4個並行度
> alter table leo_t parallel 4;
啟動執行計劃
> set autotrace trace explain stat
> select * from leo_t where id=100;  使用索引檢索的資料，並沒有啟動並行
Execution Plan  執行計劃
----------------------------------------------------------
Plan hash value: 2049660393
-----------------------------------------------------------------------------------------
| Id  | Operation                   | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |           |     1 |    28 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| LEO_T     |     1 |    28 |     2   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | LEO_T_IDX |     1 |       |     1   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("ID"=100)
Statistics   統計資訊
----------------------------------------------------------
          1  recursive calls
          0  db block gets
          4  consistent gets   4次一致性讀，即處理4個資料塊
          0  physical reads
          0  redo size
        544  bytes sent via SQL*Net to client
        381  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed
說明：我們在這個表上啟動了並行但沒有起作用是因為CBO最佳化器使用了B-tree索引來檢索的資料直接就定位到rowid（B-tree索引特點適合重複率比較低的欄位），所以才發生了4個一致性讀，發現使用索引效率非常高，資源代價比較小沒有使用並行的必要了。
（2)讀懂一個並行執行計劃
> select object_type,count(*) from leo_t group by object_type;  物件型別分組統計
35 rows selected.
Execution Plan   並行執行計劃
----------------------------------------------------------
Plan hash value: 852105030
------------------------------------------------------------------------------------------------------------------
| Id  | Operation                | Name     | Rows  | Bytes | Cost (%CPU)| Time     |    TQ  |IN-OUT| PQ Distrib |
------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT         |          | 10337 |   111K|     6  (17)| 00:00:01 |        |      |            |
|   1 |  PX COORDINATOR          |          |       |       |            |          |        |      |            |
|   2 |   PX SEND QC (RANDOM)    | :TQ10001 | 10337 |   111K|     6  (17)| 00:00:01 |  Q1,01 | P->S | QC (RAND)  |
|   3 |    HASH GROUP BY         |          | 10337 |   111K|     6  (17)| 00:00:01 |  Q1,01 | PCWP |            |
|   4 |     PX RECEIVE           |          | 10337 |   111K|     6  (17)| 00:00:01 |  Q1,01 | PCWP |            |
|   5 |      PX SEND HASH        | :TQ10000 | 10337 |   111K|     6  (17)| 00:00:01 |  Q1,00 | P->P | HASH       |
|   6 |       HASH GROUP BY      |          | 10337 |   111K|     6  (17)| 00:00:01 |  Q1,00 | PCWP |            |
|   7 |        PX BLOCK ITERATOR |          | 10337 |   111K|     5   (0)| 00:00:01 |  Q1,00 | PCWC |            |
|   8 |         TABLE ACCESS FULL| LEO_T    | 10337 |   111K|     5   (0)| 00:00:01 |  Q1,00 | PCWP |            |
------------------------------------------------------------------------------------------------------------------
Statistics   統計資訊
----------------------------------------------------------
         44  recursive calls
          0  db block gets
        259  consistent gets  259次一致性讀，即處理259個資料塊
          0  physical reads
          0  redo size
       1298  bytes sent via SQL*Net to client
        403  bytes received via SQL*Net from client
          4  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
         35  rows processed
ps -ef | grep oracle  從後臺程式上看也能發現起了4個並行程式和1個協調程式
oracle   25075     1  0 22:58 ?        00:00:00 ora_p000_LEO
oracle   25077     1  0 22:58 ?        00:00:00 ora_p001_LEO
oracle   25079     1  0 22:58 ?        00:00:00 ora_p002_LEO
oracle   25081     1  0 22:58 ?        00:00:00 ora_p003_LEO
oracle   25083     1  0 22:58 ?        00:00:00 ora_p004_LEO
說明：在進行分組整理的select中，會處理大量的資料集（發生了259次一致性讀），這時使用並行來分割資料塊處理可以提高效率，因此oracle使用了並行技術，解釋一下並行執行計劃步驟，並行執行計劃應該從下往上讀，當看見PX（parallel execution）關鍵字說明使用了並行技術
1.首先全表掃描
2.並行程式以迭代iterator的方式訪問資料塊，並將掃描結果提交給父程式做hash group
3.並行父程式對子程式傳遞過來的資料做hash group操作
4.並行子程式（PX SEND HASH）將處理完的資料傳送出去，子和父是相對而言的，我們定義傳送端為子程式，接收端為父程式
5.並行父程式（PX RECEIVE）將處理完的資料接收
6.按照隨機順序傳送給並行協調程式QC（query coordinator）整合結果（物件型別分組統計）
7.完畢後QC將整合結果返回給使用者
說明並行執行計劃中特有的IN-OUT列的含義（指明瞭操作中資料流的方向）              
Parallel to Serial（P->S）: 表示一個並行操作向一個序列操作傳送資料，通常是將並行結果傳送給並行排程程式QC進行彙總
Parallel to Parallel（P->P）：表示一個並行操作向另一個並行操作傳送資料，一般是並行父程式與並行子程式之間的資料交流。
Parallel Combined with parent(PCWP): 同一個從屬程式執行的並行操作，同時父操作也是並行的。
Parallel Combined with Child(PCWC): 同一個從屬程式執行的並行操作，同時子操作也是並行的。
Serial to Parallel（S->P）: 表示一個序列操作向一個並行操作傳送資料，如果select部分是序列操作，就會出現這個情況
（3）介紹4個我們常用的並行初始化引數
parallel_min_percent           50%    表示指定SQL並行度最小閥值才能執行，如果沒有達到這個閥值，oracle將會報ora-12827錯誤
parallel_adaptive_multi_user  TRUE    表示按照系統資源情況動態調整SQL並行度，已取得最好的執行效能
parallel_instance_group               表示在幾個例項間起並行
parallel_max_servers          100     表示整個資料庫例項的並行程式數不能超過這個值
parallel_min_servers          0       表示資料庫啟動時初始分配的並行程式數，如果我們設定的並行度小於這個值，並行協調程式會按我們的並行度來分配並行程式數，如果我們設定的並行度大於這個值，並行協調程式會額外啟動其他的並行程式來滿足我們的需求
（4）使用hint方式測試DML並行查詢效能
首先說一下什麼時候可以使用並行技術
1.物件屬性：在建立的時候，就指定了並行關鍵字，長期有效
2.sql強制執行：在sql中使用hint提示方法使用並行，臨時有效，它是約束sql語句的執行方式，本次測試就是使用的hint方式
> select /*+ parallel(leo_t 4) */ count(*) from leo_t where object_name in (select /*+ parallel(leo_t1 4) */ object_name from
leo_t1);
Execution Plan   執行計劃
----------------------------------------------------------
Plan hash value: 3814758652
-------------------------------------------------------------------------------------------------------------------
| Id  | Operation                 | Name     | Rows  | Bytes | Cost (%CPU)| Time     |    TQ  |IN-OUT| PQ Distrib |
-------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT          |          |     1 |    94 |    16   (0)| 00:00:01 |        |      |            |
|   1 |  SORT AGGREGATE           |          |     1 |    94 |            |          |        |      |            |
|   2 |   PX COORDINATOR          |          |       |       |            |          |        |      |            |
|   3 |    PX SEND QC (RANDOM)    | :TQ10002 |     1 |    94 |            |          |  Q1,02 | P->S | QC (RAND)  |
|   4 |     SORT AGGREGATE        |          |     1 |    94 |            |          |  Q1,02 | PCWP |            |
|*  5 |      HASH JOIN SEMI       |          | 10337 |   948K|    16   (0)| 00:00:01 |  Q1,02 | PCWP |            |
|   6 |       PX RECEIVE          |          | 10337 |   282K|     5   (0)| 00:00:01 |  Q1,02 | PCWP |            |
|   7 |        PX SEND HASH       | :TQ10000 | 10337 |   282K|     5   (0)| 00:00:01 |  Q1,00 | P->P | HASH       |
|   8 |         PX BLOCK ITERATOR |          | 10337 |   282K|     5   (0)| 00:00:01 |  Q1,00 | PCWC |            |
|   9 |          TABLE ACCESS FULL| LEO_T    | 10337 |   282K|     5   (0)| 00:00:01 |  Q1,00 | PCWP |            |
|  10 |       PX RECEIVE          |          | 10700 |   689K|    11   (0)| 00:00:01 |  Q1,02 | PCWP |            |
|  11 |        PX SEND HASH       | :TQ10001 | 10700 |   689K|    11   (0)| 00:00:01 |  Q1,01 | P->P | HASH       |
|  12 |         PX BLOCK ITERATOR |          | 10700 |   689K|    11   (0)| 00:00:01 |  Q1,01 | PCWC |            |
|  13 |          TABLE ACCESS FULL| LEO_T1   | 10700 |   689K|    11   (0)| 00:00:01 |  Q1,01 | PCWP |            |
-------------------------------------------------------------------------------------------------------------------
並行先掃描子查詢leo_t1表，然後對主查詢leo_t表進行掃描，按照隨機順序傳送到並行協調程式QC整合結果，最後將結果返回給使用者
Predicate Information (identified by operation id):
---------------------------------------------------
   5 - access("OBJECT_NAME"="OBJECT_NAME")
Note
-----
   - dynamic sampling used for this statement
Statistics   統計資訊
----------------------------------------------------------
         28  recursive calls
          0  db block gets
        466  consistent gets   466次一致性讀，即處理了446個資料塊
          0  physical reads
          0  redo size
        413  bytes sent via SQL*Net to client
        381  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          2  sorts (memory)
          0  sorts (disk)
          1  rows processed
（5）並行DDL測試
使用10046事件生成文法追蹤檔案，level 12：包括sql語句解析、執行、提取、提交和回滾與等待事件，這是最高階別，而且向下相容
10046事件解釋：10046 event是oracle用於系統效能分析的重要事件。當啟用這個事件後，將通知oracle kernel追蹤會話的相關即時資訊，並寫入到相應trace檔案中。這些有用的資訊主要包括sql是如何進行解析，繫結變數的使用情況，會話中發生的等待事件等10046event 可分成不同的級別（level），分別追蹤記錄不同程度的有用資訊。對於這些不同的級別，應當注意的是向下相容的，即高一級的trace資訊包含低於此級的所有資訊。
啟動10046事件命令：alter session set events '10046 trace name context forever,level 12';
關閉10046事件命令：alter session set events '10046 trace name context off';
注：oracle提供了一個tkprof工具來對trace檔案進行格式化翻譯，過濾出有用的資訊
> alter session set events '10046 trace name context forever,level 12';
Session altered.
表物件屬性，在建立的時候就直接指定好了並行度，後面我們會從trace檔案中看出，已經列出了sql解析、執行、取操作的效能指標，後面又列出了等待事件，在等待事件中我們可以看到PX並行等待事件，說明使用了並行技術執行
> create table leo_t2 parallel 4 as select * from dba_objects;
Table created.
格式化trace檔案
[oracle@secdb1 udump]$ pwd
/u01/app/oracle/admin/LEO/udump
[oracle@secdb1 udump]$ tkprof leo_ora_20558.trc leo.txt sys=no
TKPROF: Release 10.2.0.1.0 - Production on Sat Aug 4 14:54:21 2012
Copyright (c) 1982, 2005, Oracle.  All rights reserved.
輸出內容
create table leo_t2 parallel 4 as select * from dba_objects
call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.01       0.03          0          0          1           0
Execute      1      0.41       4.26        199       2985       1176       10336
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        2      0.42       4.29        199       2985       1177       10336
Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 27
Elapsed times include waiting on following events:
  Event waited on  等待時間列表               Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  os thread startup                               7        0.21          0.43
  PX Deq: Join ACK          連線應答              5        0.01          0.05
  PX qref latch             閂                    2        0.01          0.01
  PX Deq: Parse Reply       解析回覆              4        0.17          0.23
  enq: PS - contention                            1        0.00          0.00
  PX Deq: Execute Reply     執行回覆              12        1.01          2.24
  rdbms ipc reply                                 3        0.13          0.33
  db file scattered read                          3        0.00          0.00
  log file sync             日誌檔案同步          2        0.00          0.00
  PX Deq: Signal ACK        訊號應答              4        0.01          0.01
  SQL*Net message to client                       1        0.00          0.00
  SQL*Net message from client                     1        0.00          0.00
********************************************************************************
索引物件屬性，在建立索引的時候使用並行可以大大提高執行的效率，前提是系統資源充裕，否則可能適得其反哦：）
機制：把全部索引分成4份給4個並行程式去處理，把處理完的資料隨機順序發給QC整合結果，最後QC把最終結果返回給使用者，完成sql操作
建立B-tree索引
> create index leo2_t_index on leo_t2(object_id) parallel 4;
Index created.
重建索引
> alter index leo2_t_index rebuild parallel 4;
Index altered.
輸出內容
create index leo2_t_index on leo_t2(object_id) parallel 4
call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        2      0.02       0.06          0          3          0           0
Execute      2      0.11       4.72         80        632        471           0
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        4      0.14       4.79         80        635        471           0
Misses in library cache during parse: 2
Optimizer mode: ALL_ROWS
Parsing user id: 27
Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  os thread startup                              10        0.04          0.25
  PX Deq: Join ACK                               10        0.01          0.02
  enq: PS - contention                            4        0.00          0.00
  PX qref latch                                  37        0.09          0.37
  PX Deq: Parse Reply                             7        0.01          0.06
  PX Deq: Execute Reply                          81        1.96          3.15
  PX Deq: Table Q qref                            3        0.24          0.24
  log file sync                                   2        0.00          0.00
  PX Deq: Signal ACK                              6        0.00          0.01
  latch: session allocation                       1        0.01          0.01
  SQL*Net message to client                       2        0.00          0.00
  SQL*Net message from client                     2        0.00          0.00
********************************************************************************
alter index leo2_t_index rebuild parallel 4

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        2      0.02       0.09          0         54          6           0
Execute      2      0.03       0.83        122        390        458           0
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        4      0.05       0.93        122        444        464           0
Misses in library cache during parse: 2
Optimizer mode: ALL_ROWS
Parsing user id: 27
Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  enq: PS - contention                            3        0.00          0.00
  PX Deq: Parse Reply                             3        0.00          0.00
  PX Deq: Execute Reply                          84        0.06          0.40
  PX qref latch                                   3        0.08          0.09
  PX Deq: Table Q qref                            4        0.00          0.01
  log file sync                                   5        0.00          0.00
  PX Deq: Signal ACK                              7        0.00          0.00
  reliable message                                2        0.00          0.00
  enq: RO - fast object reuse                     2        0.00          0.00
  db file sequential read                         2        0.00          0.00
  rdbms ipc reply                                 4        0.00          0.00
  SQL*Net message to client                       2        0.00          0.00
  SQL*Net message from client                     2        0.00          0.00
********************************************************************************
（6）並行DML測試
前提：首先說明oracle對並行操作是有限制的，必須設定啟用會話並行度，否則即使SQL指定了並行，oracle也不會執行DML並行操作
其次oracle只對partition table分割槽表做並行處理（有幾個分割槽就開幾個並行），普通表oracle不做並行處理，只限delete update merge操作
> alter session enable parallel dml;  啟動會話並行度
Session altered.
我的表leo_t1是普通表，liusheng_hash分割槽表（包括10個分割槽）
> explain plan for delete /*+ parallel(leo_t1 2) */ from leo_t1;
Explained.
> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT   執行計劃，對於普通表即使設定了並行度，oracle也不做並行處理，看還是使用的全表掃描
--------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 3964128955
---------------------------------------------------------------------
| Id  | Operation          | Name   | Rows  | Cost (%CPU)| Time     |
---------------------------------------------------------------------
|   0 | DELETE STATEMENT   |        | 10700 |    40   (0)| 00:00:01 |
|   1 |  DELETE            | LEO_T1 |       |            |          |
|   2 |   TABLE ACCESS FULL| LEO_T1 | 10700 |    40   (0)| 00:00:01 |
---------------------------------------------------------------------
Note
-----
   - dynamic sampling used for this statement
> explain plan for delete /*+ parallel(liusheng_hash 2) */ from liusheng_hash;
Explained.
> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT   執行計劃，oracle對於分割槽表是做並行處理的，從in-out欄位上也可以看出並行全表掃描
--------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 1526574995
----------------------------------------------------------------------------------------------------------------------------
| Id  | Operation             | Name          | Rows  | Cost (%CPU)| Time     | Pstart| Pstop |    TQ  |IN-OUT| PQ Distrib |
----------------------------------------------------------------------------------------------------------------------------
|   0 | DELETE STATEMENT      |               | 10996 |    26   (0)| 00:00:01 |       |       |        |      |            |
|   1 |  PX COORDINATOR       |               |       |            |          |       |       |        |      |            |
|   2 |   PX SEND QC (RANDOM) | :TQ10000      | 10996 |    26   (0)| 00:00:01 |       |       |  Q1,00 | P->S | QC (RAND)  |
|   3 |    DELETE             | LIUSHENG_HASH |       |            |          |       |       |  Q1,00 | PCWP |            |
|   4 |     PX BLOCK ITERATOR |               | 10996 |    26   (0)| 00:00:01 |     1 |    10 |  Q1,00 | PCWC |            |
|   5 |      TABLE ACCESS FULL| LIUSHENG_HASH | 10996 |    26   (0)| 00:00:01 |     1 |    10 |  Q1,00 | PCWP |            |
----------------------------------------------------------------------------------------------------------------------------
Note
-----
   - dynamic sampling used for this statement
> explain plan for update /*+ parallel(liusheng_hash 4) */ liusheng_hash set object_name=object_name||' ';
Explained.
> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT    執行計劃  更新操作也是一樣
--------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 225854777
------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation             | Name          | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |    TQ  |IN-OUT| PQ Distrib |
------------------------------------------------------------------------------------------------------------------------------------
|   0 | UPDATE STATEMENT      |               | 10996 |   708K|    13   (0)| 00:00:01 |       |       |        |      |            |
|   1 |  PX COORDINATOR       |               |       |       |            |          |       |       |        |      |            |
|   2 |   PX SEND QC (RANDOM) | :TQ10000      | 10996 |   708K|    13   (0)| 00:00:01 |       |       |  Q1,00 | P->S | QC (RAND)  |
|   3 |    UPDATE             | LIUSHENG_HASH |       |       |            |          |       |       |  Q1,00 | PCWP |            |
|   4 |     PX BLOCK ITERATOR |               | 10996 |   708K|    13   (0)| 00:00:01 |     1 |    10 |  Q1,00 | PCWC |            |
|   5 |      TABLE ACCESS FULL| LIUSHENG_HASH | 10996 |   708K|    13   (0)| 00:00:01 |     1 |    10 |  Q1,00 | PCWP |            |
------------------------------------------------------------------------------------------------------------------------------------
Note
-----
   - dynamic sampling used for this statement
接下來做insert並行測試，在insert測試中只有insert into ...... select ......做並行才有意義，insert into ......values ......單條插入沒有意義
> explain plan for insert /*+ parallel(leo_t1 4) */ into leo_t1 select /*+ parallel(leo_t2 4) */ * from leo_t2;
Explained.
> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT   執行計劃 insert和select操作別分使用了並行，它們是相互獨立的互不干涉
--------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 1922268564
-----------------------------------------------------------------------------------------------------------------
| Id  | Operation               | Name     | Rows  | Bytes | Cost (%CPU)| Time     |    TQ  |IN-OUT| PQ Distrib |
-----------------------------------------------------------------------------------------------------------------
|   0 | INSERT STATEMENT        |          | 10409 |  1799K|    11   (0)| 00:00:01 |        |      |            |
|   1 |  PX COORDINATOR         |          |       |       |            |          |        |      |            |
|   2 |   PX SEND QC (RANDOM)   | :TQ10001 | 10409 |  1799K|    11   (0)| 00:00:01 |  Q1,01 | P->S | QC (RAND)  |
|   3 |    LOAD AS SELECT       | LEO_T1   |       |       |            |          |  Q1,01 | PCWP |            |
|   4 |     PX RECEIVE          |          | 10409 |  1799K|    11   (0)| 00:00:01 |  Q1,01 | PCWP |            |
|   5 |      PX SEND ROUND-ROBIN| :TQ10000 | 10409 |  1799K|    11   (0)| 00:00:01 |  Q1,00 | P->P | RND-ROBIN  |
|   6 |       PX BLOCK ITERATOR |          | 10409 |  1799K|    11   (0)| 00:00:01 |  Q1,00 | PCWC |            |
|   7 |        TABLE ACCESS FULL| LEO_T2   | 10409 |  1799K|    11   (0)| 00:00:01 |  Q1,00 | PCWP |            |
-----------------------------------------------------------------------------------------------------------------
Note
-----
   - dynamic sampling used for this statement
下面的insert語句沒有在select使用並行，那麼我們看看select語句是否用的序列操作
> explain plan for insert /*+ parallel(leo_t1 4) */ into leo_t1 select * from leo_t2;
Explained.
> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT   執行計劃的in-out(程式間資料流)中可以看出S->P：Serial to Parallel一個序列操作（全表掃描）向一個並行操作傳送資料，例如
select子句是序列操作，所以就會出現這種情況
--------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 2695467291
------------------------------------------------------------------------------------------------------------------
| Id  | Operation                | Name     | Rows  | Bytes | Cost (%CPU)| Time     |    TQ  |IN-OUT| PQ Distrib |
------------------------------------------------------------------------------------------------------------------
|   0 | INSERT STATEMENT         |          | 10409 |  1799K|    40   (0)| 00:00:01 |        |      |            |
|   1 |  PX COORDINATOR          |          |       |       |            |          |        |      |            |
|   2 |   PX SEND QC (RANDOM)    | :TQ10001 | 10409 |  1799K|    40   (0)| 00:00:01 |  Q1,01 | P->S | QC (RAND)  |
|   3 |    LOAD AS SELECT        | LEO_T1   |       |       |            |          |  Q1,01 | PCWP |            |
|   4 |     BUFFER SORT          |          |       |       |            |          |  Q1,01 | PCWC |            |
|   5 |      PX RECEIVE          |          | 10409 |  1799K|    40   (0)| 00:00:01 |  Q1,01 | PCWP |            |
|   6 |       PX SEND ROUND-ROBIN| :TQ10000 | 10409 |  1799K|    40   (0)| 00:00:01 |        | S->P | RND-ROBIN  |
|   7 |        TABLE ACCESS FULL | LEO_T2   | 10409 |  1799K|    40   (0)| 00:00:01 |        |      |            |
------------------------------------------------------------------------------------------------------------------
Note
-----
   - dynamic sampling used for this statement
下面的insert語句沒有在insert使用並行，讓我們看看效果怎麼樣
> explain plan for insert into leo_t1 select /*+ parallel(leo_t2 4) */ * from leo_t2;
Explained.
> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT   執行計劃 TABLE ACCESS FULL - PCWP 全表掃描用的是並行，PX SEND QC (RANDOM) - P->S 表示一個並行操作向一個序列操作傳送數
據，這就表示了我們先用並行select後面insert用的是序列了
--------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 985193522
--------------------------------------------------------------------------------------------------------------
| Id  | Operation            | Name     | Rows  | Bytes | Cost (%CPU)| Time     |    TQ  |IN-OUT| PQ Distrib |
--------------------------------------------------------------------------------------------------------------
|   0 | INSERT STATEMENT     |          | 10409 |  1799K|    11   (0)| 00:00:01 |        |      |            |
|   1 |  PX COORDINATOR      |          |       |       |            |          |        |      |            |
|   2 |   PX SEND QC (RANDOM)| :TQ10000 | 10409 |  1799K|    11   (0)| 00:00:01 |  Q1,00 | P->S | QC (RAND)  |
|   3 |    PX BLOCK ITERATOR |          | 10409 |  1799K|    11   (0)| 00:00:01 |  Q1,00 | PCWC |            |
|   4 |     TABLE ACCESS FULL| LEO_T2   | 10409 |  1799K|    11   (0)| 00:00:01 |  Q1,00 | PCWP |            |
--------------------------------------------------------------------------------------------------------------
Note
-----
   - dynamic sampling used for this statement
（7）使用並行的3種方法
1.hint 方式 臨時有效
> set autotrace trace exp
> select /*+ parallel(leo_t1 4) */ * from leo_t1;
> select /*+ parallel(leo_t1 4) */ count(*) from leo_t1;
Execution Plan   執行計劃 hint方式
----------------------------------------------------------
Plan hash value: 2648044456
--------------------------------------------------------------------------------------------------------
| Id  | Operation              | Name     | Rows  | Cost (%CPU)| Time     |    TQ  |IN-OUT| PQ Distrib |
--------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT       |          |     1 |    11   (0)| 00:00:01 |        |      |            |
|   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 |          | 10700 |    11   (0)| 00:00:01 |  Q1,00 | PCWC |            |
|   6 |       TABLE ACCESS FULL| LEO_T1   | 10700 |    11   (0)| 00:00:01 |  Q1,00 | PCWP |            |
--------------------------------------------------------------------------------------------------------
Note
-----
   - dynamic sampling used for this statement
2.alter table 定義方式 長期有效
> alter table leo_t1 parallel 4;
Table altered.
> select count(*) from leo_t1;
Execution Plan   執行計劃 定義方式
----------------------------------------------------------
Plan hash value: 2648044456
--------------------------------------------------------------------------------------------------------
| Id  | Operation              | Name     | Rows  | Cost (%CPU)| Time     |    TQ  |IN-OUT| PQ Distrib |
--------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT       |          |     1 |    11   (0)| 00:00:01 |        |      |            |
|   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 |          | 10700 |    11   (0)| 00:00:01 |  Q1,00 | PCWC |            |
|   6 |       TABLE ACCESS FULL| LEO_T1   | 10700 |    11   (0)| 00:00:01 |  Q1,00 | PCWP |            |
--------------------------------------------------------------------------------------------------------
Note
-----
   - dynamic sampling used for this statement
3.alter session force parallel   強制定義並行度
> alter table leo_t1 parallel 1;       首先我們已經修改並行度為1
Table altered.
> alter session force parallel query parallel 4;     再次強制定義並行度為4
Session altered.
> select count(*) from leo_t1;
Execution Plan   執行計劃  強制使用並行度4執行SQL
----------------------------------------------------------
Plan hash value: 2648044456
--------------------------------------------------------------------------------------------------------
| Id  | Operation              | Name     | Rows  | Cost (%CPU)| Time     |    TQ  |IN-OUT| PQ Distrib |
--------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT       |          |     1 |    11   (0)| 00:00:01 |        |      |            |
|   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 |          | 10700 |    11   (0)| 00:00:01 |  Q1,00 | PCWC |            |
|   6 |       TABLE ACCESS FULL| LEO_T1   | 10700 |    11   (0)| 00:00:01 |  Q1,00 | PCWP |            |
--------------------------------------------------------------------------------------------------------
Note
-----
   - dynamic sampling used for this statement
（8）/*+ append */直接載入
直接載入：指資料不經過db_buffer_cache記憶體區，直接寫入到資料檔案中，實際上是直接追加到資料段的最後，不在段中尋找空閒空間而插入
> create table leo_t3 as select * from dba_objects;               建立表leo_t3
Table created.
> insert /*+ append*/ into leo_t3 select * from dba_objects;      直接載入資料
10337 rows created.
> create table leo_t4 as select * from leo_t3 where rownum<10000; 建立表leo_t4
Table created.
> select segment_name,extent_id,bytes from user_extents where segment_name='LEO_T4';  表leo_t4佔用了16個區
SEGMENT_NAME                                                                       EXTENT_ID      BYTES
--------------------------------------------------------------------------------- ---------- ----------
LEO_T4                                                                                     0      65536
LEO_T4                                                                                     1      65536
LEO_T4                                                                                     2      65536
LEO_T4                                                                                     3      65536
LEO_T4                                                                                     4      65536
LEO_T4                                                                                     5      65536
LEO_T4                                                                                     6      65536
LEO_T4                                                                                     7      65536
LEO_T4                                                                                     8      65536
LEO_T4                                                                                     9      65536
LEO_T4                                                                                    10      65536
LEO_T4                                                                                    11      65536
LEO_T4                                                                                    12      65536
LEO_T4                                                                                    13      65536
LEO_T4                                                                                    14      65536
LEO_T4                                                                                    15      65536
LEO_T4                                                                                    16    1048576
> delete from leo_t4;    刪除所有記錄
9999 rows deleted.
> commit;                提交
Commit complete.
> select segment_name,extent_id,bytes from user_extents where segment_name='LEO_T4'; 刪除之後為什麼還佔用16個區呢，我來解釋一下，oracle在delete操作後資料並沒有真實的刪除了。只是打上一個“標記”說明這些資料不可用了，也說明了為什麼刪除之後磁碟空間沒有回收的問題。
SEGMENT_NAME                                                                       EXTENT_ID      BYTES
--------------------------------------------------------------------------------- ---------- ----------
LEO_T4                                                                                     0      65536
LEO_T4                                                                                     1      65536
LEO_T4                                                                                     2      65536
LEO_T4                                                                                     3      65536
LEO_T4                                                                                     4      65536
LEO_T4                                                                                     5      65536
LEO_T4                                                                                     6      65536
LEO_T4                                                                                     7      65536
LEO_T4                                                                                     8      65536
LEO_T4                                                                                     9      65536
LEO_T4                                                                                    10      65536
LEO_T4                                                                                    11      65536
LEO_T4                                                                                    12      65536
LEO_T4                                                                                    13      65536
LEO_T4                                                                                    14      65536
LEO_T4                                                                                    15      65536
LEO_T4                                                                                    16    1048576
> insert into leo_t4 select * from leo_t3 where rownum<10000;   傳統載入 oracle會找段中的空閒空間插入資料，看還是利舊了原來的16個區
9999 rows created.
> select segment_name,extent_id,bytes from user_extents where segment_name='LEO_T4';
SEGMENT_NAME                                                                       EXTENT_ID      BYTES
--------------------------------------------------------------------------------- ---------- ----------
LEO_T4                                                                                     0      65536
LEO_T4                                                                                     1      65536
LEO_T4                                                                                     2      65536
LEO_T4                                                                                     3      65536
LEO_T4                                                                                     4      65536
LEO_T4                                                                                     5      65536
LEO_T4                                                                                     6      65536
LEO_T4                                                                                     7      65536
LEO_T4                                                                                     8      65536
LEO_T4                                                                                     9      65536
LEO_T4                                                                                    10      65536
LEO_T4                                                                                    11      65536
LEO_T4                                                                                    12      65536
LEO_T4                                                                                    13      65536
LEO_T4                                                                                    14      65536
LEO_T4                                                                                    15      65536
LEO_T4                                                                                    16    1048576
> delete from leo_t4;   刪除所有記錄
9999 rows deleted.
> commit;
Commit complete.
> select count(*) from leo_t4;   記錄數為0
  COUNT(*)
----------
         0
> select segment_name,extent_id,bytes from user_extents where segment_name='LEO_T4'; 這個表還是佔用16個區，資料塊有資料但是可以覆
蓋，我們認為是空閒的塊
SEGMENT_NAME                                                                       EXTENT_ID      BYTES
--------------------------------------------------------------------------------- ---------- ----------
LEO_T4                                                                                     0      65536
LEO_T4                                                                                     1      65536
LEO_T4                                                                                     2      65536
LEO_T4                                                                                     3      65536
LEO_T4                                                                                     4      65536
LEO_T4                                                                                     5      65536
LEO_T4                                                                                     6      65536
LEO_T4                                                                                     7      65536
LEO_T4                                                                                     8      65536
LEO_T4                                                                                     9      65536
LEO_T4                                                                                    10      65536
LEO_T4                                                                                    11      65536
LEO_T4                                                                                    12      65536
LEO_T4                                                                                    13      65536
LEO_T4                                                                                    14      65536
LEO_T4                                                                                    15      65536
LEO_T4                                                                                    16    1048576
> insert /*+ append */ into leo_t4 select * from leo_t3 where rownum<10000;  直接載入方式，oracle把新資料直接插入到新的20個區裡了，並沒有使用原來的16個區空閒塊，也就應了不在段中尋找空閒塊插入
9999 rows created.
> commit; 必須commit之後，oracle才講HWM高水位線移動到新資料塊之上，如果沒有commit，oracle不會移動HWM高水位線，因此看不到資料字典裡面的變化（也就是不顯示後面的20個區），如果此時回滾的話，HWM高水位線不用動，就想什麼都沒有發生一樣
Commit complete.
>  select segment_name,extent_id,bytes from user_extents where segment_name='LEO_T4';
SEGMENT_NAME                                                                       EXTENT_ID      BYTES
--------------------------------------------------------------------------------- ---------- ----------
LEO_T4                                                                                     0      65536
LEO_T4                                                                                     1      65536
LEO_T4                                                                                     2      65536
LEO_T4                                                                                     3      65536
LEO_T4                                                                                     4      65536
LEO_T4                                                                                     5      65536
LEO_T4                                                                                     6      65536
LEO_T4                                                                                     7      65536
LEO_T4                                                                                     8      65536
LEO_T4                                                                                     9      65536
LEO_T4                                                                                    10      65536
LEO_T4                                                                                    11      65536
LEO_T4                                                                                    12      65536
LEO_T4                                                                                    13      65536
LEO_T4                                                                                    14      65536
LEO_T4                                                                                    15      65536
LEO_T4                                                                                    16      65536
LEO_T4                                                                                    17      65536
LEO_T4                                                                                    18      65536
LEO_T4                                                                                    19      65536
LEO_T4                                                                                    20      65536
LEO_T4                                                                                    21      65536
LEO_T4                                                                                    22      65536
LEO_T4                                                                                    23      65536
LEO_T4                                                                                    24      65536
LEO_T4                                                                                    25      65536
LEO_T4                                                                                    26      65536
LEO_T4                                                                                    27      65536
LEO_T4                                                                                    28      65536
LEO_T4                                                                                    29      65536
LEO_T4                                                                                    30      65536
LEO_T4                                                                                    31      65536
LEO_T4                                                                                    32      65536
LEO_T4                                                                                    33      65536
LEO_T4                                                                                    34      65536
LEO_T4                                                                                    35      65536
LEO_T4                                                                                    36      65536
37 rows selected.
（9）/*+ append */直接載入和redo
> create table leo_t5 as select object_id,object_name from dba_objects;  建立表leo_t5
Table created.
> create table leo_t6 as select object_id,object_name from dba_objects;  建立表leo_t6
Table created.
> alter table leo_t5 logging;  設定產生redo日誌模式
Table altered.
> truncate table leo_t5;  截斷表
Table truncated.
> set autotrace trace stat;  啟動統計資訊
insert into leo_t5 select * from leo_t6;  傳統載入
>
10340 rows created.
Statistics   統計資訊
----------------------------------------------------------
        197  recursive calls
        185  db block gets
         92  consistent gets
         60  physical reads
      37128  redo size                    37128 redo量
        664  bytes sent via SQL*Net to client
        571  bytes received via SQL*Net from client
          4  SQL*Net roundtrips to/from client
          3  sorts (memory)
          0  sorts (disk)
      10340  rows processed
> rollback;   回滾
Rollback complete.
> insert /*+ append */ into leo_t5 select * from leo_t6;  直接載入
10340 rows created.
Statistics
----------------------------------------------------------
        111  recursive calls
        180  db block gets
         79  consistent gets
         21  physical reads
      36640  redo size                    36640 redo量
        664  bytes sent via SQL*Net to client
        585  bytes received via SQL*Net from client
          4  SQL*Net roundtrips to/from client
          2  sorts (memory)
          0  sorts (disk)
      10340  rows processed
小結：我們看到傳統載入和直接載入產生的redo量並沒有太大的差異，因為只要底層資料塊發生變化，就會生成redo資訊，不管傳統和直接都會修改資料塊，用來恢復依據，所以並沒有太大的差異。
（10）直接載入和索引
> set autotrace trace stat;
> insert /*+ append */ into leo_t5 select * from leo_t6;   直接載入，但表上沒有索引
10340 rows created.
Statistics   統計資訊
----------------------------------------------------------
        111  recursive calls
        175  db block gets
         81  consistent gets
         15  physical reads
      36816  redo size              36816 redo量
        664  bytes sent via SQL*Net to client
        585  bytes received via SQL*Net from client
          4  SQL*Net roundtrips to/from client
          2  sorts (memory)
          0  sorts (disk)
      10340  rows processed
> create index leo_t5_index on leo_t5(object_id);     給表建立索引
Index created.
> rollback;  回滾
Rollback complete.
> insert /*+ append */ into leo_t5 select * from leo_t6;  直接載入，但表上有索引
10340 rows created.
Statistics   統計資訊
----------------------------------------------------------
        120  recursive calls
        193  db block gets
         85  consistent gets
         22  physical reads
      37344  redo size              37344 redo量
        664  bytes sent via SQL*Net to client
        585  bytes received via SQL*Net from client
          4  SQL*Net roundtrips to/from client
          3  sorts (memory)
          0  sorts (disk)
      10340  rows processed
小結：因為有了索引，直接載入redo量比沒有索引時有一定的提升，可能是我的測試資料少所以這種提升並不明顯，如果在實際生產庫上發生了大量的redo，建議先將索引drop，載入資料後，在重建rebuild索引
（11）直接載入和並行
直接載入和並行是可以一起使用的，以此大幅度提高sql執行效率
> alter session enable parallel dml;   設定會話並行度
Session altered.
> alter session set events '10046 trace name context forever,level 12';   使用trace檔案跟蹤sql效能指標
Session altered.
> insert /*+ append parallel(leo_t5,2) */ into leo_t5 select * from leo_t6; 直接載入+並行插入
10340 rows created.
> rollback;
Rollback complete.
> insert /*+ parallel(leo_t5,2) */ into leo_t5 select * from leo_t6;  並行插入
10340 rows created.
> rollback;
Rollback complete.
> insert /*+ append */ into leo_t5 select * from leo_t6;  直接載入
10340 rows created.
> rollback;
Rollback complete.
> insert into leo_t5 select * from leo_t6;  什麼特性也沒有用
10340 rows created.
> commit;    提交
Commit complete.
[oracle@secdb1 udump]$ tkprof leo_ora_20558.trc leo.txt sys=no      格式化trace檔案
TKPROF: Release 10.2.0.1.0 - Production on Sun Aug 5 22:13:38 2012
Copyright (c) 1982, 2005, Oracle.  All rights reserved.
insert /*+ append parallel(leo_t5,2) */ into leo_t5 select * from leo_t6
call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.01          0          1          0           0
Execute      1      0.03       2.51          8         46         67       10340
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        2      0.04       2.53          8         47         67       10340
Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 27
Rows     Row Source Operation
-------  ---------------------------------------------------
      2  PX COORDINATOR  (cr=46 pr=0 pw=0 time=2201632 us)
      0   PX SEND QC (RANDOM) :TQ10001 (cr=0 pr=0 pw=0 time=0 us)
      0    LOAD AS SELECT  (cr=0 pr=0 pw=0 time=0 us)
      0     BUFFER SORT (cr=0 pr=0 pw=0 time=0 us)
      0      PX RECEIVE  (cr=0 pr=0 pw=0 time=0 us)
      0       PX SEND ROUND-ROBIN :TQ10000 (cr=0 pr=0 pw=0 time=0 us)
  10340        TABLE ACCESS FULL LEO_T6 (cr=42 pr=0 pw=0 time=1356361 us)
insert /*+ parallel(leo_t5,2) */ into leo_t5 select * from leo_t6
call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.01          0          1          0           0
Execute      1      0.02       1.66          7         44         64       10340
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        2      0.02       1.67          7         45         64       10340
Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 27
Rows     Row Source Operation
-------  ---------------------------------------------------
      2  PX COORDINATOR  (cr=44 pr=0 pw=0 time=1209712 us)
      0   PX SEND QC (RANDOM) :TQ10001 (cr=0 pr=0 pw=0 time=0 us)
      0    LOAD AS SELECT  (cr=0 pr=0 pw=0 time=0 us)
      0     BUFFER SORT (cr=0 pr=0 pw=0 time=0 us)
      0      PX RECEIVE  (cr=0 pr=0 pw=0 time=0 us)
      0       PX SEND ROUND-ROBIN :TQ10000 (cr=0 pr=0 pw=0 time=0 us)
  10340        TABLE ACCESS FULL LEO_T6 (cr=42 pr=0 pw=0 time=186185 us)
insert /*+ append */ into leo_t5 select * from leo_t6
call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          1          0           0
Execute      1      0.06       0.24         62        113        373       10340
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        2      0.06       0.24         62        114        373       10340
Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 27
Rows     Row Source Operation
-------  ---------------------------------------------------
      1  LOAD AS SELECT  (cr=113 pr=62 pw=39 time=241775 us)
  10340   TABLE ACCESS FULL LEO_T6 (cr=42 pr=0 pw=0 time=62104 us)   沒有使用並行操作
insert into leo_t5 select * from leo_t6
call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0         43          0           0
Execute      1      0.14       0.54        100        101       1022       10340
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        2      0.15       0.55        100        144       1022       10340
Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 27
Rows     Row Source Operation
-------  ---------------------------------------------------
  10340  TABLE ACCESS FULL LEO_T6 (cr=42 pr=0 pw=0 time=744520 us)   只有全表掃描
小結：insert /*+ append parallel(leo_t5,2) */ into leo_t5 select * from leo_t6和insert /*+ parallel(leo_t5,2) */ into leo_t5 select * from leo_t6執行計劃是一樣的，因為當使用parallel並行插入時，oracle預設使用直接載入方式來載入資料，因此append關鍵字可忽略了。
注：如果執行alter session disable parallel dml; oracle就會禁用DML並行操作，就算有hint提示也不會起作用，那麼insert /*+ append parallel(leo_t5,2) */和insert /*+ append */的執行計劃都應該是一樣的了，都是隻有直接載入，沒有並行效果了
（12）直接載入和sqlload
sqlload 是我們常用的文字載入工具，它可以把文字檔案按照一定的格式批次載入到資料庫中去，現在我們測試傳統載入conventional、直接載入direct、並行parallel直接載入的效能對比和執行效率。
-rwxrwxrwx   1 oracle oinstall       283 Aug  9 00:11 leo_test.ctl     控制檔案
-rwxrwxrwx   1 oracle oinstall   8983596 Aug  8 20:57 leo_test.data    資料檔案，10萬行資料，9個欄位
-rwxrwxrwx   1 oracle oinstall      2099 Aug  9 00:15 leo_test.log     日誌檔案
1.傳統載入conventional   10萬行記錄->表LEO_TEST_SQLLOAD  
 
sqlldr userid=ls/ls control=leo_test.ctl     傳統載入資料
>  select count(*) from leo_test_sqlload;
  COUNT(*)
----------
    100000
SQL*Loader: Release 10.2.0.1.0 - Production on Thu Aug 9 00:14:15 2012
Copyright (c) 1982, 2005, Oracle.  All rights reserved.
Control File:   leo_test.ctl                   控制檔案
Data File:      /home/oracle/leo_test.data     資料檔案
  Bad File:     leo_test.bad                   壞檔案
  Discard File:  none specified
 (Allow all discards)
Number to load: ALL
Number to skip: 0
Errors allowed: 50
Bind array:     64 rows, maximum of 256000 bytes
Continuation:    none specified
Path used:      Conventional                     sqlload採用傳統載入方式，資料要透過緩衝區載入到表中
Table LEO_TEST_SQLLOAD, loaded from every logical record.
Insert option in effect for this table: APPEND   採用追加的方式載入，新資料不覆蓋舊資料，而是結尾累加
TRAILING NULLCOLS option in effect
   Column Name                  Position   Len  Term Encl Datatype             列資訊
------------------------------ ---------- ----- ---- ---- ---------------------
START_TIME                          FIRST     *   |       DATE YYYY-MM-DD HH24:MI:SS
END_TIME                             NEXT     *   |       DATE YYYY-MM-DD HH24:MI:SS
PROTOCOL                             NEXT     *   |       CHARACTER
PRIVATE_IP                           NEXT     *   |       CHARACTER
PRIVATE_PORT                         NEXT     *   |       CHARACTER
SRC_IP                               NEXT     *   |       CHARACTER
SRC_PORT                             NEXT     *   |       CHARACTER
DEST_IP                              NEXT     *   |       CHARACTER
DEST_PORT                            NEXT     *   |       CHARACTER
Table LEO_TEST_SQLLOAD:
  100000 Rows successfully loaded.               10萬行記錄成功載入
  0 Rows not loaded due to data errors.
  0 Rows not loaded because all WHEN clauses were failed.
  0 Rows not loaded because all fields were null.
Space allocated for bind array:                 148608 bytes(64 rows)
Read   buffer bytes: 1048576
Total logical records skipped:          0
Total logical records read:        100000
Total logical records rejected:         0
Total logical records discarded:        0
Run began on Thu Aug 09 00:14:15 2012
Run ended on Thu Aug 09 00:15:21 2012
Elapsed time was:     00:01:05.60                耗時65秒
CPU time was:         00:00:00.81
2.直接載入direct   10萬行記錄->表LEO_TEST_SQLLOAD1
> select df.tablespace_name "表空間名",totalspace "總空間M",freespace "剩餘空間M",round((1-freespace/totalspace)*100,2) "使用率%"
from (select tablespace_name,round(sum(bytes)/1024/1024) totalspace from dba_data_files group by tablespace_name) df,
(select tablespace_name,round(sum(bytes)/1024/1024) freespace from dba_free_space group by tablespace_name) fs
 where df.tablespace_name=fs.tablespace_name order by df.tablespace_name ;  2    3    4    5
表空間名                           總空間M     剩餘空間M     使用率%
------------------------------ ---------- ------------- ----------
CTXSYS                                 32            27      15.63
EXAMPLE                               200           199         .5
SYSAUX                                325           266      18.15
SYSTEM                                325            84      74.15
UNDOTBS                               200           189        5.5
USERS                                 600           501       16.5  沒有載入表leo_test_sqlload1之前空間情況
sqlldr userid=ls/ls control=leo_test1.ctl data=leo_test.data log=leo_test1.log direct=true    直接載入10萬行資料
> select count(*) from leo_test_sqlload1;
  COUNT(*)
----------
    100000 （3M）
>  select df.tablespace_name "表空間名",totalspace "總空間M",freespace "剩餘空間M",round((1-freespace/totalspace)*100,2) "使用
率%"
from
(select tablespace_name,round(sum(bytes)/1024/1024) totalspace from dba_data_files group by tablespace_name) df,
 (select tablespace_name,round(sum(bytes)/1024/1024) freespace from dba_free_space group by tablespace_name) fs
 where df.tablespace_name=fs.tablespace_name order by df.tablespace_name ;   2    3    4    5
表空間名                          總空間M      剩餘空間M    使用率%
------------------------------ ---------- ------------- ----------
CTXSYS                                 32            27      15.63
EXAMPLE                               200           199         .5
SYSAUX                                325           266      18.15
SYSTEM                                325            84      74.15
UNDOTBS                               200           189        5.5
USERS                                 600           498         17   10萬行記錄載入後使用了3M空間
SQL*Loader: Release 10.2.0.1.0 - Production on Thu Aug 9 01:07:52 2012
Copyright (c) 1982, 2005, Oracle.  All rights reserved.
Control File:   leo_test1.ctl        控制檔案
Data File:      leo_test.data        資料檔案，10萬行資料，9個欄位
  Bad File:     leo_test.bad         壞檔案
  Discard File:  none specified
 (Allow all discards)
Number to load: ALL
Number to skip: 0
Errors allowed: 50
Continuation:    none specified
Path used:      Direct               sqlload採用直接載入方式，資料不透過緩衝區和sql語法引擎直接載入到表中
Table LEO_TEST_SQLLOAD1, loaded from every logical record.
Insert option in effect for this table: APPEND     採用追加的方式載入，新資料不覆蓋舊資料，而是結尾累加
TRAILING NULLCOLS option in effect
   Column Name                  Position   Len  Term Encl Datatype             列資訊
------------------------------ ---------- ----- ---- ---- ---------------------
START_TIME                          FIRST     *   |       DATE YYYY-MM-DD HH24:MI:SS
END_TIME                             NEXT     *   |       DATE YYYY-MM-DD HH24:MI:SS
PROTOCOL                             NEXT     *   |       CHARACTER
PRIVATE_IP                           NEXT     *   |       CHARACTER
PRIVATE_PORT                         NEXT     *   |       CHARACTER
SRC_IP                               NEXT     *   |       CHARACTER
SRC_PORT                             NEXT     *   |       CHARACTER
DEST_IP                              NEXT     *   |       CHARACTER
DEST_PORT                            NEXT     *   |       CHARACTER
Table LEO_TEST_SQLLOAD1:
  100000 Rows successfully loaded.                     10萬行記錄成功載入，佔用3M磁碟空間
  0 Rows not loaded due to data errors.
  0 Rows not loaded because all WHEN clauses were failed.
  0 Rows not loaded because all fields were null.
  Date cache:
   Max Size:      1000
   Entries :        65
   Hits    :    199935
   Misses  :         0
Bind array size not used in direct path.
Column array  rows :    5000
Stream buffer bytes:  256000
Read   buffer bytes: 1048576
Total logical records skipped:          0
Total logical records read:        100000
Total logical records rejected:         0
Total logical records discarded:        0
Total stream buffers loaded by SQL*Loader main thread:       26
Total stream buffers loaded by SQL*Loader load thread:       17
Run began on Thu Aug 09 01:07:52 2012
Run ended on Thu Aug 09 01:07:56 2012
Elapsed time was:     00:00:03.53                           耗時3秒.53  比  傳統載入65秒節約了94%時間
CPU time was:         00:00:00.25
小結：因此我們知道直接載入要比傳統載入執行效率高很多，當我們的系統負載不高，資源充裕時可以考慮使用直接載入direct方式批次匯入資料，即減少了I/O和記憶體開銷，又提高了資料載入效率。
3.並行直接載入direct   10萬行記錄->表LEO_TEST_SQLLOAD2
表空間名                          總空間M      剩餘空間M    使用率%
------------------------------ ---------- ------------- ----------
USERS                                 600           498         17    沒有載入前表空間的空間狀態
sqlldr userid=ls/ls control=leo_test2.ctl data=leo_test.data log=leo_test2.log direct=true parallel=true     並行直接載入10萬行資料
> select count(*) from leo_test_sqlload2;
  COUNT(*)
----------
    100000 （8M）
表空間名                          總空間M      剩餘空間M    使用率%
------------------------------ ---------- ------------- ----------
USERS                                 600           490      18.33    10萬行記錄載入後使用了8M空間
SQL*Loader: Release 10.2.0.1.0 - Production on Thu Aug 9 07:25:00 2012
Copyright (c) 1982, 2005, Oracle.  All rights reserved.
Control File:   leo_test2.ctl         控制檔案
Data File:      leo_test.data         資料檔案，10萬行資料，9個欄位
  Bad File:     leo_test.bad          壞檔案
  Discard File:  none specified
 (Allow all discards)
Number to load: ALL
Number to skip: 0
Errors allowed: 50
Continuation:    none specified
Path used:      Direct - with parallel option.    sqlload採用並行+直接載入方式，既有並行，又有直接載入，雙重功效，效率更高
Table LEO_TEST_SQLLOAD2, loaded from every logical record.
Insert option in effect for this table: APPEND     採用追加的方式載入，新資料不覆蓋舊資料，而是結尾累加
TRAILING NULLCOLS option in effect
   Column Name                  Position   Len  Term Encl Datatype             列資訊
------------------------------ ---------- ----- ---- ---- ---------------------
START_TIME                          FIRST     *   |       DATE YYYY-MM-DD HH24:MI:SS
END_TIME                             NEXT     *   |       DATE YYYY-MM-DD HH24:MI:SS
PROTOCOL                             NEXT     *   |       CHARACTER
PRIVATE_IP                           NEXT     *   |       CHARACTER
PRIVATE_PORT                         NEXT     *   |       CHARACTER
SRC_IP                               NEXT     *   |       CHARACTER
SRC_PORT                             NEXT     *   |       CHARACTER
DEST_IP                              NEXT     *   |       CHARACTER
DEST_PORT                            NEXT     *   |       CHARACTER
Table LEO_TEST_SQLLOAD2:
  100000 Rows successfully loaded.                        10萬行記錄成功載入，佔用8M磁碟空間
  0 Rows not loaded due to data errors.
  0 Rows not loaded because all WHEN clauses were failed.
  0 Rows not loaded because all fields were null.
  Date cache:
   Max Size:      1000
   Entries :        65
   Hits    :    199935
   Misses  :         0
Bind array size not used in direct path.
Column array  rows :    5000
Stream buffer bytes:  256000
Read   buffer bytes: 1048576
Total logical records skipped:          0
Total logical records read:        100000
Total logical records rejected:         0
Total logical records discarded:        0
Total stream buffers loaded by SQL*Loader main thread:       26
Total stream buffers loaded by SQL*Loader load thread:       17
Run began on Thu Aug 09 07:25:00 2012
Run ended on Thu Aug 09 07:25:13 2012
Elapsed time was:     00:00:12.77                           耗時00:00:12.77  比  直接載入3秒.53節約了93%時間
CPU time was:         00:00:00.98
小結：從時間成本上我們就可看出，並行直接載入效率要遠遠的高出序列直接載入，在海量資料的環境中使用並行和直接載入的技術，對提高效率和效能那是如虎添翼（並行並不一定比序列好，主要看業務型別其次看資源情況），我們應該思考“理解技術如何為業務服務”，這要比單純學技術更加重要，謝謝！！！
（12）sqlload直接載入對索引的影響
所謂對索引的影響是指使用sqlload載入存在索引的表的資料時索引是否有效
非約束索引：sqlload直接載入完畢後維護索引的完整性，此時索引不失效
約束索引：例如 主鍵 外來鍵 唯一索引 sqlload直接載入完畢後，資料會入庫但索引會失效unusable，此時要重建索引
1.非約束索引，直接載入完畢後維護索引的完整性，此時索引不失效
> select count(*) from leo_test_sqlload1;           表中有10條記錄
  COUNT(*)
----------
    100000
> create index leo_test_sqlload1_index on leo_test_sqlload1(private_ip);   在private_ip上建立B-tree索引
Index created.
> select status from user_indexes where table_name='LEO_TEST_SQLLOAD1';    檢查索引的有效性valid
STATUS
--------
VALID
sqlldr userid=ls/ls control=leo_test1.ctl data=leo_test.data log=leo_test1.log direct=true  直接載入後會維護索引的完整性
SQL*Loader: Release 10.2.0.1.0 - Production on Thu Aug 9 15:27:03 2012
Copyright (c) 1982, 2005, Oracle.  All rights reserved.
Load completed - logical record count 100000.          10萬行記錄成功載入
> select count(*) from leo_test_sqlload1;        現在表中有20萬條記錄
  COUNT(*)
----------
    200000
> select status from user_indexes where table_name='LEO_TEST_SQLLOAD1';    自動維護索引的有效性vaild，對非約束索引而言
STATUS
--------
VALID
2.約束索引：例如 主鍵 外來鍵 唯一索引 sqlload直接載入完畢後，資料會入庫但索引會失效unusable，此時要重建索引
> create table leo_test_sqlload3
(
START_TIME date,
END_TIME date,
PROTOCOL varchar(20),
PRIVATE_IP varchar(20),
PRIVATE_PORT varchar(20) constraint pk_leo_test_sqlload3 primary key ,    我們建立一個帶主鍵的表
SRC_IP varchar(20),
SRC_PORT varchar(20),
DEST_IP varchar(20),
DEST_PORT varchar(20)
);
Table created.
> select * from leo_test_sqlload3;                                   現在表中沒有資料
no rows selected
sqlldr userid=ls/ls control=leo_test3.ctl data=leo_test1.data log=leo_test3.log direct=true
SQL*Loader: Release 10.2.0.1.0 - Production on Thu Aug 9 15:49:10 2012
Copyright (c) 1982, 2005, Oracle.  All rights reserved.
Load completed - logical record count 100.                100行記錄成功載入
> select * from leo_test_sqlload3;                  資料已載入，但PRIVATE_PORT主鍵索引已經失效，因為我們的值全是一樣的
START_TIME             END_TIME               PR PRIVATE_IP   PRIV SRC_IP       SRC_PORT DEST_IP      DEST
---------------------- ---------------------- -- ------------ ---- ------------ -------- ------------ ----
2012-08-08 20:59:54    2012-08-08 21:00:28    6  2886756061   1111 3395517721   45031    3419418065   80
2012-08-08 21:00:26    2012-08-08 21:00:28    6  2886900807   1111 3395507143   51733    3658060738   80
2012-08-08 20:59:55    2012-08-08 21:00:28    6  2886917742   1111 3395507159   43516    2071873572   80
2012-08-08 20:59:55    2012-08-08 21:00:28    6  2886917742   1111 3395507159   43534    2071873572   80
2012-08-08 20:59:55    2012-08-08 21:00:28    6  2886917742   1111 3395507159   43523    2071873572   80
2012-08-08 21:00:14    2012-08-08 21:00:28    6  2886832065   1111 3395507109   51442    2099718013   80
2012-08-08 21:00:26    2012-08-08 21:00:28    6  2886794376   1111 3395507104   57741    2071819251   80
2012-08-08 21:00:27    2012-08-08 21:00:28    6  2886758392   1111 3395517723   56875    1007173560   80
2012-08-08 21:00:22    2012-08-08 21:00:28    6  2886862137   1111 3395517760   17744    3626142915   7275
2012-08-08 21:00:25    2012-08-08 21:00:28    6  2886741689   1111 3395517708   14954    2007469330   80
2012-08-08 21:00:27    2012-08-08 21:00:28    6  2886891044   1111 3395517787   23626    1872834975   443
2012-08-08 21:00:26    2012-08-08 21:00:28    6  2886790049   1111 3395507100   54215    1884995806   80
2012-08-08 21:00:15    2012-08-08 21:00:28    6  2886771544   1111 3395507083   32261    1872832004   80
2012-08-08 21:00:24    2012-08-08 21:00:28    6  2886796616   1111 3395517729   18634    2007467546   80
2012-08-08 21:00:26    2012-08-08 21:00:28    6  2886839912   1111 3395507117   10102    1850510469   5242
2012-08-08 21:00:23    2012-08-08 21:00:28    6  2886742978   1111 3395517709   28276    1021181676   80
2012-08-08 21:00:16    2012-08-08 21:00:28    6  2886792600   1111 3395507103   15204    974546887    80
2012-08-08 21:00:23    2012-08-08 21:00:28    6  2886890096   1111 3395517786   30741    1884983225   80
2012-08-08 21:00:00    2012-08-08 21:00:28    6  2886743885   1111 3395517710   18678    1884968358   80
2012-08-08 21:00:16    2012-08-08 21:00:28    6  2886792600   1111 3395507103   15237    974547338    80
2012-08-08 21:00:10    2012-08-08 21:00:28    6  2886828509   1111 3395507106   30179    2007493616   80
2012-08-08 21:00:25    2012-08-08 21:00:28    6  2886811814   1111 3395517743   34249    2072702869   80
2012-08-08 20:59:57    2012-08-08 21:00:28    6  2886780595   1111 3395507091   63169    1872834775   80
2012-08-08 21:00:26    2012-08-08 21:00:28    6  2886745283   1111 3395517711   38566    1863134645   80
2012-08-08 21:00:26    2012-08-08 21:00:28    6  2886852868   1111 3395507129   19216    989566331    80
2012-08-08 21:00:22    2012-08-08 21:00:28    6  2886758076   1111 3395517723   37910    3061190502   80
2012-08-08 21:00:22    2012-08-08 21:00:28    6  2886758076   1111 3395517723   37886    2079006794   80
2012-08-08 21:00:25    2012-08-08 21:00:28    6  2886788330   1111 3395507099   15078    460553383    80
2012-08-08 21:00:26    2012-08-08 21:00:28    6  2886756269   1111 3395517721   57538    2008813541   80
2012-08-08 21:00:26    2012-08-08 21:00:28    6  2886906371   1111 3395507148   65509    1884961048   80
2012-08-08 20:59:51    2012-08-08 21:00:28    6  2886893244   1111 3395517789   27585    2071802397   995
2012-08-08 21:00:26    2012-08-08 21:00:28    6  2886810351   1111 3395517742   10465    1971814472   80
2012-08-08 21:00:26    2012-08-08 21:00:28    6  2886908390   1111 3395507150   58599    3419418057   80
2012-08-08 21:00:11    2012-08-08 21:00:28    6  2886811967   1111 3395517743   43433    2099759129   80
2012-08-08 21:00:27    2012-08-08 21:00:28    6  2886908416   1111 3395507150   60161    1027056891   80
2012-08-08 21:00:24    2012-08-08 21:00:28    6  2886794472   1111 3395507104   63499    1872769542   80
2012-08-08 21:00:26    2012-08-08 21:00:28    6  2886859643   1111 3395507135   41589    1008470934   80
2012-08-08 21:00:26    2012-08-08 21:00:28    6  2886908926   1111 3395507151   26758    1027061456   80
2012-08-08 21:00:27    2012-08-08 21:00:28    6  2886844821   1111 3395507121   48598    989542829    80
2012-08-08 21:00:14    2012-08-08 21:00:28    6  2886811914   1111 3395517743   40207    2071819051   80
2012-08-08 21:00:27    2012-08-08 21:00:28    6  2886776231   1111 3395507087   57398    1027061476   80
2012-08-08 21:00:21    2012-08-08 21:00:28    6  2886895128   1111 3395507138   31084    1020918811   80
2012-08-08 21:00:27    2012-08-08 21:00:28    6  2886896369   1111 3395507139   41560    2071819499   80
2012-08-08 21:00:15    2012-08-08 21:00:28    6  2886866997   1111 3395517764   53220    1008528500   80
2012-08-08 21:00:26    2012-08-08 21:00:28    6  2886733364   1111 3395517700   27617    1850417510   80
2012-08-08 21:00:26    2012-08-08 21:00:28    6  2886763900   1111 3395507076   21749    2072679568   80
2012-08-08 21:00:24    2012-08-08 21:00:28    6  2886848688   1111 3395507125   24485    460553373    80
2012-08-08 20:59:50    2012-08-08 21:00:28    6  2886866792   1111 3395517764   40930    2072313366   80
2012-08-08 20:59:55    2012-08-08 21:00:28    6  2886917742   1111 3395507159   43536    2071873572   80
2012-08-08 20:59:55    2012-08-08 21:00:28    6  2886917742   1111 3395507159   43542    2071873572   80
2012-08-08 20:59:53    2012-08-08 21:00:28    6  2886801934   1111 3395517734   17623    2007483189   8080
2012-08-08 20:59:55    2012-08-08 21:00:28    6  2886917742   1111 3395507159   43537    2071873572   80
2012-08-08 21:00:26    2012-08-08 21:00:28    6  2886886283   1111 3395517782   58048    2071816694   80
2012-08-08 21:00:26    2012-08-08 21:00:28    6  2886735314   1111 3395517702   16591    2071799544   80
2012-08-08 20:59:55    2012-08-08 21:00:28    6  2886917742   1111 3395507159   43524    2071873572   80
2012-08-08 21:00:20    2012-08-08 21:00:28    6  2886849684   1111 3395507126   20262    2008825959   80
2012-08-08 21:00:27    2012-08-08 21:00:28    6  2886872604   1111 3395517770   5537     3419418056   80
2012-08-08 21:00:27    2012-08-08 21:00:28    6  2886853794   1111 3395507130   10753    2099722272   80
2012-08-08 21:00:27    2012-08-08 21:00:28    6  2886755008   1111 3395517720   45872    1883357744   80
2012-08-08 21:00:21    2012-08-08 21:00:28    6  2886895128   1111 3395507138   31121    2078933535   80
2012-08-08 21:00:26    2012-08-08 21:00:28    6  2886864839   1111 3395517762   51804    1850417452   80
2012-08-08 21:00:19    2012-08-08 21:00:28    6  2886858061   1111 3395507134   10700    2071819372   80
2012-08-08 21:00:27    2012-08-08 21:00:28    6  2886776231   1111 3395507087   57410    1027061476   80
2012-08-08 21:00:27    2012-08-08 21:00:28    6  2886858854   1111 3395507134   58306    1020914578   80
2012-08-08 21:00:21    2012-08-08 21:00:28    6  2886774805   1111 3395507086   35831    1883303354   80
2012-08-08 21:00:27    2012-08-08 21:00:28    6  2886794557   1111 3395507105   4593     3708103499   80
2012-08-08 21:00:27    2012-08-08 21:00:28    6  2886747135   1111 3395517713   21641    2099740446   80
2012-08-08 21:00:27    2012-08-08 21:00:28    6  2886863802   1111 3395517761   53630    1863145458   5224
2012-08-08 21:00:22    2012-08-08 21:00:28    6  2886911235   1111 3395507153   37254    2095615735   21
2012-08-08 21:00:27    2012-08-08 21:00:28    6  2886860043   1111 3395507136   1581     294986889    5223
2012-08-08 20:59:56    2012-08-08 21:00:28    6  2886780595   1111 3395507091   63161    1883302610   80
2012-08-08 21:00:27    2012-08-08 21:00:28    6  2886732547   1111 3395517699   42653    294986856    5223
2012-08-08 20:59:54    2012-08-08 21:00:28    6  2886734208   1111 3395517701   14230    2007484922   80
2012-08-08 21:00:26    2012-08-08 21:00:28    6  2886866964   1111 3395517764   51273    2072105082   80
2012-08-08 21:00:00    2012-08-08 21:00:28    6  2886780595   1111 3395507091   63144    1872834775   80
2012-08-08 21:00:27    2012-08-08 21:00:28    6  2886914262   1111 3395507156   26777    2072104968   80
2012-08-08 20:59:54    2012-08-08 21:00:28    6  2886734208   1111 3395517701   14273    2007484922   80
2012-08-08 21:00:25    2012-08-08 21:00:28    6  2886847997   1111 3395507124   47084    2021394494   80
2012-08-08 21:00:21    2012-08-08 21:00:28    6  2886785128   1111 3395507096   15002    294986849    5223
2012-08-08 21:00:25    2012-08-08 21:00:28    6  2886783177   1111 3395507094   26001    2072101596   443
2012-08-08 21:00:26    2012-08-08 21:00:28    6  2886735924   1111 3395517702   53178    1850417918   80
2012-08-08 21:00:09    2012-08-08 21:00:28    6  2886837532   1111 3395507114   59353    2071819198   80
2012-08-08 21:00:26    2012-08-08 21:00:28    6  2886891515   1111 3395517787   51880    1884983223   80
2012-08-08 21:00:27    2012-08-08 21:00:28    6  2886737305   1111 3395517704   8009     1872834975   443
2012-08-08 21:00:16    2012-08-08 21:00:28    6  2886755910   1111 3395517721   35947    2918544417   80
2012-08-08 21:00:27    2012-08-08 21:00:28    6  2886771117   1111 3395507083   6645     1884960474   80
2012-08-08 21:00:20    2012-08-08 21:00:28    6  2886785801   1111 3395507096   55430    2099718013   80
2012-08-08 21:00:24    2012-08-08 21:00:28    6  2886756061   1111 3395517721   45056    3419418065   80
2012-08-08 21:00:14    2012-08-08 21:00:28    6  2886771706   1111 3395507083   41990    1883302599   80
2012-08-08 20:59:55    2012-08-08 21:00:28    6  2886917742   1111 3395507159   43511    2071873572   80
2012-08-08 21:00:26    2012-08-08 21:00:28    6  2886853131   1111 3395507129   34983    296567345    443
2012-08-08 20:59:55    2012-08-08 21:00:28    6  2886917742   1111 3395507159   43538    2071873572   80
2012-08-08 21:00:23    2012-08-08 21:00:28    6  2886857519   1111 3395507133   42212    460553373    80
2012-08-08 21:00:26    2012-08-08 21:00:28    6  2886886465   1111 3395517783   4972     989566680    80
2012-08-08 21:00:25    2012-08-08 21:00:28    6  2886753976   1111 3395517719   47964    1884981528   80
2012-08-08 20:59:56    2012-08-08 21:00:28    6  2886809185   1111 3395517741   4537     2071872692   80
2012-08-08 21:00:26    2012-08-08 21:00:28    6  2886840353   1111 3395507117   36547    1027051331   80
2012-08-08 21:00:20    2012-08-08 21:00:28    6  2886840637   1111 3395507117   53634    1872832059   80
2012-08-08 21:00:19    2012-08-08 21:00:28    6  2886876032   1111 3395517773   19163    1884968518   80
2012-08-08 21:00:19    2012-08-08 21:00:28    6  2886876032   1111 3395517773   19158    1884968518   80
100 rows selected.
> select index_name,index_type,status from user_indexes where table_name='LEO_TEST_SQLLOAD3';  我們建立主鍵已經失效
INDEX_NAME                     INDEX_TYPE                  STATUS
------------------------------ --------------------------- --------
PK_LEO_TEST_SQLLOAD3           NORMAL                      UNUSABLE
3.sqlload並行+直接載入存在索引的表，此時載入會失敗，skip_index_maintenance=true引數可以跳過索引維護完成載入，此時索引狀態unusable需要手工重建rebuild
create table leo_test_sqlload4                                        定義一個有主鍵的表
(
START_TIME date,
END_TIME date,
PROTOCOL varchar(20),
PRIVATE_IP varchar(20),
PRIVATE_PORT varchar(20) constraint pk_leo_test_sqlload4 primary key ,
SRC_IP varchar(20),
SRC_PORT varchar(20),
DEST_IP varchar(20),
DEST_PORT varchar(20)
);
sqlldr userid=ls/ls control=leo_test4.ctl data=leo_test1.data log=leo_test4.log direct=true parallel=true   並行+直接載入
SQL*Loader: Release 10.2.0.1.0 - Production on Thu Aug 9 16:19:25 2012
Copyright (c) 1982, 2005, Oracle.  All rights reserved.
SQL*Loader-951: Error calling once/load initialization              報錯：載入初始化引數錯誤
ORA-26002: Table LS.LEO_TEST_SQLLOAD4 has index defined upon it.    表上有索引定義，所以載入會失敗
> select index_name,index_type,status from user_indexes where table_name='LEO_TEST_SQLLOAD4';
INDEX_NAME                     INDEX_TYPE                  STATUS
------------------------------ --------------------------- --------
PK_LEO_TEST_SQLLOAD4           NORMAL                      VALID     現在索引還是有效的
sqlldr userid=ls/ls control=leo_test4.ctl data=leo_test1.data log=leo_test4.log direct=true parallel=true skip_index_maintenance=true;
SQL*Loader: Release 10.2.0.1.0 - Production on Thu Aug 9 16:30:52 2012
Copyright (c) 1982, 2005, Oracle.  All rights reserved.
Load completed - logical record count 100.   使用skip_index_maintenance=true跳過索引維護，100行記錄成功載入
> select count(*) from leo_test_sqlload4;
  COUNT(*)
----------
       100
> select index_name,index_type,status from user_indexes where table_name='LEO_TEST_SQLLOAD4';
INDEX_NAME                     INDEX_TYPE                  STATUS
------------------------------ --------------------------- --------
PK_LEO_TEST_SQLLOAD4           NORMAL                      UNUSABLE   載入後索引狀態變成unusable需要手工重建rebuild
小結：我們在sqlload工具載入資料時一定要關注表上是否有索引，並且是什麼型別的，正像世界萬物一樣，沒有完美的工具，有得必有失，如果提高效能就會索引失效，如果要維護索引的完整性那麼就會增加效能開銷，我們要做的更加細心、嚴謹、謙虛，以不變應萬變。

Leonarding
2012.8.10
天津&summer
分享技術~收穫快樂
Blog：http://space.itpub.net/26686207
 
 
 

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

相關文章