關於日期與性能的注意事項。Oracle 中的字元/數字/Varchar2

我已經使用 Oracle 11g 大約 7 年了，我被要求在一個小型項目中擔任諮詢工作。他們遇到的問題是在以 Windows2008 為平台的 11gR2 上 CPU 使用率很高（超過 90%）。他們讓這台伺服器偶爾崩潰，所以他們增加了資源，但仍然有很高的 CPU 使用率。根據我的理解，高 CPU 使用率本身並不意味著問題，但是當我深入研究時，它變得更有趣：

有一個查詢，每次執行都會花費大量時間。由於他們使用的是 Oracle 標準版，我無法使用 AWR 的優勢，但是，當我檢查查詢時，我發現了一些非常奇怪的東西。

查詢如下，使用函式 foo：

update LIST_JOURNAL
  set STATUS       = '4',
      END_DATE     = :b1,
      END_TIME     = :b2,
      END_TYPE     = '1',
      USER_ID  = 'SYSTEM',
      ELAPSED_TIME = FOO_FUNCTION(START_DATE, START_TIME, :b1, :b2)
where (((TERM_ID = :b5 and NODE_CD = :b6) and GROUP_CD = :b7) and
      STATUS < '4');

他們只使用CHAR,VARCHAR2和NUMBER作為他們的數據類型（不知道原因，可能是由於從另一個數據庫遷移）並且FOO_FUNCTION在這個查詢中有這個計算兩個時間戳之間的間隔，比如說其中一個是數據庫中的記錄和其中之一是 sysdate，（日期和時間使用CHARandNUMBER數據類型儲存，而不是DATEor TIMESTAMP）該函式使用 將 sysdate 轉換為字元串TO_CHAR，然後與 TIME 欄位連接，然後執行 a TO_DATE，然後減去，然後乘以一天中的秒數（60* 60*24) 並返回結果。（由於契約原因，我不能透露功能程式碼）

該函式對我來說看起來很奇怪，我想將其標記為我的數據庫性能提升報告中非常重要的 CPU 瓶頸。我還沒有看到其餘的功能，但我很確定無論在哪裡DATE需要，都會發生類似的事情。

但我有點不願意這樣做，因為我在 Oracle 文件或其他地方沒有看到任何證據表明這可能是一個問題。（我知道，這個函式寫得太奇怪了，以至於我的不情願看起來很荒謬）還有一個很好的機會是軟體供應商不接受這個改變。

我的問題是：

1. 上面的功能可以是CPU密集型的嗎？重構和使用DATE數據TIMESTAMP類型可以幫助提高性能嗎？為什麼？我可以推斷出一些原因，但由於我需要介紹這一點，如果有一些文件討論過這個問題，我將不勝感激看到連結。
2. 表本身沒有任何索引——除了主鍵——如果我們被目前的程式碼和情況困住，索引可以幫助提高性能嗎？這能有多有效和可持續？我知道我不能期待詳細的答案，但該表包含大約 370 萬行，我認為對於更新查詢，一些索引可以提供幫助。

還有我的一般問題：

3. CHAR使用andNUMBER代替DATEand是否有性能下降TIMESTAMP？
4. 你會向我的客戶推薦什麼？

更新：

我終於得到了結果。我們添加了以下複合索引：

create index I_LIST_JOURNAL ON LIST_JOURNAL (TERM_ID, NODE_CD, STATUS);

我們立即得到了一些不錯的結果，CPU 下降到 80%，幾個查詢的執行時間大幅減少（有問題的查詢的平均執行時間為 1500 萬秒，現在平均約為 2400 秒）但同樣，在幾個查詢之後幾天之內，它再次上升到 99%。我希望再進行一次統計數據收集和解釋計劃。但我不確定這是否有助於減少負載。我正在考慮在具有長時間執行查詢的表上添加幾個其他復合索引或簡單索引，以消除解釋計劃中的“表訪問已滿”，但鑑於上述情況和我擁有的工具，我還有其他方法可以嗎使用以獲得更好的結果？

