SQL 語句處理階段
透徹理解 SQL 處理有助於理解 SQL 統計資訊。在 SQL 語句處理過程中，有四個重要階段：分析、繫結、執行和提取。
反向箭頭表示處理方案（例如，提取-（再）繫結-執行-提取）。
提取階段僅適用於帶有 returning 子句的查詢和 DML 語句。
注：有關 SQL 語句處理的詳細說明，請參閱《Oracle Database 11g Application Developers Guide: Fundamentals》和《Oracle Database 11g: Concepts》。

分析階段
分析是 SQL 語句處理的一個階段。當應用程式執行 SQL 語句時，其將對 Oracle DB 進行一次分析呼叫。在分析呼叫期間，Oracle DB 將：
檢查語句的語法和語義有效性
確定執行該語句的程式是否具有執行的許可權
在共享池中搜尋該語句的可共享匹配
為該語句分配專用 SQL 區域
存在兩類分析操作：
軟分析：提交一條 SQL 語句，並在共享池找到一個匹配。該匹配可以是另一使用者之前執行的結果。會共享該 SQL 語句，這對效能有利。但是，軟分析仍需要語法和安全檢查，這將佔用系統資源。
硬分析：第一次提交一條 SQL 語句，而在共享池中未找到可共享的匹配。硬分析最耗費資源，且不可伸縮，因為硬分析會執行分析中所涉及的所有操作。
如果正確使用繫結變數，則可能存在更多的軟分析，從而減少硬分析，並將分析後的語句在庫快取記憶體中儲存更長時間。

SQL 儲存
Oracle 伺服器使用庫快取記憶體和 SQLAREA 來儲存 SQL 語句和 PL/SQL 塊。如果將語句儲存在快取記憶體中，Oracle 伺服器將：
將語句減少到 ASCII 文字的數值
使用此數字的雜湊函式
將此語句的遊標置於雜湊鏈上
雜湊值不是唯一值，多條語句可以雜湊到相同的值。這些語句的遊標上下文都儲存在同一雜湊鏈中。搜尋雜湊鏈以獲得正確的語句。任何時候提交語句，都會搜尋快取記憶體。如果沒有找到遊標控制程式碼，則會從該語句構建遊標。後續提交語句時，會找到該遊標控制程式碼並重復使用此遊標。
若已分析並執行該語句，且該遊標控制程式碼仍位於客戶機快取記憶體中，則可呼叫並執行該遊標，而無需在共享池中搜尋該語句。不管何時作出分析請求，分析計數統計資訊都會遞增，但是在會話快取記憶體中尋找語句的開銷明顯降低。
注：理想情況下，在 SQL 語句第一次提交時進行一次硬分析，而後對每個使用該語句的其它會話進行一次軟分析。這取決於會話快取記憶體和共享池中是否存在足夠的記憶體來保留遊標資訊。

遊標的使用和分析
每個開發人員都希望其程式碼能夠以儘可能快的速度執行。對於使用 SQL 語句的程式碼，這意味著遊標訪問的速度必須很快。訪問遊標可能的最快方法是透過伺服器會話的會話記憶體中開啟的遊標快取記憶體進行訪問。在開啟的遊標快取記憶體中每個開啟的遊標都有一個指向該遊標控制程式碼 SGA 記憶體位置的指標。要執行該遊標，需要使用指標，而不需要分析。一個開啟的遊標是已經過分析的遊標，並且遊標控制程式碼位於庫快取記憶體中。
如果已將 SESSION_CACHED_CURSORS 引數設定為某個值，則在關閉遊標後，會將遊標資訊移至會話已關閉的遊標快取記憶體中。（版本 10.2.0.2 之前，預設值為 0，在該版本中已更改為 50。）
開啟遊標時，會話會雜湊 SQL 語句，並在關閉的遊標快取記憶體中執行雜湊查詢。如果找到該遊標，則會將其移至開啟的遊標快取記憶體中，然後使用指向共享池中該遊標控制程式碼的指標來執行該遊標，無需進行任何分析。
如果未在會話中找到遊標，則會使用雜湊值在共享池中搜尋該遊標控制程式碼的雜湊鏈。搜尋將註冊為分析。如果找到遊標控制程式碼，並且剩餘的遊標尚未到期釋放，則執行此遊標。這屬於軟分析。

遊標的使用和分析（續）
如果遊標已在共享池中到期釋放，或者遊標不存在於共享池中，則會構建該遊標。這屬於硬分析。構建遊標需要查詢獨立物件（如表、索引、區和序列）的後設資料。如果尚未將這些物件的後設資料快取記憶體在共享池中，則會生成遞迴 SQL 以提取資料字典中的資訊。
在大量遊標提交到共享池且共享池記憶體分配不足的某些情況下，遊標可能很快便在快取記憶體中到期釋放，即時此時位於兩次提取之間。這種情況將導致出現大量硬分析。
注：有關最佳化共享池，以便最佳化遊標處理的詳細資訊，請參閱“最佳化共享池”課程。

繫結階段
繫結階段期間：
Oracle DB 檢查該語句對繫結變數的引用。
Oracle DB 會為每個變數分配或重新分配一個值。
當在某一語句中使用繫結變數時，最佳化程式將假設需要使用遊標共享，且不同的呼叫應使用相同的執行計劃。這有助於透過減少大量執行硬分析來改善效能。
如果存在直方圖，則最佳化程式將假定資料分配與該最佳化程式的預設假設不匹配。因此，如果使用不同的執行計劃，在使用不同繫結變數多次呼叫遊標時將獲得顯著的優勢。此情況下，自適應遊標共享將建立新計劃。如果不嘗試新計劃，則效能可能會因為某些繫結變數值而降低。
遊標共享受 Oracle Database 11g 的資料庫初始化引數和自適應遊標共享功能影響。有關詳細資訊，請參閱“最佳化共享池”課程。

執行階段
執行計劃是一系列步驟，伺服器程式使用這些步驟訪問並確定資料緩衝區中的所需資料行。多個使用者可共享相同執行計劃。Oracle DB 針對 DML 語句執行物理讀取或邏輯讀取/寫入，同時視需要進行資料排序。
注：物理讀取為磁碟讀取；邏輯讀取涉及的是資料庫緩衝區快取記憶體記憶體中已存在的塊。物理讀取需要磁碟 I/O，因此會佔用更多資源和時間。
提取階段
提取階段，Oracle DB 針對 SELECT 語句進行行檢索。每個提取操作通常使用陣列提取對多行進行檢索。陣列檢索可透過減少網路往返次數來提高效能。每個 Oracle 工具都有自己的方式來調整陣列大小；例如，在 SQL*Plus 中，可透過使用 ARRAYSIZE 設定來更改提取大小：
SQL> show arraysize arraysize 15 SQL> set arraysize 50
SQL*Plus 每次預設處理 15 行。過大的陣列幾乎不具優勢，或根本不具優勢。

