通過分析SQL語句的執行計畫最佳化SQL(二)

來源:互聯網
上載者:User
最佳化|語句|執行

第5章 ORACLE的執行計畫

  背景知識:
       
  為了更好的進行下面的內容我們必須瞭解一些概念性的術語:

  共用sql語句

     為了不重複解析相同的SQL語句(因為解析操作比較費資源,會導致效能下降),在第一次解析之後,ORACLE將SQL語句及解析後得到的執行計畫存放在記憶體中。這塊位於系統全域地區SGA(system global area)的共用池(shared buffer pool)中的記憶體可以被所有的資料庫使用者共用。因此,當你執行一個SQL語句(有時被稱為一個遊標)時,如果該語句和之前的執行過的某一語句完全相同,並且之前執行的該語句與其執行計畫仍然在記憶體中存在,則ORACLE就不需要再進行分析,直接得到該語句的執行路徑。ORACLE的這個功能大大地提高了SQL的執行效能並大大節省了記憶體的使用。使用這個功能的關鍵是將執行過的語句儘可能放到記憶體中,所以這要求有大的共用池(通過設定shared buffer pool參數值)和儘可能的使用綁定變數的方法執行SQL語句。

     當你向ORACLE 提交一個SQL語句,ORACLE會首先在共用記憶體中尋找是否有相同的語句。這裡需要註明的是,ORACLE對兩者採取的是一種嚴格匹配,要達成共用,SQL語句必須完全相同(包括空格,換行等)。

     下面是判斷SQL語句是否與共用記憶體中某一SQL相同的步驟:
  1). 對所發出語句的文本串進行hashed。如果hash值與已在共用池中SQL語句的hash值相同,則進行第2步:
        2)         將所發出語句的文本串(包括大小寫、空白和注釋)與在第1步中識別的所有
        已存在的SQL語句相比較。
        例如:
        SELECT * FROM emp WHERE empno = 1000;
        和下列每一個都不同
        SELECT * from emp WHERE empno = 1000;
        SELECT * FROM EMP WHERE empno = 1000;
        SELECT * FROM emp WHERE empno = 2000;
        在上面的語句中列值都是直接SQL語句中的,今後我們將這類sql成為寫入程式碼SQL或字面值SQL
       
        使用綁定變數的SQL語句中必須使用相同的名字的綁定變數(bind variables) ,
例如:
        a. 該2個sql語句被認為相同
        select pin , name from people where pin = :blk1.pin;
        select pin , name from people where pin = :blk1.pin;
        b. 該2個sql語句被認為不相同
        select pin , name from people where pin = :blk1.ot_ind;
        select pin , name from people where pin = :blk1.ov_ind;
        今後我們將上面的這類語句稱為綁定變數SQL。

        3). 將所發出語句中涉及的對象與第2步中識別的已存在語句所涉及對象相比較。
           例如:
           如使用者user1與使用者user2下都有EMP表,則
           使用者user1發出的語句:SELECT * FROM EMP; 與
           使用者user2發出的語句:SELECT * FROM EMP; 被認為是不相同的語句,
           因為兩個語句中引用的EMP不是指同一個表。
   
        4). 在SQL語句中使用的捆綁變數的捆綁類型必須一致。

        如果語句與當前在共用池中的另一個語句是等同的話,Oracle並不對它進行文法分析。而直接執行該語句,提高了執行效率,因為文法分析比較耗費資源。

        注意的是,從oracle 8i開始,新引入了一個CURSOR_SHARING參數,該參數的主要目的就是為瞭解決在編程過程中已大量使用的寫入程式碼SQL問題。因為在實際開發中,很多程式人員為了提高開發速度,而採用類似下面的開發方法:
str_sql string;
int_empno int;
int_empno = 2000;
str_sql = ‘SELECT * FROM emp WHERE empno = ‘ + int_empno;
…………
int_empno = 1000;
str_sql = ‘SELECT * FROM emp WHERE empno = ‘ + int_empno;

        上面的代碼實際上使用了寫入程式碼SQL,使我們不能使用共用SQL的功能,結果是資料庫效率不高。但是從上面的2個語句來看,產生的寫入程式碼SQL只是列值不同,其它部分都是相同的,如果僅僅因為列值不同而導致這2個語句不能共用是很可惜的,為瞭解決這個問題,引入了CURSOR_SHARING參數,使這類問題也可以使用共用SQL,從而使這樣的開發也可以利用共用SQL功能。聽起來不錯,ORACLE真為使用者著想,使使用者在不改變代碼的情況下還可以利用共用SQL的功能。真的如此嗎?天上不會無緣無故的掉一個餡餅的,ORACLE對該參數的使用做了說明,建議在經過實際測試後再改該參數的值(預設情況下,該參數的值為EXACT,語句完全一致才使用共用SQL)。因為有可能該變該值後,你的寫入程式碼SQL是可以使用共用SQL了,但資料庫的效能反而會下降。 我在實際應用中已經遇到這種情況。所以建議編寫需要穩定運行程式的開發人員最好還是一開始就使用綁定變數的SQL。

  Rowid的概念:
      
  rowid是一個偽列,既然是偽列,那麼這個列就不是使用者定義,而是系統自己給加上的。對每個表都有一個rowid的偽列,但是表中並不實體儲存體ROWID列的值。不過你可以像使用其它列那樣使用它,但是不能刪除改列,也不能對該列的值進行修改、插入。一旦一行資料插入資料庫,則rowid在該行的生命週期內是唯一的,即即使該行產生行遷移,行的rowid也不會改變。

  為什麼使用ROWID
     
  rowid對訪問一個表中的給定的行提供了最快的存取方法,通過ROWID可以直接定位到相應的資料區塊上,然後將其讀到記憶體。我們建立一個索引時,該索引不但儲存索引列的值,而且也儲存索引值所對應的行的ROWID,這樣我們通過索引快速找到相應行的ROWID後,通過該ROWID,就可以迅速將資料查詢出來。這也就是我們使用索引查詢時,速度比較快的原因。

  在ORACLE8以前的版本中,ROWID由FILE 、BLOCK、ROW NUMBER構成。隨著oracle8中對象概念的擴充,ROWID發生了變化,ROWID由OBJECT、FILE、BLOCK、ROW NUMBER構成。利用DBMS_ROWID可以將rowid分解成上述的各部分,也可以將上述的各部分組成一個有效rowid。

  Recursive SQL概念
       
  有時為了執行使用者發出的一個sql語句,Oracle必須執行一些額外的語句,我們將這些額外的語句稱之為'recursive calls'或'recursive SQL statements'。如當一個DDL語句發出後,ORACLE總是隱含的發出一些recursive SQL語句,來修改資料字典資訊,以便使用者可以成功的執行該DDL語句。當需要的資料字典資訊沒有在共用記憶體中時,經常會發生Recursive calls,這些Recursive calls會將資料字典資訊從硬碟讀入記憶體中。使用者不比關心這些recursive SQL語句的執行情況,在需要的時候,ORACLE會自動的在內部執行這些語句。當然DML語句與SELECT都可能引起recursive SQL。簡單的說,我們可以將觸發器視為recursive SQL。

  Row Source(行源)
       
  用在查詢中,由上一操作返回的合格行的集合,即可以是表的全部行資料的集合;也可以是表的部分行資料的集合;也可以為對上2個row source進行串連操作(如join串連)後得到的行資料集合。

  Predicate(謂詞)
       
  一個查詢中的WHERE限制條件

  Driving Table(驅動表)
       
  該表又稱為外層表(OUTER TABLE)。這個概念用於嵌套與HASH串連中。如果該row source返回較多的行資料,則對所有的後續操作有負面影響。注意此處雖然翻譯為驅動表,但實際上翻譯為驅動行源(driving row source)更為確切。一般說來,是應用查詢的限制條件後,返回較少行源的表作為驅動表,所以如果一個大表在WHERE條件有有限制條件(如等值限制),則該大表作為驅動表也是合適的,所以並不是只有較小的表可以作為驅動表,正確說法應該為應用查詢的限制條件後,返回較少行源的表作為驅動表。在執行計畫中,應該為靠上的那個row source,後面會給出具體說明。在我們後面的描述中,一般將該表稱為串連操作的row source 1。

  Probed Table(被探查表)
       
  該表又稱為內層表(INNER TABLE)。在我們從驅動表中得到具體一行的資料後,在該表中尋找符合串連條件的行。所以該表應當為大表(實際上應該為返回較大row source的表)且相應的列上應該有索引。在我們後面的描述中,一般將該表稱為串連操作的row source 2。

  複合式索引(concatenated index)
       
  由多個列構成的索引,如create index idx_emp on emp(col1, col2, col3, ……),則我們稱idx_emp索引為複合式索引。在複合式索引中有一個重要的概念:引導列(leading column),在上面的例子中,col1列為引導列。當我們進行查詢時可以使用”where col1 = ? ”,也可以使用”where col1 = ? and col2 = ?”,這樣的限制條件都會使用索引,但是”where col2 = ? ”查詢就不會使用該索引。所以限制條件中包含先導列時,該限制條件才會使用該複合式索引。

  可選擇性(selectivity):
       
  比較一下列中唯一鍵的數量和表中的行數,就可以判斷該列的可選擇性。如果該列的”唯一鍵的數量/表中的行數”的比值越接近1,則該列的可選擇性越高,該列就越適合建立索引,同樣索引的可選擇性也越高。在可選擇性高的列上進行查詢時,返回的資料就較少,比較適合使用索引查詢。


        有了這些背景知識後就開始介紹執行計畫。為了執行語句,Oracle可能必須實現許多步驟。這些步驟中的每一步可能是從資料庫中物理檢索資料行,或者用某種方法準備資料行,供發出語句的使用者使用。Oracle用來執行語句的這些步驟的組合被稱之為執行計畫。執行計畫是SQL最佳化中最為複雜也是最為關鍵的部分,只有知道了ORACLE在內部到底是如何執行該SQL語句後,我們才能知道最佳化器選擇的執行計畫是否為最優的。執行計畫對於DBA來說,就象財務報表對於財務人員一樣重要。所以我們面臨的問題主要是:如何得到執行計畫;如何分析執行計畫,從而找出影響效能的主要問題。下面先從分析樹型執行計畫開始介紹,然後介紹如何得到執行計畫,再介紹如何分析執行計畫。
       
  舉例:這個例子顯示關於下面SQL語句的執行計畫。
