熱點推薦:
您现在的位置: 電腦知識網 >> 編程 >> Oracle >> 正文

通過分析SQL語句的執行計劃優化SQL(三)

2013-11-13 22:20:12  來源: Oracle 
章  SQL語句處理的過程

  在調整之前我們需要了解一些背景知識只有知道這些背景知識我們才能更好的去調整sql語句
本節介紹了SQL語句處理的基本過程主要包括
  ·        查詢語句處理
  ·        DML語句處理(insert update delete)
  ·        DDL 語句處理(create drop alter )
  ·        事務控制(commit rollback)

  SQL 語句的執行過程(SQL Statement Execution)
            
   圖 概要的列出了處理和運行一個sql語句的需要各個重要階段在某些情況下Oracle運行sql的過程可能與下面列出的各個階段的順序有所不同如DEFINE階段可能在FETCH階段之前這主要依賴你如何書寫代碼

  對許多oracle的工具來說其中某些階段會自動執行絕大多數用戶不需要關心各個階段的細節問題然而知道執行的各個階段還是有必要的這會幫助你寫出更高效的SQL語句來而且還可以讓你猜測出性能差的SQL語句主要是由於哪一個階段造成的然後我們針對這個具體的階段找出解決的辦法

  圖   SQL語句處理的各個階段

  DML語句的處理
      
  本節給出一個例子來說明在DML語句處理的各個階段到底發生了什麼事情假設你使用Pro*C程序來為指定部門的所有職員增加工資程序已經連到正確的用戶你可以在你的程序中嵌入如下的SQL語句
EXEC SQL UPDATE employees
SET salary = * salary WHERE department_id = :var_department_id; var_department_id是程序變量裡面包含部門號我們要修改該部門的職員的工資當這個SQL語句執行時使用該變量的值

  每種類型的語句都需要如下階段
  ·        第步: Create a Cursor     創建游標
  ·        第步: Parse the Statement  分析語句
  ·        第步: Bind Any Variables    綁定變量
  ·        第步: Run the Statement    運行語句
  ·        第步: Close the Cursor     關閉游標

  如果使用了並行功能還會包含下面這個階段
  ·        第步: Parallelize the Statement   並行執行語句

  如果是查詢語句則需要以下幾個額外的步驟如圖 所示
  ·        第步: Describe Results of a Query   描述查詢的結果集
  ·        第步: Define Output of a Query      定義查詢的輸出數據
  ·        第步: Fetch Rows of a Query        取查詢出來的行

  下面具體說一下每一步中都發生了什麼事情

  第步: 創建游標(Create a Cursor)


        由程序接口調用創建一個游標(cursor)任何SQL語句都會創建它特別在運行DML語句時都是自動創建游標的不需要開發人員干預多數應用中游標的創建是自動的然而在預編譯程序(pro*c)中游標的創建可能是隱含的也可能顯式的創建在存儲過程中也是這樣的

  第步:分析語句(Parse the Statement)
 
  在語法分析期間SQL語句從用戶進程傳送到OracleSQL語句經語法分析後SQL語句本身與分析的信息都被裝入到共享SQL區在該階段中可以解決許多類型的錯誤

  語法分析分別執行下列操作
l        翻譯SQL語句驗證它是合法的語句即書寫正確
l        實現數據字典的查找以驗證是否符合表和列的定義
l        在所要求的對象上獲取語法分析鎖使得在語句的語法分析過程中不改變這些對象的定義
l        驗證為存取所涉及的模式對象所需的權限是否滿足
l        決定此語句最佳的執行計劃
l        將它裝入共享SQL區
l        對分布的語句來說把語句的全部或部分路由到包含所涉及數據的遠程節點
      
  以上任何一步出現錯誤都將導致語句報錯中止執行

  只有在共享池中不存在等價SQL語句的情況下才對SQL語句作語法分析在這種情況下數據庫內核重新為該語句分配新的共享SQL區並對語句進行語法分析進行語法分析需要耗費較多的資源所以要盡量避免進行語法分析這是優化的技巧之一

  語法分析階段包含了不管此語句將執行多少次而只需分析一次的處理要求Oracle只對每個SQL語句翻譯一次在以後再次執行該語句時只要該語句還在共享SQL區中就可以避免對該語句重新進行語法分析也就是此時可以直接使用其對應的執行計劃對數據進行存取這主要是通過綁定變量(bind variable)實現的也就是我們常說的共享SQL後面會給出共享SQL的概念

  雖然語法分析驗證了SQL語句的正確性但語法分析只能識別在SQL語句執行之前所能發現的錯誤(如書寫錯誤權限不足等)因此有些錯誤通過語法分析是抓不到的例如在數據轉換中的錯誤或在數據中的錯(如企圖在主鍵中插入重復的值)以及死鎖等均是只有在語句執行階段期間才能遇到和報告的錯誤或情況

  查詢語句的處理
      
  查詢與其它類型的SQL語句不同因為在成功執行後作為結果將返回數據其它語句只是簡單地返回成功或失敗而查詢則能返回一行或許多行數據查詢的結果均采用表格形式結果行被一次一行或者批量地被檢索出來從這裡我們可以得知批量的fetch數據可以降低網絡開銷所以批量的fetch也是優化的技巧之一

       有些問題只與查詢處理相關查詢不僅僅指SELECT語句同樣也包括在其它SQL語句中的隱含查詢例如下面的每個語句都需要把查詢作為它執行的一部分
