怎么調(diào)優(yōu)Oracle SQL

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

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

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

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

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

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

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

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

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

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

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

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

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

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

表1-1 所需知識

1.4 SQL調(diào)優(yōu)的任務(wù)和工具

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

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

調(diào)優(yōu)會話的細(xì)節(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)的問題。

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

1.識別高負(fù)載SQL語句

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

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

優(yōu)化程序統(tǒng)計信息對SQL調(diào)優(yōu)至關(guān)重要。 如果這些統(tǒng)計信息不存在或不再準(zhǔn)確，則優(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)的計劃進(jìn)行比較。 這種比較以及諸如數(shù)據(jù)量變化之類的信息可以幫助確定性能下降的原因。

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

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

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

4.定義問題的范圍

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

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

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

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

6.防止SQL性能回歸

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

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)計信息和度量標(biāo)準(zhǔn)的基本工具。數(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計劃管理是一種可以防止性能回歸的機(jī)制，還可以幫助您提高SQL性能。

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

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

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

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

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

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

在數(shù)據(jù)庫維護(hù)窗口期間作為自動維護(hù)任務(wù)運(yùn)行時，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)化程序估計的更正。此信息可以改進(jìn)基數(shù)的優(yōu)化器估計，基數(shù)是執(zhí)行計劃中的操作估計或?qū)嶋H返回的行數(shù)，以及選擇性。 這些改進(jìn)的估計導(dǎo)致優(yōu)化器選擇更好的計劃。

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

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

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

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

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

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

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

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

• 創(chuàng)建新索引

• 重建索引，然后將其標(biāo)記為不可用或不可見

• 刪除索引

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

執(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)計信息。

Oracle數(shù)據(jù)庫的實時SQL監(jiān)視功能使您可以在執(zhí)行時監(jiān)視SQL語句的性能。默認(rèn)情況下，當(dāng)一個語句并行運(yùn)行，或者在一次執(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

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

跟蹤文件有時可用于診斷SQL性能問題。您可以使用 DBMS_MONITOR 或 DBMS_SESSION包為特定會話啟用和禁用SQL跟蹤。當(dāng)您啟用跟蹤機(jī)制時，Oracle數(shù)據(jù)庫通過為每個服務(wù)器進(jìn)程生成跟蹤文件來實現(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ù)庫的所有層唯一地跟蹤特定的最終客戶端。

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)境進(jì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)注億速云網(wǎng)站，小編將為大家輸出更多高質(zhì)量的實用文章！