【正文】 v$lock視圖記錄了每個(gè) session取得鎖或者等待鎖的情況：ID1和 ID2標(biāo)識了鎖定的對象，在 TM和 TX鎖中的含義如下：2023/2/27 星期六 169。更詳細(xì)的有關(guān) dml鎖機(jī)制的說明可以參看轉(zhuǎn)引網(wǎng)文《 oracle多粒度封鎖機(jī)制研究（論壇） .doc》或其他相關(guān)資料。? 關(guān)于死鎖需要注意的是，就一般而言 oracle中并不會長期存在真正意義上的死鎖。2023/2/27 星期六 169。案例：lock 2023/2/27 星期六 169。中國平安保險(xiǎn)（集團(tuán)）股份有限公司 秘密 版權(quán)所有，不得侵犯sql語句的執(zhí)行過程? sql語句的執(zhí)行步驟? hard parse與 soft parse? soft parse也會有消耗2023/2/27 星期六 169。語義分析，檢查語句中涉及的所有數(shù)據(jù)庫對象是否存在，且用戶有相應(yīng)的權(quán)限。表達(dá)式轉(zhuǎn)換， 將復(fù)雜的 SQL 表達(dá)式轉(zhuǎn)換為較簡單的等效連接表達(dá)式。選擇連接順序， 對多表連接 ORACLE 選擇哪一對表先連接，選擇這兩表中哪個(gè)表做為源數(shù)據(jù)表。運(yùn)行 執(zhí)行計(jì)劃 。中國平安保險(xiǎn)（集團(tuán)）股份有限公司 秘密 版權(quán)所有，不得侵犯hard parse與 soft parse18的步驟也就是我們通常所說的 parse， 通過 parse得到一條語句的執(zhí)行計(jì)劃，可以看出 parse的過程是一個(gè)比較昂貴的消費(fèi)，顯然如果每次執(zhí)行 sql都需要進(jìn)行一次完整的 parse， 那么將是非常大的消耗。因此，大部分?jǐn)?shù)據(jù)庫都提供了 sql的共享的機(jī)制。 2023/2/27 星期六 169。 案例：實(shí)際 上如果能 夠 在 pga空 間 中的 cursor cache找到同 樣 的 語 句， 則 不需要再到 shared pool中 查 找， 這 個(gè) 過 程是消耗最小的。 2023/2/27 星期六 169。應(yīng)該選擇什么樣的執(zhí)行計(jì)劃，一個(gè)比較基本的看法，首先應(yīng)關(guān)注那些直接的查詢條件(也就是表的列直接和帶入?yún)?shù)進(jìn)行比較的查詢條件 )，這些查詢條件中哪些能夠首先篩選掉較多的記錄從而有效的降低結(jié)果集，那么應(yīng)當(dāng)優(yōu)先執(zhí)行這些查詢條件，降低整個(gè) sql執(zhí)行過程中需要處理的結(jié)果集。下面我們依次看看這些問題。中國平安保險(xiǎn)（集團(tuán)）股份有限公司 秘密 版權(quán)所有，不得侵犯關(guān)于索引與表掃描? B Tree索引的數(shù)據(jù)結(jié)構(gòu)? 判斷是否適合使用索引? 索引使用不合理的常見問題2023/2/27 星期六 169。2023/2/27 星期六 169。中國平安保險(xiǎn)（集團(tuán)）股份有限公司 秘密 版權(quán)所有，不得侵犯B Tree索引的數(shù)據(jù)結(jié)構(gòu)如上假設(shè)我們要查找索引列值為 75的記錄，只需要在根節(jié)點(diǎn)中找到 57到 81這個(gè)范圍的節(jié)點(diǎn)群，然后依次根據(jù)范圍最終在葉節(jié)點(diǎn)中找到索引列為 75的記錄的 rowid。中國平安保險(xiǎn)（集團(tuán)）股份有限公司 秘密 版權(quán)所有，不得侵犯判斷是否適合使用索引索引之所以能夠起到優(yōu)化查詢的作用，就在于它將查詢用到的條件 (列 )作為關(guān)鍵字 (其對應(yīng)值指向?qū)?yīng)的記錄 )并組織為一個(gè)排序的結(jié)構(gòu)，這樣我們能在這個(gè)排序結(jié)構(gòu)中快速的定位到要查找的記錄而不需要去遍歷全部的數(shù)據(jù) (就好像查字典一樣，根據(jù)拼音或者筆畫就能很快的查到一個(gè)字，而不需要把整個(gè)字典翻一遍 )。但同時(shí)我們也看到，對于單獨(dú)的一條記錄而言通過索引掃描在讀取這條記錄的花費(fèi)上增加了掃描索引和通過 rowid定位的操作。假設(shè)一個(gè)字典記錄了 1000個(gè)字，而我們需要查找其中的 900個(gè)字，這種情況下如果還先查索引在找到對應(yīng)的字就不如直接把整個(gè)字典翻看一遍來的要快。2023/2/27 星期六 169。 (實(shí)際上計(jì)算索引掃描的成本是用需要 access table的 block數(shù)來計(jì)算 access的次數(shù)，也就是說，假設(shè)索引的順序和表存儲的順序完全一致 (比如sequence作的主鍵索引 )，則這個(gè)比例可以擴(kuò)大一些；但實(shí)際上這個(gè)假設(shè)成立的情況比較少，而且即便如此這個(gè)比例也不會很大 )2023/2/27 星期六 169。? =:1 and =:2， 這種情況主要看 :1 :2 之間這個(gè)范圍的記錄數(shù)占到總記錄數(shù)的比例；范圍太大 (比例比較高 )的話則不適合使用 a列的索引? in (list)， 這種情況主要看 in list中的列值包含的記錄數(shù)占到總記錄數(shù)的百分比，這個(gè)百分比較大的話就不適合使用 a的索引。? =， 通過表 table作為驅(qū)動表與表 tab做表連接，連接條件是 table表的 b列 =tab表的 a列，這里主要看表 table用于表連接的結(jié)果集其每條記錄的 b列值對應(yīng)在 tab表的 a列能夠選取到的記錄數(shù)的總和占 tab表記錄數(shù)的百分比 (這里用 tab表 a列的索引指的是使用 nested loop表連接方式的情況下，使用 hash join或其他的表連接方式，這個(gè)比例的計(jì)算并不適用，關(guān)于表連接的方式，我們在后面討論 )，如果表 table用于作為驅(qū)動表的結(jié)果集比較小、且結(jié)果集中 b列的列值對應(yīng) tab表中 a列的列值能夠選取的到的記錄數(shù)比較低，則適合使用 tab表上 a列的索引。中國平安保險(xiǎn)（集團(tuán)）股份有限公司 秘密 版權(quán)所有，不得侵犯索引使用不合理的常見問題? 缺少合適的索引可用 (選擇更加優(yōu)化的字段或者合理的復(fù)合索引首列 ) 案例 1：index 在這個(gè)案例中：語句 (1)存在一個(gè)日期范圍查詢可以使用在日期字段上的索引，但是如果時(shí)間范圍跨度過大，這個(gè)索引的效率也就不高了；語句 (2)能夠有查詢條件的字段當(dāng)中只有一個(gè)區(qū)分度很低的字段建了索引 (千萬條數(shù)據(jù)只有幾十個(gè)獨(dú)立列值 )， 這個(gè)字段是不適合建立單列索引的，查詢使用這個(gè)索引的效率也非常低；這兩個(gè)語句我們通過分析語句，都發(fā)現(xiàn)了有區(qū)分度比較高且適用的查詢條件字段，只要在這些字段建立索引，就能優(yōu)化語句的執(zhí)行效率。2023/2/27 星期六 169。但問題就出來這兩個(gè)查詢語句中，前面幾列使用的都是模糊查詢，而根據(jù)實(shí)際情況，往往傳入的都是百分號，導(dǎo)致索引掃描的時(shí)候無法根據(jù)關(guān)鍵字的范圍快速的定位到需要的索引結(jié)點(diǎn)，在這里反而使用這個(gè)索引效率比全表掃描還要低得多 (實(shí)際情況是幾個(gè)小時(shí) )。由于 B Tree索引是關(guān)鍵字排序，如果復(fù)合索引的首列不能根據(jù)查詢條件有效的篩選，就需要掃描大量冗余的索引結(jié)點(diǎn)；在這個(gè)案例中由于前面幾列都出現(xiàn)了 %號的情況，導(dǎo)致幾乎是將整個(gè)索引結(jié)點(diǎn)掃描了一遍才得到結(jié)果，效率非常低。2023/2/27 星期六 169。這一特性是在 oracle9i以后引入的。對于列值分布不均勻的列來說，通過直方圖，就能準(zhǔn)確計(jì)算出不同列值的數(shù)據(jù)量，而不僅僅簡單的根據(jù) (總記錄數(shù) /獨(dú)立列值數(shù) )來計(jì)算其數(shù)據(jù)量 (平均情況 )。中國平安保險(xiǎn)（集團(tuán)）股份有限公司 秘密 版權(quán)所有，不得侵犯索引使用不合理的常見問題? 不均勻分布的列 值 在 bind peeking和 histogram的影響下 ，影響 索引 的使用以下引用《 ORACLE數(shù)據(jù)庫優(yōu)化案例簡報(bào) (第一期 )》的相關(guān)內(nèi)容：由于 8i 還沒有 bind peeking 技術(shù)，使用綁定變量以后無法使用 histogram， 所以最好在編程時(shí)對具有 skew 值的列不使用 bind 變量 ,這樣 , 生成計(jì)劃時(shí) , 其可根據(jù)histogram 的值來估算返回的數(shù)據(jù)量 , 并生成合適的計(jì)劃。2023/2/27 星期六 169。139。中國平安保險(xiǎn)（集團(tuán)）股份有限公司 秘密 版權(quán)所有，不得侵犯索引使用不合理的常見問題? 不均勻分布的列 值 在 bind peeking和 histogram的影響下 ，影響 索引 的使用這兩條語句都是對表 acc_policy_cert進(jìn)行查詢，在 policy_no字段上有主鍵索引 (復(fù)合索引的首列 )， 正常的情況下，應(yīng)該走這個(gè)索引而不是全表掃描。問題就出現(xiàn)了，假設(shè)第一次執(zhí)行時(shí)帶入的是 70的這個(gè)值，顯然執(zhí)行計(jì)劃會走全表掃描，但是由于使用了綁定變量，以后即使是帶入其他值，執(zhí)行計(jì)劃依然會走全表掃描。中國平安保險(xiǎn)（集團(tuán)）股份有限公司 秘密 版權(quán)所有，不得侵犯索引使用不合理的常見問題? 不均勻分布的列 值 在 bind peeking和 histogram的影響下 ，影響 索引 的使用解決辦法有 3個(gè)：)不使用綁定變量，但是這樣就會導(dǎo)致大量的 hard parse， 對 shared pool也會產(chǎn)生大量的消耗。《 ORACLE數(shù)據(jù)庫優(yōu)化案例簡報(bào) (第一期 )》中相關(guān)內(nèi)容。中國平安保險(xiǎn)（集團(tuán)）股份有限公司 秘密 版權(quán)所有，不得侵犯關(guān)于表連接? 三種主要的表連接方式? 何時(shí)使用哪種表連接方式2023/2/27 星期六 169。循環(huán)嵌套連接是最基本也是最古老的表連接方式。由于排序需要內(nèi)存空間， sort merge join對內(nèi)存有比較大的消耗，如果內(nèi)存空間 (8i為 sort_area_size， 9i及以上使用 PGA)不足，則會使用臨時(shí)表空間，這樣會降低排序合并連接的效率。? hash join哈希連接：將行源 1計(jì)算成一張基于連接鍵的 hash表，行源 2的每條記錄依次掃描這張hash表，找到匹配的記錄放到結(jié)果集。2023/2/27 星期六 169。中國平安保險(xiǎn)（集團(tuán)）股份有限公司 秘密 版權(quán)所有，不得侵犯三種主要的表連接方式? sort merge join2023/2/27 星期六 169。中國平安保險(xiǎn)（集團(tuán)）股份有限公司 秘密 版權(quán)所有，不得侵犯何時(shí)使用哪種表連接方式? nested loop join表連接方式的適用情況nested loop join適合于：作為表連接的驅(qū)動表 (也就是之前的行源 1，也稱為外部表)記錄數(shù)比較少或者通過直接的查詢條件能篩選出比較少的記錄數(shù)，被連接表 (也就是之前的行源 2，也稱為內(nèi)部表 )在連接條件上有區(qū)分度很高的索引；驅(qū)動表上的每條記錄通過被連接表在連接條件上的索引能快速的匹配到少量的記錄；整體的結(jié)果集比較小，這樣就比較適合使用 nested loop join。例如這樣的查詢語句： select * from a,b where =:1 and =； 這里 a表有 100條記錄，通過 col1=:1的條件能夠篩選出 50條記錄， b表只有 10條記錄；這里如果以 a表作驅(qū)動表的話，則匹配次數(shù)是 50*10；如果用 b表作為驅(qū)動表的話，則匹配次數(shù)是10*100；顯然應(yīng)該使用 a表作為驅(qū)動表。2023/2/27 星期六 169。 由于 sort merge join需要對作連接的兩張表都作排序，實(shí)際上如果語句中沒有排序需求，oracle更加傾向于選擇 hash join。sort merge join對內(nèi)存消耗比較大，如果內(nèi)存空間不足以完成排序，則需要用到臨時(shí)表空間，效率會有較大的降低。2023/2/27 星期六 169。 hash join雖然也需要將一張表的所有記錄依次和 hash表中的記錄進(jìn)行匹配，但掃描 hash表的速度要比掃描 B Tree索引快的多，所以在大結(jié)果集和缺少良好索引的情況下，使用 hash join能得到比較好的效率 。hash join需要計(jì)算一張 hash table， 與 sort merge join一樣，需要消耗大量的內(nèi)存空間 ， 如果內(nèi)存空間不足則需要用到臨時(shí)表空間，效率會有較大的降低。例如語句： select * from a,b where =:1 and =:2 and =在這個(gè)語句中， a表通過 col1=:1和 b表通過 col1=:2篩選后的記錄數(shù)依然比較大，雖然col2在 a表和 b表上都是很不錯(cuò)的索引，但是因?yàn)樽鬟B接的結(jié)果集比較大，使用 nested loop效率不高，這時(shí)使用 hash join就能得到較好的效率。中國平安保險(xiǎn)（集團(tuán)）股份有限公司 秘密 版權(quán)所有，不得侵犯何時(shí)使用哪種表連接方式案例 1：《 join 》在這個(gè)案例中，原來的執(zhí)行計(jì)劃，選擇了首先掃描兩張小表 SELECT_CONDITION_TMP，然后與大表 ASSET_COMBINATION進(jìn)行 nested loop join， 兩張小表結(jié)合起來的條件大概會形成一個(gè)幾百條的結(jié)果集，而與表 ASSET_COMBINATION進(jìn)行 nested loop join也是一個(gè)具有一定區(qū)分度的索引，看起來似乎選擇 nested loop join并沒有錯(cuò)。分析之下，作為直接條件 the_date=:1能夠篩過濾掉大量的記錄，且有不錯(cuò)的索引 ，有效的減小結(jié)果集，因此這里應(yīng)該首先掃描 ASSET_COMBINATION減少冗余的掃描 (不必像原執(zhí)行計(jì)劃一樣重復(fù)掃描很多次 )； 然后與兩張小表 SELECT_CONDITION_TMP進(jìn)行連接時(shí)，因?yàn)檫@兩張表的數(shù)據(jù)比 ASSET_COMBINATION通過 the_date=:1選出來的結(jié)果集還要小很多，這里適合使用 hash