Oracle SQL處理

Oracle® Database   SQL Tuning Guide

Part II   Query Optimizer Fundamentals

3 SQL Processing

4 Query Optimizer Concepts

5 Query Transformations

3 SQL 處理

本章介紹資料庫如何處理DDL 語句 ( 建立物件 ) 、DML 語句( 修改資料 ) 以及查詢 語句( 檢索資料 ) 。

3.1 關於 SQL 處理

SQL 處理是 SQL 語句的解析、最佳化、行源生成和執行。

下圖描述了SQL 處理的一般階段。根據語句的不同，資料庫可能會省略其中一些階段。

3.1.1   SQL 解析

SQL 處理的第一階段是解析。

解析階段包括將SQL 語句的各個部分分離為其他例程可以處理的資料結構。資料庫根據應用程式的指示解析語句，這意味著只有應用程式而不是資料庫本身可以減少解析的次數。

當應用程式發出SQL 語句時，應用程式對資料庫進行解析呼叫，以準備執行語句。解析呼叫開啟或建立一個遊標，該遊標是特定於會話的私有 SQL 區域的控制程式碼，該區域包含已解析的 SQL 語句和其他處理資訊。遊標和私有 SQL 區域位於程式全域性區域 (PGA) 中。

在解析呼叫期間，資料庫執行檢查，以確定在語句執行之前可以找到的錯誤。有些錯誤無法透過解析捕獲。例如，只有在語句執行期間，資料庫才能在資料轉換中遇到死鎖或錯誤。

3.1.1.1   語法檢查

Oracle 資料庫必須檢查每個 SQL 語句的語法有效性。

如果語句違反了SQL 語法規則，則檢查失敗。例如，下面的語句失敗，因為關鍵字 FROM 被拼寫為 FORM:

SQL> SELECT * FORM employees;

 SELECT * FORM employees

*

ERROR at line 1:

ORA-00923: FROM keyword not found where expected

3.1.1.2   語義 檢查

語句的語義就是它的意義。語義檢查確定語句是否有意義，例如，語句中的物件和列是否存在。

語法 檢查透過了，語義檢查不一定能透過， 如下面 示例查詢的表不存在 :

SQL> SELECT * FROM nonexistent_table;

SELECT * FROM nonexistent_table

*

ERROR at line 1:

ORA-00942: table or view does not exist

3.1.1.3   共享 池檢查

在解析期間，資料庫執行共享池檢查，以確定是否可以跳過語句處理的資源密集型步驟。

為此，資料庫使用 雜湊 演算法為每個SQL 語句生成 雜湊 值。語句 雜湊 值是V$SQL. sql_id 中顯示的 SQL ID 。這個 雜湊 值在Oracle 資料庫的某個版本中是確定的，因此單個例項或不同例項中的相同語句具有相同的 SQL ID 。

當使用者提交SQL 語句時，資料庫將搜尋共享 SQL 區域，以檢視現有的已解析語句是否具有相同的 雜湊 值。SQL 語句的雜湊值與以下值不同 :

•語句的記憶體地址

Oracle 資料庫使用 SQL ID 在查詢表中執行鍵讀。透過這種方式，資料庫可以獲得語句可能的記憶體地址。

•語句執行計劃的 雜湊 值

一個SQL 語句在共享池中可以有多個計劃。通常，每個計劃都有不同的 雜湊 值。如果相同的SQL ID 具有多個計劃 雜湊 值，則資料庫知道此SQL ID 存在多個計劃。

根據提交語句的型別和 雜湊 檢查的結果，解析操作可以分為以下幾類:

•硬解析

如果Oracle 資料庫不能重用現有程式碼，那麼它必須構建應用程式程式碼的新可執行版本。此操作稱為硬解析或庫快取丟失。

注：

資料庫總是對DDL 語句 執行硬解析。

在硬解析過程中，資料庫多次訪問庫快取和資料字典快取來檢查資料字典。當資料庫訪問這些區域時，它使用一個稱為鎖存器的序列化裝置來訪問所需的物件，這樣它們的定義就不會改變。鎖存爭用會增加語句執行時間並降低併發性。

•軟解析

軟解析是任何非硬解析的解析。如果提交的語句與共享池中的可重用SQL 語句相同，那麼 Oracle 資料庫將重用現有程式碼。這種程式碼重用也稱為庫快取命中。

軟解析在執行的工作量方面可能有所不同。例如，配置會話共享SQL 區域有時可以減少軟解析中的鎖存數量，使它們變得“更軟”。

通常，軟解析比硬解析更可取，因為資料庫跳過了最佳化和行源生成步驟，直接執行。

下圖是專用伺服器體系結構中UPDATE 語句的共享池檢查的簡化表示。

