需要性能調整幫助
我有一個查詢涉及跨 7 個不同表的連接。
以下是查詢：
SELECT /*+qb_name(select1) dynamic_sampling(nbtd 2) */ td.tsf_no C1 ,td.tsf_seq_no C2 ,NVL(th.from_loc,-999) C3 ,NVL(th.to_loc,-999) C4 ,NVL(th.to_loc_type,'-1') C5 ,NVL (s.to_loc,'-999') C6 ,td.item C7 ,NVL(ss.qty_expected, 0) C8 ,NVL(ss.carton,'-1') C9 ,NVL(ss.distro_no,'-1') C10 ,NVL(ss.distro_type,'-1') C11 ,NVL(ss.qty_expected, 0) C12 ,NVL(ss.weight_received, 0) C13 ,NVL(ss.weight_received_uom, 0) C14 ,s.shipment C15 ,NVL(s.bol_no,'-1') C16 ,NVL(s.order_no, 1) C17 ,ils.av_cost C18 ,il.unit_retail C19 ,user C20 ,to_char(sysdate, 'YYYYMMDD') C21 ,'T' C22 ,ss.seq_no C23 ,nbtd.expense_ind C24 ,nbtd.process_expense_ind C25 ,to_char(th.create_date, 'YYYYMMDD') C26 FROM nb_tsfdetail nbtd ,tsfdetail td ,tsfhead th ,shipsku ss ,shipment s ,item_loc il ,item_loc_soh ils WHERE td.tsf_no = nbtd.tsf_no AND td.tsf_seq_no = nbtd.tsf_seq_no AND td.tsf_no = th.tsf_no AND ss.distro_no = td.tsf_no AND ss.item = td.item AND ss.shipment = s.shipment AND td.item = il.item AND il.loc = th.from_loc AND ils.item = il.item AND ils.loc = il.loc AND nbtd.process_expense_ind = 'N' AND nbtd.expense_ind = 'Y';每個表中的數據是：
select count(1) from nb_tsfdetail;--692,336,190 select count(1) from tsfdetail;--693,317,818 select count(1) from tsfhead;--3,697,876 select count(1) from shipsku;-- 1,273,823,438 select count(1) from shipment;--514,593,396 select count(1) from item_loc;-- 109,587,391 select count(1) from item_loc_soh;--108,435,856我附上了上述查詢的解釋。
從哪兒開始
首先盡可能準確地定義您遇到的性能問題。我將假設您的查詢執行速度太慢。它需要多快完成？您需要這些資訊來確定某些權衡是否值得。例如，向基礎表添加索引可能會提高此查詢的性能，但它會佔用數據庫上的額外空間，並且在 DML 期間需要額外的維護。
EXPLAIN 註釋
接下來，盡可能多地了解您所擁有的查詢的解釋計劃。特別注意行估計。如果您認為 Oracle 執行查詢的速度太慢，那麼提出一個您認為可以更快完成的解釋計劃是一個很好的練習。您可能會想到需要添加的索引。在最壞的情況下，您可以添加提示來測試理論並找出重寫 SQL 的方法（或在絕對必要時保留提示）以提高性能。解釋計劃可以提供很好的線索，但它只包含 Oracle 認為會發生的情況。如果您的數據庫獲得了調整包的許可，那麼您可以使用實時 SQL 監控在 SQL 開發人員中。這是一個很好的特性，可以找出查詢執行緩慢的原因，因為它為您提供了有關實際行數與估計行數、等待時間以及每個步驟花費的時間量的執行時資訊。您發布的所有內容都是一個解釋計劃，因此我將僅以此為基礎盡力而為。
對於您的查詢，驅動表是 NB_TSFDETAIL。Oracle 預計使用對錶的全掃描將 700 M 行過濾到 168k。Oracle 期望與 ITEM_LOC 的嵌套循環連接將結果集進一步過濾到 35k 行。其餘的連接預計不會有意義地改變基數。除了 TSFHEAD 上的雜湊連接之外，每個連接都是嵌套循環連接。這可能只是因為該表上沒有好的索引候選而被實現為雜湊連接。
連接順序、表訪問方法和連接類型的重要性
在查看查詢時，我主要關註三個方面：連接順序、表訪問方法和連接類型。如果其中任何一個看起來效率低下或不符合我的預期，那麼我會更深入地研究它。
舉一個連接順序何時出錯的例子，如果有一個連接從結果集中消除了許多行，那麼我通常希望在查詢計劃中盡可能早地處理該連接。如果它作為最終連接處理，那麼我可能對最終結果集中不會返回的行做了很多不必要的工作。對於您的查詢，Oracle 選擇 NB_TSDETAIL 表作為要訪問的第一個表。這似乎很合理，因為過濾器預計會從查詢中最大的行之一中消除超過 99.9% 的行。大多數連接預計不會改變基數，所以我並不擔心這個查詢的連接順序，假設估計的基數接近現實。
舉一個表訪問方法何時可能出錯的範例，有時表沒有正確索引以進行查詢。看起來您的查詢需要對 NB_TSFDETAIL 進行全表掃描。如果該完整掃描花費的時間超過了您可以容忍的最大響應時間，那麼您如何調整查詢就無關緊要了。您可能需要通過創建索引來修復底層數據模型問題。另一個表訪問方法可能出錯的例子是，如果在不應該使用索引時使用了索引。如果 Oracle 需要訪問表中的大量行，則使用索引可能會非常低效。在這種情況下，全表掃描通常更有效。
舉一個連接類型何時出錯的範例，如果外部表的行太多，嵌套循環連接可能效率低下。這裡 Oracle 估計 NB_TSDETAIL 只會返回 168k 行。相對而言，這並沒有那麼多行，因此使用該結果集作為外部表的嵌套循環連接可能比執行散列連接或合併連接具有更低的成本。但是，如果從表返回的實際行數是 168M 怎麼辦？這意味著時間成本將大約減少一千倍。如果 Oracle 有更準確的基數資訊，它可能會選擇散列連接，而這可能會執行得更好。
當然，查詢計劃還有其他可能導致性能下降的問題。我認為這個查詢最值得一提的是缺乏並行性。也許這是故意的，並且您的伺服器太忙而無法允許，但是 RDBMS 所需的工作量讓我認為查詢可以從並行性中受益。
結論
總之，以下是您遇到的性能問題的最可能原因：
- Oracle 估計從 NB_TSFDETAIL 返回的行數為 168982，這是一個嚴重的低估。如果該估計值是固定的，那麼 Oracle 將選擇一個嵌套循環連接較少的計劃，該計劃將執行得更好。改進該估計的方法包括創建多列統計資訊（在 PROCESS_EXPENSE_IND 和 EXPENSE_IND 列之間可能存在 Oracle 不知道的隱藏關係）、更新表上的統計資訊或通過使用更大的樣本大小進行動態抽樣暗示。
- NB_TSDETAIL 中估計的行數大約是正確的，但是通過對 7 億行表的表掃描來找到它們需要很長時間。在這些列上定義索引可以提高查詢性能。
- 您正在對 700 M 行表執行查詢，但未使用並行性。這可能是由 DBA 配置的，並不是您有意的。如果您的工作負載允許並且此查詢足夠重要，您應該考慮使用 PARALLEL(X) 提示，看看這是否會縮短執行時間。
- 其他一些數據庫或硬體設置導致查詢緩慢。這可能是磁碟配置、數據庫參數或任何數量的東西。
進一步行動
對您來說可行的後續步驟包括：
- 定義您的性能問題和響應時間要求。
- 確定 NB_TSFDETAIL 表上 168k 行估計的準確性。
- 對 Oracle 需要對 NB_TSFDETAIL 執行全表掃描的時間進行基準測試。您可以通過精心構造的 COUNT(*) 查詢來做到這一點。
- 如有必要，請與您的 DBA 討論允許此查詢並行執行。
- 您比我們更了解表結構和數據，因此請嘗試其他理論，看看它們是否能提高查詢性能。
如果這假設是一個 OLAP 查詢 -
請添加以下提示
use_hash(nbtd,td,th,ss,s,il,ils)這個想法是消除所有這些對大容量來說是性能殺手的嵌套循環。
如果這假設是一個 OLTP 查詢 -
請添加以下提示
first_rows這個想法是消除 HASH JOIN 並引導優化器使用索引。