1、Oracle與sql簡單優化與鎖機制淺析系統運營二部徐海濤oracle數據庫的基本概念與原理對象的存儲，segment、extent、block SGA、PGA內存域，內存與存儲的關系 事務、undo、redo與ORA-01555關于鎖機制2022/9/25中國平安保險（集團）股份有限公司 秘密 版權所有，不得侵犯對象的存儲oracle中的對象以segment的形式存儲。我們可以在dba_segment這張視圖中查詢到所有我們創建的表和索引。segment由extent組成。其擴展是以extent為單位。一張表在初始化時會首先產生至少一個設定大小的extent，以后如果記錄數逐漸增多，則需要擴

2、展segment的空間，每次以設定大小擴展一個extent(即增加一個設定大小的extent到segment中)。extent由block組成。block是oracle存儲中最基本的單位。一個block上會存儲一條或多條數據記錄，讀取一條數據記錄時至少需要讀取出這條記錄所在block。在block header上記錄了一些非常重要的信息，包含塊的類型(表還是索引)、關于塊上活動和過時的事務信息、塊在磁盤上的位置等等。一個segment屬于一個唯一的tablespace，而一個tablespace則可以包含一個或多個數據文件。2022/9/25中國平安保險（集團）股份有限公司 秘密 版權所有，不

3、得侵犯oracle的內存結構 SGA內存域ORACLE使用的所有共享內存空間被稱為SGA(system global area)的內存結構SGA主要包含下面的內存域：data buffer：用于放置data block，ORACLE中所有的數據操作(增、刪、查、改)都需要在data buffer中完成，讀數據時需先將數據塊從存儲讀到data buffer，修改數據的操作需在data buffer中完成修改然后在回寫存儲。優化物理讀的一個辦法就是增大data buffer，使數據在data buffer的停留時間變長，提高buffer的命中率，減少物理讀，也就減小了I/O，不過這是不推薦的辦法，

4、最重要的還是要優化應用。shared pool：用于放置緩存的sql語句、sql語句的執行計劃、數據字典視圖等，sql語句執行過程中需要保持在shared pool中的語句本身和其執行計劃，dll操作也需要在shared pool中鎖住相關的數據字典。java pool：用于存放java對象。large pool：用于分配一些大塊的內存給進程應對一些特殊的需要，如語句的并行執行和備份會用到large pool，weblogic connection pool連接ORACLE數據庫也是使用large pool存放connection的相關信息。redo log buffer：用于緩存redo l

5、og，redo log會先緩存到redo log buffer然后再寫到日志組中。2022/9/25中國平安保險（集團）股份有限公司 秘密 版權所有，不得侵犯oracle的內存結構在oracle中幾乎所有操作都是SGA完成的。不論增、刪、查、改都是將需要的數據取到SGA中，在SGA中完成相關的操作。oracle通過后臺進程(DBWn)將SGA中產生的變化同步到儲存中，本身并不直接在存儲上進行增、刪、查、改的操作。 PGA內存域針對每個oracle進程(process)分配的獨占內存空間被稱為PGA(process global area) 的內存結構，是在SGA之外獨立分配的，一般情況下，se

6、ssion越多也就耗用越多的PGA?？傮w而言，PGA中需要關注的地方不是太多，在9i以上的版本，使用自動內存管理，用于hash和排序的內存空間從SGA挪到了PGA，為PGA的上限值(pga_aggregate_target)配置一個合理的值對sql語句的效率有較大影響。(oracle中另一部分非常重要的機制就是oracle中的后臺進程，這里我們不作討論，大家可以參看oracle expert one-on-one等相關的書籍) 2022/9/25中國平安保險（集團）股份有限公司 秘密 版權所有，不得侵犯事務、undo、redo 事務事務：單個邏輯工作單元執行的一系列操作。事務遵循如下的特性：原

7、子性：一個事務要么完全發生，要么完全不發生一致性：事務把數據庫從一個一致狀態轉變到另一個狀態隔離性：在事務提交以前，其他事務察覺不到事務的影響持久性：一旦事務提交，它是永久的oracle的事務是隱式開始的，從第一條dml語句開始(第一條取得TX鎖的語句開始的，后面我們將討論oracle的鎖機制，鎖也是保證事務性的重要機制，通過鎖保證了不同事務不能同時修改同一資源)，到顯式以commit或者rollback結束。oracle缺省的事務隔離級別：read committed：只能讀到其他事務已提交的變更，事務中的每一條語句都遵從語句級的讀一致性(即只能讀到每條語句開始時其他事務已提交的變更，執行過

8、程中其他事務提交的變更不被體現)，保證不會臟讀。2022/9/25中國平安保險（集團）股份有限公司 秘密 版權所有，不得侵犯事務、undo、redo 事務需要注意的是完整性約束檢查的點是在語句執行結束的時候開始的，也就是說只要有一行的修改違反完整性約束，則整體條語句失敗。在oracle中頻繁的commit并不是一個良好的習慣：oracle的所有變化都是在SGA中完成的，然后通過后臺進程同步到存儲中；但這一同步過程并不是只在commit的時候才發生，而是有一定量的數據被修改就會發生；實際上每次commit的消耗都是比較小的，因為大量修改的數據其實已經寫到存儲中了；過于頻繁的commit反而帶來冗

