怎么調優Oracle SQL

本篇內容介紹了“怎么調優Oracle SQL”的有關知識，在實際案例的操作過程中，不少人都會遇到這樣的困境，接下來就讓小編帶領大家學習一下如何處理這些情況吧！希望大家仔細閱讀，能夠學有所成！

1 SQL調優簡介

SQL調優是嘗試診斷和修復不符合性能標準的SQL語句。

1.1 關于SQL調優

SQL調優是提高SQL語句性能以滿足特定、可衡量和可實現目標的迭代過程。

SQL調優意味著修復已部署正在運行的應用程序中的問題。

1.2 SQL調優的目的

當SQL語句無法按照預定和可測量的標準執行時，它就成為一個問題。

確定問題后，典型的調優會話具有以下目標之一：

• 減少用戶響應時間，減少用戶發出語句和收到響應之間的時間

• 提高吞吐量，處理語句訪問所有行需要的最少量資源

1.3 SQL調優的前提

SQL性能調優需要數據庫知識的基礎。

假定您具有下表中顯示的知識和技能。

表1-1 所需知識

1.4 SQL調優的任務和工具

在確定調優會話的目標后，例如，將用戶響應時間從三分鐘縮短到不到一秒，問題就變成了如何實現此目標。

1.4.1 SQL調優任務

調優會話的細節取決于許多因素，包括您是主動調優還是被動調優。

在主動SQL調優中，您經常使用SQL Tuning Advisor來確定是否可以使SQL語句更好地執行。 在被動SQL調優中，您可以更正用戶遇到的與SQL相關的問題。

無論您是主動，還是被動地進行調優，典型的SQL調優會話都涉及以下所有或大部分任務：

1.識別高負載SQL語句

查看過去的執行歷史記錄，以查找負責大量應用程序工作負載和系統資源的語句。

2.收集與性能相關的數據

優化程序統計信息對SQL調優至關重要。 如果這些統計信息不存在或不再準確，則優化程序無法生成最佳執行計劃。 與SQL性能相關的其他數據包括語句訪問的表和視圖的結構，以及語句可用的索引的定義。

3.確定問題的原因

通常，SQL性能問題的原因包括：

• 設計效率低下的SQL語句
  如果編寫SQL語句以便執行不必要的工作，那么優化器無法提高其性能。 低效設計的例子包括:

• 忽略添加Join條件，這會導致笛卡爾連接

• 使用hint將大表指定為連接中的驅動表

• 指定UNION而不是UNION ALL

• 為外部查詢中的每一行執行子查詢

• 次優的執行計劃
  查詢優化器（也稱為優化器）是內部軟件，用于確定哪個執行計劃最有效。 有時，優化器會選擇具有次優訪問路徑的計劃，這是數據庫從數據庫中檢索數據的方法。 例如，具有低選擇性的查詢謂詞的計劃，可以在大表而不是索引上使用全表掃描。

您可以將執行最佳SQL語句的執行計劃與次優的計劃進行比較。 這種比較以及諸如數據量變化之類的信息可以幫助確定性能下降的原因。

• 缺少SQL訪問結構
  缺少SQL訪問結構（例如，索引和物化視圖）是SQL性能欠佳的典型原因。 最佳訪問結構集可以將SQL性能提高幾個數量級。

• 過時的優化程序統計信息
  當統計維護操作（自動或手動）無法跟上DML引起的對表數據的更改時，DBMS_STATS收集的統計信息可能會變得陳舊。 由于表上的陳舊統計信息無法準確反映表數據，因此優化程序可能會根據錯誤信息做出決策并生成次優執行計劃。

• 硬件問題
  次優性能可能與內存、I/O和CPU問題有關。

4.定義問題的范圍

解決方案的范圍必須與問題的范圍相匹配。需要考慮數據庫級別的問題和語句級別的問題。例如，共享池太小，這會導致游標快速老化，從而導致許多硬解析。使用初始化參數來增加共享池大小可以修復數據庫級別的問題并提高所有會話的性能。但是，如果單個SQL語句未使用有用的索引，則更改整個數據庫的優化程序初始化參數可能會損害整體性能。如果單個SQL語句出現問題，那么適當范圍的解決方案只能通過此語句解決此問題。

