Oracle 性能收集資料

1、弄清楚你的的業(yè)務(wù)類型型OLLTP oor OLLAP在Oraccle數(shù)據(jù)據(jù)庫(kù)系統(tǒng)中中，很多人人沒有弄清清楚自己的的業(yè)務(wù)類型型到底是什什么，就在在開始盲目目的尋求優(yōu)優(yōu)化方法，而而往往是把把OLAPP的方法使使用在OLLTP上，或或者是OLLTP的方方法使用在在OLAPP上。這樣樣的使用，有有的時(shí)候，對(duì)對(duì)性能沒有有任何的提提高，甚至至是大大的的影響了性性能，得到到適得其反反的效果。所以，在在優(yōu)化系統(tǒng)統(tǒng)之前，弄弄清楚自己己的業(yè)務(wù)類類型。OLTP (聯(lián)聯(lián)機(jī)事務(wù)處處理系統(tǒng)）也稱為面向向交易的處處理系統(tǒng)，其其基本特征征是顧客的的原始數(shù)據(jù)據(jù)可以立即即傳送到計(jì)計(jì)算中心進(jìn)進(jìn)行處理，并并在很短的的時(shí)間內(nèi)給給出處理

2、結(jié)結(jié)果。這樣樣做的最大大優(yōu)點(diǎn)是可可以即時(shí)地地處理輸入入的數(shù)據(jù)，及及時(shí)地回答答。也稱為為實(shí)時(shí)系統(tǒng)統(tǒng)(Reaal tiime SSysteem)。衡衡量聯(lián)機(jī)事事務(wù)處理系系統(tǒng)的一個(gè)個(gè)重要性能能指標(biāo)是系系統(tǒng)性能，具具體體現(xiàn)為為實(shí)時(shí)響應(yīng)應(yīng)時(shí)間(RRespoonse Timee)，即用用戶在終端端上送入數(shù)數(shù)據(jù)之后，到到計(jì)算機(jī)對(duì)對(duì)這個(gè)請(qǐng)求求給出答復(fù)復(fù)所需要的的時(shí)間。OLAP（聯(lián)聯(lián)機(jī)分析處處理系統(tǒng)）聯(lián)機(jī)分析處處理 (OOLAP) 的概念念最早是由由關(guān)系數(shù)據(jù)據(jù)庫(kù)之父EE.F.CCodd于于19933年提出的的，他同時(shí)時(shí)提出了關(guān)關(guān)于OLAAP的122條準(zhǔn)則。OLAPP的提出引引起了很大大的反響，OOLAP作作為一

3、類產(chǎn)產(chǎn)品同聯(lián)機(jī)機(jī)事務(wù)處理理 (OLLTP) 明顯區(qū)分分開來。 當(dāng)今的數(shù)據(jù)據(jù)處理大致致可以分成成兩大類：聯(lián)機(jī)事務(wù)務(wù)處理OLLTP（oon-liine ttranssactiion pproceessinng）、聯(lián)聯(lián)機(jī)分析處處理OLAAP（Onn-Linne Annalytticall Proocesssing）。OLTPP是傳統(tǒng)的的關(guān)系型數(shù)數(shù)據(jù)庫(kù)的主主要應(yīng)用，主主要是基本本的、日常常的事務(wù)處處理，例如如銀行交易易。OLAAP是數(shù)據(jù)據(jù)倉(cāng)庫(kù)系統(tǒng)統(tǒng)的主要應(yīng)應(yīng)用，支持持復(fù)雜的分分析操作，側(cè)側(cè)重決策支支持，并且且提供直觀觀易懂的查查詢結(jié)果。 OLTP與與OLAPP之間的比比較:OLTPOLAP用戶操作人員

4、，低低層管理人人員決策人員，高高級(jí)管理人人員功能日常操作處處理分析決策DB 設(shè)計(jì)計(jì)面向應(yīng)用面向主題數(shù)據(jù)最新的，細(xì)細(xì)節(jié)的，二二維的，分分立的歷史的，聚聚集的，多多維的，集集成的存取規(guī)模讀/寫數(shù)條條（甚至數(shù)數(shù)百條）記記錄讀上百萬（甚甚至上億）條條記錄操作頻度非常頻繁（以以秒計(jì)）比較稀松（以以小時(shí)甚至至以周計(jì)）工作單位嚴(yán)格的事務(wù)務(wù)復(fù)雜的查詢?cè)冇脩魯?shù)數(shù)百個(gè)-數(shù)數(shù)千萬個(gè)數(shù)個(gè)-數(shù)百百個(gè)DB 大小小100MBB-GB100GBB-TB1、什么是是OLTPP OLTPP，也叫聯(lián)聯(lián)機(jī)事務(wù)處處理(Onnlinee Traansacctionn Proocesssing)，表示事事務(wù)性非常常高的系統(tǒng)統(tǒng)，一般都都是高

5、可用用的在線系系統(tǒng)，以小小的事務(wù)以以及小的查查詢?yōu)橹鳎u(píng)評(píng)估其系統(tǒng)統(tǒng)的時(shí)候，一一般看其每每秒執(zhí)行的的trannsacttion以以及exeecutee sqll的數(shù)量。在這樣的的系統(tǒng)中，每每秒處理的的trannsacttion往往往超過幾幾百個(gè)，或或者是幾千千個(gè)，seelectt 語(yǔ)句的的執(zhí)行量每每秒幾千甚甚至幾萬個(gè)個(gè)。典型的的OLTPP系統(tǒng)如電電子商務(wù)系系統(tǒng)，銀行行，證卷等等等，如美美國(guó)ebaay的業(yè)務(wù)務(wù)數(shù)據(jù)庫(kù)，就就是很典型型的OLTTP數(shù)據(jù)庫(kù)庫(kù)。 OOLTP系系統(tǒng)最容易易出現(xiàn)的瓶瓶頸就是CCPU與磁磁盤子系統(tǒng)統(tǒng)。cpuu則取決于于邏輯讀以以及內(nèi)部調(diào)調(diào)用，如函函數(shù)等等。一個(gè)執(zhí)行行頻繁的SSQ