9、余的checkpoint(簡單來講，檢查內存和存儲中的信息是否完全一致，不一致則調用相關的同步操作)的消耗；只需要在應該commit時候(需要被其他事務可見的時候)commit。2022/9/25中國平安保險（集團）股份有限公司 秘密 版權所有，不得侵犯事務、undo、redo redo所謂重做，顧名思義，就是重新做已經做過的動作。redo log(重做日志)對于oracle數據庫是至關重要的，數據庫中的所有的改變都會記錄到redo log(比如dml、ddl操作等)，一旦數據庫出現故障，oracle能夠根據redo log“重做”，恢復到故障前的情況。 由于重做基本上是不能避免的、也不是浪費

10、，需要注意數據庫過于頻繁的dml操作會帶來大量記錄重做日志的消耗。當然這通常只能增加redo log的日志組或者提高archive log的效率來滿足應用的需要。2022/9/25中國平安保險（集團）股份有限公司 秘密 版權所有，不得侵犯事務、undo、redo undo撤銷：也就是取消之前的操作，回滾到操作前的情況。oracle對于每次數據的修改，都會記錄變化前的數據，這個數據會記錄在rollback segment(回滾段)中。對應的dml操作會在改變的data block和記錄變更前數據的rollback block產生一個相對應的transaction slot，記錄事務的相關信息。如

11、果要回滾一個事務所做的dml操作，oracle根據該事務產生的所有transaction slot中的信息，在rollback segment中找到變更前的數據并回寫到對應的data block即可。(注意這個過程仍是首先在內存中完成，然后通過后臺進程同步到存儲上)如果事務沒有結束，那么這個事務產生的回滾信息就不能被清理。但是如果事務已經提交或者回滾，那么這個事務產生的回滾信息就能夠被清理重用。2022/9/25中國平安保險（集團）股份有限公司 秘密 版權所有，不得侵犯事務、undo、redo ORA-01555由于存在回滾段的循環使用和讀一致性的關系，這就使得open過長時間的cursor可

12、能產生 ORA-01555:snapshot too old 的問題。ORA-01555產生的原因是因為不能讀取到查詢開始時的數據引起。由于讀一致性，sql語句讀取的數據必須是查詢開始時的數據，在查詢過程中產生的變更不能被這個查詢所讀取。對于cursor而言，就是open cursor的時候為查詢開始的時候，close是查詢結束。如果在查詢執行或者open cursor fetch的過程中，原來查詢的數據有被更改，則這個查詢必須到回滾段中取相關修改前的數據。但因為回滾段是循環使用的，假設這個查詢執行的時間過長或者open cursor的時間過長，就可能導致查詢過程中被修改的數據的回滾信息已經被

13、重用(因為更改這些數據的事務已經提交了，顯然也不會被查詢阻塞)，不能找到需要的修改前的數據，從而發生ORA-01555。更詳細可以參見文檔關于ORA-01555的成因和應對措施.doc或者其他相關的資料。2022/9/25中國平安保險（集團）股份有限公司 秘密 版權所有，不得侵犯關于鎖機制鎖(lock)：oracle中用于保護資源的共享機制，對于任何資源、對象的訪問都需要對其進行加鎖，用以保護對資源的并發訪問時用戶在存取同一數據庫對象時的正確性(即無丟失修改、可重復讀、不讀“臟”數據)；鎖也是保證oracle事務特性的重要機制，通過鎖機制保證了不同的事務不能同時發起對同一資源的并發修改。在or

14、acle中，鎖簡單來講有兩個維度：一個是鎖的類別(lock_type)，這個維度表示了是在哪種資源、對象上的鎖，比如JQ表示在job對象上的鎖、TM表示對象鎖(表鎖)、TX表示事務鎖(行鎖)、TS表示表空間(tablespace)的鎖等等。另一個是鎖的模式(mode)，包含0-6。2022/9/25中國平安保險（集團）股份有限公司 秘密 版權所有，不得侵犯關于鎖機制鎖的模式(mode)：0：None1：null2：row share，即RS、行級共享鎖3：row exclusive，即RX、行級排它鎖4：share，即S、共享鎖5：share row exclusive，即SRX、共享行級排它

15、鎖6：exclusive，即X、排它鎖2022/9/25中國平安保險（集團）股份有限公司 秘密 版權所有，不得侵犯關于鎖機制不同的鎖模式(lock mode)的相容列表見下：2022/9/25中國平安保險（集團）股份有限公司 秘密 版權所有，不得侵犯關于鎖機制oracle中的不同操作需要對不同的對象加不同模式的鎖；通過鎖的類別來表示對某種對象加鎖；而通過不同的鎖的模式的相容規則，來控制哪些操作可以并行，哪些操作是互斥的；通過這樣的鎖機制來保證每個用戶訪問對象的正確性。一個操作可能需要對多種對象加鎖(需要申請一種以上type的鎖)，同時根據操作的不同申請不同的鎖模式(lock mode)。比如：

