oracle存儲：block extent segment (clob字段影響：單獨存放一個段）
10046事件監(jiān)控等待事件 10046 trace文件
等待事件為db file sequential read酝静。
多塊讀 單塊讀
索引列含null
每次I/O都讀取1MB數(shù)據(jù)
索引的集群因子很小
PGA中的work area vs SGA
https://blog.csdn.net/robinson1988/article/details/51148332
如何避免執(zhí)行計劃中產(chǎn)生FILTER以及執(zhí)行計劃中產(chǎn)生了FILTER怎么優(yōu)化
標量子查詢
子查詢無法展開 unnest no_unnest
本地索引 vs global索引

集群因子：
是通過一個索引掃描一張表時需要訪問的表的數(shù)據(jù)塊的數(shù)量
(1) 掃描一個索引
(2) 比較某行的ROWID和前一行的ROWID，如果這兩個ROWID不屬于同一個數(shù)據(jù)塊扭仁，那么集群因子增加1炊琉；
(3) 整個索引掃描完畢后展蒂，就得到了該索引的集群因子。
集群因子反映了索引范圍掃描可能帶來的對整個表訪問過程的IO開銷情況
如果集群因子接近于表存儲的塊數(shù)苔咪，說明這張表是按照索引字段的順序存儲的锰悼。
如果集群因子接近于行的數(shù)量，那說明這張表不是按索引字段順序存儲的团赏。
在計算索引訪問成本時箕般，集群因子十分有用。集群因子乘以選擇性參數(shù)就是訪問索引的開銷舔清。

SGA：是用于存儲數(shù)據(jù)庫信息的內(nèi)存區(qū)丝里，該信息為數(shù)據(jù)庫進程所共享。它包含Oracle服務器的數(shù)據(jù)和控制信息,它是在Oracle服務器所駐留的計算機的實際內(nèi)存中得以分配体谒，如果實際內(nèi)存不夠再往虛擬內(nèi)存中寫丙者。
PGA：包含單個服務器進程或單個后臺進程的數(shù)據(jù)和控制信息，與幾個進程共享的SGA正相反,PGA是只被一個進程使用的區(qū)域营密，PGA在創(chuàng)建進程時分配,在終止進程時回收。

unnest：展開子查詢 目锭，會與其它表作連接
no_unnest：不展開子查詢评汰，本身單獨運行，成為FILTER

謂詞filter和access：
執(zhí)行計劃如果顯示是access痢虹，就表示這個謂詞條件的值將會影響數(shù)據(jù)的訪問路徑(表還是索引)
filter表示謂詞條件的值并不會影響數(shù)據(jù)訪問路徑被去，只起到過濾的作用。

邏輯讀：掃描塊的個數(shù)
物理讀：實際I/O次數(shù)奖唯，可能少于邏輯讀（多塊讀的時候可以一次讀多塊）

各訪問路徑對比

第三章 執(zhí)行計劃

3.1惨缆、獲取和查看執(zhí)行計劃

第一種方式 ：set autot

set autot on：該命令會運行SQL并且顯示運行結(jié)果，執(zhí)行計劃和統(tǒng)計信息。
set autot trace：該命令會運行SQL坯墨，但不顯示運行結(jié)果寂汇，會顯示執(zhí)行計劃和統(tǒng)計信息。
set autot trace exp：運行該命令查詢語句不執(zhí)行捣染，DML語句會執(zhí)行骄瓣，只顯示執(zhí)行計劃。
set autot trace stat：該命令會運行SQL耍攘，只顯示統(tǒng)計信息榕栏。
set autot off：關(guān)閉AUTOTRACE。

統(tǒng)計信息解讀

recursive calls
表示遞歸調(diào)用的次數(shù)蕾各。一個SQL第一次執(zhí)行就會發(fā)生硬解析扒磁，在硬解析的時候，優(yōu)化器會隱含地調(diào)用一些內(nèi)部SQL式曲，因此當一個SQL第一次執(zhí)行妨托，recursive calls會大于0；第二次執(zhí)行的時候不需要遞歸調(diào)用检访，recursive calls會等于0始鱼。
SQL語句中有自定義函數(shù)，recursive calls就會顯示為自定義函數(shù)被調(diào)用了多少次

db block gets
表示有多少個塊發(fā)生變化脆贵，一般情況下医清，只有DML語句才會導致塊發(fā)生變化，所以查詢語句中db block gets一般為0卖氨。如果有延遲塊清除会烙，或者SQL語句中調(diào)用了返回CLOB的函數(shù)，db block gets也有可能會大于0筒捺，不要覺得奇怪柏腻。

consistent gets
表示邏輯讀，單位是塊系吭。在進行SQL優(yōu)化的時候五嫂，我們應該想方設法減少邏輯讀個數(shù)。通常情況下邏輯讀越小肯尺，性能也就越好沃缘。需要注意的是，邏輯讀并不是衡量SQL執(zhí)行快慢的唯一標準则吟，需要結(jié)合I/O等其他綜合因素共同判斷槐臀。

physical reads
表示從磁盤讀取了多少個數(shù)據(jù)塊，如果表已經(jīng)被緩存在buffer cache中氓仲，沒有物理讀水慨，physical reads等于0得糜。

redo size
表示產(chǎn)生了多少字節(jié)的重做日志，一般情況下只有DML語句才會產(chǎn)生redo晰洒，查詢語句一般情況下不會產(chǎn)生redo朝抖，所以這里redo size為0。如果有延遲塊清除欢顷，查詢語句也會產(chǎn)生redo槽棍。

bytes sent via SQLNet to client
表示從數(shù)據(jù)庫服務器發(fā)送了多少字節(jié)到客戶端。
bytes received via SQLNet from client
表示從客戶端發(fā)送了多少字節(jié)到服務端抬驴。

SQL*Net roundtrips to/from client
表示客戶端與數(shù)據(jù)庫服務端交互次數(shù)对雪，我們可以通過設置arraysize減少交互次數(shù)抹剩。

sorts (memory)和sorts (disk)
分別表示內(nèi)存排序和磁盤排序的次數(shù)蛮穿。

rows processed
表示SQL一共返回多少行數(shù)據(jù)钓账。我們在做SQL優(yōu)化的時候最關(guān)心這部分數(shù)據(jù)，因為可以根據(jù)SQL返回的行數(shù)判斷整個SQL應該是走HASH連接還是走嵌套循環(huán)题暖。如果rows processed很大按傅，一般走HASH連接；如果rows processed很小胧卤，一般走嵌套循環(huán)唯绍。

第二種方式：explain plan for sql_str

還可設置advanced-projection, 查看高級執(zhí)行計劃，高級執(zhí)行計劃比普通執(zhí)行計劃多了Query Block Name/Object Alias和Outline Data枝誊。

第三種方式：帶有A-TIME的執(zhí)行計劃

alter session set statistics_level=all
Starts表示這個操作執(zhí)行的次數(shù)况芒。
E-Rows表示優(yōu)化器估算的行數(shù)，就是普通執(zhí)行計劃中的Rows叶撒。
A-Rows表示真實的行數(shù)绝骚。
A-Time表示累加的總時間。
與普通執(zhí)行計劃不同的是祠够，普通執(zhí)行計劃中的Time是假的压汪，而A-Time是真實的。
Buffers表示累加的邏輯讀古瓤。
Reads表示累加的物理讀止剖。

三種方式不同點：

• 使用AUTOTRACE或者EXPLAIN PLAN FOR獲取的執(zhí)行計劃來自于PLAN_TABLE；帶有A-Time的執(zhí)行計劃來自于V$SQL_PLAN落君。
• PLAN_TABLE的執(zhí)行計劃并不是SQL真實的執(zhí)行計劃穿香，它只是優(yōu)化器估算出來的；V$SQL_PLAN叽奥，是真實的執(zhí)行計劃

查看正在執(zhí)行的SQL的執(zhí)行計劃

需要獲取SQL的SQL_ID以及SQL的CHILD_NUMEBR

第四章 訪問路徑 access path

4.1常見訪問路徑

TABLE ACCESS FULL

表示全表掃描，一般情況下是多塊讀痛侍，HINT: FULL(表名/別名)

TABLE ACCESS BY USER ROWID

表示直接用ROWID獲取數(shù)據(jù)朝氓，單塊讀魔市。該訪問路徑在Oracle所有的訪問路徑中性能是最好的。

TABLE ACCESS BY ROWID RANGE

表示ROWID范圍掃描赵哲，多塊讀待德。因為同一個塊里面的ROWID是連續(xù)的，同一個EXTENT里面的ROWID也是連續(xù)的枫夺，所以可以多塊讀将宪。
where條件中直接使用rowid進行范圍掃描就會使用該執(zhí)行計劃。

TABLE ACCESS BY INDEX ROWIDT

表示回表橡庞，單塊讀较坛。

INDEX UNIQUE SCAN

表示索引唯一掃描，單塊讀扒最。對唯一索引或者對主鍵列進行等值查找丑勤，就會走INDEX UNIQUE SCAN。因為對唯一索引或者對主鍵列進行等值查找吧趣，CBO能確保最多只返回1行數(shù)據(jù)法竞，所以這時可以走索引唯一掃描。
其性能僅次于TABLE ACCESS BY USER ROWID

INDEX RANGE SCANINDEX RANGE SCAN

表示索引范圍掃描强挫，單塊讀岔霸，返回的數(shù)據(jù)是有序的（默認升序）。HINT: INDEX（表名/別名 索引名）俯渤。對唯一索引或者主鍵進行范圍查找呆细，對非唯一索引進行等值查找，范圍查找稠诲，就會發(fā)生INDEX RANGESCAN侦鹏。
在檢查執(zhí)行計劃的時候我們要注意索引范圍掃描返回多少行數(shù)據(jù)，如果返回少量數(shù)據(jù)臀叙，不會出現(xiàn)性能問題略水；如果返回大量數(shù)據(jù)，在沒有回表的情況下也還好劝萤；如果返回大量數(shù)據(jù)同時還有回表渊涝，這時我們應該考慮通過創(chuàng)建組合索引消除回表或者使用全表掃描來代替它。

INDEX SKIP SCAN

表示索引跳躍掃描床嫌，單塊讀跨释。返回的數(shù)據(jù)是有序的（默認升序）。HINT: INDEX_SS（表名/別名 索引名）厌处。當組合索引的引導列（第一個列）沒有在where條件中鳖谈，并且組合索引的引導列/前幾個列的基數(shù)很低，where過濾條件對組合索引中非引導列進行過濾的時候就會發(fā)生索引跳躍掃描

INDEX FULL SCAN

表示索引全掃描阔涉，單塊讀缆娃，返回的數(shù)據(jù)是有序的（默認升序）捷绒。HINT: INDEX（表名/別名 索引名）。索引全掃描會掃描索引中所有的葉子塊（從左往右掃描）贯要，如果索引很大暖侨，會產(chǎn)生嚴重性能問題（因為是單塊讀）

INDEX FAST FULL SCANI

表示索引快速全掃描，多塊讀崇渗。HINT：INDEX_FFS（表名/別名 索引名）字逗。當需要從表中查詢出大量數(shù)據(jù)但是只需要獲取表中部分列的數(shù)據(jù)的，我們可以利用索引快速全掃描代替全表掃描來提升性能宅广。索引快速全掃描的掃描方式與全表掃描的掃描方式是一樣葫掉，都是按區(qū)掃描，所以它可以多塊讀乘碑，而且可以并行掃描挖息。為了解決oracle 是行存儲需要掃描所有列而產(chǎn)生的。
INDEX FAST FULL SCAN可以多塊讀兽肤，而INDEX FULL SCAN是單塊讀

INDEX FULL SCAN（MIN/MAX）

表示索引最小/最大值掃描套腹、單塊讀，該訪問路徑發(fā)生在 SELECT MAX（COLUMN）FROM TABLE 或者SELECT MIN（COLUMN）FROM TABLE等SQL語句中资铡。INDEX FULL SCAN（MIN/MAX）只會訪問“索引高度”個索引塊电禀，其性能與INDEX UNIQUE SCAN一樣，僅次于TABLE ACCESS BY USER ROWID笤休。

4.2單塊讀與多塊讀

從磁盤1次讀取1個塊到buffer cache就叫單塊讀尖飞，
從磁盤1次讀取多個塊到buffer cache就叫多塊讀。

如果數(shù)據(jù)塊都已經(jīng)緩存在buffer cache中店雅，那就不需要物理I/O了政基，沒有物理I/O也就不存在單塊讀與多塊讀。

絕大多數(shù)的平臺闹啦，一次I/O最多只能讀取或者寫入1MB數(shù)據(jù)沮明，Oracle的塊大小默認是8k，那么一次I/O最多只能寫入128個塊到磁盤窍奋，最多只能讀取128個塊到buffer cache荐健。
在判斷哪個訪問路徑性能好的時候，通常是估算每個訪問路徑的I/O次數(shù)琳袄，誰的I/O次數(shù)少江场，誰的性能就好。

4.3為什么有時候索引掃描比全表掃描更慢

索引也需要耗費大量的物理I/O

第五章 表連接方式

驅(qū)動表 與 被驅(qū)動表

驅(qū)動表窖逗，即需要從驅(qū)動表中拿出來每條記錄址否，去與被驅(qū)動表的所有記錄進行匹配探測。
驅(qū)動表碎紊，作為外層循環(huán)佑附，若能只進行一次IO把所有數(shù)據(jù)拿出來最好用含，這就比較適合順序讀取 。
被驅(qū)動表帮匾，即里層循環(huán)，由于需要不斷的拿外層循環(huán)傳進來的每條記錄去匹配痴鳄，所以如果是適合隨機讀取的瘟斜，那么效率就會比較高。如果表上有索引痪寻，實際上就意味著這個表是適合隨機讀取的螺句。
理解驅(qū)動表和被驅(qū)動表的差異，最本質(zhì)的問題橡类，需要理解順序讀取和隨機讀取的差異蛇尚，內(nèi)存是適合隨機讀取的，但是硬盤就不是顾画，對于硬盤來說順序讀取的效率比較好取劫。

5.1　嵌套循環(huán)（NESTED LOOPS）

嵌套循環(huán)被驅(qū)動表必須走索引，走INDEX UNIQUE SCAN或者INDEX RANGE SCAN研侣。
兩表關(guān)聯(lián)返回少量數(shù)據(jù)才能走嵌套循環(huán)
外連接走嵌套循環(huán)的時候驅(qū)動表只能是主表谱邪。Hint設置從表為驅(qū)動表不會生效。

提問：兩表關(guān)聯(lián)走不走NL是看兩個表關(guān)聯(lián)之后返回的數(shù)據(jù)量多少?還是看驅(qū)動表返回的數(shù)據(jù)量多少庶诡？
回答：看兩表關(guān)聯(lián)之后返回的數(shù)據(jù)量惦银。
如果兩個表是1∶N關(guān)系，驅(qū)動表為1末誓，被驅(qū)動表為N并且N很大扯俱，這時兩表關(guān)聯(lián)之后返回的數(shù)據(jù)量會很多。
返回的數(shù)據(jù)量多喇澡，應該走HASH連接迅栅。

提問：大表是否可以當嵌套循環(huán)（NL）驅(qū)動表？
回答：可以撩幽，如果大表過濾之后返回的數(shù)據(jù)量很少就可以當NL驅(qū)動表库继。

5.2　HASH連接（HASH JOIN）

兩表關(guān)聯(lián)返回少量數(shù)據(jù)應該走嵌套循環(huán)
兩表關(guān)聯(lián)返回大量數(shù)據(jù)應該走HASH連接。

HASH連接的算法：兩表等值關(guān)聯(lián)窜醉，返回大量數(shù)據(jù)宪萄，將較小的表選為驅(qū)動表，將驅(qū)動表的“select列和join列”讀入PGA中的work area榨惰，然后對驅(qū)動表的連接列進行hash運算生成hash table拜英，當驅(qū)動表的所有數(shù)據(jù)完全讀入PGA中的work area之后，再讀取被驅(qū)動表（被驅(qū)動表不需要讀入PGA中的workarea）琅催，對被驅(qū)動表的連接列也進行hash運算居凶，然后到PGA中的work area去探測hash table虫给，找到數(shù)據(jù)就關(guān)聯(lián)上，沒找到數(shù)據(jù)就沒關(guān)聯(lián)上侠碧。

哈希連接只支持等值連接抹估。

嵌套循環(huán)被驅(qū)動表需要掃描多次，
HASH連接的被驅(qū)動表只需要掃描一次弄兜。
嵌套循環(huán)不需要消耗PGA药蜻。
HASH連接的驅(qū)動表與被驅(qū)動表的連接列都不需要創(chuàng)建索引。
HASH連接沒有傳值的過程替饿。

OLTP環(huán)境一般是高并發(fā)小事物居多语泽，此類SQL返回結(jié)果很少，SQL執(zhí)行計劃多以嵌套循環(huán)為主视卢，因此OLTP環(huán)境SGA設置較大踱卵，PGA設置較小（因為嵌套循環(huán)不消耗PGA）据过。
而OLAP環(huán)境多數(shù)SQL都是大規(guī)模的ETL惋砂，此類SQL返回結(jié)果集很多，SQL執(zhí)行計劃通常以HASH連接為主绳锅，往往要消耗大量PGA班利，所以OLAP系統(tǒng)PGA設置較大。

思考：怎么優(yōu)化HASH連接榨呆？
回答：因為HASH連接需要將驅(qū)動表的select列和join列放入PGA中罗标，所以，我們應該盡量避免書寫select * from....語句积蜻，將需要的列放在select list中闯割，這樣可以減少驅(qū)動表對PGA的占用，避免驅(qū)動表被溢出到臨時表空間竿拆，從而提升查詢性能宙拉。如果無法避免驅(qū)動表被溢出到臨時表空間，我們可以將臨時表空間創(chuàng)建在SSD上或者RAID 0上丙笋，加快臨時數(shù)據(jù)的交換速度谢澈。

5.3　排序合并連接（SORT MERGE JOIN）

HASH連接主要用于處理兩表等值關(guān)聯(lián)返回大量數(shù)據(jù)。排序合并連接主要用于處理兩表非等值關(guān)聯(lián)御板，比如>锥忿，>=，<怠肋，<=敬鬓，<>，但是不能用于instr、substr钉答、like础芍、regexp_like關(guān)聯(lián)，instr数尿、substr仑性、like、regexp_like關(guān)聯(lián)只能走嵌套循環(huán)右蹦。
排序合并連接的算法：兩表關(guān)聯(lián)虏缸，先對兩個表根據(jù)連接列進行排序，將較小的表作為驅(qū)動表（Oracle官方認為排序合并連接沒有驅(qū)動表嫩实，筆者認為是有的），然后從驅(qū)動表中取出連接列的值窥岩，到已經(jīng)排好序的被驅(qū)動表中匹配數(shù)據(jù)甲献，如果匹配上數(shù)據(jù)，就關(guān)聯(lián)成功颂翼。驅(qū)動表返回多少行晃洒，被驅(qū)動表就要被匹配多少次，這個匹配的過程類似嵌套循環(huán)朦乏，但是嵌套循環(huán)是從被驅(qū)動表的索引中匹配數(shù)據(jù)球及，而排序合并連接是在內(nèi)存中（PGA中的work area）匹配數(shù)據(jù)。

如果兩表是等值關(guān)聯(lián)呻疹，一般不建議走排序合并連接吃引。因為排序合并連接需要將兩個表放入PGA中，而HASH連接只需要將驅(qū)動表放入PGA中刽锤，排序合并連接與HASH連接相比镊尺，需要耗費更多的PGA。即使排序合并連接中有一個表走的是INDEX FULL SCAN并思，另外一個表也需要放入PGA中庐氮，而這個表往往是大表，如果走HASH連接宋彼，大表會作為被驅(qū)動表弄砍，是不會被放入PGA中的。因此输涕，兩表等值關(guān)聯(lián)音婶，要么走NL（返回數(shù)據(jù)量少），要么走HASH（返回數(shù)據(jù)量多）莱坎，一般情況下不要走SMJ桃熄。

5.4　笛卡兒連接（CARTESIAN JOIN）

兩個表關(guān)聯(lián)沒有連接條件的時候會產(chǎn)生笛卡兒積，這種表連接方式就叫笛卡兒連接。

5.5　標量子查詢（SCALAR SUBQUERY）

當一個子查詢介于select與from之間瞳收，這種子查詢就叫標量子查詢

5.9　IN與EXISTS誰快誰慢

我相信很多人都受到過in與exists誰快誰慢的困擾碉京。如果執(zhí)行計劃中沒有產(chǎn)生FILTER，那么我們可以參考以下思路：in與exists是半連接螟深，半連接也屬于表連接谐宙，那么既然是表連接，我們需要關(guān)心兩表的大小以及兩表之間究竟走什么連接方式界弧，還要控制兩表的連接方式凡蜻，才能隨心所欲優(yōu)化SQL，而不是去記什么時候in跑得快垢箕，什么時候exists跑得快划栓。如果執(zhí)行計劃中產(chǎn)生了FILTER，大家還需閱讀7.1節(jié)才能徹底知道答案条获。

5.10　SQL語句的本質(zhì)

標量子查詢可以改寫為外連接（需要注意表與表之間關(guān)系忠荞，去重），半連接可以改寫為內(nèi)連接（需要注意表與表之間關(guān)系帅掘，去重）委煤，反連接可以改寫為外連接（不需要注意表與表之間關(guān)系，也不需要去重）修档。SQL語句中幾乎所有的子查詢都能改寫為表連接的方式碧绞，所以我們提出這個觀點：SQL語句其本質(zhì)就是表連接（內(nèi)連接與外連接），以及表與表之間是幾比幾關(guān)系再加上GROPU BY吱窝。