共計 6422 個字符，預(yù)計需要花費 17 分鐘才能閱讀完成。

本篇內(nèi)容介紹了“怎么調(diào)優(yōu) Oracle SQL”的有關(guān)知識，在實際案例的操作過程中，不少人都會遇到這樣的困境，接下來就讓丸趣 TV 小編帶領(lǐng)大家學(xué)習(xí)一下如何處理這些情況吧！希望大家仔細閱讀，能夠?qū)W有所成！

1 SQL 調(diào)優(yōu)簡介

SQL 調(diào)優(yōu)是嘗試診斷和修復(fù)不符合性能標準的 SQL 語句。

1.1 關(guān)于 SQL 調(diào)優(yōu)

SQL 調(diào)優(yōu)是提高 SQL 語句性能以滿足特定、可衡量和可實現(xiàn)目標的迭代過程。

SQL 調(diào)優(yōu)意味著修復(fù)已部署正在運行的應(yīng)用程序中的問題。

1.2 SQL 調(diào)優(yōu)的目的

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

確定問題后，典型的調(diào)優(yōu)會話具有以下目標之一：

減少用戶響應(yīng)時間，減少用戶發(fā)出語句和收到響應(yīng)之間的時間

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

1.3 SQL 調(diào)優(yōu)的前提

SQL 性能調(diào)優(yōu)需要數(shù)據(jù)庫知識的基礎(chǔ)。

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

表 1 -1 所需知識

所需知識說明數(shù)據(jù)庫架構(gòu)數(shù)據(jù)庫體系結(jié)構(gòu)不僅僅是管理員所要了解的內(nèi)容。作為開發(fā)人員，您希望在最少的時間內(nèi)針對 Oracle 數(shù)據(jù)庫開發(fā)應(yīng)用程序，這需要利用數(shù)據(jù)庫體系結(jié)構(gòu)和特性。例如，不理解 Oracle 數(shù)據(jù)庫并發(fā)控制和多版本讀取一致性，可能會使應(yīng)用程序破壞數(shù)據(jù)的完整性，運行緩慢并降低可擴展性。SQL 和 PL/SQL 由于存在基于 GUI 的工具，因此可以在不知道 SQL 的情況下創(chuàng)建應(yīng)用程序和管理數(shù)據(jù)庫。但是，如果不了解 SQL，就無法調(diào)整應(yīng)用程序或數(shù)據(jù)庫。SQL 調(diào)優(yōu)工具數(shù)據(jù)庫生成性能統(tǒng)計信息，并提供解釋這些統(tǒng)計信息的 SQL 調(diào)優(yōu)工具。1.4 SQL 調(diào)優(yōu)的任務(wù)和工具

在確定調(diào)優(yōu)會話的目標后，例如，將用戶響應(yīng)時間從三分鐘縮短到不到一秒，問題就變成了如何實現(xiàn)此目標。

1.4.1 SQL 調(diào)優(yōu)任務(wù)

調(diào)優(yōu)會話的細節(jié)取決于許多因素，包括您是主動調(diào)優(yōu)還是被動調(diào)優(yōu)。

在主動 SQL 調(diào)優(yōu)中，您經(jīng)常使用 SQL Tuning Advisor 來確定是否可以使 SQL 語句更好地執(zhí)行。在被動 SQL 調(diào)優(yōu)中，您可以更正用戶遇到的與 SQL 相關(guān)的問題。

無論您是主動，還是被動地進行調(diào)優(yōu)，典型的 SQL 調(diào)優(yōu)會話都涉及以下所有或大部分任務(wù)：

1. 識別高負載 SQL 語句

查看過去的執(zhí)行歷史記錄，以查找負責(zé)大量應(yīng)用程序工作負載和系統(tǒng)資源的語句。

2. 收集與性能相關(guān)的數(shù)據(jù)

優(yōu)化程序統(tǒng)計信息對 SQL 調(diào)優(yōu)至關(guān)重要。如果這些統(tǒng)計信息不存在或不再準確，則優(yōu)化程序無法生成最佳執(zhí)行計劃。與 SQL 性能相關(guān)的其他數(shù)據(jù)包括語句訪問的表和視圖的結(jié)構(gòu)，以及語句可用的索引的定義。