16、select for update操作需要對表申請mode=3(即RX)的TM鎖(lock type=TM)，然后對選到的行申請mode=6(即X)的TX鎖(lock type=TX)(網上很多文檔說是加mode=2的TM鎖，是在8i庫上，在9i或者10g的庫實測加的是mode=3的TM鎖，如果有分區則對對應分區增加的是mode=2的TM鎖)；執行DML操作也是一樣，需要對表增加mode=3的TM鎖，對作dml操作的行增加mode=6的TX鎖。那么根據鎖相容的模式，mode=3的鎖是相容的(即RX與RX是相容的)，但mode=6的鎖是不相容的(即X與X是不相容的)；因此同時在一張表上執行dml

17、操作和select for update操作是不阻塞的(同時對一張表增加mode=3的TM鎖是相容的)；但如果涉及到相同的行則會阻塞一方，直到另一方事務完成(同時對一行增加mode=6的TX鎖是不相容的)。2022/9/25中國平安保險（集團）股份有限公司 秘密 版權所有，不得侵犯關于鎖機制通過這個過程，我們可以簡單理解oracle的鎖機制是如何控制不同操作的相容和互斥。實際上，oracle的每種操作都有不同的鎖策略(需要申請什么類型的鎖、什么模式的鎖)，這些復雜的鎖策略隨著不同的數據庫版本也有所變化；通過這些復雜的機制，來保證用戶訪問對象的正確性和一致性。 oracle的dml鎖所有鎖機制中

18、，最為常見也最為常用的就是進行各種增、刪、查、改操作中的dml鎖機制。dml鎖，顧名思義，就是在各種dml操作中產生的鎖，這里主要是出現TX、TM兩種類型鎖。在dml鎖機制中，TX鎖會出現在實際發生改變的部分用于保證dml操作的正確性。也就是我們通常講的事務鎖(實際上這個事務所真正改變的部分)或者行鎖，用于鎖定發生改變的行，從而保證修改的正確性(不同時被其他session修改)；就像我們之前看到的是用了mode=6的鎖從而阻塞了其他的修改操作。TM鎖在這里則是一種意向鎖，也就是說需要修改某一個對象時，對其上層對象增加一個鎖，表明修改其下級對象意愿，可以理解為一種操作的入隊；就像我們之前看到的，

19、會增加mode=3的TM鎖鎖定做dml操作的表；這個鎖不會阻塞其他session對這張表同時進行的增刪查改操作，但會阻塞對這張表的ddl操作(大部分，會使用獨占的ddl鎖定，比如add column等等)，保證對象的正確性。2022/9/25中國平安保險（集團）股份有限公司 秘密 版權所有，不得侵犯關于鎖機制 v$lock視圖v$lock視圖記錄了每個session取得鎖或者等待鎖的情況：ID1和ID2標識了鎖定的對象，在TM和TX鎖中的含義如下：2022/9/25中國平安保險（集團）股份有限公司 秘密 版權所有，不得侵犯關于鎖機制通過v$lock視圖我們就能查到session之間持有和等待鎖

20、以及相互阻塞的情況。更詳細的有關dml鎖機制的說明可以參看轉引網文oracle多粒度封鎖機制研究（論壇）.doc或其他相關資料。本文大量內容引自該文檔和oracle expert one-on-one相關內容。 關于死鎖需要注意的是，就一般而言oracle中并不會長期存在真正意義上的死鎖。oracle會以一個很短的時間去輪循，檢查是否有死鎖，如果發現有死鎖出現，則會中斷掉其中一個session以解除死鎖，并拋出ORA-00060錯誤。2022/9/25中國平安保險（集團）股份有限公司 秘密 版權所有，不得侵犯關于鎖機制 一個關于外鍵關聯在dml操作中鎖機制的案例oracle的dml鎖中，比較復

21、雜的情況之一就是涉及到外鍵關聯的情況，由于存在完整性約束檢查，這里不僅僅會對發生dml的表本身產生鎖，也會對有外鍵關聯的表產生鎖。案例：lock sample1.doc2022/9/25中國平安保險（集團）股份有限公司 秘密 版權所有，不得侵犯簡單的sql優化sql語句的執行過程關于索引與表掃描關于表連接關于排序2022/9/25中國平安保險（集團）股份有限公司 秘密 版權所有，不得侵犯sql語句的執行過程sql語句的執行步驟hard parse與soft parsesoft parse也會有消耗2022/9/25中國平安保險（集團）股份有限公司 秘密 版權所有，不得侵犯sql語句的執行步驟1

22、、語法分析，分析語句的語法是否符合規范，衡量語句中各表達式的意義。2、語義分析，檢查語句中涉及的所有數據庫對象是否存在，且用戶有相應的權限。3、視圖轉換，將涉及視圖的查詢語句轉換為相應的對基表查詢語句。4、表達式轉換， 將復雜的 SQL 表達式轉換為較簡單的等效連接表達式。5、選擇優化器，不同的優化器一般產生不同的執行計劃6、選擇連接方式， ORACLE 有三種連接方式，對多表連接 ORACLE 可選擇適當的連接方式。7、選擇連接順序， 對多表連接 ORACLE 選擇哪一對表先連接，選擇這兩表中哪個表做為源數據表。8、選擇數據的搜索路徑，根據以上條件選擇合適的數據搜索路徑，如是選用全表搜索還是

