如何調優 Oracle SQL系列文章：SQL性能方法論

如何調優 Oracle SQL系列文章 第二篇： SQL性能方法論。

2 SQL性能方法論

2.1 應用程序設計指南

獲得良好SQL性能的關鍵是在設計應用程序時考慮性能。

2.1.1 數據建模指南

數據建模對于成功的應用程序設計非常重要。

你必須以根據實際的業務需求進行數據建模。在這個過程中，對于什么樣的模型是正確的數據模型可能會出現不同的爭議。重要的是將最大的建模工作應用于受最頻繁的業務事務影響的實體。

在建模階段，很有可能花費太多時間來建模非核心數據元素，這會導致開發前置時間的增加。使用建模工具可以快速生成模式定義，并且在需要快速原型時非常有用。

2.1.2 編寫有效的應用指南

在系統開發的設計和架構階段，確保應用程序開發人員了解SQL執行效率。
為實現此目標，開發環境必須支持以下特征：

• 良好的數據庫連接管理
  連接到數據庫是一項昂貴的操作，并且無法擴展。因此，最佳做法是最小化與數據庫的并發連接數。一個簡單的系統，用戶在應用程序初始化時連接，是比較理想的。但是，在基于Web或多層應用程序中，這種方法可能很困難。使用這些類型的應用程序，一般是使用數據庫連接池，而不是為每個用戶請求重新建立連接。

• 良好的游標使用和管理
  維護用戶連接對于最小化系統上的解析活動同樣重要。解析是解釋SQL語句并為其創建執行計劃的過程。此過程有許多階段，包括語法檢查，安全檢查，執行計劃生成以及將共享結構加載到共享池中。有兩種類型的解析操作：

  首次提交SQL語句，并且在共享池中找不到匹配項。硬解析是資源最密集且不可擴展的，因為它們執行解析中涉及的所有操作。

  首次提交SQL語句，并在共享池中找到匹配項。匹配可以是其他用戶先前執行的結果。共享SQL語句，這對性能來說是最佳的。但是，軟解析并不是最理想的，因為它們仍然需要語法和安全檢查，這會消耗系統資源。

• 軟解析

• 硬解析

因為解析應該盡可能地最小化，所以應用程序開發人員應該設計他們的應用程序來解析一次SQL語句并多次執行它們。這是通過游標完成的。經驗豐富的SQL程序員應該熟悉打開和重新執行游標的概念。

• 有效使用綁定變量

應用程序開發人員還必須確保在共享池中所共享SQL語句。為了實現這一目標，使用綁定變量來改造查詢。如果不這樣做，則SQL語句可能會被解析一次，并且永遠不會被其他用戶重用。要確保共享SQL，不要將字符串文字與SQL語句一起使用。例如：

帶字符串文字的語句:

SELECT
 * 
FROM
 employees 
WHERE
 last_name 
LIKE 'KING';

綁定變量的語句：

SELECT
 * 
FROM
 employees 
WHERE
 last_name 
LIKE :1;

下面的例子展示了一個簡單的OLTP應用程序的一些測試結果:

這些測試是在4顆CPU計算機上進行的。隨著系統上CPU數量的增加，差異也會增加。

2.2 部署應用程序指南

要實現最佳性能，部署應用程序時要像設計應用程序時一樣精心。

2.2.1 在測試環境中部署指南

測試過程主要包括功能測試和穩定性測試。在這個過程的某個時候，您必須執行性能測試。

以下列表描述了對應用程序進行性能測試的簡單規則。如果記錄正確，則此列表在應用程序上線后為生產應用程序和容量規劃過程提供重要信息。

• 使用自動數據庫診斷監視器（ADDM）和SQL Tuning Advisor進行設計驗證。

• 使用實際數據量和分布進行測試。

所有測試必須使用完全填充的表完成。測試數據庫應包含代表生產系統的數據，包括表之間的數據量和基數。應構建所有生產索引，并正確填充模式統計信息。

• 使用正確的優化程序模式。

使用您計劃在生產中使用的優化程序模式執行所有測試。

• 測試單個用戶性能。

在空閑或輕度使用的數據庫上測試單個用戶以獲得可接受的性能。如果單個用戶在理想條件下無法達到可接受的性能，則多個用戶在實際條件下無法實現可接受的性能。

• 獲取并記錄所有SQL語句的計劃。

獲取每個SQL語句的執行計劃。使用此過程驗證優化器是否獲得了最佳執行計劃，并且可以根據CPU時間和物理I/O來理解SQL語句的相對成本。此過程有助于識別將來最需要調優和性能工作的大量事務。

• 嘗試多用戶測試。

此過程難以準確執行，因為用戶工作負載和配置文件可能無法完全量化。但是，應測試執行DML語句的事務以確保不存在鎖定沖突或序列化問題。

• 使用正確的硬件配置進行測試。

使用盡可能靠近生產系統的配置進行測試。使用真實的系統對于網絡延遲，I/O子系統帶寬以及處理器類型和速度尤為重要。如果不使用此方法，可能會導致對潛在性能問題的錯誤分析。

• 測量穩態性能。

在基準測試時，對穩態條件下的性能進行測量是非常重要的。每個基準測試運行都應該有一個上升階段，在這個階段，用戶連接到應用程序，并逐漸開始對應用程序執行工作。這個過程允許將頻繁緩存的數據初始化到緩存中，并在穩態條件之前完成單個執行操作(例如解析)。同樣，在基準測試運行之后，有一段下降期也是有用的，這樣系統就可以釋放資源，用戶就可以停止工作并斷開連接。

2.2.2 應用程序部署指南

當新應用程序推出時，通常采用兩種策略:Big Bang方法(即所有用戶同時遷移到新系統)和Trickle方法(即用戶緩慢地從現有系統遷移到新系統)。

這兩種方法都有優點和缺點。 Big Bang方法依賴于以所需規模對應用程序進行可靠測試，但具有最小化數據轉換和與舊系統同步的優勢，因為它只是被關閉。 Trickle方法允許在工作負載增加時調試可伸縮性問題，但可能意味著必須在轉換發生時將數據遷移到遺留系統和從遺留系統遷移。

很難推薦一種方法，而不選另一種方法，因為每種技術都存在相關風險，可能會在轉換發生時導致系統中斷。當然，Trickle方法允許在實際用戶被引入新應用程序時對他們進行分析，并且允許在只影響遷移用戶的情況下重新配置系統。這種方法會影響早期采用者，但限制了支持服務的負載。因此，計劃外停機只影響一小部分用戶。

關于如何推出新應用程序的決定是針對每個業務的。任何采用的方法都有其獨特的壓力和應力。您從測試過程中獲得的測試和知識越多，就越能認識到什么時機最適合推出新應用。