3. 確定問題的原因

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

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

忽略添加 Join 條件，這會導(dǎo)致笛卡爾連接

使用 hint 將大表指定為連接中的驅(qū)動表

指定 UNION 而不是 UNION ALL

為外部查詢中的每一行執(zhí)行子查詢

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

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

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

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

硬件問題
次優(yōu)性能可能與內(nèi)存、I/ O 和 CPU 問題有關(guān)。

4. 定義問題的范圍

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

5. 為次優(yōu)執(zhí)行 SQL 語句實施更正操作

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

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

6. 防止 SQL 性能回歸

要確保最佳 SQL 性能，請驗證執(zhí)行計劃是否繼續(xù)提供最佳性能，并選擇更好的計劃（如果可用）。您可以使用優(yōu)化程序統(tǒng)計信息，SQL 配置文件和 SQL 計劃基準來實現(xiàn)這些目標。

1.4.2 SQL 調(diào)優(yōu)工具

SQL 調(diào)優(yōu)工具是自動或手動的。

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

所有調(diào)優(yōu)工具都依賴于數(shù)據(jù)庫實例收集的動態(tài)性能視圖，統(tǒng)計信息和度量標準的基本工具。數(shù)據(jù)庫本身包含調(diào)整 SQL 語句所需的數(shù)據(jù)和元數(shù)據(jù)。

1.4.2.1 自動 SQL 調(diào)優(yōu)工具

Oracle 數(shù)據(jù)庫提供了幾個與 SQL 調(diào)優(yōu)相關(guān)的顧問程序。

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

所有自動 SQL 調(diào)優(yōu)工具都可以使用 SQL 調(diào)優(yōu)集作為輸入。SQL 調(diào)優(yōu)集（STS）是一個數(shù)據(jù)庫對象，包括一個或多個 SQL 語句及其執(zhí)行統(tǒng)計信息和執(zhí)行上下文。

1.4.2.1.1 自動數(shù)據(jù)庫診斷監(jiān)視器（ADDM）

ADDM 是 Oracle 數(shù)據(jù)庫內(nèi)置的自診斷軟件。

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

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

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

1.4.2.1.2 SQL 調(diào)優(yōu)顧問

SQL 調(diào)優(yōu)顧問是內(nèi)部診斷軟件，可識別有問題的 SQL 語句，并建議如何提高語句性能。

在數(shù)據(jù)庫維護窗口期間作為自動維護任務(wù)運行時，SQL 調(diào)優(yōu)顧問稱為自動 SQL 調(diào)整顧問。

SQL 調(diào)優(yōu)顧問將一個或多個 SQL 語句作為輸入，并調(diào)用自動調(diào)整優(yōu)化器對語句執(zhí)行 SQL 調(diào)優(yōu)。顧問執(zhí)行以下類型的分析：

檢查無效或過時的統(tǒng)計信息

構(gòu)建 SQL profile
SQL profile 是一組特定于 SQL 語句的輔助信息。SQL profile 包含在自動 SQL 調(diào)整期間發(fā)現(xiàn)的次優(yōu)優(yōu)化程序估計的更正。此信息可以改進基數(shù)的優(yōu)化器估計，基數(shù)是執(zhí)行計劃中的操作估計或?qū)嶋H返回的行數(shù)，以及選擇性。這些改進的估計導(dǎo)致優(yōu)化器選擇更好的計劃。

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

標識適合于次優(yōu)計劃的 SQL 語句

產(chǎn)出的形式是報告或建議，以及每項建議的理由及其預(yù)期收益。該建議涉及對象統(tǒng)計信息的集合，新索引的創(chuàng)建，SQL 語句的重構(gòu)或 SQL profile 的創(chuàng)建。您可以選擇接受建議以完成 SQL 語句的調(diào)整。

1.4.2.1.3 SQL 訪問顧問

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

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

1.4.2.1.4 自動索引

Oracle 數(shù)據(jù)庫可以持續(xù)監(jiān)控應(yīng)用程序工作負載，自動創(chuàng)建和管理索引。

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