6、L語(yǔ)句句，如果每每個(gè)語(yǔ)句可可以減少很很少的邏輯輯讀，也相相當(dāng)于優(yōu)化化了一些邏邏輯讀很差差的大型語(yǔ)語(yǔ)句。很多多人不感覺覺不到這里里的作用，覺覺得一個(gè)語(yǔ)語(yǔ)句幾十個(gè)個(gè)邏輯讀，執(zhí)執(zhí)行時(shí)間基基本為0，就就不需要優(yōu)優(yōu)化了，其其實(shí)，只要要他的執(zhí)行行次數(shù)非常常頻繁，而而且有優(yōu)化化的余地，就就一定要優(yōu)優(yōu)化，如減減少一定的的邏輯讀或或者降低執(zhí)執(zhí)行次數(shù)，都都是優(yōu)化方方法。 另另外，一些些計(jì)算性的的函數(shù)，如如sum，ccountt，deccode被被非常頻繁繁的使用，也也是非常消消耗cpuu的，我遇遇到一個(gè)系系統(tǒng)，因?yàn)闉橐粋€(gè)sqql語(yǔ)句，大大量的使用用了summ與deccode進(jìn)進(jìn)行行列轉(zhuǎn)轉(zhuǎn)換，結(jié)果果這一個(gè)語(yǔ)語(yǔ)句就耗

7、費(fèi)費(fèi)了整個(gè)機(jī)機(jī)器一半以以上的CPPU。 那那么，在一一般的OLLTP系統(tǒng)統(tǒng)中，如果果不考慮我我上面說的的函數(shù)問題題，那么，邏邏輯讀乘以以執(zhí)行次數(shù)數(shù)，決定了了cpu的的消耗程度度，如一個(gè)個(gè)語(yǔ)句，每每秒執(zhí)行次次數(shù)為5000次，每每個(gè)邏輯讀讀為15，但但是，通過過優(yōu)化，能能讓每個(gè)語(yǔ)語(yǔ)句的邏輯輯讀從155降到100，那么，每每秒的邏輯輯讀就可以以減少5000*5=25000個(gè)，其實(shí)實(shí)就是相當(dāng)當(dāng)于優(yōu)化了了一個(gè)執(zhí)行行頻率為每每秒1次，每每次邏輯讀讀為25000個(gè)的語(yǔ)語(yǔ)句（注意意，25000個(gè)邏輯輯讀，在ooltp系系統(tǒng)是非常常差的語(yǔ)句句）。再如如，假定一一個(gè)1GHHZ的cppu每秒能能正常處理理的邏輯讀讀

8、是1000,0000個(gè)，如果果是10個(gè)個(gè)邏輯讀一一個(gè)的語(yǔ)句句，每秒可可以處理110,0000個(gè)，而而10000個(gè)邏輯讀讀一個(gè)的語(yǔ)語(yǔ)句，每秒秒則只能處處理1000個(gè)。 同同以上道理理，物理讀讀乘以執(zhí)行行次數(shù)，則則決定了存存儲(chǔ)子系統(tǒng)統(tǒng)的處理能能力，在一一個(gè)OLTTP環(huán)境中中，物理讀讀一般都是是db ffile sequuentiial rread決決定的，也也就是單塊塊讀，一個(gè)個(gè)典型的OOLTP系系統(tǒng)，dbb fille seequenntiall reaad應(yīng)當(dāng)基基本等于磁磁盤子系統(tǒng)統(tǒng)的讀的IIOPS。而磁盤子子系統(tǒng)的IIOPS處處理能力，與與cachhe命中率率以及磁盤盤個(gè)數(shù)有很很大的關(guān)系系。

9、我的一一些文章中中，也分析析到了這些些問題，如如一個(gè)155K轉(zhuǎn)速的的磁盤，每每秒最多能能處理的iiops達(dá)達(dá)到1500個(gè)，基本本就是極限限了，如果果cachhe不命中中，那么1100個(gè)磁磁盤，最多多能處理的的IOPSS僅僅是1150000個(gè)（但是是，實(shí)際上上，還基本本達(dá)不到這這個(gè)值）。 OOLTP最最常用的技技術(shù)就是ccachee技術(shù)與bbtreee索引，ccachee決定了很很多語(yǔ)句不不需要從磁磁盤子系統(tǒng)統(tǒng)獲得數(shù)據(jù)據(jù)，所以，wweb ccachee與oraacle dataa bufffer對(duì)對(duì)OLTPP系統(tǒng)是很很重要的。另外，在在索引使用用方面，語(yǔ)語(yǔ)句是越簡(jiǎn)簡(jiǎn)單越好，這這樣執(zhí)行計(jì)計(jì)劃也穩(wěn)定