5.為次優執行SQL語句實施更正操作

這些行為因環境而異。例如，您可以重寫SQL語句以提高效率，通過重寫語句以使用綁定變量來避免不必要的硬解析。 您還可以使用equijoins，從WHERE子句中刪除函數，并將復雜的SQL語句分解為多個簡單語句。

在某些情況下，您不是通過重寫語句而是通過重構模式對象來提高SQL性能。例如，您可以對表進行分區，引入派生值，甚至更改數據庫設計。

6.防止SQL性能回歸

要確保最佳SQL性能，請驗證執行計劃是否繼續提供最佳性能，并選擇更好的計劃（如果可用）。您可以使用優化程序統計信息，SQL配置文件和SQL計劃基準來實現這些目標。

1.4.2 SQL調優工具

SQL調優工具是自動或手動的。

在某種情況下，如果數據庫本身可以提供診斷，建議或糾正措施，則工具是自動化的。手動工具要求您執行所有這些操作。

所有調優工具都依賴于數據庫實例收集的動態性能視圖，統計信息和度量標準的基本工具。數據庫本身包含調整SQL語句所需的數據和元數據。

1.4.2.1 自動SQL調優工具

Oracle數據庫提供了幾個與SQL調優相關的顧問程序。

此外，SQL計劃管理是一種可以防止性能回歸的機制，還可以幫助您提高SQL性能。

所有自動SQL調優工具都可以使用SQL調優集作為輸入。 SQL調優集（STS）是一個數據庫對象，包括一個或多個SQL語句及其執行統計信息和執行上下文。

ADDM是Oracle數據庫內置的自診斷軟件。

ADDM可以自動定位性能問題的根本原因，提供糾正建議，并量化預期收益。 ADDM還可識別無需采取任何措施的區域。

ADDM和其他顧問使用自動工作負載存儲庫（AWR），它是一種為數據庫組件提供服務以收集，維護和使用統計信息的基礎結構。ADDM檢查并分析AWR中的統計信息，以確定可能的性能問題，包括高負載SQL。

例如，您可以將ADDM配置為每晚運行。在早上，您可以檢查最新的ADDM報告，以查看可能導致問題的原因以及是否存在建議的修復。該報告可能會顯示特定的SELECT語句占用了大量CPU，并建議您運行SQL調優顧問。

SQL調優顧問是內部診斷軟件，可識別有問題的SQL語句，并建議如何提高語句性能。

在數據庫維護窗口期間作為自動維護任務運行時，SQL調優顧問稱為自動SQL調整顧問。

SQL調優顧問將一個或多個SQL語句作為輸入，并調用自動調整優化器對語句執行SQL調優。 顧問執行以下類型的分析：

• 檢查無效或過時的統計信息

• 構建SQL profile
  SQL profile是一組特定于SQL語句的輔助信息。SQL profile包含在自動SQL調整期間發現的次優優化程序估計的更正。此信息可以改進基數的優化器估計，基數是執行計劃中的操作估計或實際返回的行數，以及選擇性。 這些改進的估計導致優化器選擇更好的計劃。

• 探討不同的訪問路徑是否可以顯著提高性能

• 標識適合于次優計劃的SQL語句

產出的形式是報告或建議，以及每項建議的理由及其預期收益。該建議涉及對象統計信息的集合，新索引的創建，SQL語句的重構或SQL profile的創建。 您可以選擇接受建議以完成SQL語句的調整。

SQL訪問顧問是內部診斷軟件，它建議創建，刪除或保留哪些物化視圖，索引和物化視圖日志。

SQL訪問顧問將實際工作負載作為輸入，或者顧問程序可以從模式中獲取假設的工作負載。SQL訪問顧問會考慮空間使用和查詢性能之間的權衡，并建議對新的和現有的物化視圖和索引進行最具成本效益的配置。 顧問還提出有關分區的建議。

