Oracle與sql簡(jiǎn)單優(yōu)化與鎖機(jī)制

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

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