10、定，而且一一定要使用用綁定變量量，減少語(yǔ)語(yǔ)句解析，盡盡量減少關(guān)關(guān)聯(lián)。其它它方面，基基本不使用用分區(qū)技術(shù)術(shù)，MV技技術(shù)，并行行技術(shù)以及及位圖索引引，因?yàn)椴⒉l(fā)量很高高，批量更更新可能要要盡量快速速提交避免免阻塞的發(fā)發(fā)生。 在ebaay的數(shù)據(jù)據(jù)庫(kù)設(shè)計(jì)中中，有一個(gè)個(gè)很重要的的點(diǎn)就是，數(shù)數(shù)據(jù)庫(kù)只負(fù)負(fù)責(zé)存放數(shù)數(shù)據(jù)，業(yè)務(wù)務(wù)邏輯盡量量在業(yè)務(wù)層層實(shí)現(xiàn)，因因?yàn)閿?shù)據(jù)庫(kù)庫(kù)擴(kuò)展是困困難的，而而應(yīng)用服務(wù)務(wù)器擴(kuò)展是是簡(jiǎn)單的。其實(shí)，也也就是說，在在高可用的的OLTPP環(huán)境中，數(shù)數(shù)據(jù)庫(kù)使用用越簡(jiǎn)單的的功能越好好。2、什么是是OLAPP OOLAP，也也叫聯(lián)機(jī)分分析(Onnlinee Anaalytiical Proccess

11、iing)，有有的時(shí)候也也叫DSSS決策支持持系統(tǒng)，就就是我們說說的數(shù)據(jù)倉(cāng)倉(cāng)庫(kù)。在這這樣的系統(tǒng)統(tǒng)中，語(yǔ)句句的執(zhí)行量量不是考核核標(biāo)準(zhǔn)，因因?yàn)橐粋€(gè)語(yǔ)語(yǔ)句的執(zhí)行行時(shí)間可能能會(huì)非常長(zhǎng)長(zhǎng)，讀取的的數(shù)據(jù)也非非常多。所所以，這樣樣的系統(tǒng)中中，考核的的標(biāo)準(zhǔn)往往往決定于磁磁盤子系統(tǒng)統(tǒng)的吞吐量量。 磁盤子系系統(tǒng)的吞吐吐量則直接接取決于磁磁盤的個(gè)數(shù)數(shù)，這個(gè)時(shí)時(shí)候，caache基基本是沒有有效果的，這這個(gè)時(shí)候數(shù)數(shù)據(jù)庫(kù)的讀讀寫基本上上是db filee scaatterred rread與與direect ppath readd/wriite。在在我前面的的一些文章章中描述過過，如果一一個(gè)15KK的磁盤的的IO量每每秒

12、13MM，那么，1100個(gè)磁磁盤，最多多能提供的的吞吐量則則是13000M/ss（實(shí)際上上，也基本本達(dá)不到這這個(gè)值）。如果磁盤盤個(gè)數(shù)足夠夠的話，還還需要考慮慮采用比較較大的帶寬寬，如4GGB的光纖纖接口。 在在OLAPP系統(tǒng)中，常常使用的技技術(shù)有分區(qū)區(qū)技術(shù)，并并行技術(shù)。如分區(qū)技技術(shù)可以使使得一些大大表的掃描描變得很快快（只掃描描單個(gè)分區(qū)區(qū)），而且且方便管理理。另外，如如果分區(qū)結(jié)結(jié)合并行的的話，也可可以使得整整個(gè)表的掃掃描也會(huì)變變得很快。并行技術(shù)術(shù)除了與分分區(qū)技術(shù)結(jié)結(jié)合外，在在oraccle 110g中，與與rac結(jié)結(jié)合實(shí)現(xiàn)多多節(jié)點(diǎn)的同同時(shí)掃描，效效果也非常常不錯(cuò)，把把一個(gè)任務(wù)務(wù)，如seelec

13、tt的全表掃掃描，平均均的分派到到多個(gè)raac的節(jié)點(diǎn)點(diǎn)上去。 在在OLAPP系統(tǒng)中，不不需要使用用綁定變量量，因?yàn)檎麄€(gè)系統(tǒng)的的執(zhí)行量很很少，分析析時(shí)間對(duì)于于執(zhí)行時(shí)間間來說，可可以忽略，而而且避免出出現(xiàn)錯(cuò)誤的的執(zhí)行計(jì)劃劃。但是OOLAP中中可以大量量使用位圖圖索引，物物化視圖，對(duì)對(duì)于大的事事務(wù)，盡量量的尋求速速度上的優(yōu)優(yōu)化，沒有有必要象OOLTP需需要快速提提交，甚至至要刻意減減慢執(zhí)行的的速度。3、總結(jié) 特特別是在高高可用的OOLTP環(huán)環(huán)境中，不不要盲目的的把OLAAP的技術(shù)術(shù)拿過來用用，如分區(qū)區(qū)技術(shù)，如如果不是大大范圍的使使用了分區(qū)區(qū)關(guān)鍵字作作為wheere條件件，而采用用其它的字字段作為w

14、wheree條件，那那么，如果果是本地索索引，你將將不得不掃掃描多個(gè)索索引，而性性能變的更更為低下。如果是全全局索引，那那分區(qū)的意意義又何在在，只是多多出一份分分區(qū)技術(shù)的的liceense而而已。 并并行技術(shù)也也是如此，一一般是在大大型任務(wù)的的時(shí)候才使使用，好比比說，實(shí)際際生活中，一一個(gè)比較大大型的工作作，如翻譯譯一本書，你你可以先安安排多個(gè)人人，每個(gè)人人翻譯不同同的章節(jié)，這這樣是可以以提高翻譯譯速度，但但是，你現(xiàn)現(xiàn)在只是翻翻譯一頁(yè)，你你也去分配配不同的人人翻譯不同同的行，再再組合起來來，這個(gè)時(shí)時(shí)間，你一一個(gè)人或者者早就翻譯譯完了。 位位圖索引在在我前幾篇篇文章中有有交代，如如果用在ooltp

15、環(huán)環(huán)境中，可可能因?yàn)樽枳枞秶?，很容容易阻塞與與死鎖，但但是，在oolap環(huán)環(huán)境中，可可能會(huì)因?yàn)闉槠涮赜械牡奶匦?，提提高olaap的查詢?cè)兯俣?。mmv也是基基本一樣，包包括觸發(fā)器器等等，在在dml頻頻繁的olltp系統(tǒng)統(tǒng)上，很容容易成為瓶瓶頸，而在在olapp環(huán)境上，則則可能會(huì)因因?yàn)槭褂们∏‘?dāng)而提高高查詢速度度。 HYPERLINK /article/62/1187.html http:/artiicle/62/11187.htmllOraclle索引使用限限制 和 建議什么是索引引索引是建立立在表的一一列或多個(gè)個(gè)列上的輔輔助對(duì)象，目目的是加快快訪問表中中的數(shù)據(jù)； Oraclle存儲(chǔ)索索