您想基於 CPU 瓶頸標記該功能，基於什麼？

您可以說您找到了sql_id，並檢查了，v$sql比較發現很大一部分來自並且也很高。elapsed_time``plsql_exec_time``elapsed_time``plsql_exec_time``cpu_time

驗證這一點：

select elapsed_time, plsql_exec_time, cpu_time from v$sql where sql_id = '...';

或者更好的是，您可以檢查實際花費的時間：

alter session set statistics_level=all;

執行語句：

update LIST_JOURNAL
  set STATUS       = '4',
      END_DATE     = :b1,
      END_TIME     = :b2,
      END_TYPE     = '1',
      USER_ID  = 'SYSTEM',
      ELAPSED_TIME = FOO_FUNCTION(START_DATE, START_TIME, :b1, :b2)
where (((TERM_ID = :b5 and NODE_CD = :b6) and GROUP_CD = :b7) and
      STATUS < '4');

然後使用以下命令查看此執行的實際統計資訊：

select * from table(dbms_xplan.display_cursor(format=>'allstats last'));

以上為您提供了有關 SQL 執行每個步驟的準確執行計數、時間、基數、緩衝區獲取、磁碟讀取、臨時、記憶體等資訊。這適用於任何版本的數據庫，不需要任何許可選項。有多種方法可以實現上述

表上沒有索引LIST_JOURNAL，但它有 370 萬行，上面的輸出表明您的語句執行了TABLE ACCESS FULL100000LIST_JOURNAL次緩衝區獲取但 0 次讀取，因為表在記憶體中，但此步驟實際上只返回 5 行（A-行），這一步用了 10 秒，而整個執行用了 10.2 秒？當然，您很可能會遇到缺少索引的問題。要創建什麼索引？我們不知道，因為我們不知道數據的分佈。您可能需要包含謂詞中的所有列或僅其中 3 個列的複合索引，或者僅TERM_ID列上的簡單索引就足夠了。

還是在大多數步驟中花費的時間微不足道，但在 UPDATE 步驟中花費的時間很高，並且與受影響的行plsql_exec_time相比相對較高且數量相對較少？elapsed_time當然，檢查功能。

在考慮函式中使用的數據類型之前，以上是我要檢查的絕對最小值。在理論之前先收集事實，而不知道問題的根源。

對原始問題的一些答案：

1. 是的，可以。即使使用正確的數據類型。
2. 是的，它可以。取決於數據分佈，受影響的行數。
3. 依靠。通常是的，在儲存日期時。

foo_function如果表格對日期欄位使用正確的數據類型，則不再需要 您可以將兩個日期數據類型相減，它會產生天數和天差的小數部分。Oracle 在內部將日期數據類型儲存為數字，因此您可以進行簡單的日期算術運算，並且它具有大量內置優化函式，可用於進行更高級的日期操作。正如您編寫的範例一樣，Oracle 必須在 SQL 和函式的 PL/SQL 引擎之間進行上下文切換；也許對於每一行。有你的瓶頸，再加上額外的程式碼來轉換CHAR和NUMBER內部的數據類型FOO_FUNCTION。

如果您無法更改架構，但可以使用虛擬列定義（取決於您的 Oracle 版本和供應商的軟體），您可以創建START_DATE_TIME正確DATE數據類型的虛擬列，然後無需呼叫FOO_FUNCTION.

如果你能做的就是添加一個索引，只需

create index I_LIST_JOURNAL ON LIST_JOURNAL 
 (TERM_ID, NODE_CD, GROUP_CD, STATUS);

根據每列的基數等，列的順序可能會有所不同。該STATUS列應該放在最後，因為它是范圍檢查，而不是相等檢查。如果STATUS只是少量的值，比如 20 或更少，你實際上會從上面的索引中省略它，並且STATUS如果在此期間沒有很多並發訪問表的情況下，還會在列上創建一個位映射索引這些更新。（點陣圖索引將在任何更新期間短暫鎖定表。）

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