Oracle數據庫可以持續監控應用程序工作負載，自動創建和管理索引。

手動創建索引需要深入了解數據模型，應用程序和數據分布。 DBA通常會選擇創建哪些索引，然后從不修改他們的選擇。 結果，失去了改進的機會，不必要的索引，可能會成為性能瓶頸。自動索引管理通過執行以下任務解決了此問題：

• 不斷監控工作負載

• 創建新索引

• 重建索引，然后將其標記為不可用或不可見

• 刪除索引

• 檢查自動索引管理對性能的影響

索引功能實現是自動任務，以固定間隔在后臺運行。 在每次迭代時，該任務執行以下基本步驟：

• 根據列和列組使用情況標識候選索引。

• 在不可用和不可見模式下創建一組候選索引。這些索引不占用存儲空間。

• 查詢先前執行的語句的優先級列表，以確定候選索引是否值得重建。

• 編譯語句以確定優化程序是否會選擇新索引，然后重建優化程序選擇的索引。

• 執行使用自動索引的語句

• 執行以下任一操作：

• 當語句顯著改善其性能時，將索引標記為可見。只有在驗證并將索引標記為可見之后，數據庫才會更改工作負載中語句的計劃。在此之前，數據庫不會使游標無效并繼續使用舊執行計劃。

• 標記索引在提供不足的性能優勢時無法使用。當使用其他的索引的概率較低或存在空間壓力時，此操作以延遲方式發生。

• 使用SQL計劃管理避免回歸。索引可能會使一個語句受益，但會導致第二個語句中的性能下降。在這種情況下，數據庫通過將索引標記為可見來優化第一個語句。 為了防止第二個語句的下降，數據庫使用SQL計劃管理來保護它。

• 刪除長時間未使用的索引。

您可以通過在 DBMS_AUTO_INDEX 包中執行以下過程來啟用自動索引：

EXEC DBMS_AUTO_INDEX.CONFIGURE('AUTO_INDEX_MODE','IMPLEMENT')

您還可以使用 DBMS_AUTO_INDEX 包報告自動任務并配置設置，例如保留未使用的索引的時間。

SQL計劃管理是一種預防機制，使優化程序能夠自動管理執行計劃，確保數據庫僅使用已知或已驗證的計劃。

此機制可以構建SQL計劃基準，該基準包含每個SQL語句的一個或多個接受的計劃。 通過使用基線，SQL計劃管理可以防止計劃回歸環境變化，同時允許優化程序發現和使用更好的計劃。

SQL性能分析器通過識別每個SQL語句的性能差異來確定更改對SQL工作負載的影響。

系統更改（如升級數據庫或添加索引）可能會導致執行計劃發生更改，從而影響SQL性能。 通過使用SQL性能分析器，您可以準確地預測系統更改對SQL性能的影響。 使用此信息，您可以在SQL性能下降時調整數據庫，或在SQL性能提高時驗證和測量增益。

1.4.2.2 手動SQL調優工具

在某些情況下，除了自動化工具之外，您可能還需要運行手動工具。或者，您可能無法訪問自動化工具。

執行計劃是手動SQL調優的主要診斷工具。 例如，您可以查看計劃以確定優化程序是選擇預期的計劃，還是確定在表上創建索引的效果。

您可以通過多種方式顯示執行計劃。 以下工具是最常用的：

• DBMS_XPLAN
  您可以使用 DBMS_XPLAN 包方法顯示 EXPLAIN PLAN 命令生成的執行計劃以及 V$SQL_PLAN 的查詢。

• EXPLAIN PLAN
  通過此SQL語句，您可以查看優化程序在不實際執行語句的情況下用于執行SQL語句的執行計劃。

• V$SQL_PLAN 和相關視圖
  這些視圖包含有關已執行的SQL語句及其執行計劃的信息，這些信息仍在共享池中。

• AUTOTRACE
  SQL * Plus中的 AUTOTRACE 命令生成有關查詢性能的執行計劃和統計信息。此命令提供磁盤讀取和內存讀取等統計信息。