16、引的數(shù)據(jù)據(jù)結(jié)構(gòu)是BB*樹，位位圖索引也也是如此，只只不過是葉葉子節(jié)點(diǎn)不不同B*數(shù)數(shù)索引；索索引由根節(jié)節(jié)點(diǎn)、分支支節(jié)點(diǎn)和葉葉子節(jié)點(diǎn)組組成，上級(jí)級(jí)索引塊包包含下級(jí)索索引塊的索索引數(shù)據(jù)，葉葉節(jié)點(diǎn)包含含索引數(shù)據(jù)據(jù)和確定行行實(shí)際位置置的rowwid。一、oraacle的的索引陷阱阱一個(gè)表中有有幾百萬條條數(shù)據(jù)，對(duì)對(duì)某個(gè)字段段加了索引引，但是查查詢時(shí)性能能并沒有什什么提高，這這主要可能能是oraacle的的索引限制制造成的。 oraclle的索引引有一些索索引限制，在在這些索引引限制發(fā)生生的情況下下，即使已已經(jīng)加了索索引，orraclee還是會(huì)執(zhí)執(zhí)行一次全全表掃描，查查詢的性能能不會(huì)比不不加索引有有所提高，

17、反反而可能由由于數(shù)據(jù)庫(kù)庫(kù)維護(hù)索引引的系統(tǒng)開開銷造成性性能更差。下面是一些些常見的索索引限制問問題。1、使用不不等于操作作符(, !=)下面這種情情況，即使使在列deept_iid有一個(gè)個(gè)索引，查查詢語(yǔ)句仍仍然執(zhí)行一一次全表掃掃描 selecct * fromm deppt whhere stafff_nuum 10000; 但是開發(fā)中中的確需要要這樣的查查詢，難道道沒有解決決問題的辦辦法了嗎？ 有！ 通過把用 or 語(yǔ)語(yǔ)法替代不不等號(hào)進(jìn)行行查詢，就就可以使用用索引，以以避免全表表掃描：上上面的語(yǔ)句句改成下面面這樣的，就就可以使用用索引了。 selecct * fromm deppt shher

18、e stafff_nuum 10000; 2、使用 is nnull 或 iss nott nulll使用 iss nulll 或iis nuuo nuull也會(huì)會(huì)限制索引引的使用，因因?yàn)閿?shù)據(jù)庫(kù)庫(kù)并沒有定定義nulll值。如如果被索引引的列中有有很多nuull，就就不會(huì)使用用這個(gè)索引引（除非索索引是一個(gè)個(gè)位圖索引引，關(guān)于位位圖索引，會(huì)會(huì)在以后的的blogg文章里做做詳細(xì)解釋釋）。在ssql語(yǔ)句句中使用nnull會(huì)會(huì)造成很多多麻煩。 解決這個(gè)問問題的辦法法就是：建建表時(shí)把需需要索引的的列定義為為非空(nnot nnull)3、使用函函數(shù)如果沒有使使用基于函函數(shù)的索引引，那么wwheree子句中對(duì)

19、對(duì)存在索引引的列使用用函數(shù)時(shí)，會(huì)會(huì)使優(yōu)化器器忽略掉這這些索引。下面的查查詢就不會(huì)會(huì)使用索引引： selecct * fromm staaff wwheree truunc(bbirthhdatee) = 01-MAY-82; 但是把函數(shù)數(shù)應(yīng)用在條條件上，索索引是可以以生效的，把把上面的語(yǔ)語(yǔ)句改成下下面的語(yǔ)句句，就可以以通過索引引進(jìn)行查找找。 selecct * fromm staaff wwheree birrthdaate showw parrametter ooptimmizerr_inddex_ccost_adjNAME TTYPEVVALUEE optimmizerr_inddex_c

20、cost_adj inntegeer 1000為了多使用用索引，可可減小它的的值SQL alteer syystemm sett opttimizzer_iindexx_cosst_addj=100;進(jìn)一步設(shè)設(shè)置為了進(jìn)一步步優(yōu)化，ooraclle建議對(duì)對(duì)小表不要要建索引，那那什么為小小表呢，ooraclle一次II/O就可可讀寫完整整個(gè)表的表表為小表，查查看oraacle110g一次次讀寫的數(shù)數(shù)據(jù)量SQL showw parrametter ddb_fiile_mmultiiblocck_reead_ccounttNAME TYPPE VALLUE db_fiile_mmultiiblocck

21、_reead_tegerr 1228可見當(dāng)表小小于1288k時(shí)就不不要建索引引了二、各種索索引使用場(chǎng)場(chǎng)合及建議議（1）B*Treee索引。常規(guī)索引，多多用于olltp系統(tǒng)統(tǒng)，快速定定位行，應(yīng)應(yīng)建立于高高carddinallity列列（即列的的唯一值除除以行數(shù)為為一個(gè)很大大的值，存存在很少的的相同值）。Creaate iindexx inddexnaame oon taablenname(coluumnnaameccolummnnamme)（2）反向向索引。B*Treee的衍生生產(chǎn)物，應(yīng)應(yīng)用于特殊殊場(chǎng)合，在在ops環(huán)環(huán)境加序列列增加的列列上建立，不不適合做區(qū)區(qū)域掃描。Creaate iindex

22、x inddexnaame oon taablenname(coluumnnaameccolummnnamme) rreverrse（3）降序序索引。B*Treee的衍生生產(chǎn)物，應(yīng)應(yīng)用于有降降序排列的的搜索語(yǔ)句句中，索引引中儲(chǔ)存了了降序排列列的索引碼碼，提供了了快速的降降序搜索。Creaate iindexx inddexnaame oon taablenname(coluumnnaame DDESCcoluumnnaame)（4）位圖圖索引。位圖方式管管理的索引引，適用于于OLAPP（在線分分析）和DDSS（決決策處理）系系統(tǒng)，應(yīng)建建立于低ccardiinaliity列，適合集中讀讀取，不適

