Oracle delete語句調優一例

Oracle delete語句調優一例

今天剛上上班，就接到客戶的郵件，說生產環境中執行某一條delete sql語句的時間超過了3個小時。最後客戶無奈取消了這次資料清理，準備今天在申請時間重做。所以希望我在下午之前能夠調優一下sql語句。

我拿到sql語句。是一個簡單的delete語句，這個表是一個分區表，表中的資料大約有6億條，要刪除的資料大概有900多萬條。

delete event
          where cycle_code = 25
          and cycle_month = 2
          and cycle_year = 2015
          and customer_id = 5289835;
先來看看執行計畫
Plan hash value: 2439880320
 -----------------------------------------------------------------------------------------------------------------------
 | Id  | Operation                          | Name            | Rows  | Bytes | Cost (%CPU)| Time    | Pstart| Pstop |
 -----------------------------------------------------------------------------------------------------------------------
 |  0 | DELETE STATEMENT                    |                |  3238K|  135M|  404K  (1)| 01:20:52 |      |      |
 |  1 |  DELETE                            |      EVENT    |      |      |            |          |      |      |
 |  2 |  PARTITION RANGE ITERATOR          |                |  3238K|  135M|  404K  (1)| 01:20:52 |  241 |  261 |
 |*  3 |    TABLE ACCESS BY LOCAL INDEX ROWID|      EVENT    |  3238K|  135M|  404K  (1)| 01:20:52 |  241 |  261 |
 |*  4 |    INDEX RANGE SCAN                |      EVENT_1UQ |  1370K|      | 40255  (1)| 00:08:04 |  241 |  261 |
 -----------------------------------------------------------------------------------------------------------------------
發現走了索引掃描，看起來效能也不會差到哪去啊？
 從整體來看，從6億條記錄中刪除900多萬條資料，走索引掃描感覺感覺確實是不錯的選擇。

 首先查看了表的分區規則和基本的資料分布情況，
 分區規則是基於cycle_code,cycle_month,sub_partition_id這三個欄位，從查詢條件來看，cycle_code，cycle_month剛好就是分區欄位。
TABLE_NAME          PARTITION PARTITION_COUNT COLUMN_LIST                    PART_COUNTS SUBPAR_COUNT STATUS
 -------------------- --------- --------------- ------------------------------ ----------- ------------ ------
 EVENT              RANGE                721 CYCLE_CODE,CYCLE_MONTH,SUB_PAR TITION_ID          3            0 VALID                                             
資料分布的情況如下，根據分區邏輯，資料只可能在這20個分區中。
partition_name  high_value  tablespace_name      num_rows
 C25_M2_S1    25, 2, 5        DATAH01              84246910
 C25_M2_S2    25, 2, 10      DATAH01              3427570
 C25_M2_S3    25, 2, 15      DATAH01              3523431
 C25_M2_S4    25, 2, 20      DATAH01              3988140
 C25_M2_S5    25, 2, 25      DATAH01              2700687
 C25_M2_S6    25, 2, 30      DATAH01              2477792
 C25_M2_S7    25, 2, 35      DATAH01              2490349
C25_M2_S8    25, 2, 40      DATAH01              11755212
 C25_M2_S9    25, 2, 45      DATAH01              3184953
 C25_M2_S10  25, 2, 50      DATAH01              2656802
 C25_M2_S11  25, 2, 55      DATAH01              4434668
 C25_M2_S12  25, 2, 60      DATAH01              2776079
 C25_M2_S13  25, 2, 65      DATAH01              2949885
 C25_M2_S14  25, 2, 70      DATAH01              2837790
 C25_M2_S15  25, 2, 75      DATAH01              6285172
 C25_M2_S16  25, 2, 80      DATAH01              2743439
 C25_M2_S17  25, 2, 85      DATAH01              3574228
 C25_M2_S18  25, 2, 90      DATAH01              3600820
 C25_M2_S19  25, 2, 95      DATAH01              7415434
 C25_M2_S20  25, 2, 100      DATAH01              3446285