DML 處理步驟
資料操縱語言 (DML) 語句僅需要兩個處理階段：
分析階段與處理查詢使用的分析階段相同。
執行階段需要執行其它處理才能進行資料更改。
DML 執行階段
執行 DML 語句：
1. 如果緩衝區快取記憶體中沒有資料塊和回退塊，則伺服器程式會將其從資料檔案讀取到緩衝區快取記憶體。伺服器程式將鎖定要修改的行。
2. 伺服器程式將記錄對資料緩衝區所作的更改以及還原更改。這些更改將在修改記憶體中的資料和回退緩衝區之前寫入到重做日誌緩衝區中。這稱作“先行寫事件記錄”。
3. 回退緩衝區包含修改操作前資料的值。回退緩衝區用於儲存之前的資料映像，因此可視需要回退 DML 語句。資料緩衝區將記錄資料的新值。
4. 使用者將獲得該 DML 操作的反饋（如該操作影響的行數）。

DML 處理步驟（續）
DML 執行階段（續）
由於 DML 而更改的所有記憶體中資料塊和回退塊（緩衝區快取記憶體中）將標記為灰緩衝區，即不同於磁碟上的對應塊。資料庫寫入程式 (DBWR) 不會將這些緩衝區立即寫入到磁碟。當提交事務處理時，日誌寫入程式將立即在重做日誌檔案中記錄對這些塊進行的更改的重做更改記錄，且灰塊最終由 DBWR 寫入磁碟，這將由增量檢查點演算法確定。注：在 DBWR 將灰塊寫入磁碟之前，必須將灰塊的重做更改記錄寫入重做日誌檔案。
UPDATE、DELETE 或 INSERT 命令均使用類似的步驟。DELETE 命令執行之前的映像包含要刪除行的列值，而 INSERT 命令執行之前的映像僅包含行位置資訊。
提交事務處理之前，對塊進行的更改僅記錄在記憶體結構中，而不會立即寫入到磁碟中。例項程式遵循一種惰性寫入演算法，以提高整體效能。提交事務處理之後，寫入操作將是永久性的。在 LWGR 程式將重做資訊記錄到磁碟之前，不會發布“已提交”訊息，以確保完整的可恢復性。DBWR 根據檢查點演算法將資料塊寫入到磁碟。提交事務處理前，如果計算機出現故障導致 SGA 丟失，則將同時丟失這些更改。遵循的規則是：提交事務處理之前，該事務處理並非永久性的。
有關處理資料庫緩衝區快取記憶體的詳細資訊，請參閱“最佳化緩衝區快取記憶體”課程。