23、適合插入和和修改，提提供比B*Treee索引更節(jié)節(jié)省的空間間。Creaate BBITMAAP inndex indeexnamme onn tabblenaame(ccolummnnammecoolumnnnamee.)在實(shí)際應(yīng)用用中，如果果某個(gè)字段段的值需要要頻繁更新新，那么就就不適合在在它上面創(chuàng)創(chuàng)建位圖索索引。在位圖索引引中，如果果你更新或或插入其中中一條數(shù)值值為N的記記錄，那么相應(yīng)表表中數(shù)值為為N的記錄錄（可能成成百上千條條）全部被被Oraccle鎖定定，這就意味著著其它用戶戶不能同時(shí)時(shí)更新這些些數(shù)值為NN的記錄，其其它用戶必必須要等第第一個(gè)用戶戶提交后，才能獲得鎖鎖，更新或或插入數(shù)據(jù)

24、據(jù)，bittmap indeex它主要要用于決策策支持系統(tǒng)統(tǒng)或靜態(tài)數(shù)數(shù)據(jù)。（5）函數(shù)數(shù)索引。B*Treee的衍生生產(chǎn)物，應(yīng)應(yīng)用于查詢?cè)冋Z(yǔ)句條件件列上包含含函數(shù)的情情況，索引中儲(chǔ)存存了經(jīng)過函函數(shù)計(jì)算的的索引碼值值?？梢栽谠诓恍薷膽?yīng)應(yīng)用程序的的基礎(chǔ)上能能提高查詢?cè)冃省Ｋ饕齽?chuàng)建策策略 1.導(dǎo)入數(shù)數(shù)據(jù)后再創(chuàng)創(chuàng)建索引 2.不需要要為很小的的表創(chuàng)建索索引 3.對(duì)于取取值范圍很很小的字段段（比如性性別字段）應(yīng)應(yīng)當(dāng)建立位位圖索引 4.限制表表中的索引引的數(shù)目 5.為索引引設(shè)置合適適的PCTTFREEE值 6.存儲(chǔ)索索引的表空空間最好單單獨(dú)設(shè)定 唯一索引和和不唯一索索引都只是是針對(duì)B樹樹索引而言言. Orac

25、lle最多允允許包含332個(gè)字段段的復(fù)合索索引 由此估計(jì)出出一個(gè)查詢?cè)內(nèi)绻褂糜媚硞€(gè)索引引會(huì)需要讀讀入的數(shù)據(jù)據(jù)塊塊數(shù)。需要讀入的的數(shù)據(jù)塊越越多，則 costt 越大，OOraclle 也就就越有可能能不選擇使使用 inndex三、能用唯唯一索引，一一定用唯一一索引 能加非空，就就加非空約約束 一定要統(tǒng)計(jì)計(jì)表的信息息，索引的的信息，柱柱狀圖的信信息。 聯(lián)合索引的的順序不同同，影響索索引的選擇擇，盡量將將值少的放放在前面 只有做到以以上四點(diǎn)，數(shù)數(shù)據(jù)庫(kù)才會(huì)會(huì)正確的選選擇執(zhí)行計(jì)計(jì)劃。ORACLLE索引介介紹與高性性能SQLL優(yōu)化什么是索引引索引是建立立在表的一一列或多個(gè)個(gè)列上的輔輔助對(duì)象，目目的是加快

26、快訪問表中中的數(shù)據(jù)；Oraccle存儲(chǔ)儲(chǔ)索引的數(shù)數(shù)據(jù)結(jié)構(gòu)是是B*樹，位位圖索引也也是如此，只只不過是葉葉子節(jié)點(diǎn)不不同B*數(shù)數(shù)索引；索索引由根節(jié)節(jié)點(diǎn)、分支支節(jié)點(diǎn)和葉葉子節(jié)點(diǎn)組組成，上級(jí)級(jí)索引塊包包含下級(jí)索索引塊的索索引數(shù)據(jù)，葉葉節(jié)點(diǎn)包含含索引數(shù)據(jù)據(jù)和確定行行實(shí)際位置置的rowwid。使用索引的的目的加快查詢速速度減少I/OO操作消除磁盤排排序何時(shí)使用索索引查詢返回的的記錄數(shù)排序表440%非排序表400%，排序序表7%，建議采采用并行機(jī)機(jī)制來提高高訪問速度度，DDSS；索引訪問。最常用的的方法，包包括索引唯唯一掃描和和索引范圍圍掃描，OOLTP；快速完全索索引掃描。訪問索引引中所有數(shù)數(shù)據(jù)塊，結(jié)結(jié)果

27、相當(dāng)于于全表掃描描，可以用用索引掃描描代替全表表掃描，例例如：Selecctseerv_iid,coount(*)frommtg_cdr001grroupbysserv_id;評(píng)估全表掃掃描的合法法性如何實(shí)現(xiàn)并并行掃描。永久并行行化（不推推薦）alterrtabbleccustoomerparaallelldeggree8;。單個(gè)查詢?cè)儾⑿谢痵elecct/*+fuull(eemp)paraallell(empp,8)*/*frommempp;分區(qū)表效果果明顯優(yōu)化SQLL語(yǔ)句排序序排序的操作作：。ordeerbyy子句。grouupbyy子句。seleectddistiinct子子句。創(chuàng)建索引