不斷監(jiān)控工作負載

創(chuàng)建新索引

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

刪除索引

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

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

根據(jù)列和列組使用情況標識候選索引。

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

查詢先前執(zhí)行的語句的優(yōu)先級列表，以確定候選索引是否值得重建。

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

執(zhí)行使用自動索引的語句

執(zhí)行以下任一操作：

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

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

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

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

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

EXEC DBMS_AUTO_INDEX.CONFIGURE(AUTO_INDEX_MODE , IMPLEMENT)

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

1.4.2.1.5 SQL 計劃管理

SQL 計劃管理是一種預(yù)防機制，使優(yōu)化程序能夠自動管理執(zhí)行計劃，確保數(shù)據(jù)庫僅使用已知或已驗證的計劃。

此機制可以構(gòu)建 SQL 計劃基準，該基準包含每個 SQL 語句的一個或多個接受的計劃。通過使用基線，SQL 計劃管理可以防止計劃回歸環(huán)境變化，同時允許優(yōu)化程序發(fā)現(xiàn)和使用更好的計劃。

1.4.2.1.6 SQL 性能分析器

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

系統(tǒng)更改（如升級數(shù)據(jù)庫或添加索引）可能會導(dǎo)致執(zhí)行計劃發(fā)生更改，從而影響 SQL 性能。通過使用 SQL 性能分析器，您可以準確地預(yù)測系統(tǒng)更改對 SQL 性能的影響。使用此信息，您可以在 SQL 性能下降時調(diào)整數(shù)據(jù)庫，或在 SQL 性能提高時驗證和測量增益。

1.4.2.2 手動 SQL 調(diào)優(yōu)工具

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

1.4.2.2.1 執(zhí)行計劃

執(zhí)行計劃是手動 SQL 調(diào)優(yōu)的主要診斷工具。例如，您可以查看計劃以確定優(yōu)化程序是選擇預(yù)期的計劃，還是確定在表上創(chuàng)建索引的效果。

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

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

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

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

AUTOTRACE
SQL * Plus 中的  AUTOTRACE  命令生成有關(guān)查詢性能的執(zhí)行計劃和統(tǒng)計信息。此命令提供磁盤讀取和內(nèi)存讀取等統(tǒng)計信息。

1.4.2.2.2 實時 SQL 監(jiān)控和實時數(shù)據(jù)庫操作

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

數(shù)據(jù)庫操作是由最終用戶或應(yīng)用程序代碼定義的一組數(shù)據(jù)庫任務(wù)，例如，批處理作業(yè)或提取，轉(zhuǎn)換和加載（ETL）處理。您可以定義，監(jiān)視和報告數(shù)據(jù)庫操作。實時數(shù)據(jù)庫操作提供自動監(jiān)視復(fù)合操作的功能。執(zhí)行開始后，數(shù)據(jù)庫會自動監(jiān)視并行查詢，DML 和 DDL 語句。

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

V$ACTIVE_SESSION_HISTORY

V$SESSION

V$SESSION_LONGOPS

V$SQL

V$SQL_PLAN

1.4.2.2.3 應(yīng)用程序跟蹤

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

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

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

TKPROF

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

trcsess

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

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

1.4.2.2.4 優(yōu)化器 Hint

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

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

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

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

1.4.3 SQL 調(diào)優(yōu)工具的用戶界面

Cloud Control 是一種系統(tǒng)管理工具，可以對數(shù)據(jù)庫環(huán)境進行集中管理。Cloud Control 提供對大多數(shù)調(diào)優(yōu)工具的訪問。

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

您可以使用命令行界面訪問所有 SQL 調(diào)優(yōu)工具。例如，DBMS_SQLTUNE  包是 SQL 調(diào)優(yōu)顧問的命令行界面。

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

“怎么調(diào)優(yōu) Oracle SQL”的內(nèi)容就介紹到這里了，感謝大家的閱讀。如果想了解更多行業(yè)相關(guān)的知識可以關(guān)注丸趣 TV 網(wǎng)站，丸趣 TV 小編將為大家輸出更多高質(zhì)量的實用文章！