23、利用索引或是其他的方式。9、運行執行計劃。 2022/9/25中國平安保險（集團）股份有限公司 秘密 版權所有，不得侵犯hard parse與soft parse1-8的步驟也就是我們通常所說的parse，通過parse得到一條語句的執行計劃，可以看出parse的過程是一個比較昂貴的消費，顯然如果每次執行sql都需要進行一次完整的parse，那么將是非常大的消耗。因此，大部分數據庫都提供了sql的共享的機制。一條sql語句如果做一次完整的parse并生成全新的執行計劃，這個過程被稱為hard parse；如果已經parse過并仍然存在于緩存中的sql語句，再次執行時則直接使用已經在緩存中的執行

24、計劃，不需要再重新生成執行計劃，這個過程稱為soft parse。正是因為這樣，我們大量使用綁定變量，使得只是參數不同的同構sql語句在oracle為同一條sql語句(只是具體執行時使用的參數不一樣)，由此使得sql語句的執行計劃可以得到復用，減少hard parse，盡量用到soft parse，從而減少parse 帶來的消耗。 2022/9/25中國平安保險（集團）股份有限公司 秘密 版權所有，不得侵犯soft parse也會有消耗盡管如此，soft parse也并非全無消耗，soft parse同樣需要在shared pool中取得相關內存空間的latch(鎖住存儲sql語句、執行計劃以

25、及需要鎖住的相關數據字典的內存空間)；而對latch的分配和操作本身就是一個比較耗cpu的動作，latch的數量也是有限的，因此過量的并發執行，即使都是soft parse依然會造成很大的消耗。 案例：實際上如果能夠在pga空間中的cursor cache找到同樣的語句，則不需要再到shared pool中查找，這個過程是消耗最小的。默認情況下，oracle并不會去為session緩存cursor，需要我們去設置session_cashed_cursor來指定oracle為session緩存的cursor數量(當然這會消耗pga內存空間)。 2022/9/25中國平安保險（集團）股份有限公司

26、秘密 版權所有，不得侵犯sql語句的執行過程接下來，運行“執行計劃”，就是通常sql性能最重要的部分；選擇了怎樣的執行計劃、如何做表連接、如何進行表的掃描、是否使用索引、使用什么索引，等等問題。應該選擇什么樣的執行計劃，一個比較基本的看法，首先應關注那些直接的查詢條件(也就是表的列直接和帶入參數進行比較的查詢條件)，這些查詢條件中哪些能夠首先篩選掉較多的記錄從而有效的降低結果集，那么應當優先執行這些查詢條件，降低整個sql執行過程中需要處理的結果集。當然實際上sql的執行計劃必須全盤考慮整個查詢過程怎樣才是較優的查詢路徑，包括每個環節步驟選擇什么索引、什么掃描方式、什么表連接方式。下面我們依次

27、看看這些問題。 2022/9/25中國平安保險（集團）股份有限公司 秘密 版權所有，不得侵犯關于索引與表掃描B Tree索引的數據結構判斷是否適合使用索引索引使用不合理的常見問題2022/9/25中國平安保險（集團）股份有限公司 秘密 版權所有，不得侵犯B Tree索引的數據結構索引，正如其名稱一樣，就好像字典中的索引，通過它數據庫能夠根據一些特定的信息很快的定位到所需要的數據而并不需要察看全部的數據才能得到想要的結果。B Tree索引的數據結構是一個根據關鍵字排序的B+樹結構(一個多層的N叉樹)，由一群(關鍵字、值)對組成；關鍵字就是索引列的列值(如果是復合索引，則是多個列值)，值就是對應記

28、錄的rowid。其中，根節點存儲1-N個關鍵字和2-N+1個指針，其指針指向內層節點或者葉結點(如果索引足夠小)；內層節點存儲(N+1)/2-1-N個關鍵字和 (N+1)/2-N+1個指針，其指針指向葉節點或其他內層節點；葉節點存儲(N+1)/2-N個關鍵字和 (N+1)/2-N+1個指針，其最后一個指針指向下一個葉節點；其余的指針指向對應的行記錄(也就是上面說的rowid)，關鍵字保存對應記錄索引列的列值。2022/9/25中國平安保險（集團）股份有限公司 秘密 版權所有，不得侵犯B Tree索引的數據結構根節點和內層節點的關鍵字表示一個范圍，其指針分別指向了小于該關鍵字或者大于等于該關鍵字

29、的節點群，如下圖： 葉節點的關鍵字為對應的記錄索引列的列值，除最后一個指針指向下一個葉結點外其余指針則指向了對應的記錄(rowid)，如下圖： 2022/9/25中國平安保險（集團）股份有限公司 秘密 版權所有，不得侵犯B Tree索引的數據結構如上假設我們要查找索引列值為75的記錄，只需要在根節點中找到57到81這個范圍的節點群，然后依次根據范圍最終在葉節點中找到索引列為75的記錄的rowid。 2022/9/25中國平安保險（集團）股份有限公司 秘密 版權所有，不得侵犯判斷是否適合使用索引索引之所以能夠起到優化查詢的作用，就在于它將查詢用到的條件(列)作為關鍵字(其對應值指向對應的記錄)并