28、引時(shí)。unioon或miinus。排序合并并連接如何避免排排序。添加索引引。在索引中中使用diistinnct子句句。避免排序序合并連接接使用提示進(jìn)進(jìn)行調(diào)整使用提示的的原則。語(yǔ)法：/*+hhint*/。使用表別別名:seelectt/*+inddex(eedeppt_iddx)*/*ffromempe。檢驗(yàn)提示示常用的提示示。rulee。all_rowss。firsst_roows。use_nl。use_hashh。use_mergge。indeex。indeex_assc。no_iindexx。indeex_deesc（常常用于使用用max內(nèi)內(nèi)置函數(shù)）。indeex_coombinne(強(qiáng)制

29、制使用位圖圖索引)。indeex_fffs（索引引快速完全全掃描）。use_conccat(將將查詢中所所有or條條件使用uunionnalll)。paraallell。nopaaralllel。fulll。ordeered（基基于成本）調(diào)整表連接接表連接的類類型。等連接wheree條件中中用等式連連接；。外部連接接（左、右右連接）在wherre條件子子句的等式式謂詞放置置一個(gè)(+)來實(shí)現(xiàn)現(xiàn)，例如：seleccta.enamme,mmfroomemmpa,bonuusbwherrea.enamme=b.enamme(+);該語(yǔ)句返回回所有emmp表的記記錄；。自連接Seleccta.valuu

30、etootal,B.vvalueeharrd,(A.vaalue-b.valuue)ssoft,Roundd(b.valuue/a.valuue)*1100,11)peercFromv$syysstaata,v$syysstaatbWhereea.sstatiisticc#=179andBB.staatisttic#=1880;反連接反連接常用用于nottinornnoteexistts中，是是指在查詢?cè)冎姓业降牡娜魏斡涗涗浂疾话诮Y(jié)果集集中的子查查詢；不建建議使用nnotiinorrnottexiists;。半連接查詢中使用用exissts，含含義：即使使在子查詢?cè)冎蟹祷囟喽鄺l重復(fù)的的記

31、錄，外外部查詢也也只返回一一條記錄。嵌套循環(huán)連連接。被連接表表中存在索索引的情況況下使用；。使用usse_nll。hash連連接。Hashh連接將驅(qū)驅(qū)動(dòng)表加載載在內(nèi)存中中，并使用用hashh技術(shù)連接接第二個(gè)表表，提高等等連接速度度。適合于大大表和小表表連接；。使用usse_haash。排序合并連連接。排序合并并連接不使使用索引。使用原則則：連接表子段段中不存在在可用索引引；查詢返回兩兩個(gè)表中大大部分的數(shù)數(shù)據(jù)快；CBO認(rèn)為為全表掃描描比索引掃掃描執(zhí)行的的更快。使用usse_meerge使用臨時(shí)/中間表多個(gè)大表關(guān)關(guān)聯(lián)時(shí)，可可以分別把把滿足條件件的結(jié)果集集存放到中中間表，然然后用中間間表關(guān)聯(lián)；SQL

32、子查查詢的調(diào)整整關(guān)聯(lián)與非關(guān)關(guān)聯(lián)子查詢?cè)?。關(guān)聯(lián)：子子查詢的內(nèi)內(nèi)部引用的的是外部表表，每行執(zhí)執(zhí)行一次；。非關(guān)聯(lián)：子查詢只只執(zhí)行一次次，存放在在內(nèi)存中。調(diào)整nottin和nottexiists語(yǔ)語(yǔ)句?？梢允褂糜猛獠窟B接接優(yōu)化nootinn子句，例例如：selecctennamefrommemppwheereddept_nonnotiin(seleectddept_noffromdepttwheereddept_namee=MMath);改為：selecctennamefrommempp,depptwhereeempp.deppt_noo=deppt.deept_nnoandddept.deptt_n

33、ammeissnulll;使用索引調(diào)調(diào)整SQLLOraclle為什什么不使用用索引。檢查被索索引的列或或組合索引引的首列是是否出現(xiàn)在在PL/SSQL語(yǔ)句句的WHEERE子句句中，這是是“執(zhí)行計(jì)計(jì)劃”能用用到相關(guān)索索引的必要要條件?？床捎昧肆四姆N類型型的連接方方式。ORRACLEE的共有SSortMerggeJooin（SSMJ）、HashhJoiin（HJJ）和NeesteddLooopJooin（NNL）。在在兩張表連連接，且內(nèi)內(nèi)表的目標(biāo)標(biāo)列上建有有索引時(shí)，只只有NesstedLoopp才能有效效地利用到到該索引。SMJ即即使相關(guān)列列上建有索索引，最多多只能因索索引的存在在，避免數(shù)數(shù)據(jù)排序過

34、過程。HJJ由于須做做HASHH運(yùn)算，索索引的存在在對(duì)數(shù)據(jù)查查詢速度幾幾乎沒有影影響。看連接順順序是否允允許使用相相關(guān)索引。假設(shè)表eemp的ddeptnno列上有有索引，表表deptt的列deeptnoo上無索引引，WHEERE語(yǔ)句句有empp.depptno=deptt.depptno條條件。在做做NL連接接時(shí)，emmp做為外外表，先被被訪問，由由于連接機(jī)機(jī)制原因，外外表的數(shù)據(jù)據(jù)訪問方式式是全表掃掃描，emmp.deeptnoo上的索引引顯然是用用不上，最最多在其上上做索引全全掃描或索索引快速全全掃描。是否用到到系統(tǒng)數(shù)據(jù)據(jù)字典表或或視圖。由由于系統(tǒng)數(shù)數(shù)據(jù)字典表表都未被分分析過，可可能導(dǎo)致極極