有了這些資訊，發現收穫還是不小的，我寫了一個指令碼，來嵌入customer_id這個欄位，來查看每個分區中需要刪除的資料情況，結果發現第一個分區有8千多萬條資料，查詢的時間很長，最後竟然沒有資料可以刪除，其它的分區測試的時候執行速度都很快。
 分區C25_M2_S8中的要刪除的資料有9百多萬，其它分區都沒有匹配的資料，從資料層面，我是沒法確定這些分區一定沒有可能插入新資料的。
 所以分析了上面的情況，我對分區C25_M2_S1做了特殊處理，按照執行計畫是走索引掃描的，因為查詢條件的範圍有點大，還沒有匹配的資料，所以我嘗試走全表掃描，開啟了並行，經過測試，發現速度還是很快的，基本在1分鐘左右就能夠很快過濾出資料來。
 所以從資料層面我提供的語句如下，把最大的分區放在了最後處理。
set linesize 200
 set timing on
 set time on
 alter session force parallel dml parallel 16;
 delete event partition(C25_M2_S2)  where cycle_code=25 and cycle_month=2 and cycle_year=2015 and customer_id=5289835;
 commit;
 delete event partition(C25_M2_S3)  where cycle_code=25 and cycle_month=2 and cycle_year=2015 and customer_id=5289835;
 commit;
 delete event partition(C25_M2_S4)  where cycle_code=25 and cycle_month=2 and cycle_year=2015 and customer_id=5289835;
 commit;
。。。。。。。
delete event partition(C25_M2_S20)  where cycle_code=25 and cycle_month=2 and cycle_year=2015 and customer_id=5289835;
 commit;
 delete /*+ full(rated_event) parallel(rated_event,16) */ event partition(C25_M2_S1)  where cycle_code=25 and cycle_month=2 and cycle_year=2015 and customer_id=5289835;
 commit;

事情到此一般就結束了，開發找到我，我們做了進一步的溝通，她根據我提供的指令碼提出了一些問題，她從業務層面來做了確認，說資料只會在C25_M2_S8這個分區上，有了業務確認，調優的語句就更加簡化了。
set linesize 200
set timing on
set time on
alter session force parallel dml parallel 16;
 delete event partition(C25_M2_S8)  where cycle_code=25 and cycle_month=2 and cycle_year=2015 and customer_id=5289835;
 commit;

查看執行計畫，合理的走了全表掃描，因為分區中有1千多萬的記錄，刪除900多萬的資料，走全表掃描還是情理之中的。
Plan hash value: 1742190108
 ----------------------------------------------------------------------------------------------------------------------------------
 | Id  | Operation            | Name        | Rows  | Bytes | Cost (%CPU)| Time    | Pstart| Pstop |    TQ  |IN-OUT| PQ Distrib |
 ----------------------------------------------------------------------------------------------------------------------------------
 |  0 | DELETE STATEMENT      |            |  9115K|  382M| 19351  (1)| 00:03:53 |      |      |        |      |            |
 |  1 |  PX COORDINATOR      |            |      |      |            |          |      |      |        |      |            |
 |  2 |  PX SEND QC (RANDOM) | :TQ10000    |  9115K|  382M| 19351  (1)| 00:03:53 |      |      |  Q1,00 | P->S | QC (RAND)  |
 |  3 |    DELETE            |      EVENT |      |      |            |          |      |      |  Q1,00 | PCWP |            |
 |  4 |    PX BLOCK ITERATOR |            |  9115K|  382M| 19351  (1)| 00:03:53 |  248 |  248 |  Q1,00 | PCWC |            |
 |*  5 |      TABLE ACCESS FULL|      EVENT |  9115K|  382M| 19351  (1)| 00:03:53 |  248 |  248 |  Q1,00 | PCWP |            |
 ----------------------------------------------------------------------------------------------------------------------------------

通過這個例子，我們可以看到原本索引掃描的執行計畫看起來很好，但是執行效率卻大打折扣，在分析了分區表的分區規則和資料分布情況之後，發現可以把原本700多個分區簡化到20個，加上業務層面的確認，本來20個分區的刪除可以簡化到有一個特定的分區，效能調優在這個時候就是一個接力棒式的工作。問題經過一步一步的分析和確認，也變得清晰起來。