INSERT INTO table SELECT
UPDATE table SET x = y WHERE
DELETE FROM table WHERE
CREATE table AS SELECT

  具體來說查詢
·        要求讀一致性
·        可能使用回滾段作中間處理
·        可能要求SQL語句處理描述定義和取數據階段

  第步: 描述查詢結果(Describe Results of a Query)
 
  描述階段只有在查詢結果的各個列是未知時才需要例如當查詢由用戶交互地輸入需要輸出的列名在這種情況要用描述階段來決定查詢結果的特征(數據類型長度和名字)

  第步: 定義查詢的輸出數據(Define Output of a Query)  
      
  在查詢的定義階段你指定與查詢出的列值對應的接收變量的位置大小和數據類型這樣我們通過接收變量就可以得到查詢結果如果必要的話Oracle會自動實現數據類型的轉換這是將接收變量的類型與對應的列類型相比較決定的

  第步: 綁定變量(Bind Any Variables)
      
  此時Oracle知道了SQL語句的意思但仍沒有足夠的信息用於執行該語句Oracle 需要得到在語句中列出的所有變量的值在該例中Oracle需要得到對department_id列進行限定的值得到這個值的過程就叫綁定變量(binding variables)

  此過程稱之為將變量值捆綁進來程序必須指出可以找到該數值的變量名(該變量被稱為捆綁變量變量名實質上是一個內存地址相當於指針)應用的最終用戶可能並沒有發覺他們正在指定捆綁變量因為Oracle 的程序可能只是簡單地指示他們輸入新的值其實這一切都在程序中自動做了因為你指定了變量名在你再次執行之前無須重新捆綁變量你可以改變綁定變量的值而Oracle在每次執行時僅僅使用內存地址來查找此值如果Oracle 需要實現自動數據類型轉換的話(除非它們是隱含的或缺省的)你還必須對每個值指定數據類型和長度關於這些信息可以參考oracle的相關文檔如Oracle Call Interface Programmers Guide

  第步: 並行執行語句(Parallelize the Statement )
     
  ORACLE 可以在SELECTs INSERTs UPDATEs MERGEs DELETEs語句中執行相應並行查詢操作對於某些DDL操作如創建索引用子查詢創建表在分區表上的操作也可以執行並行操作並行化可以導致多個服務器進程(oracle server processes)為同一個SQL語句工作使該SQL語句可以快速完成但是會耗費更多的資源所以除非很有必要否則不要使用並行查詢

  第步: 執行語句(Run the Statement)
      
  到了現在這個時候Oracle擁有所有需要的信息與資源因此可以真正運行SQL語句了如果該語句為SELECT查詢或INSERT語句則不需要鎖定任何行因為沒有數據需要被改變然而如果語句為UPDATE或DELETE語句則該語句影響的所有行都被鎖定防止該用戶提交或回滾之前別的用戶對這些數據進行修改這保證了數據的一致性對於某些語句你可以指定執行的次數這稱為批處理(array processing)指定執行N次則綁定變量與定義變量被定義為大小為N的數組的開始位置這種方法可以減少網絡開銷也是優化的技巧之一

  第步: 取出查詢的行(Fetch Rows of a Query)
      
  在fetch階段行數據被取出來每個後續的存取操作檢索結果集中的下一行數據直到最後一行被取出來上面提到過批量的fetch是優化的技巧之一

  第步: 關閉游標(Close the Cursor)
      
  SQL語句處理的最後一個階段就是關閉游標

  DDL語句的處理(DDL Statement Processing)
     
  DDL語句的執行不同與DML語句和查詢語句的執行這是因為DDL語句執行成功後需要對數據字典數據進行修改對於DDL語句語句的分析階段實際上包括分析查找數據字典信息和執行事務管理語句會話管理語句系統管理語句只有分析與執行階段為了重新執行該語句會重新分析與執行該語句

  事務控制(Control of Transactions)
      
  一般來說只有使用ORACLE編程接口的應用設計人員才關心操作的類型並把相關的操作組織在一起形成一個事務一般來說我門必須定義事務這樣在一個邏輯單元中的所有工作可以同時被提交或回滾保證了數據的一致性一個事務應該由邏輯單元中的所有必須部分組成不應該多一個也不應該少一個
  ·        在事務開始和結束的這段時間內所有被引用表中的數據都應該在一致的狀態(或可以被回溯到一致的狀態)
  ·        事務應該只包含可以對數據進行一致更改(one consistent change to the data)的SQL語句

  例如在兩個帳號之間的轉帳(這是一個事務或邏輯工作單元)應該包含從一個帳號中借錢(由一個SQL完成)然後將借的錢存入另一個帳號(由另一個SQL完成)個操作作為一個邏輯單元應該同時成功或同時失敗其它不相關的操作如向一個帳戶中存錢不應該包含在這個轉帳事務中

  在設計應用時除了需要決定哪種類型的操作組成一個事務外還需要決定使用BEGIN_DISCRETE_TRANSACTIO存儲過程是否對提高小的非分布式的事務的性能有作用


From:http://tw.wingwit.com/Article/program/Oracle/201311/18806.html
    推薦文章
    Copyright © 2005-2013 電腦知識網 Computer Knowledge   All rights reserved.