35、差的“執(zhí)執(zhí)行計(jì)劃”。但是不不要擅自對(duì)對(duì)數(shù)據(jù)字典典表做分析析，否則可可能導(dǎo)致死死鎖，或系系統(tǒng)性能下下降。索引列是是否函數(shù)的的參數(shù)。如如是，索引引在查詢時(shí)時(shí)用不上。是否存在在潛在的數(shù)數(shù)據(jù)類型轉(zhuǎn)轉(zhuǎn)換。如將將字符型數(shù)數(shù)據(jù)與數(shù)值值型數(shù)據(jù)比比較，ORRACLEE會(huì)自動(dòng)將將字符型用用to_nnumbeer()函函數(shù)進(jìn)行轉(zhuǎn)轉(zhuǎn)換，從而而導(dǎo)致上一一種現(xiàn)象的的發(fā)生。是否為表表和相關(guān)的的索引搜集集足夠的統(tǒng)統(tǒng)計(jì)數(shù)據(jù)。對(duì)數(shù)據(jù)經(jīng)經(jīng)常有增、刪、改的的表最好定定期對(duì)表和和索引進(jìn)行行分析，可可用SQLL語(yǔ)句“aanalyyzettableexxxxxcoomputtesttatissticssforralllinddexess;

36、”。OORACLLE掌握了了充分反映映實(shí)際的統(tǒng)統(tǒng)計(jì)數(shù)據(jù)，才才有可能做做出正確的的選擇。索引列的的選擇性不不高。我我們假設(shè)典典型情況，有有表empp，共有一一百萬行數(shù)數(shù)據(jù)，但其其中的emmp.deeptnoo列，數(shù)據(jù)據(jù)只有4種種不同的值值，如100、20、30、440。雖然然emp數(shù)數(shù)據(jù)行有很很多，ORRACLEE缺省認(rèn)定定表中列的的值是在所所有數(shù)據(jù)行行均勻分布布的，也就就是說每種種depttno值各各有25萬萬數(shù)據(jù)行與與之對(duì)應(yīng)。假設(shè)SQQL搜索條條件DEPPTNO=10，利利用depptno列列上的索引引進(jìn)行數(shù)據(jù)據(jù)搜索效率率，往往不不比全表掃掃描的高。索引列值值是否可為為空（NUULL）。如果

37、索引引列值可以以是空值，在在SQL語(yǔ)語(yǔ)句中那些些要返回NNULL值值的操作，將將不會(huì)用到到索引，如如COUNNT（*），而而是用全表表掃描。這這是因?yàn)樗魉饕写鎯?chǔ)儲(chǔ)值不能為為全空?？词欠裼杏杏玫讲⑿行胁樵儯≒PQO）。并行查詢?cè)儗⒉粫?huì)用用到索引。如果從以以上幾個(gè)方方面都查不不出原因的的話，我們們只好用采采用在語(yǔ)句句中加hiint的方方式強(qiáng)制OORACLLE使用最最優(yōu)的“執(zhí)執(zhí)行計(jì)劃”。hhint采采用注釋的的方式，有有行注釋和和段注釋兩兩種方式。如我我們想要用用到A表的的IND_COL11索引的話話，可采用用以下方式式：“SELEECT/*+IINDEXX（AIIND_CCOL1）*/*FROM

38、MAWWHEREECOLL1=XXX;如何屏蔽索索引語(yǔ)句的執(zhí)行行計(jì)劃中有有不良索引引時(shí)，可以以人為地屏屏蔽該索引引，方法：。數(shù)值型：在索引字字段上加00，例如selecct*frommemppwheereeemp_nno+0=v_emp_no;。字符型：在索引字字段上加，例如如selecct*frommtg_cdr001whheremsissdn|=vv_msiisdn; HYPERLINK /post/19858/207902 SQLPLLUS中的的set小小節(jié)在寫腳本的的時(shí)候，往往往需要對(duì)對(duì)輸出的格格式進(jìn)行規(guī)規(guī)范，我們們常常用sset語(yǔ)句句在設(shè)置一一些需要的的格式規(guī)范范。下面，列列舉了幾個(gè)

39、個(gè)常用的sset ，和和他們的應(yīng)應(yīng)用。SQL set echoo on設(shè)置置運(yùn)行命令令是是否顯顯示語(yǔ)句SQL set feeddbackk on設(shè)置顯顯示“已選擇XXX行”SQL set colssep | 設(shè)置列與與列之間的的分割符號(hào)號(hào)SQL set pageesizee 10設(shè)置置每一頁(yè)的的行數(shù)SQL SET SERVVEROUUTPUTT ON設(shè)置置允許顯示示輸出類似似dbmss_outtputSQL set headding on設(shè)置置顯示列名名SQL set timiing oon設(shè)設(shè)置顯示“已用時(shí)間間：XXXXX”SQL set timee on設(shè)置顯示示當(dāng)前時(shí)間間SQL set

40、autootracce onn設(shè)設(shè)置允許對(duì)對(duì)執(zhí)行的ssql進(jìn)行行分析00:388:28 SQL sett echho onn00:388:37 SQL E:echoo.sqll00:388:41 SQL sellect stattus ffrom v$daatafiile wwheree rowwnum sett echho offf00:388:58 SQL E:echoo.sqllSTATUUSSYSTEEMONLINNEONLINNEONLINNEONLINNE已選擇5行行。已用時(shí)間: 00: 00: 01.0900:399:08 SQLSQL set feeddbackk onSSQL

41、 set colssep |SQL sett paggesizze 100SQL set servverouutputt onSSQL set headding onSQQL sett timming onSQQL sett timme onn00:554:111 SQLL SELLECT FILEE#, SSTATUUS, EENABLLED FFROM V$DAATAFIILE;FFILE#|STAATUS |ENAABLEDD|1|SYSSTEM |REAAD WRITTE2|ONLLINE |REAAD WRITTE3|ONLLINE |REAAD WRITTE4|ONLLINE |R

42、EAAD WRITTE5|ONLLINE |REAAD WRITTE6|ONLLINE |REAAD WRITTE7|ONLLINE |REAAD WRITTEFILLE#|SSTATUUS |EENABLLED|99|ONLLINE |REAAD WRITTE10|ONLIINE |READD WRITTE11|ONLIINE |READD WRITTE12|ONLIINE |READD WRITTE11 rowss sellecteed.Ellapseed: 000:000:01.07000:54:34 SQL DECLLARE00:555:00 2 BEEGIN00:555:00 3 D

