幫助理解 Oracle 中的解釋計劃

我在一些大表中執行查詢，雖然它執行良好，即使是大量數據，我想了解它的哪一部分對執行有影響。不幸的是，我不太擅長解釋計劃，所以我尋求幫助。

以下是有關這些表的一些數據：

• history_state_table 7.424.65行（其中僅剩下13.412t1.alarm_type = 'AT1'行）
• costumer_price_history 448.284.169行
• cycle_table 215行

這將是查詢（不要介意邏輯，僅供參考）：

SELECT t1.id_alarm, t2.load_id, t2.reference_date
 FROM history_state_table t1,
      (SELECT   op_code, contract_num,
                COUNT (DISTINCT id_ponto) AS num_pontos,
                COUNT
                   (DISTINCT CASE
                       WHEN vlr > 0
                          THEN id_ponto
                       ELSE NULL
                    END
                   ) AS bigger_than_zero,
                MAX (load_id) AS load_id,
                MAX (reference_date) AS reference_date
           FROM costumer_price_history
          WHERE load_id IN
                           (42232, 42234, 42236, 42238, 42240, 42242, 42244) /* arbitrary IDs depending on execution*/
            AND sistema = 'F1'          /* Hardcoded filters */
            AND rec_type = 'F3'         /* Hardcoded filters */
            AND description = 'F3'      /* Hardcoded filters */
            AND extract_type IN
                   ('T1', 'T2', 'T3')
       GROUP BY op_code, contract_num) t2
WHERE t1.op_code = t2.op_code
  AND t1.contract_num = t2.contract_num
  AND t1.alarm_type = 'AT1'
  AND t1.alarm_status = 'DONE'
  AND (   (    t1.prod_type = 'COMBO'
           AND t2.bigger_than_zero = t2.num_pontos - 1
          )
       OR (    t1.prod_type != 'COMBO'
           AND t2.bigger_than_zero = t2.num_pontos
          )
      )
      /* arbitrary filter depending on execution*/
  AND t1.data_tratado BETWEEN (SELECT data_inicio
                                 FROM cycle_table
                                WHERE id_ciclo = 160) AND (SELECT data_fim
                                                             FROM cycle_table
                                                            WHERE id_ciclo =
                                                                          160)

最後是解釋計劃：

Plan
SELECT STATEMENT  ALL_ROWSCost: 5,485                           
   13 NESTED LOOPS                         
       7 NESTED LOOPS  Cost: 5,483  Bytes: 115  Cardinality: 1                     
           5 VIEW  Cost: 12  Bytes: 59  Cardinality: 1                 
               4 SORT GROUP BY  Cost: 12  Bytes: 85  Cardinality: 1            
                   3 INLIST ITERATOR       
                       2 TABLE ACCESS BY INDEX ROWID TABLE RAIDPIDAT.COSTUMER_PRICE_HISTORY Cost: 11  Bytes: 85  Cardinality: 1    
                           1 INDEX RANGE SCAN INDEX RAIDPIDAT.IDX_COSTUMER_PRICE_HISTORY_2 Cost: 10  Cardinality: 3  
           6 INDEX RANGE SCAN INDEX RAIDPIDAT.IDX_HISTORY_STATE_TABLE_1TPALM Cost: 662  Cardinality: 102,068               
       12 TABLE ACCESS BY INDEX ROWID TABLE RAIDPIDAT.HISTORY_STATE_TABLE Cost: 5,471  Bytes: 56  Cardinality: 1                   
           9 TABLE ACCESS BY INDEX ROWID TABLE RAIDPIDAT.CYCLE_TABLE Cost: 1  Bytes: 12  Cardinality: 1                
               8 INDEX UNIQUE SCAN INDEX (UNIQUE) RAIDPIDAT.PK_CYCLE_TABLE Cost: 0  Cardinality: 1             
           11 TABLE ACCESS BY INDEX ROWID TABLE RAIDPIDAT.CYCLE_TABLE Cost: 1  Bytes: 12  Cardinality: 1               
               10 INDEX UNIQUE SCAN INDEX (UNIQUE) RAIDPIDAT.PK_CYCLE_TABLE Cost: 0  Cardinality: 1    

請注意，我不是在問“如何更有效地重寫它”，而是我如何通過解釋計劃找到那裡最昂貴的操作。同時我正在閱讀它，但我會很感激一些幫助。

解釋計劃並沒有告訴你實際上什麼是最昂貴的“操作”。“成本”列是一個猜測- 它是優化器估計的值。“基數”列和“字節”列也是如此。http://docs.oracle.com/cd/B28359_01/server.111/b28274/ex_plan.htm#i18300

在您的範例中，您的優化器告訴您：我決定使用這個計劃*，因為*我猜循環將花費大約 5,483。我希望這將是執行成本最高的部分，但我不能保證這一點。

這同樣遞歸地適用於樹的所有深度。

如果您深入到最低級別（即直覺上循環最多、執行次數最多的級別），您會發現在預期成本和預期元素數量方面特別突出的操作是

6 INDEX RANGE SCAN INDEX RAIDPIDAT.IDX_HISTORY_STATE_TABLE_1TPALM Cost: 662  Cardinality: 102,068 

因此，優化器猜測此查詢的最佳執行是圍繞一個糟糕的主力 RAIDPIDAT.IDX_HISTORY_STATE_TABLE_1TPALM 循環很多。我真的看不出您查詢的哪一部分與它直接相關，但我懷疑 t1.data_tratado 條件。而且，我看不出它是否真的是最昂貴的部分。

我將嘗試將解釋計劃中的循環語法轉換為過程虛擬碼：

/* begin step 13 (by "step 13" I mean a line that reads "   13 NESTED LOOPS") */
 /* begin step 7 */
   do step 5
   myresult = rows from step 5
   for each row from myresult {
      do step 6
      for each row from step 6 {
          join to a row from myresult the matching row from step 6
      }
   }
 /* end step 7 */
 for each row from myresult {
    do step 12
    for each row from step 12 {
        join to a row from myresult the matching row from step 12
    }
 }
/* end step 13 */
return myresult

看起來很複雜，但每個“嵌套循環”的真正目的是以最天真的方式創建一個連接（由兩個表組成的單個表），即循環內循環。

引用自：https://dba.stackexchange.com/questions/23965