快速提交
Oracle 採用一種快速提交機制，來保證出現例項故障時能夠恢復已提交的更改。
系統更改號
每當提交事務處理時，Oracle DB 將為該事務處理分配一個唯一的系統更改號 (SCN)。Oracle DB 將 SCN 用作內部時間戳來讓資料保持同步，這樣從資料檔案中檢索資料時可提供讀一致性。透過 SCN 例項可執行一致性檢查，而無需依賴作業系統的日期和時間。
發出 COMMIT 時，將執行以下步驟：
伺服器程式將提交記錄連同 SCN 一起儲存在重做日誌緩衝區中。
後臺日誌寫入程式 (LGWR) 對直到提交記錄為止(包括提交記錄）的所有重做日誌緩衝區條目執行一次相鄰寫入，並寫入到重做日誌檔案中。這將保證即使出現例項故障，也不會丟失更改。
伺服器程式向使用者程式傳送一條訊息，說明事務處理完成。
最後 DBWR 基於其自身內部計時機制和增量檢查點設定，將實際資料塊更改寫回磁碟。

Oracle 最佳化程式的作用
最佳化程式是 Oracle DB 的一部分，用於為 SQL 語句建立執行計劃。確定執行計劃是處理任何 SQL 語句的重要一步，會對執行時間產生重大影響。
執行計劃是執行語句時按順序執行的一系列操作。“影響最佳化程式”課程講述了各步驟的詳細資訊。最佳化程式會考慮與被引用物件相關的以及與查詢中所指定的條件相關的許多因素。最佳化程式所需的資訊包括：
為系統（I/O、CPU 等）以及方案物件（行數、索引等）蒐集的統計資訊
字典中的資訊
WHERE 子句限定詞
開發人員提供的提示
使用諸如 Enterprise Manager、EXPLAIN PLAN 和 SQL*Plus AUTOTRACE 等診斷工具時，可以看到最佳化程式選擇的執行計劃。
注：根據其功能的不同，Oracle Database 11g 最佳化程式具有兩個名稱：查詢最佳化程式或執行時最佳化程式和自動最佳化程式。

Oracle 最佳化程式的作用（續）
最佳化程式操作：對於由 Oracle Server 處理的任何 SQL 語句，最佳化程式將執行以下操作：
評估表示式和條件：最佳化程式首先儘可能全面評估含有常數的表示式和條件。
語句轉換：對於涉及的複雜語句，例如，關聯的子查詢或檢視，最佳化程式可能將原始語句轉換為等效的聯接語句。
選擇最佳化程式方法：最佳化程式確定最佳化目標。
選擇訪問路徑：對於語句訪問的每個表，最佳化程式選擇一或多個可用的訪問路徑以獲得表資料。如果沒有可用的統計資訊（如使用點陣圖索引），最佳化程式將跳過某些訪問路徑。
選擇聯接順序：對於聯接兩個以上表的聯接語句，該最佳化程式首先選擇聯接哪兩個表，然後選擇哪個表將聯接到結果，等等。
選擇聯接方法：對於任何聯接語句，最佳化程式選擇用於聯接的操作。
注：對於不同的 Oracle DB 版本，最佳化程式可能不會作出相同的決定。在最近的版本中，因為有更多資訊可用，最佳化程式可能作出不同的決定。
最佳化程式有兩種工作模式，第一種為執行時最佳化程式，是常用模式，該模式在執行時建立執行計劃。在此模式中，該最佳化程式的時間有限，其僅可考慮有限個數的備選方案。第二種模式稱為自動最佳化程式 (ATO)。在此模式中，最佳化程式有更多時間考慮更多選項和收集統計資訊。ATO 可生成更好的計劃，並建立 SQL 概要檔案，未來每當提交 SQL 語句時，該概要檔案將幫助最佳化程式選擇更好的計劃。

確定不良 SQL
SQL 的優勢之一是，可以編寫不同的 SQL 語句來產生相同的結果。任何能夠產生正確結果的 SQL 語句便是正確的 SQL 語句。但是，不同的 SQL 可能需要不同數量的資源。不良 SQL 可能是正確的，但是效率不高，需要更多資源。
不良 SQL 的症狀可能是幻燈片上所列特徵的任何一項。下一張幻燈片中所示的頂級 SQL 報表提供了一種查詢消耗最多系統資源的 SQL 語句的方法。
導致不良 SQL 原因可能是不當的設計、不良的編碼，或是最佳化程式選擇了低效的執行計劃。DBA 很少有機會控制設計或程式碼，但是可以影響最佳化程式來生成更好的執行計劃。
從理論上講，給定關係資料集上任何給定的結果集都存在一個最優執行計劃。最佳化程式試圖在給定的時間和資源的限制條件下找到該最優執行計劃。找到該最優計劃可能需要很長時間。例如，您可能不願等待最佳化程式花 5 分鐘時間生成一個能將執行時減少 5 秒鐘的計劃。最佳化程式評估試用執行計劃的順序受包括 SQL 編寫方式在內的多種因素的影響。

頂級 SQL 報表
在最佳化方面，最大的投資收益在於 SQL 最佳化。頂級 SQL 報表在確定佔用系統資源最多的語句方面非常有效。研究表明，通常 20% 的 SQL 語句佔用了 80% 的資源，而 10% 的語句佔用了 50% 的資源。這意味著，透過確定並最佳化頂級 SQL 語句，可改善整個系統的效能。
使用頂級 SQL 報表簡化了查詢佔用資源最多的 SQL 語句的過程。AWR 和 Statspack 報表都包括一組頂級 SQL 列表。每個報表以若干類別按照資源使用情況排序列出了頂級 SQL 語句。這些類別包括：用時、CPU 時間、獲取數、讀取數、執行數、分析呼叫、可共享記憶體和版本計數。各個報表不包括完整的 SQL 文字，但各個報表之後按照 SQL_ID 給出了所有 SQL 文字的報表。
預設情況下，這些報表中不包括全部 SQL 語句。所包括語句的數目由 AWR 的 topnsql 引數設定和 Statspack 中的級別和閾值設定控制。有關 Statspack 引數的詳細資訊，請參閱附錄“使用 Statspack”。

什麼是執行計劃？
執行某一語句時，伺服器將執行由該最佳化程式建立的計劃步驟。每一步驟要麼從資料庫物理檢索資料行，要麼以某種方式為發出該語句的使用者準備資料行。用於執行語句的步驟的組合稱作“執行計劃”。
執行計劃包括語句所訪問的每個表的訪問方法以及這些表的順序（聯接順序）。最佳化程式還使用不同的方法來組合多個表的行（聯接方法）。執行計劃的步驟並不按編號順序執行。
透過執行計劃，可檢視最佳化程式所選的方法。有時執行計劃會明確說明某一語句效率不高的原因；例如，當某一索引查詢選擇全表掃描 (FTS) 時，這種掃描涉及很多 I/O 操作。這種情況下，問題就變為最佳化程式為什麼選擇 FTS。“影響最佳化程式”課程詳細說明了此類問題。

檢視執行計劃的方法
透過 EXPLAIN PLAN 命令，可檢視最佳化程式用來執行 SQL 語句的執行計劃，而無需執行該 SQL 語句。
SQL 跟蹤實用程式用於度量 SQL 語句的計時統計資訊。
自動工作量資料檔案庫 (AWR) 是 Oracle Database 11g 中的一個內建資料檔案庫。Oracle DB 定期捕獲其所有重要統計資訊和工作量資訊的快照，並將快照儲存在 AWR 中，包括佔用資源較多的 SQL 語句的列表。AWR 資料包括執行計劃。
V$SQL_PLAN 檢視包含有關所執行 SQL 語句及其執行計劃（仍位於共享池中）的資訊。
SQL*Plus 中可用的 AUTOTRACE 命令生成 PLAN_TABLE 輸出和有關查詢的效能的統計資訊。此命令提供了許多與 SQL 跟蹤相同的統計資訊，諸如磁碟讀取和記憶體讀取。
您可使用 DBMS_XPLAN 程式包方法來顯示由 EXPLAIN PLAN 命令和 V$SQL_PLAN 查詢以及 AWR 生成的執行計劃。

使用執行計劃
檢視執行計劃用於：
確定當前的執行計劃
確定在一個表上建立索引的效果
查詢包含某一特定訪問路徑的遊標（例如，全表掃描或索引範圍掃描）
確定最佳化程式所選或所未選的索引
確定最佳化程式是否選擇開發人員所期望的特定執行計劃（例如，巢狀迴圈聯接）
您可使用執行計劃來作出以下決定：
刪除或建立索引
生成資料庫物件的統計資訊
修改初始化引數值
將應用程式或資料庫遷移到一個新的版本
預設情況下，SQL 在共享池中到期釋放後，將不保留執行計劃。如果之前所用的計劃保留在使用者定義的表中，或作為基線計劃載入，則確定 SQL 語句效能的更改如何與該語句的執行計劃的更改相關聯是可能的。

DBMS_XPLAN 程式包：概覽
DBMS_XPLAN 程式包提供了一種簡單方法來以若干預定義格式顯示 EXPLAIN PLAN 命令的輸出。還可使用 DBMS_XPLAN 程式包來顯示儲存在 AWR 中的語句的計劃。此外，它還提供了一種方法，用以基於 V$SQL_PLAN 固定檢視和 V$SQL_PLAN_STATISTICS_ALL 固定檢視中儲存的資訊來顯示快取記憶體的 SQL 遊標的 SQL 執行計劃和 SQL 執行執行時統計資訊。
DBMS_XPLAN 程式包提供了可用於檢索和顯示執行計劃的三種表函式：
DISPLAY 格式化並顯示 PLAN_TABLE 的計劃表的內容。
DISPLAY_AWR 格式化並顯示儲存在 AWR 中的 SQL 語句的執行計劃的內容。
DISPLAY_CURSOR 格式化並顯示任何從 V$SQL_PLAN 檢視載入的遊標的執行計劃的內容。

DBMS_XPLAN 程式包：概覽（續）
此程式包的方法包含一個 FORMAT 引數，透過此引數，可指定所顯示計劃的詳細級別。
BASIC：顯示最少的計劃資訊（操作 ID、物件名稱和操作選項）
TYPICAL：預設。顯示計劃中相關性最高的資訊。可用時，僅顯示分割槽修剪、並行和謂詞。
ALL：最大級別。包括 TYPICAL 級別所顯示的資訊，並新增投影資訊以及針對並行執行伺服器而生成的 SQL 語句（僅當並行時）。
SERIAL：與 TYPICAL 類似，但不顯示並行資訊，即使並行執行計劃，也同樣如此。
此程式包以呼叫使用者的許可權執行，而不以程式包所有者的許可權 (SYS) 執行。DISPLAY_CURSOR 表函式需要以下固定檢視上的 SELECT 許可權：V$SQL_PLAN、V$SESSION 和 V$SQL_PLAN_STATISTICS_ALL。使用 DISPLAY_AWR 函式需要 DBA_HIST_SQL_PLAN、DBA_HIST_SQLTEXT 和 V$DATABASE 上的 SELECT 許可權。所有這些許可權均作為 SELECT_CATALOG_ROLE 的一部分而自動授予。但是，建議您不要不加區分地授予此角色，因為這可能導致安全問題。
DISPLAY_CURSOR 和 DISPLAY_AWR 函式接受 SQL_ID 作為一個引數（如本課程後續示例所示）。可透過查詢 V$SQL 或 DBA_HIST_SQLTEXT 來獲得語句的 SQL_ID。

EXPLAIN PLAN 命令
EXPLAIN PLAN 命令用於生成最佳化程式用以執行 SQL 語句的執行計劃。其不執行語句，但簡單生成可能使用的計劃，並將此計劃插入到表中。如果檢視計劃，可看到 Oracle 伺服器執行相應語句的方式。
要使用 EXPLAIN PLAN，必須：
首先使用 EXPLAIN PLAN 命令解釋 SQL 語句
使用 DBMS_XPLAN 程式包中的方法檢索計劃步驟
PLAN_TABLE 是作為全域性臨時表而自動建立的，用於為所有使用者儲存 EXPLAIN PLAN 語句的輸出。PLAN_TABLE 是預設的示例輸出表，EXPLAIN PLAN 語句將在其中插入說明執行計劃的行。
注：EXPLAIN PLAN 可能生成一個不同於最佳化程式實際所使用計劃的計劃，其原因如下：
EXPLAIN PLAN 命令無法訪問繫結變數。
鑑於登入觸發器或會話引數設定，SQL*Plus 會話可能具有不同的環境。
V$SQLPLAN 將使用該實際計劃。

EXPLAIN PLAN 命令：示例
此命令將 SQL 語句的執行計劃插入到計劃表中，並新增名稱標記 demo01，以便後續參考。標記是可選的。也可以使用以下語法：
EXPLAIN PLAN
FOR
SELECT e.last_name, d.department_name FROM hr.employees e, hr.departments d
WHERE e.department_id =d.department_id;

EXPLAIN PLAN 命令：輸出
DBMS_XPLAN 程式包的 DISPLAY 函式可用於格式化並顯示計劃表中儲存的最後一條語句。
幻燈片顯示了按照上一張幻燈片上所示使用 DBMS_XPLAN 程式包檢索該示例的 PLAN 表中的資訊。
還可使用以下所示語法來檢索 PLAN 表。
SELECT plan_table_output FROM TABLE(dbms_xplan.display('plan_table','demo01','serial'));
輸出的內容與幻燈片中顯示的內容相同。在此示例中，可使用另一計劃表名稱替換 PLAN_TABLE，'demo01' 代表語句 ID。
您可執行 utlxpls.sql 指令碼（位於 ORACLE_HOME/rdbms/admin/ 目錄下）以顯示所解釋的最後一條語句的 EXPLAIN PLAN。該指令碼使用 DBMS_XPLAN 程式包的 DISPLAY 表函式。

讀取執行計劃
可從執行計劃構造一棵執行樹（或“分析樹”），以更清晰瞭解如何處理語句。要構造該樹，請從步驟 1 開始。然後查詢步驟 1 的所有子步驟，並在步驟 1 下方將其繪製成子級步驟或分支。對於每個步驟，重複此過程查詢該步驟的所有子步驟，直到所有步驟都繪出。Oracle DB 為執行計劃中的每個步驟分配一個編號，表示 PLAN_TABLE 的 ID 列。每個步驟由一個“節點”表示。每個節點的操作結果會傳遞到其父節點，父節點將此結果用作輸入。
步驟的順序由這些步驟的父-子關係來確定。執行計劃的每個步驟從資料庫檢索行，或者將一或多個其它步驟（也稱作“行源”）的行接受為輸入。子步驟將至少被執行一次，且結果會饋送至父步驟。當一個父步驟具有多個子步驟時，將按照步驟的位置次序執行各子步驟。如果下層子步驟是按照從左向右排列的，則可從左向右、從下到上讀取計劃。
圖中，編號對應於 PLAN 表中的 ID 值（參看前一張幻燈片）。最佳化程式透過在主鍵列執行 FULL INDEX SCAN 而使用索引掃描，從 DEPARTMENTS 表檢索行。然後在 EMPLOYEES 表上執行一次 FULL TABLE SCAN 和 SORT 操作。然後對兩個結果集執行 MERGED 以獲得查詢的最終結果。

使用 V$SQL_PLAN
該檢視提供了一種方法，用於檢查最近執行過的遊標的執行計劃。此檢視中的資訊非常類似於 PLAN_TABLE 的輸出。但是，EXPLAIN PLAN 顯示的是執行相應語句時可以使用的理論計劃，而 V$SQL_PLAN 包含實際使用的計劃。鑑於繫結變數取數、cursor_sharing 引數設定等原因，EXPLAIN PLAN 語句獲得的執行計劃可能不同於所用的實際執行計劃。
V$SQL_PLAN 顯示了特定遊標的計劃。每條 SQL 語句可能具有多個相關的遊標，各遊標以 CHILD_NUMBER 標識。例如，如果所引用的物件位於不同方案中，則不同使用者執行的同一語句具有不同的相關遊標。提示不同或繫結變數值不同可能引起遊標不同。V$SQL_PLAN 可用於檢視同一語句的不同子游標的不同計劃。
注：另一有用檢視為 V$SQL_PLAN_STATISTICS，此檢視為每個快取的遊標的執行計劃中的每個操作提供執行統計資訊。同時，V$SQL_PLAN_STATISTICS_ALL 檢視組合了 V$SQL_PLAN 中的資訊和 V$SQL_PLAN_STATISTICS 以及 V$SQL_WORKAREA 中的執行統計資訊。

V$SQL_PLAN 列
V$SQL_PLAN 檢視的幾乎所有列都顯示在 PLAN_TABLE 列中。兩個檢視中，同名的列意義相同。
ADDRESS 和 HASH_VALUE 列可用於聯接 V$SQLAREA，以新增特定於遊標的資訊。
ADDRESS、HASH_VALUE 和 CHILD_NUMBER 列可用於聯接 V$SQL，以新增特定於子游標的資訊。

查詢 V$SQL_PLAN
可使用 DBMS_XPLAN.DISPLAY_CURSOR() 函式來查詢 V$SQL_PLAN，以顯示當前或上一次執行的語句（如示例所示）。對於給定語句，可將該語句的 SQL_ID 值作為引數傳遞，以獲得執行計劃。要獲得 SQL_ID：
SELECT e.last_name, d.department_name
   FROM hr.employees e, hr.departments d WHERE  e.department_id =d.department_id;

   SELECT SQL_ID, SQL_TEXT FROM V$SQL
WHERE SQL_TEXT LIKE '%SELECT e.last_name,%' ;

13saxr0mmz1s3  select SQL_id, sql_text from v$SQL …
cfz0cdukrfdnu  SELECT e.last_name, d.department_name …
FORMAT 引數控制計劃的詳細級別。除了標準值（BASIC、TYPICAL、SERIAL 和 ALL）外，還有兩個受支援的值，用於顯示遊標的執行時統計資訊。
RUNSTATS_LAST：顯示上一次執行該遊標的執行時統計資訊
RUNSTATS_TOT：顯示自第一次分析和執行某一特定 SQL 語句之後，該語句所有執行的全部執行時統計資訊

V$SQL_PLAN_STATISTICS 檢視
V$SQL_PLAN_STATISTICS 檢視提供了計劃中每個操作的實際執行統計資訊，如輸出行數和所用時間。除輸出行數外，所有統計資訊都是累計的結果。例如，聯接操作的統計資訊還包括其兩個輸入的統計資訊。V$SQL_PLAN_STATISTICS 中的統計資訊對已編譯（在 STATISTICS_LEVEL 初始化引數設定為 ALL 的情況下進行編譯）的遊標是可用的。
V$SQL_PLAN_STATISTICS_ALL 檢視包含了使用 SQL 記憶體的行源的記憶體使用情況統計資訊（排序或雜湊聯接）。此檢視連線 V$SQL_PLAN 中的資訊與 V$SQL_PLAN_STATISTICS 和 V$SQL_WORKAREA 的執行統計資訊。

查詢 AWR
可使用 DBMS_XPLAN.DISPLAY_AWR() 函式來顯示 AWR 中儲存的所有計劃。本示例將 SQL_ID 作為引數傳入。完成此示例的步驟如下：
1. 執行 SQL 語句。
       SQL> select /* example */ *
   2> from hr.employees natural join hr.departments;
2. 查詢 V$SQL_TEXT 以獲得 SQL_ID。
SQL> select sql_id, sql_text from v$SQL
  2> where sql_text like '%example%';

SQL_ID       SQL_TEXT
------------- -------------------------------------------
F8tc4anpz5cdb select sql_id, sql_text from v$SQL …
454rug2yva18w select /* example */ * from …
3. 使用 SQL_ID 確認 DBA_HIST_SQLTEXT 字典檢視已捕獲此語句。如果查詢未返回行，則表示 AWR 中尚未載入該語句。

查詢 AWR（續）
SQL> SELECT SQL_ID, SQL_TEXT FROM dba_hist_sqltext WHERE SQL_ID =' 454rug2yva18w';
no rows selected
您可手動獲取 AWR 快照，而不是等待下一張快照（每小時採集一次）。如果 SQL 不在 topnsql 範圍內，可能不會捕獲 SQL。為此，可使用 MODIFY_SNAPSHOT_SETTING 過程修改 topnsql 範圍來強制捕獲所有 SQL 語句。然後在 DBA_HIST_SQLTEXT 中檢查是否已捕獲：
SQL> exec –
  2> DBMS_WORKLOAD_REPOSITORY.MODIFY_SNAPSHOT_SETTINGS(-
  3> topnsql => 'MAXIMUM');

PL/SQL procedure successfully completed.

SQL> exec DBMS_WORKLOAD_REPOSITORY.CREATE_SNAPSHOT();

PL/SQL procedure successfully completed.

SQL> exec –
  2>  DBMS_WORKLOAD_REPOSITORY.MODIFY_SNAPSHOT_SETTINGS(-
  3>  topnsql => 'DEFAULT');

PL/SQL procedure successfully completed.

SQL> SELECT SQL_ID, SQL_TEXT FROM dba_hist_sqltext WHERE SQL_ID =' 454rug2yva18w';
SQL_ID       SQL_TEXT
--------------  -------------------------------
454rug2yva18w select /* example */ * from …

4. 使用 DBMS_XPLAN.DISPLAY_AWR () 函式檢索執行計劃：
SQL>SELECT PLAN_TABLE_OUTPUT FROM TABLE (DBMS_XPLAN.DISPLAY_AWR('454rug2yva18w’));
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------
SQL_ID 454rug2yva18w
--------------------
select /* example */ * from hr.employees natural join hr.departments

Plan hash value: 2052257371

----------------------------------------------------------------------------------
| Id  | Operation       | Name       | Rows  | Bytes | Cost (%CPU)| Time    |
----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT |          |    |    |    7 (100)|       |
| 1 |  HASH JOIN       |          | 11 | 968 |    7  (15)| 00:00:01 |
| 2 | TABLE ACCESS FULL| DEPARTMENTS | 11 | 220 |    3 (0)| 00:00:01 |
| 3 | TABLE ACCESS FULL| EMPLOYEES | 107 |  7276 |    3 (0)| 00:00:01 |
----------------------------------------------------------------------------------

SQL*Plus AUTOTRACE
在 SQL*Plus 中，可透過使用 AUTOTRACE 設定來自動獲得執行計劃以及有關執行 SQL 命令的某些其它統計資訊。與 EXPLAIN PLAN 命令不同，該語句會實際執行。但是，可透過指定 AUTOTRACE TRACEONLY EXPLAIN 來選擇隱藏語句結果。
AUTOTRACE 是用於最佳化 SQL 語句的方便診斷工具。由於其完全屬宣告性質，使用起來較 EXPLAIN PLAN 容易。
命令選項
OFF 禁用 SQL 語句的自動跟蹤
ON 啟用 SQL 語句的自動跟蹤
TRACEONLY 啟用 SQL 語句的自動跟蹤，並隱藏語句輸出
EXPLAIN 顯示執行計劃，但不顯示統計資訊
STATISTICS 顯示統計資訊，但不顯示執行計劃
注：如果忽略 EXPLAIN 和 STATISTICS 命令選項，將預設顯示執行計劃和統計資訊。

使用 SQL*Plus AUTOTRACE
先決條件
要訪問 STATISTICS 資料，必須具有訪問若干動態效能表的許可權。DBA 可透過使用在 plustrce.sql 指令碼中建立的 PLUSTRACE 角色來授予許可權。DBA 必須以 SYS 使用者的身份執行該指令碼，具有 DBA 角色的任何人都可對要使用 AUTOTRACE 的選項 STATISTICS 的使用者授予 PLUSTRACE 角色。
示例
幻燈片顯示了 AUTOTRACE 命令的示例。
控制 AUTOTRACE 執行計劃佈局
執行計劃由按以下順序顯示的四列組成：
ID_PLUS_EXP 各步驟的行號
PARENT_ID_PLUS_EXP 父步驟行號
PLAN_PLUS_EXP 報表步驟
OBJECT_NODE_PLUS_EXP 所用的資料庫連結或並行查詢伺服器
您可更改這些列的格式，或透過使用 SQL*Plus COLUMN 命令隱藏這些列。有關更多資訊，請參閱《Oracle SQL*Plus User’s Guide and Reference》。

SQL*Plus AUTOTRACE：統計資訊
AUTOTRACE 將顯示多種統計資訊，這些資訊並非全部與此階段的討論有關。最重要的統計資訊包括以下內容：
db block gets 當前獲取數的邏輯 I/O 數
consistent gets 緩衝區快取記憶體塊的讀取數
physical reads 從磁碟讀取的塊數
redo size 所生成的重做數（針對 DML 語句）
sorts (memory) 在記憶體中執行的排序數
sorts (disk) 使用臨時磁碟儲存執行的排序數
注：資料庫塊獲取數為緩衝區快取記憶體中當前塊的讀取數。一致的獲取數是具有還原資料的緩衝區快取記憶體塊的讀取數。物理讀取數為磁碟塊讀取數。通常會監視三種統計資訊：資料庫塊獲取數、一致獲取數和物理讀取數。與檢索的行數相比，這些數值應該比較低。應該在記憶體中（而非在磁碟上）執行排序。

SQL 跟蹤工具
如果您使用的是標準版，或不具備診斷包，則使用 SQL 跟蹤工具和 TKPROF 可收集 SQL 執行計劃的統計資訊，以便比較效能。比較兩個執行計劃的一種較好方法是：執行這些語句，並比較統計資訊以確定哪個更好。SQL 跟蹤將其會話統計資訊輸出寫入到一個檔案，您可使用 TKPROF 對該檔案進行格式化。您可使用這些工具以及 EXPLAIN PLAN 來獲得最佳結果。
SQL 跟蹤工具：
可為會話或例項啟用
報告分析、執行和提取階段的容量和時間統計資訊
產生可由 TKPROF 格式化的輸出
當為某一會話啟用 SQL 跟蹤工具時，Oracle DB 會生成一個跟蹤檔案，其中包含該會話的跟蹤 SQL 語句的會話統計資訊。當為某一例項啟用 SQL 跟蹤工具時，Oracle DB 會為所有會話建立跟蹤檔案。
注：SQL 跟蹤涉及一些開銷，因而您可能不希望在例項級啟用 SQL 跟蹤。

SQL 跟蹤工具（續）
SQL 跟蹤工具提供有關各個 SQL 語句的效能資訊。SQL 跟蹤提供以下資訊（包括行源資訊）：
分析、執行和提取計數
CPU 時間和佔用時間
物理讀取數和邏輯讀取數
處理的行數
庫快取記憶體未命中數
每次分析所用的使用者名稱
每次提交和回退
顯示每一 SQL 語句的實際執行計劃的行操作
行數、一致讀取數、物理讀取數、物理寫入數以及針對行的每一操作的用時
注：使用 TKPROF 實用程式可獲得每個跟蹤檔案的概要。

使用 SQL 跟蹤工具的方法
必須完成以下步驟以使用 SQL 跟蹤：
1. 設定適當的初始化引數。
2. 啟用 SQL 跟蹤。
3. 執行應用程式（並在完成時禁用跟蹤）。
4. 禁用 SQL 跟蹤。
5. 關閉會話（關閉會話將同時禁用會話級的跟蹤）。
6. 使用 tkprof 格式化由 SQL 跟蹤產生的跟蹤檔案。
7. 解釋輸出，並視需要最佳化 SQL 語句。
執行 SQL 跟蹤將增加系統開銷。視需要僅使用 SQL 跟蹤，並且在會話級使用，而非在例項級使用。
注：此示例假設使用專用伺服器。在共享伺服器環境、XA 或應用程式級連線共享情況下，多個會話可能服務於某單個會話。需要跟蹤所有涉及的伺服器，並透過使用 trcsess 實用程式來組合跟蹤檔案，然後提交到 tkprof 以便格式化。有關 trcsess 的詳細資訊，請參閱“應用程式監視”課程。

初始化引數
有多個初始化引數與 SQL 跟蹤有關。
STATISTICS_LEVEL
Oracle 提供的 STATISTICS_LEVEL 初始化引數控制所有主要的統計資訊收集或資料庫中的指導。該引數設定資料庫的統計資訊收集級別。根據 STATISTICS_LEVEL 的設定收集某些指導或統計資訊。
BASIC：不收集指導或統計資訊。禁用監視和許多自動功能。Oracle 建議您不要使用此設定，因為這將禁用重要的 Oracle 功能。
TYPICAL：此為預設值，且確保收集所有主要統計資訊，同時提供最佳整體資料庫效能。對於大多數環境而言，此設定應足夠了。TYPICAL 導致啟用 TIMED_STATISTICS。
ALL：包括 TYPICAL 設定所收集的所有指導或統計資訊，加上定時作業系統統計資訊和行源執行統計資訊。
此檢視列出了受 STATISTICS_LEVEL 控制的統計資訊或指導的狀態。

初始化引數（續）
TIMED_STATISTICS
SQL 跟蹤工具提供某一程式中有關 SQL 執行的各種資訊，且可選地包括定時資訊。如果需要定時資訊，則必須將此引數設定為 TRUE。STATISTICS LEVEL 引數將自動設定此引數。透過以下方式設定引數檔案中的 TIMED_STATISTICS 引數，可從 STATISTICS_LEVEL 單獨設定此引數：
SQL> ALTER SYSTEM SET TIMED_STATISTICS = TRUE
對於特定會話，也可使用以下命令動態設定此引數：
SQL> ALTER SESSION SET timed_statistics=TRUE;
定時統計資訊以微秒計。
MAX_DUMP_FILE_SIZE
如果在例項級啟用 SQL 跟蹤工具，則每次對伺服器的呼叫都會在一個檔案（以作業系統的檔案格式）中產生一個文字行。這些檔案（作業系統塊中）的最大大小受此初始化引數限制。這是一個動態引數，也是一個會話引數。
警告：預設值為 UNLIMITED，因此這些跟蹤檔案可能增長而填滿檔案系統。
DIAGNOSTIC_DEST 是自動診斷資料檔案庫的根目錄。此目錄的預設值派生自 ORACLE_BASE 環境變數，在 UNIX 下，其為 $ORACLE_BASE/diag。啟用該 Trace 工具時生成的檔案將放在此資料檔案庫的子目錄下：../rdbms/<db_name>/<instance_name>/trace。
獲得有關引數設定的資訊
可透過查詢 V$PARAMETER 檢視來顯示當前的引數值：
SQL> SELECT name, value
  2  FROM v$parameter
  3  WHERE  name LIKE '%dest%';
或者，可使用以下方法：
SQL> SHOW PARAMETER dest

為某一會話啟用 SQL 跟蹤
可使用所示命令為會話啟用 SQL 跟蹤。當要從 PL/SQL 單元內啟用或禁用 SQL 跟蹤時，這些過程呼叫比較有用。
DBA 還可使用所提供的程式包為另一使用者的會話啟用 SQL 跟蹤。
  SQL> EXECUTE dbms_system.set_sql_trace_in_session 2  (session_id, serial_id, true);
在此過程呼叫中，session_id 和 serial_id 為 V$SESSION 的 SID 和 SERIAL# 列中的值，其中 V$SESSION 是資料庫管理員常用的資料字典檢視。
若要為整個例項啟用 SQL 跟蹤，則使用 DBMS_MONITOR 程式包中的 DATABASE_TRACE_ENABLE 過程。
警告：例項範圍內的跟蹤將產生大量的跟蹤檔案，並會影響效能。
注：在可以使用 DBMS_MONITOR 程式包之前，必須在其上授予 EXECUTE 許可權。
透過使用 10046 事件，可將等待事件資訊寫入該會話的跟蹤檔案中。有關此事件的詳細資訊，請參閱 MetaLink Note: 171647.1，“Tracing Oracle Applications”。要捕獲等待事件資訊，請執行以下 SQL 語句：
ALTER SESSION SET EVENTS '10046 trace name context forever,level 8';

為某一會話禁用 SQL 跟蹤
完成最佳化之後，使用前述方法之一禁用 SQL 跟蹤，用 FALSE 一詞替換 TRUE，或用 disable 替換 enable。如果為單次會話啟用了 SQL 跟蹤，則退出該會話將同時禁用 SQL 跟蹤。

格式化跟蹤檔案
使用 TKPROF 命令將跟蹤檔案格式化為可讀的輸出。TKPROF 語法如下：
OS> tkprof tracefile outputfile [options]
tracefile 跟蹤輸出檔案的名稱（對 TKPROF 而言是輸入）outputfile 儲存格式化結果的檔案的名稱
如果不帶任何引數執行 TKPROF 命令，則會生成一條用法訊息以及所有TKPROF 選項的說明。請參閱下一張幻燈片上的完整列表。以下為當不帶任何引數執行 TKPROF 命令時得到的輸出：Usage: tkprof tracefile outputfile [explain= ] [table= ]
            [print= ] [insert= ] [sys= ]    [sort= ]
預設情況下，.trc 檔案以 SPID 命名。可在 V$PROCESS 中找到 SPID。以下是查詢該檔案的更簡單方法：
SQL> ALTER SESSION SET TRACEFILE_IDENTIFIER = 'MY_FILE';
然後 TKPROF 中的跟蹤檔案將包含“MY_FILE”字串。

TKPROF 命令選項
黑體顯示的選項是最常用的選項：
INSERT 建立 SQL 指令碼以將 TKPROF 結果載入到資料庫表中
SORT 報表中對語句排序的順序（請參閱下一頁的值列表）
PRINT 僅在這麼多個（已排序）語句上生成報表（此選項與 SORT 選項組合尤其有用。）
EXPLAIN 以指定方案登入並執行 EXPLAIN PLAN
SYS 禁用使用者 SYS 執行的遞迴 SQL 語句的列表
AGGREGATE 禁用或啟用 TKPROF 的（預設）行為，將完全相同的 SQL 語句聚合到一條記錄中
WAITS 指定是否記錄在跟蹤檔案中找到的任何等待事件的概要
TABLE 指定在將執行計劃寫入到輸出檔案之前，用來臨時儲存執行計劃的表（如果未指定 EXPLAIN，則將忽略此引數。當若干個體同時使用 TKPROF 來最佳化同一方案時，可避免具有破壞性的干擾。）
RECORD    使用在跟蹤檔案中找到的所有非遞迴 SQL 語句建立 SQL 指令碼（此指令碼可用於後續重放最佳化會話。）

TKPROF 命令選項（續）
排序選項
prscnt 呼叫分析的次數
prscpu 分析所佔用的 CPU 時間
prsela 分析所佔用的時間
prsdsk 分析期間磁碟讀取數
prsqry 分析期間一致讀取的緩衝區數
prscu 分析期間當前讀取的緩衝區數
prsmis 分析期間庫快取記憶體中的未命中數
execnt 呼叫的執行數
execpu 執行所佔用的 CPU 時間
exeela 執行所佔用的時間
exedsk 執行期間磁碟讀取數
exeqry 執行期間一致讀取的緩衝區數
execu 執行期間當前讀取的緩衝區數
exerow 執行期間處理的行數
exemis 執行期間庫快取記憶體的未命中數
fchcnt 呼叫提取的次數
fchcpu 提取所佔用的 CPU 時間
fchela 提取所佔用的時間
fchdsk 提取期間磁碟讀取數
fchqry 提取期間一致讀取的緩衝區數
fchcu 提取期間當前讀取的緩衝區數
fchrow 提取的行數
userid 分析遊標的使用者的 ID

TKPROF 命令的輸出
TKPROF 輸出按照 SQL 處理步驟列出了 SQL 語句的統計資訊。包含統計資訊的每行的步驟由呼叫列的值來標識。
分析此步驟將 SQL 語句轉換為執行計劃，幷包括對適當安全授權的檢查和對錶、列及其它引用物件的存在的檢查。
執行此步驟中，Oracle 伺服器實際執行該語句。對於 INSERT、UPDATE 和 DELETE 語句，此步驟將修改資料（包括需要時的排序操作）。對於 SELECT 語句，此步驟將確定所選行。
提取此步驟對查詢所返回的行進行檢索，並在需要時進行排序。提取的執行僅適用於 SELECT 語句。注：“分析”值包括“硬分析”和“軟分析”。硬分析是指執行計劃的開發（包括最佳化）；然後其將儲存在庫快取記憶體中。軟分析是指，在將 SQL 語句傳送到資料庫以進行分析時，資料庫發現其位於庫快取記憶體中，僅需確認諸如訪問許可權等事宜。硬分析代價可能會比較高，尤其是因為需要最佳化。就庫快取記憶體活動而言，軟分析的代價通常比較高。

TKPROF 命令的輸出（續）
將在下頁解釋輸出。
示例輸出如下：
SQL ID ：6assxhyzbq5jf
select max(cust_credit_limit)
from customers where cust_city ='Paris'
call    count    cpu  elapsed    disk query  current       rows
------- ------  ------ -------- -------- -------- --------  ----------
Parse       1 0.00    0.02       0       0       0          0
Execute    1 0.00    0.00       0       0       0          0
Fetch       2 0.01    0.26    1455    1457       0          1
------- ------  ------ -------- -------- -------- --------  ----------
total       4 0.02    0.28    1455    1457       0          1

TKPROF 命令的輸出（續）
TKPROF 在 CALL 列旁邊顯示了每條語句的以下統計資訊：
Count 分析、執行或提取語句的次數（解釋其它列中的統計資訊之前，檢查此列                   的值是否大於 1。除非使用 AGGREGATE = NO 選項，否則 TKPROF 會將                   完全相同的語句執行聚合成一個概要表。）
CPU 所有分析、執行或提取呼叫所用的總 CPU 時間（以秒計）
Elapsed 所有分析、執行或提取呼叫所佔用的總時間（以秒計）
Disk 所有分析、執行或提取呼叫從磁碟上的資料檔案物理讀取的資料塊的總數
Query 所有分析、執行或提取呼叫在一致模式下檢索的緩衝區的總數（對於查詢，通常是在一致模式下檢索緩衝區。）
Current 當前模式下檢索的緩衝區的總數（對於 DML 語句，通常是在當前模式下檢索緩衝區。但是，始終在當前模式下檢索段頭塊。）
Rows SQL 語句處理的總行數（該總數不包括該 SQL 語句的子查詢所處理的行。對於 SELECT 語句，所返回的行數用於提取步驟。對於 UPDATE、                   DELETE 和INSERT 語句，所處理的行數用於執行步驟。）
附註
DISK 等效於 v$sysstat 或 AUTOTRACE 的物理讀取數。
QUERY 等效於 v$sysstat 或 AUTOTRACE 的一致獲取數。
CURRENT 等效於 v$sysstat 或 AUTOTRACE 的資料庫塊獲取數。

遞迴呼叫
要執行一條由使用者發出的 SQL 語句，Oracle 伺服器必須間或執行其它語句。這些語句稱作“遞迴 SQL 語句”。例如，如果在一個表中插入一行，而該表空間不足以儲存該行，則 Oracle 伺服器將進行遞迴呼叫以動態分配空間。當資料字典快取記憶體中沒有資料字典資訊而必須從磁碟檢索時，也會生成遞迴呼叫。
如果在啟用 SQL 跟蹤工具時發生遞迴呼叫，則 TKPROF 將在輸出檔案中清晰標出遞迴 SQL 語句。可透過設定 SYS=NO 命令列引數來禁用在輸出檔案中列出遞迴呼叫。請注意，遞迴 SQL 語句的統計資訊將始終包含在引起遞迴呼叫的 SQL 語句的列表中。
庫快取記憶體未命中數
TKPROF 還針對每條 SQL 語句列出了分析和執行步驟產生的庫快取記憶體未命中數。這些統計資訊將出現在表格式統計資訊之下的單行中。

遞迴呼叫（續）
行源操作
這些資訊提供了執行於行上的各個操作所處理的行數以及其它行源資訊，如物理讀取數和寫入數；cr = 一致讀取數，pw = 物理寫入數，pr = 物理讀取數，time = 時間（以微秒計），cost = 成本估計，size = 行源位元組數估計，card =基數（行數）。
分析使用者 ID
此為上一次分析該語句的使用者的 ID。
行源操作
行源操作顯示了該 SQL 語句執行的資料來源。僅當在跟蹤期間已關閉遊標時，才包含此資訊。如果行源操作未出現在跟蹤檔案中，則您可能要檢視 EXPLAIN PLAN。
執行計劃
如果在 TKPROF 命令列上指定 EXPLAIN 引數，則 TKPROF 將使用 EXPLAIN PLAN 命令來為每個跟蹤的 SQL 語句生成執行計劃。TKPROF 還顯示了執行計劃的每一步驟所處理的行數。
注：請注意，執行計劃是在執行 TKPROF 命令時生成的，而不是生成跟蹤檔案時。如果自跟蹤語句起，已建立或刪除某個索引，則結果可能明顯不同。
最佳化程式模式或提示
這表示在執行語句期間所用的最佳化程式提示。如果無提示，則將顯示所用的最佳化程式模式。

...
select max(cust_credit_limit)
from customers where cust_city ='Paris'

call    count    cpu elapsed    disk    query current       rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse       1    0.00    0.00       0       0       0          0
Execute    1    0.00    0.00       0       0       0          0
Fetch       2    0.00    0.00       77       77       0          1
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total       4    0.01    0.01       77       77       0          1

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 85  (SH)

Rows    Row Source Operation
-------  ---------------------------------------------------
   1  SORT AGGREGATE (cr=77 pr=77 pw=77 time=0 us)
   77 TABLE ACCESS BY INDEX ROWID CUSTOMERS (cr=77 pr=77 pw=77 time=555 us
cost=85 size=1260 card=90)
   77 INDEX RANGE SCAN CUST_CUST_CITY_IDX (cr=2 pr=2 pw=2 time=1 us cost=1
size=0 card=90)(object id 75264)

不帶索引的 TKPROF 輸出：示例
幻燈片中的示例展示了正從 CUSTOMERS 表提取多次執行的結果集（行）。其需要 .12 秒的 CPU 提取時間。該語句是透過全表掃描 CUSTOMERS 表而執行的，這可以從輸出的行源操作看出。
必須最佳化該語句。
注：如果 CPU 或 elapsed 的值為 0，則未設定 timed_statistics。

帶有索引的 TKPROF 輸出：示例
幻燈片中所示結果表示，當在 CUST_CITY 列上建立一個索引後，CPU 時間便減為 .01 秒。之所以實現這些結果，是因為該語句使用索引來檢索資料。另外，由於此示例是再次執行同一語句，因此大多數資料塊已經位於記憶體中。透過合理利用索引，可顯著改善效能。使用 SQL 跟蹤工具確定具有提高潛力的區域。
注：除非需要，否則不應構建索引。因為必須新增、更改或刪除對行的引用，所以索引一定會減慢 INSERT、UPDATE 和 DELETE 命令的處理速度。應刪除未使用的索引。但是，可使用索引監視功能來確定並刪除任何未使用的索引，或使用 SQL 訪問指導來確定未使用的索引，而不需透過EXPLAIN PLAN 處理所有應用程式 SQL。

生成最佳化程式跟蹤
可透過命令使最佳化程式產生對成本決定（透過命令作出）的跟蹤。此方法偶爾用於為 Oracle 技術支援提供其它有關最佳化程式行為的資訊。
ALTER SESSION SET EVENTS
'10053 trace name context forever, level 1';
可使用以下命令修改與其它跟蹤檔案位置相同的最佳化程式跟蹤的位置和跟蹤檔案的名稱：
ALTER SESSION SET TRACEFILE_IDENTIFIER='opt';
該跟蹤檔案無需格式化，但該檔案相當大，所以請確保使用以下命令增大會話中該跟蹤的允許大小：
ALTER SESSION SET MAX_DUMPFILE_SIZE=UNLIMITED;
透過退出會話或使用以下命令，停止會話中的跟蹤：
ALTER SESSION SET EVENTS
'10053 trace name context off';

Oracle 調優確定存在問題的SQL

相關文章