SELECT ename, job, sal, dname
   FROM emp, dept
WHERE emp.deptno = derpt.deptno
   AND NOT EXISTS
     ( SELECT *
FROM salgrade
WHERE emp.sal BETWEEN losal AND hisal );
       
  此語句查詢薪水不在任何建議薪水範圍內的所有僱員的名字,工作,薪水和部門名。下圖5-1顯示了一個執行計畫的圖形表示:

  執行計畫的步驟
         
  執行計畫的每一步返回一組行,它們或者為下一步所使用,或者在最後一步時返回給發出SQL語句的使用者或應用。由每一步返回的一組行叫做行源(row source)。圖5-1樹狀圖顯示了從一步到另一步行資料的流動情況。每步的編號反映了在你觀察執行計畫時所示步驟的順序(如何觀察執行計畫將被簡短地說明)。一般來說這並不是每一步被執行的先後順序。執行計畫的每一步或者從資料庫中檢索行,或者接收來自一個或多個行源的行資料作為輸入:由紅色字框指出的步驟從資料庫中的資料檔案中物理檢索資料。這種步驟被稱之為存取路徑,後面會詳細介紹在Oracle可以使用的存取路徑:
l        第3步和第6步分別的從EMP表和SALGRADE表讀所有的行。
l        第5步在PK_DEPTNO索引中尋找由步驟3返回的每個DEPTNO值。它找出與DEPT表中相關聯的那些行的ROWID。
l        第4步從DEPT表中檢索出ROWID為第5步返回的那些行。
由黑色字框指出的步驟在行源上操作,如做2表之間的關聯,排序,或過濾等操作,後面也會給出詳細的介紹:
l        第2步實現嵌套的迴圈操作(相當於C語句中的嵌套迴圈),接收從第3步和第4步來的行源,把來自第3步源的每一行與它第4步中相應的行串連在一起,返回結果行到第1步。
l        第1步完成一個過濾器操作。它接收來自第2步和第6步的行源,消除掉第2步中來的,在第6步有相應行的那些行,並將來自第2步的剩下的行返回給發出語句的使用者或應用。

  實現執行計畫步驟的順序

  執行計畫中的步驟不是按照它們編號的順序來實現的:Oracle首先實現圖5-1樹結構圖形裡作為葉子出現的那些步驟(例如步驟3、5、6)。由每一步返回的行稱為它下一步驟的行源。然後Oracle實現父步驟。

  舉例來說,為了執行圖5-1中的語句,Oracle以下列順序實現這些步驟:
l        首先,Oracle實現步驟3,並一行一行地將結果行返回給第2步。
l        對第3步返回的每一行,Oracle實現這些步驟:
-- Oracle實現步驟5,並將結果ROWID返回給第4步。
-- Oracle實現步驟4,並將結果行返回給第2步。
-- Oracle實現步驟2,將接受來自第3步的一行和來自第4步的一行,並返回給第1步一行。
-- Oracle實現步驟6,如果有結果行的話,將它返回給第1步。
-- Oracle實現步驟1,如果從步驟6返回行,Oracle將來自第2步的行返回給發出SQL語句的使用者。

  注意Oracle對由第3步返回的每一行實現步驟5,4,2,6一次。許多父步驟在它們能執行之前只需要來自它們子步驟的單一行。對這樣的父步驟來說,只要從子步驟已返回單一行時立即實現父步驟(可能還有執行計畫的其餘部分)。如果該父步驟的父步驟同樣可以通過單一行返回啟用的話,那麼它也同樣被執行。所以,執行可以在樹上串聯上去,可能包含執行計畫的餘下部分。對於這樣的操作,可以使用first_rows作為最佳化目標以便於實現快速響應使用者的請求。