30、組織為一個排序的結構，這樣我們能在這個排序結構中快速的定位到要查找的記錄而不需要去遍歷全部的數據(就好像查字典一樣，根據拼音或者筆畫就能很快的查到一個字，而不需要把整個字典翻一遍)。 相對通過全表掃描找到一條記錄，通過索引避免了很多冗余數據的掃描(我們不需要把整個字典中不是我們要查找的字的頁也翻看一遍)。但同時我們也看到，對于單獨的一條記錄而言通過索引掃描在讀取這條記錄的花費上增加了掃描索引和通過rowid定位的操作。 因此不是所有情況下，都適合使用的索引。假設一個字典記錄了1000個字，而我們需要查找其中的900個字，這種情況下如果還先查索引在找到對應的字就不如直接把整個字典翻看一遍來的要快

31、。 同樣的道理，并不是所有的字段都適合建立B Tree索引，如果一個字段的獨立列值非常少，比如100萬的記錄卻只有10個獨立列值，那么任意查詢其中一個列值都會查詢出10萬條記錄(10%)，那么這個索引就算使用效率也很低，這個字段不適合建立單列的B Tree索引。2022/9/25中國平安保險（集團）股份有限公司 秘密 版權所有，不得侵犯判斷是否適合使用索引而實際上的經驗數據，當通過索引掃描access的記錄數=:1 and tab.a=:2，這種情況主要看 :1 - :2 之間這個范圍的記錄數占到總記錄數的比例；范圍太大(比例比較高)的話則不適合使用a列的索引tab.a in (list)，這

32、種情況主要看in list中的列值包含的記錄數占到總記錄數的百分比，這個百分比較大的話就不適合使用a的索引。假設一個列有10個獨立列值，而in list中就有5個列值，那么平均計算可能就是50%，顯然這里并不適合使用a的索引。tab.a=table.b，通過表table作為驅動表與表tab做表連接，連接條件是table表的b列=tab表的a列，這里主要看表table用于表連接的結果集其每條記錄的b列值對應在tab表的a列能夠選取到的記錄數的總和占tab表記錄數的百分比(這里用tab表a列的索引指的是使用nested loop表連接方式的情況下，使用hash join或其他的表連接方式，這個比例

33、的計算并不適用，關于表連接的方式，我們在后面討論)，如果表table用于作為驅動表的結果集比較小、且結果集中b列的列值對應tab表中a列的列值能夠選取的到的記錄數比較低，則適合使用tab表上a列的索引。(這里指使用nested loop的情況，涉及到表連接索引的使用要跟表連接的方式一起考慮，在表連接的部分我們再做討論) 2022/9/25中國平安保險（集團）股份有限公司 秘密 版權所有，不得侵犯索引使用不合理的常見問題 缺少合適的索引可用(選擇更加優化的字段或者合理的復合索引首列) 案例1：index sample1.doc在這個案例中：語句(1)存在一個日期范圍查詢可以使用在日期字段上的索引

34、，但是如果時間范圍跨度過大，這個索引的效率也就不高了；語句(2)能夠有查詢條件的字段當中只有一個區分度很低的字段建了索引(千萬條數據只有幾十個獨立列值)，這個字段是不適合建立單列索引的，查詢使用這個索引的效率也非常低；這兩個語句我們通過分析語句，都發現了有區分度比較高且適用的查詢條件字段，只要在這些字段建立索引，就能優化語句的執行效率。這里我們看到，過大的范圍查詢會影響索引的效率；而過低的區分度的列則并不適合建立單列索引。2022/9/25中國平安保險（集團）股份有限公司 秘密 版權所有，不得侵犯索引使用不合理的常見問題 缺少合適的索引可用(選擇更加優化的字段或者合理的復合索引首列) 案例2：

35、index sample2.doc在這個案例中：表cjk上原來有一個復合索引(FZJZH, FBMDM, FSCDM, FGDDM, FZQDM, FHTXH, FMMLB,FCJSJ)，這個索引的區分度很高，本來是很好用的。但問題就出來這兩個查詢語句中，前面幾列使用的都是模糊查詢，而根據實際情況，往往傳入的都是百分號，導致索引掃描的時候無法根據關鍵字的范圍快速的定位到需要的索引結點，在這里反而使用這個索引效率比全表掃描還要低得多(實際情況是幾個小時)。分析這條兩條語句的查詢條件，發現FCJSJ這個查詢條件，實際操作中基本上都是查詢一天的數據，這里只需要建立一個以FCJSJ作為首列的復合索引(

36、fbdsj,fbmdm,fzjzh,fgddm,fscdm,fzqdm)，就可以優化語句的效率。由于B Tree索引是關鍵字排序，如果復合索引的首列不能根據查詢條件有效的篩選，就需要掃描大量冗余的索引結點；在這個案例中由于前面幾列都出現了%號的情況，導致幾乎是將整個索引結點掃描了一遍才得到結果，效率非常低。所以復合索引要特別注意首列的選擇。2022/9/25中國平安保險（集團）股份有限公司 秘密 版權所有，不得侵犯索引使用不合理的常見問題 不均勻分布的列值在bind peeking和histogram的影響下，影響索引的使用首先解釋下相關名詞：bind peeking：sql語句中使用到綁定變

