Oracle與sql簡單優化與鎖機制淺析系統運營二部徐海濤oracle數據庫的基本概念與原理對象的存儲，segment、extent、blockSGA、PGA內存域，內存與存儲的關系事務、undo、redo與ORA-01555關于鎖機制2023/1/18對象的存儲oracle中的對象以segment的形式存儲。我們可以在dba_segment這張視圖中查詢到所有我們創建的表和索引。segment由extent組成。其擴展是以extent為單位。一張表在初始化時會首先產生至少一個設定大小的extent，以后如果記錄數逐漸增多，則需要擴展segment的空間，每次以設定大小擴展一個extent(即增加一個設定大小的extent到segment中)。extent由block組成。block是oracle存儲中最基本的單位。一個block上會存儲一條或多條數據記錄，讀取一條數據記錄時至少需要讀取出這條記錄所在block。在blockheader上記錄了一些非常重要的信息，包含塊的類型(表還是索引)、關于塊上活動和過時的事務信息、塊在磁盤上的位置等等。一個segment屬于一個唯一的tablespace，而一個tablespace則可以包含一個或多個數據文件。2023/1/18oracle的內存結構SGA內存域ORACLE使用的所有共享內存空間被稱為SGA(systemglobalarea)的內存結構SGA主要包含下面的內存域：databuffer：用于放置datablock，ORACLE中所有的數據操作(增、刪、查、改)都需要在databuffer中完成，讀數據時需先將數據塊從存儲讀到databuffer，修改數據的操作需在databuffer中完成修改然后在回寫存儲。優化物理讀的一個辦法就是增大databuffer，使數據在databuffer的停留時間變長，提高buffer的命中率，減少物理讀，也就減小了I/O，不過這是不推薦的辦法，最重要的還是要優化應用。sharedpool：用于放置緩存的sql語句、sql語句的執行計劃、數據字典視圖等，sql語句執行過程中需要保持在sharedpool中的語句本身和其執行計劃，dll操作也需要在sharedpool中鎖住相關的數據字典。javapool：用于存放java對象。largepool：用于分配一些大塊的內存給進程應對一些特殊的需要，如語句的并行執行和備份會用到largepool，weblogicconnectionpool連接ORACLE數據庫也是使用largepool存放connection的相關信息。redologbuffer：用于緩存redolog，redolog會先緩存到redologbuffer然后再寫到日志組中。2023/1/18oracle的內存結構在oracle中幾乎所有操作都是SGA完成的。不論增、刪、查、改都是將需要的數據取到SGA中，在SGA中完成相關的操作。oracle通過后臺進程(DBWn)將SGA中產生的變化同步到儲存中，本身并不直接在存儲上進行增、刪、查、改的操作。PGA內存域針對每個oracle進程(process)分配的獨占內存空間被稱為PGA(processglobalarea)的內存結構，是在SGA之外獨立分配的，一般情況下，session越多也就耗用越多的PGA。總體而言，PGA中需要關注的地方不是太多，在9i以上的版本，使用自動內存管理，用于hash和排序的內存空間從SGA挪到了PGA，為PGA的上限值(pga_aggregate_target)配置一個合理的值對sql語句的效率有較大影響。(oracle中另一部分非常重要的機制就是oracle中的后臺進程，這里我們不作討論，大家可以參看《oracleexpertone-on-one》等相關的書籍)2023/1/18事務、undo、redo事務事務：單個邏輯工作單元執行的一系列操作。事務遵循如下的特性：原子性：一個事務要么完全發生，要么完全不發生一致性：事務把數據庫從一個一致狀態轉變到另一個狀態隔離性：在事務提交以前，其他事務察覺不到事務的影響持久性：一旦事務提交，它是永久的oracle的事務是隱式開始的，從第一條dml語句開始(第一條取得TX鎖的語句開始的，后面我們將討論oracle的鎖機制，鎖也是保證事務性的重要機制，通過鎖保證了不同事務不能同時修改同一資源)，到顯式以commit或者rollback結束。oracle缺省的事務隔離級別：readcommitted：只能讀到其他事務已提交的變更，事務中的每一條語句都遵從語句級的讀一致性(即只能讀到每條語句開始時其他事務已提交的變更，執行過程中其他事務提交的變更不被體現)，保證不會臟讀。2023/1/18事務、undo、redo事務需要注意的是完整性約束檢查的點是在語句執行結束的時候開始的，也就是說只要有一行的修改違反完整性約束，則整體條語句失敗。在oracle中頻繁的commit并不是一個良好的習慣：oracle的所有變化都是在SGA中完成的，然后通過后臺進程同步到存儲中；但這一同步過程并不是只在commit的時候才發生，而是有一定量的數據被修改就會發生；實際上每次commit的消耗都是比較小的，因為大量修改的數據其實已經寫到存儲中了；過于頻繁的commit反而帶來冗余的checkpoint(簡單來講，檢查內存和存儲中的信息是否完全一致，不一致則調用相關的同步操作)的消耗；只需要在應該commit時候(需要被其他事務可見的時候)commit。2023/1/18事務、undo、redoredo所謂重做，顧名思義，就是重新做已經做過的動作。redolog(重做日志)對于oracle數據庫是至關重要的，數據庫中的所有的改變都會記錄到redolog(比如dml、ddl操作等)，一旦數據庫出現故障，oracle能夠根據redolog“重做”，恢復到故障前的情況。