Oracle數據庫的實時SQL監視功能使您可以在執行時監視SQL語句的性能。默認情況下，當一個語句并行運行，或者在一次執行中消耗了至少5秒的CPU或I/O時間時，SQL監視會自動啟動。

數據庫操作是由最終用戶或應用程序代碼定義的一組數據庫任務，例如，批處理作業或提取，轉換和加載（ETL）處理。您可以定義，監視和報告數據庫操作。實時數據庫操作提供自動監視復合操作的功能。執行開始后，數據庫會自動監視并行查詢，DML和DDL語句。

Oracle Enterprise Manager Cloud Control（云控制）提供易于使用的SQL監控頁面。或者，您可以使用 V$SQL_MONITOR 和 V$SQL_PLAN_MONITOR 視圖監視與SQL相關的統計信息。您可以將這些視圖與以下視圖一起使用，以獲取有關正在監視的執行的更多信息：

• V$ACTIVE_SESSION_HISTORY

• V$SESSION

• V$SESSION_LONGOPS

• V$SQL

• V$SQL_PLAN

SQL跟蹤文件提供有關各個SQL語句的性能信息：解析計數，物理和邏輯讀取，庫高速緩存上的未命中等。

跟蹤文件有時可用于診斷SQL性能問題。您可以使用 DBMS_MONITOR 或 DBMS_SESSION包為特定會話啟用和禁用SQL跟蹤。當您啟用跟蹤機制時，Oracle數據庫通過為每個服務器進程生成跟蹤文件來實現跟蹤。

Oracle數據庫提供以下命令行工具來分析跟蹤文件：

• TKPROF

此實用程序接受SQL跟蹤工具生成的跟蹤文件作為輸入，然后生成格式化的輸出文件。

• trcsess

此實用程序根據會話ID，客戶端ID和服務ID等條件合并來自多個跟蹤文件的跟蹤輸出。在 trcsess 將跟蹤信息合并到單個輸出文件后，您可以使用TKPROF格式化輸出文件。 trcsess 對于合并特定會話的跟蹤以用于性能或調試目的非常有用。

端到端應用程序跟蹤簡化了診斷多層環境中性能問題的過程。在這些環境中，中間層將請求從最終客戶端路由到不同的數據庫會話，從而難以跨數據庫會話跟蹤客戶端。端到端應用程序跟蹤使用客戶端ID通過數據庫的所有層唯一地跟蹤特定的最終客戶端。

Hint是通過SQL語句中的注釋傳遞給優化程序的指令。

Hint使您可以通常由優化程序自動做出決策。 在測試或開發環境中，Hint對于測試特定訪問路徑的性能很有用。例如，您可能知道特定索引對某些查詢更具選擇性。 在這種情況下，您可以使用Hint來指示優化器使用更好的執行計劃，如以下示例所示：

SELECT /*+ INDEX (employees emp_department_ix) */ 
       employee_id, department_id 
FROM   employeesWHERE  department_id > 50;

有時，由于拼寫錯誤，無效參數，沖突提示以及通過轉換無效的提示，數據庫可能不會使用hint。 從Oracle Database 19c開始，您可以生成有關在計劃生成期間使用或未使用哪些hint的報告。

1.4.3 SQL調優工具的用戶界面

Cloud Control是一種系統管理工具，可以對數據庫環境進行集中管理。 Cloud Control提供對大多數調優工具的訪問。

通過結合圖形控制臺，Oracle管理服務器，Oracle智能代理，通用服務和管理工具，Cloud Control提供了一個全面的系統管理平臺。

您可以使用命令行界面訪問所有SQL調優工具。 例如，DBMS_SQLTUNE 包是SQL調優顧問的命令行界面。

Oracle建議將Cloud Control作為數據庫管理和調優的最佳界面。但是如果命令行界面能夠更好地說明特定的概念或任務，我們的示例也將使用命令行。

“怎么調優Oracle SQL”的內容就介紹到這里了，感謝大家的閱讀。如果想了解更多行業相關的知識可以關注億速云網站，小編將為大家輸出更多高質量的實用文章！