37、量，在第一次執行時會peeking其綁定變量的值，就相當于常量語句一樣，并根據這個具體值解析計算成本，解釋出執行計劃。這一特性是在oracle9i以后引入的。histogram：直方圖，對于不同列值更加準確的數據量的統計。對于列值分布不均勻的列來說，通過直方圖，就能準確計算出不同列值的數據量，而不僅僅簡單的根據 (總記錄數/獨立列值數)來計算其數據量(平均情況)。2022/9/25中國平安保險（集團）股份有限公司 秘密 版權所有，不得侵犯索引使用不合理的常見問題 不均勻分布的列值在bind peeking和histogram的影響下，影響索引的使用以下引用ORACLE數據庫優化案例簡報(第一期

38、)的相關內容：由于8i 還沒有bind peeking 技術，使用綁定變量以后無法使用histogram，所以最好在編程時對具有skew 值的列不使用bind 變量,這樣, 生成計劃時, 其可根據histogram 的值來估算返回的數據量, 并生成合適的計劃。9i引入了bind peeking技術，使用綁定變量以后可以用到histogram，但是如果第一次執行帶入的變量值失誤，很可能產生的執行計劃對以后的多次查詢不適合而帶來性能問題。2022/9/25中國平安保險（集團）股份有限公司 秘密 版權所有，不得侵犯索引使用不合理的常見問題 不均勻分布的列值在bind peeking和histogra

39、m的影響下，影響索引的使用我們看看ORACLE數據庫優化案例簡報(第一期)所舉的這個案例：語句：select policy_cert_no, apply_personnel_numfrom acc_policy_certwhere policy_no = :1and cert_type = 1select policy_cert_nofrom acc_policy_certwhere insurance_card_no = :1and policy_no = :22022/9/25中國平安保險（集團）股份有限公司 秘密 版權所有，不得侵犯索引使用不合理的常見問題 不均勻分布的列值在bind p

40、eeking和histogram的影響下，影響索引的使用這兩條語句都是對表acc_policy_cert進行查詢，在policy_no字段上有主鍵索引(復合索引的首列)，正常的情況下，應該走這個索引而不是全表掃描。但在policy_no的列值分布并不均勻，比如policy_no列共有100個不同的值，其中為70的占了99，為其他值的數據行僅占1（即選擇性很高），則如果不使用綁定變量，借助histogram，oracle能夠知道，查詢policy_no=70的時候應該走全表掃描效率更高，查詢policy_no為其他值的語句應該走索引效率更高。問題就出現了，假設第一次執行時帶入的是70的這個值，顯

41、然執行計劃會走全表掃描，但是由于使用了綁定變量，以后即使是帶入其他值，執行計劃依然會走全表掃描。2022/9/25中國平安保險（集團）股份有限公司 秘密 版權所有，不得侵犯索引使用不合理的常見問題 不均勻分布的列值在bind peeking和histogram的影響下，影響索引的使用解決辦法有3個：1)不使用綁定變量，但是這樣就會導致大量的hard parse，對shared pool也會產生大量的消耗。2)修改程序，針對不同情況使用不同執行計劃(不同的語句)3)不收集直方圖(這樣就會按照平均情況來計算數據量)或者使用hint綁定執行計劃，使其總能使用到索引，這樣就可以使大部分情況得到較好的效

42、率，但對于比如70這樣的值就會效率低下。關于這個問題詳細的解釋和說明可以參見ORACLE數據庫優化案例簡報(第一期)中相關內容。2022/9/25中國平安保險（集團）股份有限公司 秘密 版權所有，不得侵犯關于表連接 三種主要的表連接方式 何時使用哪種表連接方式2022/9/25中國平安保險（集團）股份有限公司 秘密 版權所有，不得侵犯三種主要的表連接方式 nested loop join循環嵌套連接：行源1的每一條記錄，依次去匹配行源2的每條記錄，將符合連接條件的記錄放在結果集中，直到行源1的所有記錄都完成這個操作。循環嵌套連接是最基本也是最古老的表連接方式。 sort merge join排

43、序合并連接：行源1和行源2的數據分別排序，然后將兩個排序的源表合并，符合連接條件的記錄放到結果集中。由于排序需要內存空間，sort merge join對內存有比較大的消耗，如果內存空間(8i為sort_area_size，9i及以上使用PGA)不足，則會使用臨時表空間，這樣會降低排序合并連接的效率。排序合并連接是最古老的表連接方式之一。 hash join哈希連接：將行源1計算成一張基于連接鍵的hash表，行源2的每條記錄依次掃描這張hash表，找到匹配的記錄放到結果集。計算hash表需要內存空間，hash join同樣對于內存有比較大的消耗，如果內存空間(8i為hash_area_size

44、，9i及以上使用PGA)不足，則會使用臨時表空間，這樣會降低哈希連接的效率。2022/9/25中國平安保險（集團）股份有限公司 秘密 版權所有，不得侵犯三種主要的表連接方式 nested loop join2022/9/25中國平安保險（集團）股份有限公司 秘密 版權所有，不得侵犯三種主要的表連接方式 sort merge join2022/9/25中國平安保險（集團）股份有限公司 秘密 版權所有，不得侵犯三種主要的表連接方式 hash join2022/9/25中國平安保險（集團）股份有限公司 秘密 版權所有，不得侵犯何時使用哪種表連接方式 nested loop join表連接方式的適用情