由于重做基本上是不能避免的、也不是浪費，需要注意數據庫過于頻繁的dml操作會帶來大量記錄重做日志的消耗。當然這通常只能增加redolog的日志組或者提高archivelog的效率來滿足應用的需要。2023/1/18事務、undo、redoundo撤銷：也就是取消之前的操作，回滾到操作前的情況。oracle對于每次數據的修改，都會記錄變化前的數據，這個數據會記錄在rollbacksegment(回滾段)中。對應的dml操作會在改變的datablock和記錄變更前數據的rollbackblock產生一個相對應的transactionslot，記錄事務的相關信息。如果要回滾一個事務所做的dml操作，oracle根據該事務產生的所有transactionslot中的信息，在rollbacksegment中找到變更前的數據并回寫到對應的datablock即可。(注意這個過程仍是首先在內存中完成，然后通過后臺進程同步到存儲上)如果事務沒有結束，那么這個事務產生的回滾信息就不能被清理。但是如果事務已經提交或者回滾，那么這個事務產生的回滾信息就能夠被清理重用。2023/1/18事務、undo、redoORA-01555由于存在回滾段的循環使用和讀一致性的關系，這就使得open過長時間的cursor可能產生ORA-01555:snapshottooold的問題。ORA-01555產生的原因是因為不能讀取到查詢開始時的數據引起。由于讀一致性，sql語句讀取的數據必須是查詢開始時的數據，在查詢過程中產生的變更不能被這個查詢所讀取。對于cursor而言，就是opencursor的時候為查詢開始的時候，close是查詢結束。如果在查詢執行或者opencursorfetch的過程中，原來查詢的數據有被更改，則這個查詢必須到回滾段中取相關修改前的數據。但因為回滾段是循環使用的，假設這個查詢執行的時間過長或者opencursor的時間過長，就可能導致查詢過程中被修改的數據的回滾信息已經被重用(因為更改這些數據的事務已經提交了，顯然也不會被查詢阻塞)，不能找到需要的修改前的數據，從而發生ORA-01555。更詳細可以參見文檔《關于ORA-01555的成因和應對措施.doc》或者其他相關的資料。2023/1/18關于于鎖鎖機機制制鎖(lock)：：oracle中用用于于保保護護資資源源的的共共享享機機制制，，對對于于任任何何資資源源、、對對象象的的訪訪問問都都需需要要對對其其進進行行加加鎖鎖，，用用以以保保護護對對資資源源的的并并發發訪訪問問時時用用戶戶在在存存取取同同一一數數據據庫庫對對象象時時的的正正確確性性(即即無無丟丟失失修修改改、、可可重重復復讀讀、、不不讀讀““臟臟””數數據據)；；鎖鎖也也是是保保證證oracle事務務特特性性的的重重要要機機制制，，通通過過鎖鎖機機制制保保證證了了不不同同的的事事務務不不能能同同時時發發起起對對同同一一資資源源的的并并發發修修改改。。在oracle中，，鎖鎖簡簡單單來來講講有有兩兩個個維維度度：：一個個是是鎖鎖的的類類別別(lock_type)，，這個個維維度度表表示示了了是是在在哪哪種種資資源源、、對對象象上上的的鎖鎖，，比比如如JQ表示示在在job對象象上上的的鎖鎖、、TM表示示對對象象鎖鎖(表表鎖鎖)、、TX表示示事事務務鎖鎖(行行鎖鎖)、、TS表示示表表空空間間(tablespace)的的鎖鎖等等等等。。另一一個個是是鎖鎖的的模模式式(mode)，，包含含0-6。。2023/1/4關于于鎖鎖機機制制鎖的的模模式式(mode)：：0：：None1：：null2：：rowshare，，即RS、、行級級共共享享鎖鎖3：：rowexclusive，，即RX、、行級級排排它它鎖鎖4：：share，，即S、、共享享鎖鎖5：：sharerowexclusive，，即SRX、、共享享行行級級排排它它鎖鎖6：：exclusive，，即X、、排它它鎖鎖2023/1/4關于于鎖鎖機機制制不同同的的鎖鎖模模式式(lockmode)的相相容容列列表表見見下下：：2023/1/4關于鎖機機制oracle中的不同同操作需需要對不不同的對對象加不不同模式式的鎖；；通過鎖鎖的類別別來表示示對某種種對象加加鎖；而而通過不不同的鎖鎖的模式式的相容容規則，，來控制制哪些操操作可以以并行，，哪些操操作是互互斥的；；通過這這樣的鎖鎖機制來來保證每每個用戶戶訪問對對象的正正確性。。一個操作作可能需需要對多多種對象象加鎖(需要申申請一種種以上type的鎖)，，同時根根據操作作的不同同申請不不同的鎖鎖模式(lockmode)。比如：selectforupdate操作需要要對表申申請mode=3(即RX)的TM鎖(locktype=TM)，然后對選選到的行行申請mode=6(即X)的TX鎖(locktype=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操作和selectforupdate操作是不不阻塞的的(同時時對一張張表增加加mode=3的TM鎖是相容容的)；但如果涉涉及到相相同的行行則會阻阻塞一方方，直到到另一方方事務完完成(同同時對一一行增加加mode=6的TX鎖是不相相容的)。2023/1/4關于鎖機機制通過這個個過程，，我們可可以簡單單理解oracle的鎖機制制是如何何控制不不同操作作的相容容和互斥斥。實際際上，oracle的每種操操作都有有不同的的鎖策略略(需要要申請什什么類型型的鎖、、什么模模式的鎖鎖)，這些復雜雜的鎖策策略隨著著不同的的數據庫庫版本也也有所變變化；通通過這些些復雜的的機制，，來保證證用戶訪訪問對象象的正確確性和一一致性。。oracle的dml鎖所有鎖機機制中，，最為常常見也最最為常用用的就是是進行各各種增、、刪、查查、改操操作中的的dml鎖機制。。dml鎖，顧名名思義，，就是在在各種dml操作中產產生的鎖鎖，這里里主要是是出現TX、TM兩種類型型鎖。在dml鎖機制中，TX鎖會出現在實實際發生改變變的部分用于于保證dml操作的正確性性。也就是我我們通常講的的事務鎖(實實際上這個事事務所真正改改變的部分)或者行鎖，用于鎖定發生生改變的行，，從而保證修修改的正確性性(不同時被被其他session修改)；就像像我們之前看看到的是用了了mode=6的鎖從而阻塞塞了其他的修修改操作。TM鎖在這里則是是一種意向鎖鎖，也就是說說需要修改某某一個對象時時，對其上層層對象增加一一個鎖，表明明修改其下級級對象意愿，，可以理解為為一種操作的的入隊；就像像我們之前看看到的，會增增加mode=3的TM鎖鎖定做dml操作的表；這這個鎖不會阻阻塞其他session對這張表同時時進行的增刪刪查改操作，，但會阻塞對對這張表的ddl操作(大部分分，會使用獨獨占的ddl鎖定，比如addcolumn等等)，保證證對象的正確確性。2023/1/4關于鎖機制v$lock視圖v$lock視圖記錄了每每個session取得鎖或者等等待鎖的情況況：ID1和ID2標識了鎖定的的對象，在TM和TX鎖中的含義如如下：2023/1/4關于鎖機制制通過v$lock視圖我們就就能查到session之間持有和和等待鎖以以及相互阻阻塞的情況況。更詳細的有有關dml鎖機制的說說明可以參參看轉引網網文《oracle多粒度封鎖鎖機制研究究（論壇））.doc》或其他相關關資料。本文大量內內容引自該該文檔和《《oracleexpertone-on-one》》相關內容。。關于死鎖需要注意的的是，就一一般而言oracle中并不會長長期存在真真正意義上上的死鎖。。oracle會以一個很很短的時間間去輪循，，檢查是否否有死鎖，，如果發現現有死鎖出出現，則會會中斷掉其其中一個session以解除死鎖鎖，并拋出出ORA-00060錯誤。2023/1/4關于鎖機制制一個關于外外鍵關聯在在dml操作中鎖機機制的案例例oracle的dml鎖中，比較較復雜的情情況之一就就是涉及到到外鍵關聯聯的情況，，由于存在在完整性約約束檢查，，這里不僅僅僅會對發發生dml的表本身產產生鎖，也也會對有外外鍵關聯的的表產生鎖鎖。案例：locksample1.doc2023/1/4簡單的sql優化sql語句的執行行過程關于索引與與表掃描關于表連接接關于排序2023/1/4sql語句句的的執執行行過過程程sql語句句的的執執行行步步驟驟hardparse與softparsesoftparse也會會有有消消耗耗2023/1/4sql語句句的的執執行行步步驟驟1、、語法法分分析析，，分分析析語語句句的的語語法法是是否否符符合合規規范范，，衡衡量量語語句句中中各各表表達達式式的的意意義義。。2、、語語義義分分析析，，檢檢查查語語句句中中涉涉及及的的所所有有數數據據庫庫對對象象是是否否存存在在，，且且用用戶戶有有相相應應的的權權限限。。3、、視視圖圖轉轉換換，，將將涉涉及及視視圖圖的的查查詢詢語語句句轉轉換換為為相相應應的的對對基基表表查查詢詢語語句句。。4、、表表達達式式轉轉換換，，將將復復雜雜的的SQL表達達式式轉轉換換為為較較簡簡單單的的等等效效連連接接表表達達式式。。5、、選選擇擇優優化化器器，，不不同同的的優優化化器器一一般般產產生生不不同同的的"執執行行計計劃劃"6、、選選擇擇連連接接方方式式，，ORACLE有三三種種連連接接方方式式，，對對多多表表連連接接ORACLE可選選擇擇適適當當的的連連接接方方式式。。7、、選選擇擇連連接接順順序序，，對對多多表表連連接接ORACLE選擇擇哪哪一一對對表表先先連連接接，，選選擇擇這這兩兩表表中中哪哪個個表表做做為為源源數數據據表表。。8、、選選擇擇數數據據的的搜搜索索路路徑徑，，根根據據以以上上條條件件選選擇擇合合適適的的數數據據搜搜索索路路徑徑，，如如是是選選用用全全表表搜搜索索還還是是利利用用索索引引或或是是其其他他的的方方式式。。9、、運運行行"執執行行計計劃劃"。。2023/1/4hardparse與softparse1-8的步驟也也就是我我們通常常所說的的parse，通過parse得到一條條語句的的執行計計劃，可可以看出出parse的過程是是一個比比較昂貴貴的消費費，顯然然如果每每次執行行sql都需要進進行一次次完整的的parse，那么將是是非常大大的消耗耗。因此，大大部分數數據庫都都提供了了sql的共享的的機制。。一條sql語句如果果做一次次完整的的parse并生成全全新的執執行計劃劃，這個個過程被被稱為hardparse；；如果已已經parse過并仍仍然存存在于于緩存存中的的sql語句，，再次次執行行時則則直接接使用用已經經在緩緩存中中的執執行計計劃，，不需需要再再重新新生成成執行行計劃劃，這這個過過程稱稱為softparse。。正是因因為這這樣，，我們們大量量使用用綁定定變量量，使使得只只是參參數不不同的的同構構sql語句在在oracle為同一一條sql語句(只是是具體體執行行時使使用的的參數數不一一樣)，由由此使使得sql語句的的執行行計劃劃可以以得到到復用用，減減少hardparse，，盡量用用到softparse，，從而減減少parse帶來的的消耗耗。2023/1/4softparse也會有有消耗耗盡管如如此，，softparse也并非非全無無消耗耗，softparse同樣需需要在在sharedpool中取得得相關關內存存空間間的latch(鎖住存存儲sql語句、、執行行計劃劃以及及需要要鎖住住的相相關數數據字字典的的內存存空間間)；；而對對latch的分配配和操操作本本身就就是一一個比比較耗耗cpu的動作作，latch的數量量也是是有限限的，，因此此過量量的并并發執執行，，即使使都是是softparse依然會會造成成很大大的消消耗。。案例：：實際上上如果果能夠夠在pga空間中中的cursorcache找到同同樣的的語句句，則則不需需要再再到sharedpool中查找找，這這個過過程是是消耗耗最小小的。。默認認情況況下，，oracle并不會會去為為session緩存存cursor，，需要要我我們們去去設設置置session_cashed_cursor來指指定定oracle為session緩存存的的cursor數量量(當當然然這這會會消消耗耗pga內存存空空間間)。2023/1/4sql語句的的執行過程接下來，運運行“執行行計劃”，，就是通常常sql性能最重要要的部分；；選擇了怎怎樣的執行行計劃、如如何做表連連接、如何何進行表的的掃描、是是否使用索索引、使用用什么索引引，等等問問題。應該選擇什什么樣的執執行計劃，，一個比較較基本的看看法，首先先應關注那那些直接的的查詢條件件(也就是是表的列直直接和帶入入參數進行行比較的查查詢條件)，這些查查詢條件中中哪些能夠夠首先篩選選掉較多的的記錄從而而有效的降降低結果集集，那么應應當優先執執行這些查查詢條件，，降低整個個sql執行過程中中需要處理理的結果集集。當然實實際上sql的執行計劃劃必須全盤盤考慮整個個查詢過程程怎樣才是是較優的查查詢路徑，，包括每個個環節步驟驟選擇什么么索引、什什么掃描方方式、什么么表連接方方式。下面我們依依次看看這這些問題。。2023/1/4關于索引與與表掃描BTree索引的數據據結構判斷是否適適合使用索索引索引使用不不合理的常常見問題2023/1/4BTree索引的數據據結構索引，正如如其名稱一一樣，就好好像字典中中的索引，，通過它數數據庫能夠夠根據一些些特定的信信息很快的的定位到所所需要的數數據而并不不需要察看看全部的數數據才能得得到想要的的結果。BTree索引的數據據結構是一一個根據關關鍵字排序序的B+樹結構(一一個多層的的N叉樹)，由由一群(關關鍵字、值值)對組成成；關鍵字字就是索引引列的列值值(如果是是復合索引引，則是多多個列值)，值就是是對應記錄錄的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)，，關鍵鍵字字保保存存對對應應記記錄錄索索引引列列的的列列值值。。2023/1/4BTree索引引的的數數據據結結構構根節節點點和和內內層層節節點點的的關關鍵鍵字字表表示示一一個個范范圍圍，，其其指指針針分分別別指指向向了了小小于于該該關關鍵鍵字字或或者者大大于于等等于于該該關關鍵鍵字字的的節節點點群群，，如如下下圖圖：：葉節節點點的的關關鍵鍵字字為為對對應應的的記記錄錄索索引引列列的的列列值值，，除除最最后后一一個個指指針針指指向向下下一一個個葉葉結結點點外外其其余余指指針針則則指指向向了了對對應應的的記記錄錄(rowid)，，如下下圖圖：：2023/1/4BTree索引的數數據結構構如上假設設我們要要查找索索引列值值為75的記錄錄，只需需要在根根節點中中找到57到81這個個范圍的的節點群群，然后后依次根根據范圍圍最終在在葉節點點中找到到索引列列為75的記錄錄的rowid。2023/1/4判斷是否否適合使使用索引引索引之所所以能夠夠起到優優化查詢詢的作用用，就在在于它將將查詢用用到的條條件(列列)作為為關鍵字字(其對對應值指指向對應應的記錄錄)并組組織為一一個排序序的結構構，這樣樣我們能能在這個個排序結結構中快快速的定定位到要要查找的的記錄而而不需要要去遍歷歷全部的的數據(就好像像查字典典一樣，，根據拼拼音或者者筆畫就就能很快快的查到到一個字字，而不不需要把把整個字字典翻一一遍)。。相對通過過全表掃掃描找到到一條記記錄，通通過索引引避免了了很多冗冗余數據據的掃描描(我們們不需要要把整個個字典中中不是我我們要查查找的字字的頁也也翻看一一遍)。。但同時時我們也也看到，，對于單單獨的一一條記錄錄而言通通過索引引掃描在在讀取這這條記錄錄的花費費上增加加了掃描描索引和和通過rowid定位的操操作。因此不是是所有情情況下，，都適合合使用的的索引。。假設一一個字典典記錄了了1000個字字，而我我們需要要查找其其中的900個個字，這這種情況況下如果果還先查查索引在在找到對對應的字字就不如如直接把把整個字字典翻看看一遍來來的要快快。同樣的道道理，并并不是所所有的字字段都適適合建立立BTree索引，如如果一個個字段的的獨立列列值非常常少，比比如100萬的的記錄卻卻只有10個獨獨立列值值，那么么任意查查詢其中中一個列列值都會會查詢出出10萬萬條記錄錄(10%)，，那么這這個索引引就算使使用效率率也很低低，這個個字段不不適合建建立單列列的BTree索引。。2023/1/4判斷是是否適適合使使用索索引而實際際上的的經驗驗數據據，當當通過過索引引掃描描access的記錄錄數<=總總記錄錄數的的6%的時時候，，使用用索引引是有有效率率的，，可見見掃描描索引引的數數據結結構本本身和和通過過索引引多次次的去去accesstable也有著著相當當的消消耗。。(實實際上上計算算索引引掃描描的成成本是是用需需要accesstable的block數來計計算access的次數數，也也就是是說，，假設設索引引的順順序和和表存存儲的的順序序完全全一致致(比比如sequence作的主主鍵索索引)，則則這個個比例例可以以擴大大一些些；但但實際際上這這個假假設成成立的的情況況比較較少，，而且且即便便如此此這個個比例例也不不會很很大)另外需需要注注意的的是，，BTree索引并不記記錄null值，也就是是說是用isnull或者isnotnull這樣的條件件是不可能能用到BTree索引的。2023/1/4判斷是否適適合使用索索引判斷是否應應該使用索索引或者說說是否使用用到合適的的索引，主主要在于下下面幾種情情況：tab.a=:1，，這種情況主主要看:1在整個a的獨立列值值中占了多多少百分比比(也就是是a列取值為:1的記錄錄數占到整整個記錄數數的比例)；這個比比例很低的的話則適合合使用a列的索引，，反之oracle就會傾向于于使用全表表掃描。tab.a>=:1andtab.a<=:2，這種情況主主要看:1-:2之之間這個范范圍的記錄錄數占到總總記錄數的的比例；范范圍太大(比例比較較高)的話話則不適合合使用a列的索引tab.ain(list)，這種情況主主要看inlist中的列值包包含的記錄錄數占到總總記錄數的的百分比，，這個百分分比較大的的話就不適適合使用a的索引。假假設一個列列有10個個獨立列值值，而inlist中就有5個個列值，那那么平均計計算可能就就是50%，顯然這這里并不適適合使用a的索引。tab.a=table.b，通過表table作為驅動表表與表tab做表連接，，連接條件件是table表的b列=tab表的a列，這里主主要看表table用于表連接接的結果集集其每條記記錄的b列值對應在在tab表的a列能夠選取取到的記錄錄數的總和和占tab表記錄數的的百分比(這里用tab表a列的索引指指的是使用用nestedloop表連接方式式的情況下下，使用hashjoin或其他的表表連接方式式，這個比比例的計算算并不適用用，關于表表連接的方方式，我們們在后面討討論)，如如果表table用于作為驅驅動表的結結果集比較較小、且結結果集中b列的列值對對應tab表中a列的列值能能夠選取的的到的記錄錄數比較低低，則適合合使用tab表上a列的索引。。(這里指指使用nestedloop的情況，涉涉及到表連連接索引的的使用要跟跟表連接的的方式一起起考慮，在在表連接的的部分我們們再做討論論)2023/1/4索引引使使用用不不合合理理的的常常見見問問題題缺少少合合適適的的索索引引可可用用(選選擇擇更更加加優優化化的的字字段段或或者者合合理理的的復復合合索索引引首首列列)案例例1：：indexsample1.doc在這這個個案案例例中中：：語句句(1)存存在在一一個個日日期期范范圍圍查查詢詢可可以以使使用用在在日日期期字字段段上上的的索索引引，，但但是是如如果果時時間間范范圍圍跨跨度度過過大大，，這這個個索索引引的的效效率率也也就就不不高高了了；；語句句(2)能能夠夠有有查查詢詢條條件件的的字字段段當當中中只只有有一一個個區區分分度度很很低低的的字字段段建建了了索索引引(千千萬萬條條數數據據只只有有幾幾十十個個獨獨立立列列值值)，，這個個字字段段是是不不適適合合建建立立單單列列索索引引的的，，查查詢詢使使用用這這個個索索引引的的效效率率也也非非常常低低；；這兩兩個個語語句句我我們們通通過過分分析析語語句句，，都都發發現現了了有有區區分分度度比比較較高高且且適適用用的的查查詢詢條條件件字字段段，，只只要要在在這這些些字字段段建建立立索索引引，，就就能能優優化化語語句句的的執執行行效效率率。。這里里我我們們看看到到，，過過大大的的范范圍圍查查詢詢會會影影響響索索引引的的效效率率；；而而過過低低的的區區分分度度的的列列則則并并不不適適合合建建立立單單列列索索引引。。2023/1/4索引引使使用用不不合合理理的的常常見見問問題題缺少少合合適適的的索索引引可可用用(選選擇擇更更加加優優化化的的字字段段或或者者合合理理的的復復合合索索引引首首列列)案例例2：：indexsample2.doc在這這個個案案例例中中：：表cjk上原原來來有有一一個個復復合合索索引引(FZJZH,FBMDM,FSCDM,FGDDM,FZQDM,FHTXH,FMMLB,FCJSJ)，，這個個索索引引的的區區分分度度很很高高，，本本來來是是很很好好用用的的。。但但問問題題就就出出來來這這兩兩個個查查詢詢語語句句中中，，前前面面幾幾列列使使用用的的都都是是模模糊糊查查詢詢，，而而根根據據實實際際情情況況，，往往往往傳傳入入的的都都是是百百分分號號，，導導致致索索引引掃掃描描的的時時候候無無法法根根據據關關鍵鍵字字的的范范圍圍快快速速的的定定位位到到需需要要的的索索引引結結點點，，在在這這里里反反而而使使用用這這個個索索引引效效率率比比全全表表掃掃描描還還要要低低得得多多(實實際際情情況況是是幾幾個個小小時時)。。分析析這這條條兩兩條條語語句句的的查查詢詢條條件件，，發發現現FCJSJ這個個查查詢詢條條件件，，實實際際操操作作中中基基本本上上都都是是查查詢詢一一天天的的數數據據，，這這里里只只需需要要建建立立一一個個以以FCJSJ作為首首列的的復合合索引引(fbdsj,fbmdm,fzjzh,fgddm,fscdm,fzqdm)，就可以以優化化語句句的效效率。。由于BTree索引是是關鍵鍵字排排序，，如果果復合合索引引的首首列不不能根根據查查詢條條件有有效的的篩選選，就就需要要掃描描大量量冗余余的索索引結結點；；在這這個案案例中中由于于前面面幾列列都出出現了了%號號的情情況，，導致致幾乎乎是將將整個個索引引結點點掃描描了一一遍才才得到到結果果，效效率非非常低低。所所以復復合索索引要要特別別注意意首列列的選選擇。。2023/1/4索引使使用不不合理理的常常見問問題不均勻勻分布布的列列值在在bindpeeking和histogram的影響響下，影響響索引的使用用首先解解釋下下相關關名詞詞：bindpeeking：：sql語句中中使用用到綁綁定變變量，，在第第一次次執行行時會會peeking其綁定定變量量的值，，就相相當于于常量量語句句一樣樣，并并根據據這個個具體體值解解析計計算成成本，，解釋釋出執執行計計劃。。這一一特性性是在在oracle9i以后引引入的的。histogram：直方圖圖，對對于不不同列列值更更加準準確的的數據據量的的統計計。對對于列列值分分布不不均勻勻的列列來說說，通通過直直方圖圖，就就能準準確計計算出出不同同列值值的數數據量量，而而不僅僅僅簡簡單的的根據據(總記記錄數數/獨立列列值數數)來來計算算其數數據量量(平平均情情況)。2023/1/4索引引使使用用不不合合理理的的常常見見問問題題不均均勻勻分分布布的的列列值值在在bindpeeking和histogram的影影響響下下，影影響響索引引的使使用用以下下引引用用《《ORACLE數據據庫庫優優化化案案例例簡簡報報(第第一一期期)》的的相相關關內內容容：：由于8i還沒有bindpeeking技術，使使用綁定定變量以以后無法法使用histogram，，所以最好在編程程時對具具有skew值的列不不使用bind變量,這這樣,生生成計計劃時,其可可根據histogram的值來估估算返回回的數據據量,并并生成成合適的的計劃。。9i引入了bindpeeking技術，使使用綁定定變量以以后可以以用到histogram，，但是如果果第一次執行帶帶入的變變量值失失誤，很很可能產產生的執執行計劃劃對以后后的多次次查詢不不適合而而帶來性能問題題。2023/1/4索引使用用不合理理的常見見問題不均勻分分布的列列值在bindpeeking和histogram的影響下下，影響索引的使用我們看看看《ORACLE數據庫優優化案例例簡報(第一期期)》所舉的的這個案案例：語句：selectpolicy_cert_no,apply_personnel_numfromacc_policy_certwherepolicy_no=:1andcert_type='1'selectpolicy_cert_nofromacc_policy_certwhereinsurance_card_no=:1andpolicy_no=:22023/1/4索引使用不合合理的常見問問題不均勻分布的的列值在bindpeeking和histogram的影響下，影響索引的使用這兩條語句都都是對表acc_policy_cert進行查詢，在在policy_no字段上有主鍵鍵索引(復合合索引的首列列)，正常的情況下下，應該走這這個索引而不不是全表掃描描。但在policy_no的列值分布并并不均勻，比比如policy_no列共有100個不同的值值，其中為70的占了99％，為其其他值的數據據行僅占1％％（即選擇性性很高），則則如果不使用用綁定變量，，借助histogram，oracle能夠知道，查查詢policy_no=70的時候應該走走全表掃描效效率更高，查查詢policy_no為其他值的語語句應該走索索引效率更高高。問題就出現了了，假設第一一次執行時帶帶入的是70的這個值，，顯然執行計計劃會走全表表掃描，但是是由于使用了了綁定變量，，以后即使是是帶入其他值值，執行計劃劃依然會走全全表掃描。2023/1/4索引使用不合合理的常見問問題不均勻分布的的列值在bindpeeking和histogram的影響下，影響索引的使用解決辦法有有3個：1)不使用用綁定變量量，但是這這樣就會導導致大量的的hardparse，對sharedpool也會產生大大量的消耗耗。2)修改程程序，針對對不同情況況使用不同同執行計劃劃(不同的的語句)3)不收集集直方圖(這樣就會會按照平均均情況來計計算數據量量)或者使用hint綁定執行計計劃，使其其總能使用用到索引，，這樣就可可以使大部部分情況得得到較好的的效率，但但對于比如如70這樣樣的值就會會效率低下下。關于這個問問題詳細的的解釋和說說明可以參參見《ORACLE數據庫優化化案例簡報報(第一期期)》中相關內內容。2023/1/4關于表連接接三種主要的的表連接方方式何時使用哪哪種表連接接方式2023/1/4三種主要的的表連接方方式nestedloopjoin循環嵌套連連接：行源源1的每一一條記錄，，依次去匹匹配行源2的每條記記錄，將符符合連接條條件的記錄錄放在結果果集中，直直到行源1的所有記記錄都完成成這個操作作。循環嵌嵌套連接是是最基本也也是最古老老的表連接接方式。sortmergejoin排序合并連連接：行源源1和行源源2的數據據分別排序序，然后將將兩個排序序的源表合合并，符合合連接條件件的記錄放放到結果集集中。由于于排序需要要內存空間間，sortmergejoin對內存有比比較大的消消耗，如果果內存空間間(8i為sort_area_size，9i及以上使用用PGA)不足，則會會使用臨時時表空間，，這樣會降降低排序合合并連接的的效率。排排序合并連連接是最古古老的表連連接方式之之一。hashjoin哈希連接：：將行源1計算成一一張基于連連接鍵的hash表，行源2的每條記記錄依次掃掃描這張hash表，找到匹匹配的記錄錄放到結果果集。計算算hash表需要內存存空間，hashjoin同樣對于內內存有比較較大的消耗耗，如果內內存空間(8i為hash_area_size，9i及以上使用用PGA)不足足，，則則會會使使用用臨臨時時表表空空間間，，這這樣樣會會降降低低哈哈希希連連接接的的效效率率。。2023/1/4三種種主主要要的的表表連連接接方方式式nestedloopjoin2023/1/4三種種主主要要的的表表連連接接方方式式sortmergejoin2023/1/4三種主主要的的表連連接方方式hashjoin2023/1/4何時使使用哪哪種表表連接接方式式nestedloopjoin表連接接方式式的適適用情情況nestedloopjoin適合于于：作作為表表連接接的驅驅動表表(也也就是是之前前的行行源1，也也稱為為外部部表)記錄數數比較較少或或者通通過直直接的的查詢詢條件件能篩篩選出出比較較少的的記錄錄數，，被連連接表表(也也就是是之前前的行行源2，也也稱為為內部部表)在連連接條條件上上有區區分度度很高高的索索引；；驅動動表上上的每每條記記錄通通過被被連接接表在在連接接條件件上的的索引引能快快速的的匹配配到少少量的的記錄錄；整整體的的結果果集比比較小小，這這樣就就比較較適合合使用用nestedloopjoin。。nestedloopjoin選擇驅驅動表表時應應優先先選擇擇記錄錄數比比較少少的、、通過過直接接查詢詢條件件能夠夠篩選選出比比較少少記錄錄的表表作為為驅動動表，，這樣樣能夠夠有效效的減減少匹匹配次次數。。例如如這樣樣的查查詢語語句：：select*froma,bwherea.col1=:1anda.col2=b.col2；這里a表有100條記記錄，，通過過col1=:1的條件件能夠夠篩選選出50條條記錄錄，b表只有有10條記記錄；；這里里如果果以a表作驅驅動表表的話話，則則匹配配次數數是50*10；如如果用用b表作為為驅動動表的的話，，則匹匹配次次數是是10*100；顯顯然應應該使使用a表作為為驅動動表。。如果連連接條條件沒沒有很很好的的索引引、或或者作作為表表連接接的兩兩張表表結果果集都都相當當大，，則并并不適適合使使用nestedloopjoin。。2023/1/4何時使使用哪哪種表表連接接方式式sortmergejoin表連接接方式式的適適用情情況sortmergejoin適用于于：當當表連連接的的兩張張表的的結果果集都都比較較大，，或沒沒有很很好的的條件件可以以篩選選，連連接條條件缺缺少很很好的的索引引時，，可以以選擇擇使用用sortmergejoin。由于sortmergejoin需要對對作連連接的的兩張張表都都作排排序，，實際際上如如果語語句中中沒有有排序序需求求，oracle更加傾傾向于于選擇擇hashjoin。但如果果語句句中本本身就就有排排序的的需求求，sortmergejoin則有可可能省省去單單獨的的排序序。sortmergejoin對內存存消耗耗比較較大，，如果果內存存空間間不足足以完完成排排序，，則需需要用用到臨臨時表表空間間，效效率會會有較較大的的降低低。sortmergejoin只能用用于等等價連連接。。2023/1/4何時使使用哪哪種表表連接接方式式hashjoin表連接接方式式的適適用情情況hashjoin適用于于：當當表連連接的的兩張張表的的結果果集都都比較較大，，或沒沒有很很好的的條件件可以以篩選選，連連接條條件缺缺少很很好的的索引引時，，使用用hashjoin能夠取取得比比較好好的效效率。hashjoin雖然也也需要要將一一張表表的所所有記記錄依依次和和hash表中的的記錄錄進行行匹配配，但但掃描描hash表的速速度要要比掃掃描BTree索引快快的多多，所所以在在大結結果集集和缺缺少良良好索索引的的情況況下，，使用用hashjoin能得到到比較較好的的效率率。與sortmergejoin相比，，hashjoin只需要要進行行一次次排序序，因因此大大部分分情況況下，，oracle會更傾傾向于于選擇擇hashjoin。hashjoin需要計計算一一張hashtable，，與sortmergejoin一樣，，需要要消耗耗大量量的內內存空空間，如果內內存空空間不不足則則需要要用到到臨時時表空空間，，效率率會有有較大大的降降低。。hashjoin只能CBO優化器器下使使用，，只能能用于于等價價連接接。例如語語句：：select*froma,bwherea.col1=:1andb.col1=:2anda.col2=b.col2在這個個語句句中，，a表通過過col1=:1和b表通過過col1=:2篩選后后的記記錄數數依然然比較較大，，雖然然col2在a表和b表上都都是很很不錯錯的索索引，，但是是因為為作連連接的的結果果集比比較大大，使使用nestedloop效率不不高，，這時時使用用hashjoin就能得得到較較好的的效率率。2023/1/4何時使使用哪哪種表表連接接方式式案例1：《joinsample1.doc》在這個個案例例中，，原來來的執執行計計劃，，選擇擇了首首先掃掃描兩兩張小小表SELECT_CONDITION_TMP，，然后與與大表表ASSET_COMBINATION進行nestedloopjoin，，兩張小小表結結合起起來的的條件件大概概會形形成一一個幾幾百條條的結結果集集，而而與表表ASSET_COMBINATION進行nestedloopjoin也是一一個具具有一一定區區分度度的索索引，，看起起來似似乎選選擇nestedloopjoin并沒有有錯。。但仔細細分析析：首首先這這里在在nestedloopjoin時使用的的索引是是字段the_date上的索引引，也就就是說連連接條件件curno、cmbno并沒有起起到快速速查找定定位的作作用；其其次由于于兩張小小表SELECT_CONDITION_TMP只是一些些查詢條條件的組組合本身身數據量量非常少少，但依依然會使使得通過過the_date=:1這個條件件掃描ASSET_COMBINATION表會重復復很多次次；再次次，ASSET_COMBINATION這個表上上并沒有有以curno或cmbno為首列的的索引，，而以這這兩個列列作為條條件區分分度并不不是很高高。分析之下下，作為為直接條條件the_date=:1能夠篩過過濾掉大大量的記記錄，且且有不錯錯的索引引，有效的減減小結果果集，因因此這里里應該首首先掃描描ASSET_COMBINATION減少冗余余的掃描描(不必必像原執執行計劃劃一樣重重復掃描描很多次次)；然后與兩兩張小表表SELECT_CONDITION_TMP進行連接接時，因因為這兩兩張表的的數據比比ASSET_COMBINATION通過the_date=:1選出來的的結果集集還要小小很多，，這里適適合使用用hashjoin有比較好好的效率率。2023/1/4何時使用用哪種表表連接方方式案例2：：《joinsample2.msg》在這個案案例，原原語句因因為唯一一能夠首首先過濾濾掉較多多結果集集的條件件TASK_STATUS_ID=‘12’本身仍然然會篩選選出數量量較大的的結果集集，而PA_TASK_TLR_ADMIN本身是個個小表，，因此優優先使用用直接條條件篩選選掉一些些結果集集，然后后再與小小表使用用hashjoin；；看起來這這似乎并并沒有什什么問題題。但仔細分分析：表表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_ID上的索引引nestedloopjoin表PA_CUSTOMER_CAMPAIGN，因為這個個索引的的效率要要好得多多了，反反而能夠夠取得比比較好的的效率。。2023/1/4關于排排序排序是是數據據庫中中一個個比較較常見見的操操作，，使用用orderby是一個個很司司空見見慣的的東西西；但但排序序的發發生并并不只只是在在orderby的時候候，排排序產產生的的消耗耗有時時也會會產生生很大大的影影響，，對于于排序序也有有一些些需要要注意意的地地方。一個關關于排排序的的sql：投資數數據庫庫有一一個很很簡單單的sql，是一個個統計計報表表類的的sql，只是將將一張張數據據表中中某一一段時時間的的數據據做一一個按按照一一些統統計條條件groupby的操作作；通通常會會計算算一年年的數數據，，雖然然數據據量比比較大大，但但因為為是個個統計計操作作，執執行次次數很很低，，在生生產環環境大大概每每次執執行需需要10幾幾秒。。但在測測試環環境的的一次次測試試中，，這條條語句句發現現執行行得很很慢，，幾分分鐘才才能跑跑出結結果。。對此跟跟蹤之之下，，發現現這條條語句句執行行時有有大量量的directpathread/write等待(后面面我們們會討討論)，也也就是是說排排序使使用臨臨時表表空間間產生生了很很大消消耗。。經檢查查，這這張表表有百百萬級級的數數據量量，占占了幾幾百M的空間間。根根據查查詢條條件約約會查查詢出出1/3的的記錄錄數來來作統統計，，算下下來大大約是是100M的數據