如果檢查確定共享池中的語句具有相同的 雜湊 值，則資料庫將執行語義和環境檢查，以確定語句是否具有相同的含義。相同的語法是不夠的。例如，假設兩個不同的使用者登入到資料庫併發出以下SQL 語句 :

CREATE TABLE my_table ( some_col INTEGER );

SELECT * FROM my_table;

這兩個使用者的SELECT 語句在語法上是相同的，但是兩個獨立的模式物件名為 my_table 。這種語義上的差異意味著第二條語句不能重用第一條語句的程式碼。

即使兩個語句在語義上是相同的，環境差異也會強制進行硬解析。在此上下文中，最佳化器環境是會影響執行計劃生成的會話設定的總和，例如工作區域大小或最佳化器設定( 例如，最佳化器模式 ) 。考慮以下由單個使用者執行的一系列 SQL 語句 :

ALTER   SESSION   SET  OPTIMIZER_MODE = ALL_ROWS ;

ALTER   SYSTEM   FLUSH   SHARED_POOL ;  # optimizer environment 1

SELECT   *   FROM  sh.sales ;

ALTER   SESSION   SET  OPTIMIZER_MODE = FIRST_ROWS ;  # optimizer environment 2

SELECT   *   FROM  sh.sales ;

ALTER   SESSION   SET   SQL_TRACE = true ;  # optimizer environment 3

SELECT   *   FROM  sh.sales ;

在前面的示例中，相同的SELECT 語句在三個不同的最佳化器環境中執行。因此，資料庫為這些語句建立了三個獨立的共享 SQL 區域，並強制對每個語句進行硬解析。

3.1.2   SQL 最佳化

在最佳化過程中，Oracle 資料庫必須對每個唯一的 DML 語句至少執行一次硬解析，並在此解析過程中執行最佳化。

資料庫沒有最佳化DDL 。唯一的例外是當 DDL 包含 DML 元件（如需要最佳化的子查詢）時。

3.1.3   SQL 行源生成

行源生成器是從最佳化器接收最優執行計劃並生成可由資料庫的其餘部分使用的迭代執行計劃的軟體。

迭代計劃是一個二進位制程式，當由SQL 引擎執行時，生成結果集。每一步都返回一個行集。下一步使用這個集合中的行，或者最後一步將這些行返回給發出 SQL 語句的應用程式。

行源是執行計劃中的一個步驟返回的行集，以及可以迭代處理行的控制結構。行源可以是聯接或分組操作的表、檢視或結果。

行源生成器生成行源樹，它是行源的集合。行源樹顯示以下資訊:

•語句引用的表的順序

•語句中提到的每個表的訪問方法

•語句中受連線操作影響的表的連線方法

•過濾、排序或聚合等資料操作

示例 3-1

此示例顯示了啟用AUTOTRACE 時 SELECT 語句的執行計劃。該語句為其姓氏以字母 a 開頭的所有員工選擇姓氏、職位名稱和部門名稱。該語句的執行計劃是行源生成器的輸出。

SELECT  e.last_name ,  j.job_title ,  d.department_name

   FROM  hr.employees e ,  hr.departments d ,  hr.jobs j

  WHERE  e.department_id =  d.department_id

    AND  e.job_id =  j.job_id

    AND  e.last_name LIKE   'A%' ;

3.1.4   SQL 執行

在執行期間，SQL 引擎執行行源生成器生成的樹中的每個行源。此步驟是 DML 處理中惟一必需的步驟。

圖3-3 是一個執行樹，也稱為解析樹，它顯示了示例 3-1 中計劃中從一個步驟到另一個步驟的行源流。通常，執行中的步驟的順序與計劃中的順序相反，因此您可以自底向上閱讀計劃。

執行計劃中的每個步驟都有一個ID 號。圖 3-3 中的數字對應於例 3-1 所示計劃中的 Id 列。 p 執行計劃 的Operation 列中的初始空格表示層次關係。例如，如果一個操作的名稱前面有兩個空格，那麼這個操作就是前面有一個空格的操作的子操作。前跟一個空格的操作是 SELECT 語句本身的子語句。

在圖3-3 中，樹的每個節點都充當行源，這意味著示例 3-1 中的執行計劃的每一步要麼從資料庫中檢索行，要麼接受來自一個或多個行源的行作為輸入。 SQL 引擎執行每個行源，如下所示 :

•黑盒指示的步驟從資料庫中的物件物理檢索資料。這些步驟是用於從資料庫中檢索資料的訪問路徑或技術。

- 步驟 6 使用全表掃描來檢索所有來自部門表的行。

- 步驟 5 使用全表掃描來檢索 jobs 表中的所有行。