45、況nested loop join適合于：作為表連接的驅動表(也就是之前的行源1，也稱為外部表)記錄數比較少或者通過直接的查詢條件能篩選出比較少的記錄數，被連接表(也就是之前的行源2，也稱為內部表)在連接條件上有區分度很高的索引；驅動表上的每條記錄通過被連接表在連接條件上的索引能快速的匹配到少量的記錄；整體的結果集比較小，這樣就比較適合使用nested loop join。nested loop join選擇驅動表時應優先選擇記錄數比較少的、通過直接查詢條件能夠篩選出比較少記錄的表作為驅動表，這樣能夠有效的減少匹配次數。例如這樣的查詢語句：select * from a,b where a.c

46、ol1=:1 and a.col2=b.col2；這里a表有100條記錄，通過col1=:1的條件能夠篩選出50條記錄，b表只有10條記錄；這里如果以a表作驅動表的話，則匹配次數是50*10；如果用b表作為驅動表的話，則匹配次數是10*100；顯然應該使用a表作為驅動表。如果連接條件沒有很好的索引、或者作為表連接的兩張表結果集都相當大，則并不適合使用nested loop join。2022/9/25中國平安保險（集團）股份有限公司 秘密 版權所有，不得侵犯何時使用哪種表連接方式 sort merge join表連接方式的適用情況sort merge join適用于：當表連接的兩張表的結果集都

47、比較大，或沒有很好的條件可以篩選，連接條件缺少很好的索引時，可以選擇使用sort merge join。由于sort merge join需要對作連接的兩張表都作排序，實際上如果語句中沒有排序需求，oracle更加傾向于選擇hash join。但如果語句中本身就有排序的需求，sort merge join則有可能省去單獨的排序。sort merge join對內存消耗比較大，如果內存空間不足以完成排序，則需要用到臨時表空間，效率會有較大的降低。sort merge join只能用于等價連接。2022/9/25中國平安保險（集團）股份有限公司 秘密 版權所有，不得侵犯何時使用哪種表連接方式 ha

48、sh join表連接方式的適用情況hash join適用于：當表連接的兩張表的結果集都比較大，或沒有很好的條件可以篩選，連接條件缺少很好的索引時，使用hash join能夠取得比較好的效率。hash join雖然也需要將一張表的所有記錄依次和hash表中的記錄進行匹配，但掃描hash表的速度要比掃描B Tree索引快的多，所以在大結果集和缺少良好索引的情況下，使用hash join能得到比較好的效率。與sort merge join相比，hash join只需要進行一次排序，因此大部分情況下，oracle會更傾向于選擇hash join。hash join需要計算一張hash table，與s

49、ort merge join一樣，需要消耗大量的內存空間，如果內存空間不足則需要用到臨時表空間，效率會有較大的降低。hash join只能CBO優化器下使用，只能用于等價連接。例如語句：select * from a,b where a.col1=:1 and b.col1=:2 and a.col2=b.col2在這個語句中，a表通過col1=:1和b表通過col1=:2篩選后的記錄數依然比較大，雖然col2在a表和b表上都是很不錯的索引，但是因為作連接的結果集比較大，使用nested loop效率不高，這時使用hash join就能得到較好的效率。2022/9/25中國平安保險（集團）股份

50、有限公司 秘密 版權所有，不得侵犯何時使用哪種表連接方式案例1：join sample1.doc在這個案例中，原來的執行計劃，選擇了首先掃描兩張小表SELECT_CONDITION_TMP，然后與大表ASSET_COMBINATION進行nested loop join，兩張小表結合起來的條件大概會形成一個幾百條的結果集，而與表ASSET_COMBINATION進行nested loop join也是一個具有一定區分度的索引，看起來似乎選擇nested loop join并沒有錯。但仔細分析：首先這里在nested loop join時使用的索引是字段the_date上的索引，也就是說連接條件

51、curno、cmbno并沒有起到快速查找定位的作用；其次由于兩張小表SELECT_CONDITION_TMP只是一些查詢條件的組合本身數據量非常少，但依然會使得通過the_date=:1這個條件掃描ASSET_COMBINATION表會重復很多次；再次，ASSET_COMBINATION這個表上并沒有以curno或cmbno為首列的索引，而以這兩個列作為條件區分度并不是很高。分析之下，作為直接條件the_date=:1能夠篩過濾掉大量的記錄，且有不錯的索引，有效的減小結果集，因此這里應該首先掃描ASSET_COMBINATION減少冗余的掃描(不必像原執行計劃一樣重復掃描很多次)；然后與兩張小

52、表SELECT_CONDITION_TMP進行連接時，因為這兩張表的數據比ASSET_COMBINATION通過the_date=:1選出來的結果集還要小很多，這里適合使用hash join有比較好的效率。2022/9/25中國平安保險（集團）股份有限公司 秘密 版權所有，不得侵犯何時使用哪種表連接方式案例2：join sample2.msg在這個案例，原語句因為唯一能夠首先過濾掉較多結果集的條件TASK_STATUS_ID = 12本身仍然會篩選出數量較大的結果集，而PA_TASK_TLR_ADMIN本身是個小表，因此優先使用直接條件篩選掉一些結果集，然后再與小表使用hash join；看起