43、BBMS_OOUTPUUT.PUUT_LIINE(=TThis is ddbms_outpput.pput_lline!=);00:555:00 4 ENND;00:555:02 5 /=Thhis iis dbbms_ooutpuut.puut_liine!=PLL/SQLL proccedurre suuccesssfullly ccomplletedd.Elaapsedd: 000:00:00.00200:55:005 SQQLSQL set autootracce onnSQL SELEECT EE.LASST_NAAME, D.DEEPARTTMENTT_NAMME, DD.LOCC

44、ATIOON_IDD2 FROOM HRR.EMPPLOYEEES EE3 JOIIN HRR.DEPPARTMMENTSS D4 USINNG (DDEPARRTMENNT_IDD);LAAST_NNAME DEPAARTMEENT_NNAME LOCAATIONN_ID KKing Execcutivve 17700Koochhaar Exxecuttive 17000De HHaan Execcutivve 17700Huunoldd IT 14000Ernsst ITT 140001066 rowws seelectted.EExecuutionn Plaan00 SELLECT ST

45、ATTEMENNT Opptimiizer=CHOOOSE (Costt=5 CCard=106 Bytees=31180)1 00 HASSH JOINN (Cosst=5 Cardd=1066 Byttes=33180)2 1 TABLLE ACCEESS (FULLL) OF DDEPARRTMENNTS (Cosst=2 Cardd=27 Bytees=5113)3 1 TAABLE ACCEESS (FULLL) OF EEMPLOOYEESS (CCost=2 Caard=1107 BBytess=11777)Sttatissticss7 recuursivve caalls00

46、db blocck geets233 connsisttent getss12 pphysiical readds0 rredo sizee28011 byttes ssent via SQL*NNet tto cllientt580 bytees reeceivved vvia SSQL*NNet ffrom clieent9 SQL*NNet rrounddtripps too/fromm cliient00 sorrts (memoory)00 sorrts (diskk)106 rowss proocesssedSQQLStartt witth connnectt by prioor在

47、SELEECT命令令中使用CCONNEECT BBY和藹SSTARTT WITTH子句可可以查詢表表中的樹型型結(jié)構(gòu)關(guān)系系。其命令令格式如下下：SELECCT。CONNEECT BBY PPRIORR列名1=列名2|列名1=PRIOOR裂名22STARRT WIITH；其中：COONNECCT BYY子句說明明每行數(shù)據(jù)據(jù)將是按層層次順序檢檢索，并規(guī)規(guī)定將表中中的數(shù)據(jù)連連入樹型結(jié)結(jié)構(gòu)的關(guān)系系中。PRRIORYY運(yùn)算符必必須放置在在連接關(guān)系系的兩列中中某一個(gè)的的前面。對(duì)對(duì)于節(jié)點(diǎn)間間的父子關(guān)關(guān)系，PRRIOR運(yùn)運(yùn)算符在一一側(cè)表示父父節(jié)點(diǎn)，在在另一側(cè)表表示子節(jié)點(diǎn)點(diǎn)，從而確確定查找樹樹結(jié)構(gòu)是的的順序是自自

48、頂向下還還是自底向向上。在連連接關(guān)系中中，除了可可以使用列列名外，還還允許使用用列表達(dá)式式。STAART WWITH子子句為可選選項(xiàng)，用來來標(biāo)識(shí)哪個(gè)個(gè)節(jié)點(diǎn)作為為查找樹型型結(jié)構(gòu)的根根節(jié)點(diǎn)。若若該子句被被省略，則則表示所有有滿足查詢?cè)儣l件的行行作為根節(jié)節(jié)點(diǎn)。Startt witthConnnect By子句句遞歸查詢?cè)円话阌糜谟谝粋€(gè)表維維護(hù)樹形結(jié)結(jié)構(gòu)的應(yīng)用用。創(chuàng)建示例表表：CREATTE TAABLE TBL_TESTT(ID NUMMBER,NAME VARCCHAR22(1000 BYTTE),PID NUMMBER DEFFAULTT 0);插入測(cè)試數(shù)數(shù)據(jù)：INSERRT INNTO TTB

49、L_TTEST(ID,NNAME,PID) VALLUES(1,10,0);INSERRT INNTO TTBL_TTEST(ID,NNAME,PID) VALLUES(2,11,1);INSERRT INNTO TTBL_TTEST(ID,NNAME,PID) VALLUES(3,20,0);INSERRT INNTO TTBL_TTEST(ID,NNAME,PID) VALLUES(4,12,1);INSERRT INNTO TTBL_TTEST(ID,NNAME,PID) VALLUES(5,1211,22);從Roott往樹末梢梢遞歸selecct * fromm TBLL_TESST

50、startt witth idd=1conneect bby prrior id = piddorderr by pid從末梢往樹樹ROOTT遞歸selecct * fromm TBLL_TESSTstartt witth idd=5conneect bby prrior pid = iddorderr by id*CREATTE TAABLE xxutts_teest_sstartt(ID varrcharr2(1000),NAME VARCCHAR22(1000);selecct idd,namme,leevel fromm xxuuts_ttest_starrtstartt witth

51、idd=Aconneect bby prrior namee=idorderr by idid naame levvelA BB 1A C 1A D 1B E 2B F 2C H 2E G 3* SELEECT ttranssactiion_iid, pprimaary_qquanttity, pprimaary_uunit_of_mmeasuure, ssourcce_dooc_unnit_oof_meeasurre, ttranssactiion_ttype, pparennt_trransaactioon_idd FRROM rrcv_ttranssactiions WHEERE iinvoiice_sstatuus_coode IINVOIICED addded trx datee rannge aas peer gaap2288 - rsk 07/112/077/*AND TRUNNC (ttranssactiion_ddate)BBETWEEEN TTRUNCC (NVVL (tto_daate(2008806011,YYYYYMMMDD