- 步驟 4 按順序掃描 emp_name_ix 索引，查詢每個以字母 A 開頭的鍵並檢索相應的 rowid 。例如，與 Atkinson 對應的 rowid 是 AAAPzRAAFAAAABSAAe 。

- 步驟 3 從 employees 表中檢索由步驟 4 返回其行 id 的行。例如，資料庫使用 rowid AAAPzRAAFAAAABSAAe 來檢索 Atkinson 的行。

•清除方框指示的步驟操作行源。

- 步驟 2 執行 雜湊 連線，接受來自步驟3 和步驟 5 的行源，將來自步驟 5 的行源的每一行連線到步驟 3 中相應的行，並將結果行返回到步驟 1 。

例如，employee Atkinson 的行與作業名稱 Stock Clerk 相關聯。

- 步驟 1 執行另一個 雜湊 連線，接受來自步驟2 和步驟 6 的行源，將來自步驟 6 源的每一行連線到步驟 2 中相應的行，並將結果返回給客戶端。

例如，employee Atkinson 的行與名為 Shipping 的部門相關聯。

在一些執行計劃中，步驟是迭代的，而在另一些執行計劃中，步驟是連續的。例3-1 中顯示的雜湊連線是連續的。資料庫根據連線順序完整地完成這些步驟。資料庫從 emp_name_ix 的索引範圍掃描開始。使用從索引中檢索的 rowid ，資料庫讀取 employees 表中匹配的行，然後掃描 jobs 表。從 jobs 表中檢索行之後，資料庫執行雜湊連線。

在執行期間，如果資料不在記憶體中，資料庫將資料從磁碟讀入記憶體。資料庫還會取出確保資料完整性所需的所有鎖和鎖存器，並記錄SQL 執行期間所做的任何更改。處理 SQL 語句的最後一個階段是關閉遊標。

3.2   Oracle 資料庫如何處理 DML

大多數DML 語句都有一個查詢元件。在查詢中，遊標的執行將查詢的結果放入稱為結果集的一組行中。

3.2.1   如何獲取行集

結果集行可以一次取一行，也可以取組。

在獲取階段，資料庫選擇行，如果查詢請求，則對行進行排序。每個連續的獲取都將檢索結果的另一行，直到最後一行被獲取。

通常，在獲取最後一行之前，資料庫無法確定查詢要檢索的行數。Oracle 資料庫在響應 fetch 呼叫時檢索資料，因此資料庫讀取的行越多，它執行的工作就越多。對於某些查詢，資料庫會盡可能快地返回第一行，而對於其他查詢，它會在返回第一行之前建立整個結果集。

3.2.2   讀 一致性

通常，查詢透過使用Oracle 資料庫讀一致性機制來檢索資料，該機制確保查詢讀取的所有資料塊在某個時間點上是一致的。

讀取一致性使用撤消資料來顯示資料的過去版本。例如，假設一個查詢必須在一次全表掃描中讀取100 個資料塊。查詢處理前 10 個塊，而 DML 在不同的會話中修改第 75 塊。當第一個會話到達第 75 塊時，它實現更改並使用撤消資料來檢索資料的舊的、未修改的版本，並在記憶體中構造第 75 塊的非當前版本。

3.2.3   資料更改

必須更改資料的DML 語句使用讀取一致性，以便在修改開始時僅檢索與搜尋條件匹配的資料。

然後，這些語句檢索資料塊，因為它們存在於當前狀態，並進行所需的修改。資料庫必須執行與資料修改相關的其他操作，例如生成重做和撤消資料。

3.3   Oracle 資料庫如何處理 DDL

Oracle 資料庫處理 DDL 的方式與 DML 不同。

例如，建立表時，資料庫不會最佳化CREATE   TABLE 語句。相反， Oracle 資料庫解析 DDL 語句並執行命令。

資料庫處理DDL 的方式不同，因為它是在資料字典中定義物件的一種方法。通常， Oracle 資料庫必須解析並執行許多遞迴 SQL 語句才能執行 DDL 語句。假設您建立一個表，如下所示： CREATE   TABLE  mytable ( mycolumn INTEGER );

通常，資料庫會執行許多遞迴語句來執行前面的語句。遞迴SQL 將執行以下操作 :

•在執行 CREATE TABLE 語句之前發出 COMMIT

•驗證使用者特權足以建立表

•確定表應該駐留在哪個表空間中

•確保沒有超出表空間配額

•確保模式中的物件沒有相同的名稱

•將定義表的行插入到資料字典中

•如果 DDL 語句成功，則發出 COMMIT; 如果沒有成功，則發出 ROLLBACK。

歡迎關注我的微信公眾號"IT小Chen" ，共同學習，共同成長！！！