對每個由子步驟依次檢索出來的每一行,Oracle就實現父步驟及所有串聯在一起的步驟一次。對由子步驟返回的每一行所觸發的父步驟包括表存取,索引存取,嵌套的迴圈串連和過濾器。

        有些父步驟在它們被實現之前需要來自子步驟的所有行。對這樣的父步驟,直到所有行從子步驟返回之前Oracle不能實現該父步驟。這樣的父步驟包括排序,排序一合并的串連,組功能和總計。對於這樣的操作,不能使用first_rows作為最佳化目標,而可以用all_rows作為最佳化目標,使該中類型的操作耗費的資源最少。

  有時語句執行時,並不是象上面說的那樣一步一步有先有後的進行,而是可能並行運行,如在實際環境中,3、5、4步可能並行運行,以便取得更好的效率。從上面的樹型圖上,是很難看出各個操作執行的先後順序,而通過ORACLE產生的另一種形式的執行計畫,則可以很容易的看出哪個操作先執行,哪個後執行,這樣的執行計畫是我們真正需要的,後面會給出詳細說明。現在先來看一些預備知識。

  訪問路徑(方法) -- access path
      
  最佳化器在形成執行計畫時需要做的一個重要選擇是如何從資料庫查詢出需要的資料。對於SQL語句存取的任何錶中的任何行,可能存在許多存取路徑(存取方法),通過它們可以定位和查詢出需要的資料。最佳化器選擇其中自認為是最佳化的路徑。
       
  在物理層,oracle讀取資料,一次讀取的最小單位為資料庫塊(由多個連續的作業系統塊組成),一次讀取的最大值由作業系統一次I/O的最大值與multiblock參數共同決定,所以即使只需要一行資料,也是將該行所在的資料庫塊讀入記憶體。邏輯上,oracle用如下存取方法訪問資料:

  1) 全表掃描(Full Table Scans, FTS)
        
  為實現全表掃描,Oracle讀取表中所有的行,並檢查每一行是否滿足語句的WHERE限制條件。Oracle順序地讀取分配給表的每個資料區塊,直到讀到表的最高水線處(high water mark, HWM,標識表的最後一個資料區塊)。一個多塊讀操作可以使一次I/O能讀取多塊資料區塊(db_block_multiblock_read_count參數設定),而不是唯讀取一個資料區塊,這極大的減少了I/O總次數,提高了系統的輸送量,所以利用多塊讀的方法可以十分高效地實現全表掃描,而且只有在全表掃描的情況下才能使用多塊讀操作。在這種訪問模式下,每個資料區塊只被讀一次。由於HWM標識最後一塊被讀入的資料,而delete操作不影響HWM值,所以一個表的所有資料被delete後,其全表掃描的時間不會有改善,一般我們需要使用truncate命令來使HWM值歸為0。幸運的是oracle 10G後,可以人工收縮HWM的值。

        由FTS模式讀入的資料被放到快取的Least Recently Used (LRU)列表的尾部,這樣可以使其快速交換出記憶體,從而不使記憶體重要的資料被交換出記憶體。使用FTS的前提條件:在較大的表上不建議使用全表掃描,除非取出資料的比較多,超過總量的5% -- 10%,或你想使用並行查詢功能時。
        使用全表掃描的例子:
        ~~~~~~~~~~~~~~~~~~~~~~~~
        SQL> explain plan for select * from dual;
        Query Plan
        -----------------------------------------
        SELECT STATEMENT     [CHOOSE] Cost=
          TABLE ACCESS FULL DUAL

  2) 通過ROWID的表存取(Table Access by ROWID或rowid lookup)
      
  行的ROWID指出了該行所在的資料檔案、資料區塊以及行在該塊中的位置,所以通過ROWID來存取資料可以快速定位到目標資料上,是Oracle存取單行資料的最快方法。為了通過ROWID存取表,Oracle 首先要擷取被選擇行的ROWID,或者從語句的WHERE子句中得到,或者通過表的一個或多個索引的索引掃描得到。Oracle然後以得到的ROWID為依據定位每個被選擇的行。
    
  這種存取方法不會用到多塊讀操作,一次I/O只能讀取一個資料區塊。我們會經常在執行計畫中看到該存取方法,如通過索引查詢資料。

  使用ROWID存取的方法:
SQL> explain plan for select * from dept where rowid = 'AAAAyGAADAAAAATAAF';
Query Plan
------------------------------------
SELECT STATEMENT [CHOOSE] Cost=1
TABLE ACCESS BY ROWID DEPT [ANALYZED]

  3)索引掃描(Index Scan或index lookup)
      
  我們先通過index尋找到資料對應的rowid值(對於非唯一索引可能返回多個rowid值),然後根據rowid直接從表中得到具體的資料,這種尋找方式稱為索引掃描或索引尋找(index lookup)。一個rowid唯一的表示一行資料,該行對應的資料區塊是通過一次i/o得到的,在此情況下該次i/o只會讀取一個資料庫塊。

  在索引中,除了儲存每個索引的值外,索引還儲存具有此值的行對應的ROWID值。索引掃描可以由2步組成:(1) 掃描索引得到對應的rowid值。 (2) 通過找到的rowid從表中讀出具體的資料。每步都是單獨的一次I/O,但是對於索引,由於經常使用,絕大多數都已經CACHE到記憶體中,所以第1步的I/O經常是邏輯I/O,即資料可以從記憶體中得到。但是對於第2步來說,如果表比較大,則其資料不可能全在記憶體中,所以其I/O很有可能是物理I/O,這是一個機械操作,相對邏輯I/O來說,是極其費時間的。所以如果多大表進行索引掃描,取出的資料如果大於總量的5% -- 10%,使用索引掃描會效率下降很多。
如下列所示:
SQL> explain plan for select empno, ename from emp where empno=10;
Query Plan
------------------------------------
SELECT STATEMENT [CHOOSE] Cost=1
TABLE ACCESS BY ROWID EMP [ANALYZED]
    INDEX UNIQUE SCAN EMP_I1
       
  注意TABLE ACCESS BY ROWID EMP部分,這表明這不是通過FTS存取路徑訪問資料,而是通過rowid lookup存取路徑訪問資料的。在此例中,所需要的rowid是由於在索引尋找empno列的值得到的,這種方式是INDEX UNIQUE SCAN尋找,後面給予介紹,EMP_I1為使用的進行索引尋找的索引名字。

        但是如果查詢的資料能全在索引中找到,就可以避免進行第2步操作,避免了不必要的I/O,此時即使通過索引掃描取出的資料比較多,效率還是很高的,因為這隻會在索引中讀取。所以上面我在介紹基於規則的最佳化器時,使用了select count(id) from SWD_BILLDETAIL where cn <'6',而沒有使用select count(cn) from SWD_BILLDETAIL where cn <'6'。因為在實際情況中,只查詢被索引列的值的情況極為少,所以,如果我在查詢中使用count(cn),則不具有代表性。

SQL> explain plan for select empno from emp where empno=10;  -- 只查詢empno列值
Query Plan
------------------------------------
SELECT STATEMENT [CHOOSE] Cost=1
  INDEX UNIQUE SCAN EMP_I1

        進一步講,如果sql語句中對索引列進行排序,因為索引已經預先排序好了,所以在執行計畫中不需要再對索引列進行排序
SQL> explain plan for select empno, ename from emp
where empno > 7876 order by empno;
Query Plan
--------------------------------------------------------------------------------
SELECT STATEMENT   [CHOOSE] Cost=1   
TABLE ACCESS BY ROWID EMP [ANALYZED]
  INDEX RANGE SCAN EMP_I1 [ANALYZED]
       
  從這個例子中可以看到:因為索引是已經排序了的,所以將按照索引的順序查詢出合格行,因此避免了進一步排序操作。

[1] [2] 下一頁  



相關文章

Beyond APAC's No.1 Cloud

19.6% IaaS Market Share in Asia Pacific - Gartner IT Service report, 2018

Learn more >

Apsara Conference 2019

The Rise of Data Intelligence, September 25th - 27th, Hangzhou, China

Learn more >

Alibaba Cloud Free Trial

Learn and experience the power of Alibaba Cloud with a free trial worth $300-1200 USD

Learn more >

聯繫我們

該頁面正文內容均來源於網絡整理,並不代表阿里雲官方的觀點,該頁面所提到的產品和服務也與阿里云無關,如果該頁面內容對您造成了困擾,歡迎寫郵件給我們,收到郵件我們將在5個工作日內處理。

如果您發現本社區中有涉嫌抄襲的內容,歡迎發送郵件至: info-contact@alibabacloud.com 進行舉報並提供相關證據,工作人員會在 5 個工作天內聯絡您,一經查實,本站將立刻刪除涉嫌侵權內容。