53、來這似乎并沒有什么問題。但仔細分析：表PA_CUSTOMER_CAMPAIGN是個大表，而TASK_STATUS_ID又是一個區分度很低條件，首先使用條件TASK_STATUS_ID = 12查詢表PA_CUSTOMER_CAMPAIGN就是一個效率不高的動作。發現在表PA_CUSTOMER_CAMPAIGN上字段MGR_QUEUE_ID具有相對好一些的區分度(當然就這個表數據量來講，不算太好)，而表PA_TASK_TLR_ADMIN又是一個小表，雖然沒有首先使用TASK_STATUS_ID = 12過濾掉較多的結果集，但是這里使用小表PA_TASK_TLR_ADMIN通過在MGR_QUEUE

54、_ID上的索引nested loop join表PA_CUSTOMER_CAMPAIGN，因為這個索引的效率要好得多了，反而能夠取得比較好的效率。2022/9/25中國平安保險（集團）股份有限公司 秘密 版權所有，不得侵犯關于排序排序是數據庫中一個比較常見的操作，使用order by是一個很司空見慣的東西；但排序的發生并不只是在order by的時候，排序產生的消耗有時也會產生很大的影響，對于排序也有一些需要注意的地方。一個關于排序的sql：投資數據庫有一個很簡單的sql，是一個統計報表類的sql，只是將一張數據表中某一段時間的數據做一個按照一些統計條件group by的操作；通常會計算一年的

55、數據，雖然數據量比較大，但因為是個統計操作，執行次數很低，在生產環境大概每次執行需要10幾秒。但在測試環境的一次測試中，這條語句發現執行得很慢，幾分鐘才能跑出結果。對此跟蹤之下，發現這條語句執行時有大量的direct path read/write等待(后面我們會討論)，也就是說排序使用臨時表空間產生了很大消耗。經檢查，這張表有百萬級的數據量，占了幾百M的空間。根據查詢條件約會查詢出1/3的記錄數來作統計，算下來大約是100M的數據。發現測試環境的pga_aggregate_target設置的是200M(如果沒有記錯的話，總之是一個比較小的值)左右；由于PGA的可用空間比較小，排序不能完全在內

56、存中完成，因此大量使用了臨時表空間，結果排序效率是成倍下降。2022/9/25中國平安保險（集團）股份有限公司 秘密 版權所有，不得侵犯關于排序可以看到，排序是很消耗內存的操作，而且根據排序的算法，1M的數據排序所需要的空間顯然會大于1M；因此要注意控制合理的排序，讓排序盡量在內存中就能夠完成，過大的排序會帶來巨大的消耗。為排序設置合理內存空間：8i是sort_area_size，9i以上則是pga_aggregate_target。避免不必要的排序：除order by以外，group by、distinct、unique、union都會產生排序的操作；如果沒有必要的話，不使用distinct

57、、unique，使用union all代替union。減小排序的結果集：盡量只讓必要的結果集進行排序而不是排序后再選出需要的結果集；需要根據需求和效率在下面的兩種情況作出合理的選擇：1 select * from (select * from a order by c1) where rownum =: 12 select * from (select * from a where rownum = :1) order by c12022/9/25中國平安保險（集團）股份有限公司 秘密 版權所有，不得侵犯簡單的等待事件 什么是等待事件 優化等待事件與sql優化的關系 等待事件相關的視圖(v$s

58、ession_wait) 典型的等待事件及其分析2022/9/25中國平安保險（集團）股份有限公司 秘密 版權所有，不得侵犯什么是等待事件等待事件：oracle wait event 或者 oracle wait interface，ORACLE中對于任何操作、任務、過程都可以使用一個等待事件來描述，描述了當前session對資源的等待和所執行的操作，通過等待事件能夠獲知當前session在等待某項資源和性能狀況。也可以說等待事件是oracle提供的一個用以體現當前運行和性能狀況一個接口。等待事件分為空閑等待事件和非空閑等待事件，通常不需要關注空閑等待事件。常見的空閑等待事件：SQL*Net

59、message to client、SQL*Net message from client、SQL*Net break/reset to client、Streams AQ: waiting for messages in the queue、Streams AQ: waiting for time management or cleanup tasks、jobq slave wait；通常不需要關注；但空閑等待事件中有一部分是涉及到dblink的，如：SQL*Net message from dblink、SQL*Net break/reset to dblink，發生這樣的等待事件可能表示

60、遠端數據庫存在效率問題。關注等待事件能夠從當前的運行、消耗、性能狀況來查知數據庫的一些性能問題，分析出現問題的原因，而不僅僅是去分析執行時間較長的sql(執行時間長的sql可能代表著效率問題，但不一定是造成當前性能問題的原因)。 2022/9/25中國平安保險（集團）股份有限公司 秘密 版權所有，不得侵犯優化等待事件與優化sql的關系可以說，當前oracle數據庫優化方法已經從優化sql轉向優化優化等待事件。這不是說優化等待事件與優化sql沖突，而是因為等待事件能夠更直觀的反應數據庫在效率方面的問題，從而更加準確的發現問題所在，加以針對優化；這里不僅是有sql效率所引起的問題，也包括各種資源、